Database Configuration

Configure and manage MariaDB database for Trexzactyl panel.

Installation

The installer automatically sets up MariaDB with optimal configuration.

Automatic Setup

bash <(curl -s http://installer.trexz.xyz/install.sh)

Select 3. Install Database Host from the menu.

The installer will:

1. Install MariaDB 10.11
2. Secure the installation
3. Create database and user
4. Configure optimal settings
5. Set up remote access (if needed)

Manual Installation

If you need to install MariaDB manually:

Install MariaDB

sudo apt update
sudo apt install -y mariadb-server mariadb-client

Secure Installation

sudo mysql_secure_installation

Follow the prompts:

• Set root password
• Remove anonymous users
• Disallow root login remotely
• Remove test database
• Reload privilege tables

Create Database

mysql -u root -p

CREATE DATABASE panel;
CREATE USER 'pterodactyl'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON panel.* TO 'pterodactyl'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Configuration

Panel Database Settings

Edit /var/www/trexzactyl/.env:

DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=panel
DB_USERNAME=pterodactyl
DB_PASSWORD=your_secure_password

MariaDB Configuration

Optimal settings in /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
# Connection Settings
max_connections = 200
connect_timeout = 10
wait_timeout = 600
max_allowed_packet = 64M

# Buffer Settings
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Performance
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Restart MariaDB after changes:

sudo systemctl restart mariadb

Remote Database Access

To allow remote connections:

Configure MariaDB

Edit /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
bind-address = 0.0.0.0

Create Remote User

CREATE USER 'pterodactyl'@'%' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON panel.* TO 'pterodactyl'@'%';
FLUSH PRIVILEGES;

Firewall Configuration

sudo ufw allow 3306/tcp

Security Note

Only allow specific IPs:

sudo ufw allow from 192.168.1.100 to any port 3306

Database Hosts

For Wings nodes to create game server databases:

Add Database Host in Panel

1. Navigate to Admin Panel → Database Hosts
2. Click "Create New"
3. Enter details:
   • Name: Main Database
   • Host: 127.0.0.1 (or remote IP)
   • Port: 3306
   • Username: pterodactyl
   • Password: your_secure_password

Create Database User for Wings

CREATE USER 'pterodactyluser'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'pterodactyluser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Backup and Restore

Backup Database

# Full backup
mysqldump -u root -p panel > panel-backup-$(date +%Y%m%d).sql

# Compressed backup
mysqldump -u root -p panel | gzip > panel-backup-$(date +%Y%m%d).sql.gz

Automated Backups

Create backup script /usr/local/bin/backup-panel-db.sh:

#!/bin/bash
BACKUP_DIR="/var/backups/panel"
DATE=$(date +%Y%m%d-%H%M%S)
mkdir -p $BACKUP_DIR

mysqldump -u root -p'your_password' panel | gzip > $BACKUP_DIR/panel-$DATE.sql.gz

# Keep only last 7 days
find $BACKUP_DIR -name "panel-*.sql.gz" -mtime +7 -delete

Make executable:

chmod +x /usr/local/bin/backup-panel-db.sh

Add to crontab:

0 2 * * * /usr/local/bin/backup-panel-db.sh

Restore Database

# From uncompressed backup
mysql -u root -p panel < panel-backup.sql

# From compressed backup
gunzip < panel-backup.sql.gz | mysql -u root -p panel

Maintenance

Optimize Tables

USE panel;
OPTIMIZE TABLE users, servers, allocations;

Check Table Status

SHOW TABLE STATUS FROM panel;

Repair Tables

REPAIR TABLE table_name;

Analyze Tables

ANALYZE TABLE users, servers, allocations;

Monitoring

Check Database Size

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'panel'
GROUP BY table_schema;

Active Connections

SHOW PROCESSLIST;

Database Status

sudo systemctl status mariadb

Performance Metrics

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';

Troubleshooting

Connection Refused

Check if MariaDB is running:

sudo systemctl status mariadb

Start if stopped:

sudo systemctl start mariadb

Access Denied

Verify credentials:

mysql -u pterodactyl -p panel

Reset password if needed:

ALTER USER 'pterodactyl'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

Too Many Connections

Increase max_connections:

[mysqld]
max_connections = 500

Slow Queries

Enable slow query log:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

Database Corruption

Check and repair:

sudo mysqlcheck -u root -p --auto-repair --all-databases

Security Best Practices

1. Strong Passwords: Use complex passwords
2. Limited Privileges: Grant only necessary permissions
3. Regular Backups: Automate daily backups
4. Firewall Rules: Restrict database access
5. Update Regularly: Keep MariaDB updated
6. Monitor Logs: Check for suspicious activity
7. SSL/TLS: Use encrypted connections for remote access

Performance Tuning

For Small Servers (2GB RAM)

innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
max_connections = 100

For Medium Servers (4GB RAM)

innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
max_connections = 200

For Large Servers (8GB+ RAM)

innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
max_connections = 500

Useful Commands

# Check MariaDB version
mysql --version

# Login as root
mysql -u root -p

# Show databases
mysql -u root -p -e "SHOW DATABASES;"

# Show users
mysql -u root -p -e "SELECT User, Host FROM mysql.user;"

# Check configuration
mysqld --help --verbose | grep -A 1 'Default options'