Database Host Management

What are Database Hosts?

Database hosts are MySQL or MariaDB servers that provide databases for game servers. When users create a database for their server, XyraPanel provisions it on one of your configured database hosts. Use cases:

Store player data (Minecraft plugins, game stats)
Persistent configuration (web-based admin panels)
Cross-server data sharing
Leaderboards and economies

Prerequisites

Before adding a database host:

MySQL/MariaDB Server
- Running MySQL 5.7+ or MariaDB 10.3+
- Accessible from the panel and Wings nodes
- Configured for remote connections
Administrative User
- User with CREATE, DROP, ALTER privileges
- Ability to create new databases and users
- Remote connection permissions

Adding a Database Host

Navigate to database hosts

Go to Admin > Database Hosts.

Add new host

Click Add Database Host and configure:Name (required)

Friendly identifier
Example: “Main MySQL Server”, “EU Database 1”

Hostname (required)

IP address or domain name
Example: mysql.example.com, 192.0.2.10

Port (required)

MySQL port, typically 3306

Username (required)

MySQL user with admin privileges
Example: pterodactyl, dbadmin

Password (required)

Password for the MySQL user
Stored encrypted in the panel database

Default Database (optional)

Database to connect to for operations
Typically leave blank or use mysql

Max Databases (optional)

Limit on databases this host can create
Leave blank or 0 for unlimited

Test connection

After saving, click Test Connection to verify:

Hostname and port are reachable
Credentials are correct
User has required privileges

Configuring MySQL Server

Remote Access

Allow connections from the panel and nodes:

-- Create admin user for panel
CREATE USER 'pterodactyl'@'%' IDENTIFIED BY 'strong_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON *.* TO 'pterodactyl'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Using 'pterodactyl'@'%' allows connections from any IP. For security, restrict to panel and node IPs:

CREATE USER 'pterodactyl'@'192.0.2.0/24' IDENTIFIED BY 'password';

MySQL Configuration

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
# Allow remote connections
bind-address = 0.0.0.0

# Performance tuning
max_connections = 500
max_allowed_packet = 64M
innodb_buffer_pool_size = 2G

Restart MySQL:

systemctl restart mysql

Firewall Rules

Allow MySQL port:

# UFW
ufw allow 3306/tcp

# iptables
iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

Managing Database Hosts

Viewing Databases

The host list shows:

Name: Host identifier
Hostname:Port: Connection endpoint
Username: MySQL user
Databases: Current count / Maximum limit

Testing Connection

Click the test icon to verify: ✅ Connection successful
❌ Connection failed (error details shown) Common errors:

Connection refused: MySQL not running or firewall blocking
Access denied: Incorrect credentials or missing privileges
Unknown host: Hostname DNS resolution failed

Editing Hosts

You can update:

Name (display only)
Hostname/port (use caution, breaks existing databases)
Credentials (if rotated)
Max databases limit

Changing the hostname or port will break all existing database connections. Update with caution.

Deleting Hosts

You cannot delete a database host that has active databases. Remove all databases first.

Check database count

Ensure the host shows 0 databases in the list.

Delete unused host

Click the trash icon and confirm deletion.

Database Creation

When a user creates a database for their server:

Panel selects a host
- Chooses host with available capacity
- Prefers hosts on the same node (if configured)
Creates database
```
CREATE DATABASE s123_mydb;
```

Creates user

CREATE USER 's123_user'@'%' IDENTIFIED BY 'generated_password';

Grants permissions

GRANT ALL PRIVILEGES ON s123_mydb.* TO 's123_user'@'%';

Returns credentials
- Database name: s123_mydb
- Username: s123_user
- Password: (auto-generated)
- Host: mysql.example.com:3306

Database Naming

Databases are prefixed with the server ID:

s{SERVER_ID}_{DATABASE_NAME}

Example:
s123_playerdata
s123_economy
s456_website

This prevents naming conflicts between servers.

Node-Specific Hosts

Assign a database host to a specific node:

{
  "name": "US-East MySQL",
  "hostname": "mysql-us-east.example.com",
  "nodeId": "uuid-of-node"
}

Benefits:

Reduced latency (database close to server)
Geographic data residency
Load distribution

Behavior:

Servers on that node use this host by default
Falls back to general hosts if unavailable

Monitoring

Database Capacity

Track usage per host:

Main MySQL Server
  45 / 100 databases (45%)

EU Database 1
  23 / 50 databases (46%)

Set Max Databases to prevent overloading.

Connection Pooling

MySQL max_connections should accommodate:

Panel connections (1-2 per request)
Game server connections (varies by game)
Admin tools (phpMyAdmin, etc.)

Calculation:

(Average concurrent servers × Connections per server) + 50

