FiveM MySQL Database Setup 2025

MySQL database is the backbone of FiveM roleplay servers, storing player data, inventories, vehicles, jobs, and economy information. This guide covers complete MySQL setup, optimization, and framework integration for both ESX and QBCore.

MySQL Installation

Linux Installation (Ubuntu/Debian)

# Update package list
sudo apt update

# Install MySQL Server
sudo apt install mysql-server -y

# Secure installation
sudo mysql_secure_installation

# Enable and start service
sudo systemctl enable mysql
sudo systemctl start mysql

# Verify installation
mysql --version

Windows Installation

1. Download MySQL Installer from mysql.com
2. Run installer and select MySQL Server component
3. Choose Standalone MySQL Server configuration
4. Set authentication method: Use Legacy Authentication Method (for compatibility)
5. Set root password and create Windows service
6. Install MySQL Workbench for management

Database Configuration

Basic Configuration File (my.cnf / my.ini)

# FiveM Optimized MySQL Configuration
[mysqld]
# Connection settings
max_connections = 200
max_user_connections = 100
connect_timeout = 30
wait_timeout = 600
interactive_timeout = 600

# Memory settings
innodb_buffer_pool_size = 2G  # Adjust based on RAM (50-70% of total)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2  # Balance performance vs durability
key_buffer_size = 256M
tmp_table_size = 64M
max_heap_table_size = 64M

# Performance settings
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_instances = 8  # For large buffer pools

# Logging
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON

# Binary logging for backups
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100M

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

User and Permission Setup

# Connect to MySQL as root
mysql -u root -p

# Create database for FiveM
CREATE DATABASE fivem_server CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Create dedicated user
CREATE USER 'fivem_user'@'localhost' IDENTIFIED BY 'strong_password_here';
CREATE USER 'fivem_user'@'%' IDENTIFIED BY 'strong_password_here';  # For remote connections

# Grant permissions
GRANT ALL PRIVILEGES ON fivem_server.* TO 'fivem_user'@'localhost';
GRANT ALL PRIVILEGES ON fivem_server.* TO 'fivem_user'@'%';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'fivem_user'@'localhost';
FLUSH PRIVILEGES;

# Verify permissions
SHOW GRANTS FOR 'fivem_user'@'localhost';

Framework-Specific Setup

ESX Framework Database

ESX 1.2 (Legacy) Setup

# Import ESX SQL files
mysql -u fivem_user -p fivem_server < es_extended/base.sql
mysql -u fivem_user -p fivem_server < es_extended/users.sql
mysql -u fivem_user -p fivem_server < es_extended/items.sql
mysql -u fivem_user -p fivem_server < es_extended/jobs.sql
mysql -u fivem_user -p fivem_server < es_extended/vehicles.sql

# Additional tables for common ESX addons
mysql -u fivem_user -p fivem_server < esx_addonaccount.sql
mysql -u fivem_user -p fivem_server < esx_addoninventory.sql
mysql -u fivem_user -p fivem_server < esx_billing.sql
mysql -u fivem_user -p fivem_server < esx_drugs.sql

ESX 1.9+ (Modern) Setup

# ESX 1.9+ uses a single SQL file
mysql -u fivem_user -p fivem_server < es_extended/es_extended.sql

# Check tables
mysql -u fivem_user -p -e "USE fivem_server; SHOW TABLES;"

ESX Configuration (server.cfg)

# ESX Database Configuration
set mysql_connection_string "server=localhost;database=fivem_server;userid=fivem_user;password=your_password_here"

# Optional parameters
set mysql_slow_query_warning 200  # Warning threshold in milliseconds
set mysql_debug false

QBCore Framework Database

QBCore 4.0+ Setup

# Import QBCore SQL files
mysql -u fivem_user -p fivem_server < qb-core.sql

# Additional QB tables
mysql -u fivem_user -p fivem_server < qb-vehicles.sql
mysql -u fivem_user -p fivem_server < qb-phone.sql
mysql -u fivem_user -p fivem_server < qb-houses.sql
mysql -u fivem_user -p fivem_server < qb-garages.sql
mysql -u fivem_user -p fivem_server < qb-clothing.sql

# Verify installation
mysql -u fivem_user -p -e "USE fivem_server; SELECT COUNT(*) FROM players;"

QBCore Configuration (config.lua)

-- QBCore Database Configuration
Config = {}

Config.Database = {
    enabled = true,
    driver = 'mysql',  -- Options: mysql, oxmysql, ghmattimysql
    host = 'localhost',
    port = 3306,
    username = 'fivem_user',
    password = 'your_password_here',
    database = 'fivem_server',
    connectionLimit = 50,
    queueLimit = 100,
    connectTimeout = 10000,
    acquireTimeout = 10000,
    waitForConnections = true,
    charset = 'utf8mb4'
}

-- Connection string for alternative drivers
Config.ConnectionString = 'mysql://fivem_user:password@localhost/fivem_server?charset=utf8mb4'

Database Drivers and Optimization

Choosing a Database Driver

oxmysql Installation

# Download oxmysql
git clone https://github.com/overextended/oxmysql.git

# Place in server resources folder
mv oxmysql /path/to/server/resources/

# Configure in server.cfg
ensure oxmysql

# Connection string in server.cfg
set mysql_connection_string "mysql://fivem_user:password@localhost/fivem_server?charset=utf8mb4"

# Optional oxmysql settings
set mysql_debug false
set mysql_slow_query_warning 200
set mysql_connection_limit 50

Performance Optimization

Index Optimization

