Database Host Management

​

What are Database Hosts?

protected $fillable = [
    'name', 'host', 'port', 'username', 'password', 'max_databases', 'node_id',
];

​

Database Host Properties

• Name: Friendly identifier
• Host: Hostname or IP address of the MySQL server
• Port: MySQL port (usually 3306)
• Username: MySQL user with database creation privileges
• Password: Password for the MySQL user
• Max Databases: Maximum databases this host can create (optional limit)
• Node: Optional node assignment (restricts to specific node)

​

Creating a Database Host

​

Validation Rules

public static array $validationRules = [
    'name' => 'required|string|max:191',
    'host' => 'required|string|regex:/^[\w\-\.]+$/',
    'port' => 'required|numeric|between:1,65535',
    'username' => 'required|string|max:32',
    'password' => 'nullable|string',
    'node_id' => 'sometimes|nullable|integer|exists:nodes,id',
];

​

Connection Testing

public function create(DatabaseHostFormRequest $request): RedirectResponse
{
    try {
        $host = $this->creationService->handle($request->normalize());
    } catch (\Exception $exception) {
        if ($exception instanceof \PDOException || $exception->getPrevious() instanceof \PDOException) {
            $this->alert->danger(
                sprintf('There was an error while trying to connect to the host or while executing a query: "%s"', $exception->getMessage())
            )->flash();

            return redirect()->route('admin.databases')->withInput($request->validated());
        } else {
            throw $exception;
        }
    }
    // ...
}

​

MySQL User Requirements

GRANT SELECT, CREATE, ALTER, INDEX, DROP, INSERT, UPDATE, DELETE, 
      CREATE VIEW, REFERENCES, CREATE ROUTINE, ALTER ROUTINE, 
      EXECUTE, EVENT, TRIGGER 
      ON `s%`.* TO 'pterodactyl'@'%' WITH GRANT OPTION;

​

Creating a MySQL User

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

-- Grant privileges for Pterodactyl-managed databases
GRANT SELECT, CREATE, ALTER, INDEX, DROP, INSERT, UPDATE, DELETE, 
      CREATE VIEW, REFERENCES, CREATE ROUTINE, ALTER ROUTINE, 
      EXECUTE, EVENT, TRIGGER 
      ON `s%_*`.* TO 'pterodactyl'@'%' WITH GRANT OPTION;

-- Apply changes
FLUSH PRIVILEGES;

​

Database Naming Convention

s{server_id}_{database_name}

• Server ID: 42
• Database name: “playerdata”
• Result: s42_playerdata

​

Node Assignment

​

Linked to Node

• Only servers on that node can use this database host
• Useful for local MySQL servers on each node
• Better performance (reduced latency)

​

Not Linked (Global)

• Any server can use this database host
• Useful for centralized database servers
• Easier to manage, fewer database hosts needed

​

Viewing Database Hosts

• Host name and connection details
• Number of databases created
• Maximum database limit (if set)
• Associated node (if linked)

​

Managing a Database Host

​

Viewing Details

• All databases created on this host
• Associated servers
• Connection information
• Current database count vs. limit

​

Updating a Host

public function update(DatabaseHostFormRequest $request, DatabaseHost $host): RedirectResponse
{
    $redirect = redirect()->route('admin.databases.view', $host->id);

    try {
        $this->updateService->handle($host->id, $request->normalize());
        $this->alert->success('Database host was updated successfully.')->flash();
    } catch (\Exception $exception) {
        if ($exception instanceof \PDOException || $exception->getPrevious() instanceof \PDOException) {
            $this->alert->danger(
                sprintf('There was an error while trying to connect to the host or while executing a query: "%s"', $exception->getMessage())
            )->flash();

            return $redirect->withInput($request->normalize());
        } else {
            throw $exception;
        }
    }

    return $redirect;
}

​

Deleting a Host

​

Database Relationships

​

Node Relationship

public function node(): BelongsTo
{
    return $this->belongsTo(Node::class);
}

​

Databases Relationship

public function databases(): HasMany
{
    return $this->hasMany(Database::class);
}

​

Server Database Creation

1. They choose a database name
2. System selects an available database host
3. Database is created as s{server_id}_{name}
4. Credentials are generated and displayed once
5. Database is accessible from the server container

​

Remote Access Configuration

​

Edit MySQL Configuration

# /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf
[mysqld]
bind-address = 0.0.0.0

​

Restart MySQL

systemctl restart mysql
# or
systemctl restart mariadb

​

Configure Firewall

# UFW
ufw allow 3306/tcp

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

​

Best Practices

1. Use Strong Passwords: Generate strong passwords for MySQL users
2. Limit Access: Use node_id to restrict hosts to specific nodes
3. Set Max Databases: Prevent resource exhaustion with limits
4. Regular Backups: Back up databases regularly
5. Monitor Usage: Check database count vs. limits
6. Secure Connections: Use SSL for MySQL connections if possible
7. Local is Better: Use local MySQL on each node for better performance

​

Local vs. Remote Database Hosts

​

Local Database Host (Same Server as Node)

• Lower latency
• Better performance
• No network dependency
• More secure (no remote connections)

• Need MySQL on each node
• More hosts to manage
• Resources used on game server nodes

​

Remote Database Host (Dedicated Server)

• Centralized management
• Dedicated resources
• Easier backups
• Single point of configuration

• Network latency
• Single point of failure
• Network bandwidth usage
• Security considerations

​

Troubleshooting

​

Connection Failed

1. Verify MySQL is running
2. Check hostname/IP is correct
3. Ensure MySQL accepts remote connections (bind-address)
4. Verify firewall allows port 3306
5. Test connection manually:
   mysql -h HOST -P 3306 -u USERNAME -p
   

​

Permission Denied

1. Grant proper privileges (see MySQL User Requirements)
2. Ensure user can connect from Panel’s IP:
   GRANT ... ON `s%_*`.* TO 'pterodactyl'@'PANEL_IP' WITH GRANT OPTION;
   
3. Flush privileges:
   FLUSH PRIVILEGES;
   

​

Max Databases Reached

1. Increase max_databases limit
2. Delete unused databases
3. Add another database host

​

Slow Database Performance

1. Use local database hosts on each node
2. Optimize MySQL configuration
3. Add more RAM to database server
4. Enable query caching
5. Use faster storage (SSD)

​

Security Considerations

​

Network Security

1. Restrict Access: Only allow connections from node IPs
2. Use Firewalls: Block port 3306 from untrusted sources
3. Consider SSL: Use encrypted MySQL connections
4. Private Network: Use internal network for node-to-database communication

​

User Privileges

1. Minimal Privileges: Only grant required permissions
2. Database Prefix: Always use s%_* pattern, never *.*
3. No SUPER: Don’t grant SUPER or PROCESS privileges
4. Regular Audits: Review user privileges periodically

​

Next Steps