Example:
(100 servers × 3 connections) + 50 = 350 connections

Set max_connections = 500 for headroom.

Backup and Maintenance

Automated Backups

Schedule regular MySQL dumps:

#!/bin/bash
# /usr/local/bin/backup-mysql.sh

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

mysqldump --all-databases --single-transaction \
  -u root -p${MYSQL_ROOT_PASSWORD} \
  > ${BACKUP_DIR}/all-databases-${DATE}.sql

# Compress backup
gzip ${BACKUP_DIR}/all-databases-${DATE}.sql

# Retain last 7 days
find ${BACKUP_DIR} -name "all-databases-*.sql.gz" -mtime +7 -delete

Add to cron:

0 2 * * * /usr/local/bin/backup-mysql.sh

Database Maintenance

Optimize tables monthly:

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

Security Best Practices

Network Security

✅ Isolate database server

Use private network between panel/nodes and database
Firewall rules limiting access to specific IPs
Disable public internet access

❌ Avoid

Exposing MySQL to the internet
Using default passwords
Running MySQL on the same server as game servers

User Privileges

✅ Minimal permissions

-- Panel user (can create databases and users)
GRANT CREATE, DROP, ALTER, GRANT OPTION ON *.* 
  TO 'pterodactyl'@'panel-ip';

-- Server database user (limited to their database)
GRANT ALL PRIVILEGES ON s123_mydb.* TO 's123_user'@'%';

❌ Excessive permissions

-- Don't grant SUPER or FILE privileges to panel user
GRANT ALL PRIVILEGES ON *.* TO 'pterodactyl'@'%'; -- TOO PERMISSIVE

Password Management

✅ Strong passwords

Panel-generated passwords are cryptographically random
Store MySQL root password securely
Rotate credentials periodically

❌ Weak passwords

Don’t use simple passwords for admin user
Don’t reuse passwords across environments

Troubleshooting

Cannot connect to database host

Check:

MySQL is running:
```
systemctl status mysql
```
Port is listening:
```
netstat -tlnp | grep 3306
```
Firewall allows connections:
```
ufw status
```
Credentials are correct:
```
mysql -h hostname -u username -p
```

Database creation fails

Common causes:

MySQL user lacks CREATE privilege
Max databases limit reached
Disk space full on MySQL server
Invalid database name characters

Check privileges:

SHOW GRANTS FOR 'pterodactyl'@'%';

Game server can’t connect to database

Verify:

Database exists:
```
SHOW DATABASES LIKE 's%';
```

User can connect:

mysql -h hostname -u s123_user -p s123_mydb

Server has network access:

# From Wings node
telnet mysql.example.com 3306

Performance Tuning

InnoDB Buffer Pool

Set to 70-80% of available RAM:

[mysqld]
# For 8 GB RAM server
innodb_buffer_pool_size = 6G

Query Cache (MySQL 5.7)

[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

Query cache is removed in MySQL 8.0+. Use other optimization techniques.

Connection Limits

[mysqld]
max_connections = 500
wait_timeout = 600
interactive_timeout = 600

Server Databases

How users create and manage databases

Nodes

Configure node-specific database hosts

Overview

Getting Started

Server Management

Administration

Configuration

CLI

Advanced

​What are Database Hosts?

​Prerequisites

​Adding a Database Host

​Configuring MySQL Server

​Remote Access

​MySQL Configuration

​Firewall Rules

​Managing Database Hosts

​Viewing Databases

​Testing Connection

​Editing Hosts

​Deleting Hosts

​Database Creation

​Database Naming

​Node-Specific Hosts

​Monitoring

​Database Capacity

​Connection Pooling

​Backup and Maintenance

​Automated Backups

​Database Maintenance

​Security Best Practices

​Network Security

​User Privileges

​Password Management

​Troubleshooting

​Cannot connect to database host

​Database creation fails

​Game server can’t connect to database

​Performance Tuning

​InnoDB Buffer Pool

​Query Cache (MySQL 5.7)

​Connection Limits

​Related Resources

Server Databases

Nodes

Build docs developers (and LLMs) love

What are Database Hosts?

Prerequisites

Adding a Database Host

Configuring MySQL Server

Remote Access

MySQL Configuration

Firewall Rules

Managing Database Hosts

Viewing Databases

Testing Connection

Editing Hosts

Deleting Hosts

Database Creation

Database Naming

Node-Specific Hosts

Monitoring

Database Capacity

Connection Pooling

Backup and Maintenance

Automated Backups

Database Maintenance

Security Best Practices

Network Security

User Privileges

Password Management

Troubleshooting

Cannot connect to database host

Database creation fails

Game server can’t connect to database

Performance Tuning

InnoDB Buffer Pool

Query Cache (MySQL 5.7)

Connection Limits

Related Resources