-- Critical indexes for FiveM databases
-- ESX Players table
CREATE INDEX idx_players_identifier ON users (identifier);
CREATE INDEX idx_players_name ON users (name);
CREATE INDEX idx_players_last_login ON users (last_login);

-- QBCore Players table  
CREATE INDEX idx_players_citizenid ON players (citizenid);
CREATE INDEX idx_players_license ON players (license);
CREATE INDEX idx_players_last_updated ON players (last_updated);

-- Vehicles table (both frameworks)
CREATE INDEX idx_vehicles_owner ON owned_vehicles (owner);
CREATE INDEX idx_vehicles_plate ON owned_vehicles (plate);
CREATE INDEX idx_vehicles_stored ON owned_vehicles (stored);

-- Items/inventory tables
CREATE INDEX idx_items_owner ON items (owner);
CREATE INDEX idx_items_name ON items (name);
CREATE INDEX idx_items_last_updated ON items (last_updated);

Table Optimization Commands

-- Regular maintenance (run weekly)
OPTIMIZE TABLE users, items, owned_vehicles, players, vehicles;

-- Analyze table statistics
ANALYZE TABLE users, items, owned_vehicles;

-- Check table sizes
SELECT 
    table_name AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`,
    table_rows AS `Rows`
FROM information_schema.tables
WHERE table_schema = 'fivem_server'
ORDER BY (data_length + index_length) DESC;

Security Hardening

Database Security Configuration

# MySQL Security Settings (my.cnf)
[mysqld]
# Network security
skip_name_resolve = ON
bind-address = 127.0.0.1  # Only local connections (or specific IP)
# bind-address = 192.168.1.100  # For remote connections

# Authentication
default_authentication_plugin = mysql_native_password

# SSL/TLS Configuration (recommended for remote)
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON  # Force SSL connections

# Password policies
validate_password.policy = MEDIUM
validate_password.length = 12
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1

# Logging and auditing
general_log = OFF  # Disable in production
log_error = /var/log/mysql/error.log
audit_log = ON  # If audit plugin installed
audit_log_format = JSON

User Security Best Practices

1. Separate users: Different users for ESX vs QBCore vs administration
2. Limit permissions: Only grant necessary privileges (SELECT, INSERT, UPDATE, DELETE)
3. IP restrictions: Limit database access to specific server IPs
4. Regular audits: Review user permissions and login attempts
5. Password rotation: Change database passwords every 90 days

Backup and Recovery

Automated Backup Script

#!/bin/bash
# FiveM Database Backup Script
BACKUP_DIR="/backups/fivem/database"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="fivem_server"
DB_USER="fivem_user"
DB_PASS="your_password_here"
RETENTION_DAYS=30

# Create backup directory
mkdir -p $BACKUP_DIR

# Dump database
mysqldump -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers \
  --databases $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql

# Compress backup
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql

# Clean old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Sync to remote storage (optional)
# rsync -av $BACKUP_DIR/ user@remote:/backups/fivem/database/

echo "Backup completed: $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

Restoration Procedure

# Stop FiveM server
systemctl stop fivem

# Restore database
gunzip -c /backups/fivem/database/fivem_server_20250111_143022.sql.gz | mysql -u fivem_user -p

# Verify restoration
mysql -u fivem_user -p -e "USE fivem_server; SHOW TABLES; SELECT COUNT(*) FROM players;"

# Start FiveM server
systemctl start fivem

# Monitor for issues
tail -f /path/to/fivem/server/logs/server.log

Integration with txAdmin

txAdmin Database Configuration

{
  "database": {
    "mysql": {
      "host": "localhost",
      "port": 3306,
      "user": "fivem_user",
      "password": "secure_password_here",
      "database": "fivem_server",
      "connectionLimit": 20,
      "waitForConnections": true,
      "queueLimit": 100,
      "connectTimeout": 10000,
      "acquireTimeout": 10000
    },
    "backup": {
      "enabled": true,
      "schedule": "0 3 * * *",  // Daily at 3 AM
      "retention": 7,            // Keep 7 daily backups
      "compression": "gzip",
      "storage": {
        "local": "/backups/fivem/database/",
        "remote": {
          "type": "s3",
          "bucket": "fivem-backups",
          "region": "us-east-1"
        }
      }
    }
  }
}

Monitoring Database Health in txAdmin

Troubleshooting Common Issues

Best Practices

Performance Recommendations

1. Connection pooling: Use oxmysql with appropriate connection limits
2. Query optimization: Use indexed columns in WHERE clauses, avoid SELECT *
3. Regular maintenance: Weekly OPTIMIZE TABLE and ANALYZE TABLE
4. Monitoring: Implement database performance monitoring
5. Scaling: Consider read replicas for high-player-count servers

Security Recommendations

1. Network isolation: Keep database on private network, not publicly accessible
2. Principle of least privilege: Grant minimal necessary permissions
3. Encryption: Use SSL/TLS for all database connections
4. Auditing: Enable audit logs and regularly review them
5. Backup encryption: Encrypt database backups at rest

Disaster Recovery Plan

1. Regular backups: Daily full backups, hourly binary log backups
2. Backup testing: Monthly restoration tests to verify backups
3. Replication: Set up MySQL replication for quick failover
4. Documentation: Maintain detailed recovery procedures
5. Monitoring: Implement alerts for backup failures

Next Steps

• Configure txAdmin for server management
• Choose between ESX and QBCore frameworks
• Optimize FiveM server performance
• Install essential FiveM resources
• Browse other game server guides