Database Management for FiveM Servers: MySQL Best Practices

Your Database Will Fail. Are You Ready?

Here's a story that happens more often than it should:

It's Saturday night. Your server is packed with 120 players. Then, suddenly, nobody can save their inventory. Cars are disappearing. Money isn't persisting. Your Discord is exploding with angry messages.

You check your database. It's corrupted. Three months of player data: gone.

No backups. No recovery plan. Just... gone.

This happens to server owners every single week. Not because they're stupid—because nobody teaches proper database management for FiveM servers.

This guide will teach you everything you need to know: from basic setup to advanced optimization, backup strategies, and disaster recovery. By the end, you'll manage your database like a professional.

MySQL Basics for FiveM Server Owners

What is MySQL and Why Does FiveM Use It?

MySQL is a database management system—think of it as an organized filing cabinet for all your server data:

• Player accounts and characters
• Inventory items and vehicles
• Businesses and properties
• Jobs, money, and transactions
• Bans, logs, and metadata

FiveM frameworks (ESX, QBCore, QBox) all use MySQL because it's:

• Fast - Can handle thousands of queries per second
• Reliable - Industry standard for 25+ years
• Free - Open source, no licensing costs
• Scalable - Works for 10 players or 1000 players

MySQL vs MariaDB: What's the Difference?

You'll hear both terms. Here's the quick explanation:

Feature	MySQL	MariaDB
Origin	Oracle Corporation	MySQL fork (community-driven)
Performance	Excellent	Slightly faster for most operations
Compatibility	100% with all FiveM scripts	100% compatible (drop-in replacement)
Recommendation	Use if already installed	Preferred for new setups

Bottom line: They're interchangeable for FiveM. Use whichever your hosting provider offers. MariaDB is slightly better, but the difference is minimal.

Setting Up MySQL Properly

Installation (Windows)

Most server owners use Windows. Here's the proper setup:

Option 1: XAMPP (Easiest for Beginners)

1. Download XAMPP from apachefriends.org
2. Install to C:\xampp (avoid Program Files)
3. Open XAMPP Control Panel
4. Start Apache and MySQL modules
5. Click "Admin" next to MySQL to open phpMyAdmin

Option 2: Standalone MySQL (More Control)

1. Download MySQL Community Server from dev.mysql.com
2. Run installer, choose "Developer Default"
3. Set root password (WRITE IT DOWN)
4. Configure MySQL to start with Windows
5. Install MySQL Workbench for management

Critical Configuration Settings

Default MySQL settings are NOT optimized for FiveM. Here's what to change:

Edit my.ini (or my.cnf on Linux)

Location: C:\xampp\mysql\bin\my.ini or /etc/mysql/my.cnf

[mysqld]
# Connection settings
max_connections = 200              # Allow more simultaneous connections
max_connect_errors = 1000000      # Prevent lockouts

# Performance optimization
innodb_buffer_pool_size = 2G      # Use 50-70% of available RAM
innodb_log_file_size = 256M       # Larger log files for better write performance
innodb_flush_log_at_trx_commit = 2  # Better performance, minimal risk

# Query cache (only for MySQL 5.7 and older)
query_cache_type = 1
query_cache_size = 128M

# Character set (CRITICAL for FiveM)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Timeouts
wait_timeout = 600                # 10 minutes before idle timeout
interactive_timeout = 600

After editing: Restart MySQL service for changes to take effect.

Database Structure Best Practices

Naming Conventions

Consistent naming prevents errors and makes management easier:

Tables:

• Use lowercase with underscores: user_accounts
• Pluralize table names: users, vehicles, items
• Be descriptive: player_inventory not just inventory

Columns:

• Use snake_case: first_name, created_at
• Be specific: user_id instead of just id in junction tables
• Use standard names: created_at, updated_at, deleted_at

Primary Keys:

• Always use id as primary key name
• Use INT AUTO_INCREMENT for most cases
• Or CHAR(40) for identifier strings (like Steam IDs)

Data Types: Choose Wisely

Wrong data types waste space and hurt performance:

Data Type	Use For	Example
INT	IDs, numbers, counts	user_id, item_count
BIGINT	Large numbers (money)	bank_balance
VARCHAR(255)	Short text, names	username, car_model
TEXT	Long text	character_backstory
LONGTEXT	JSON, serialized data	inventory_data
DATETIME	Dates and times	created_at, last_login
TINYINT(1)	Boolean (0 or 1)	is_banned, is_admin
DECIMAL(10,2)	Money, precise decimals	item_price

Common Mistakes:

• ❌ Using VARCHAR(255) for everything (wastes space)
• ❌ Using TEXT for short strings (slower queries)
• ❌ Storing money as FLOAT (precision errors)
• ❌ Using INT for large numbers like money (overflow)

Indexes: The Performance Secret

Indexes are like a book's index—they help MySQL find data faster. Without indexes, your server will lag.

When to Add Indexes:

• ✅ Columns used in WHERE clauses
• ✅ Foreign keys (user_id, character_id, etc.)
• ✅ Columns used in ORDER BY
• ✅ Columns used in JOIN conditions

Example Index Creation:

-- Add index to identifier (used in WHERE clauses constantly)
CREATE INDEX idx_identifier ON users(identifier);

-- Add composite index (multiple columns)
CREATE INDEX idx_user_char ON characters(user_id, character_id);

-- Add unique index (ensures no duplicates)
CREATE UNIQUE INDEX idx_unique_license ON users(license);

Checking Index Usage:

-- See which indexes exist on a table
SHOW INDEX FROM users;

-- Analyze query performance (see if indexes are used)
EXPLAIN SELECT * FROM users WHERE identifier = 'steam:1100001';

If "type" column shows "ALL", your query is scanning the entire table (BAD). Add an index.

oxmysql vs mysql-async: What to Use

FiveM has evolved. Here's the current standard:

The Evolution:

1. mysql-async (2017-2020) - The original, now outdated
2. ghmattimysql (2019-2021) - Better performance, deprecated
3. oxmysql (2021-present) - The current standard

Why oxmysql?

• ✅ 5-10x faster than old libraries
• ✅ Better error handling
• ✅ Modern prepared statements (prevents SQL injection)
• ✅ Actively maintained
• ✅ Works with QBCore, ESX, and QBox

Installation:

1. Download from: github.com/overextended/oxmysql
2. Extract to resources/[standalone]/oxmysql
3. Add to server.cfg:

ensure oxmysql

set mysql_connection_string "mysql://root:password@localhost/your_database?charset=utf8mb4"

Connection String Format:

mysql://username:password@host:port/database?charset=utf8mb4

Example:
mysql://fivem:MySecurePass123@localhost:3306/fivem_server?charset=utf8mb4

Security: Protecting Your Database

User Permissions (CRITICAL)

Never use root user for your FiveM server. Create a dedicated user with limited permissions:

-- Create new MySQL user for FiveM
CREATE USER 'fivem_user'@'localhost' IDENTIFIED BY 'SecurePassword123!';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON fivem_database.* TO 'fivem_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Password Security:

• ✅ Use 16+ character passwords
• ✅ Mix uppercase, lowercase, numbers, symbols
• ✅ Never use "password", "admin", "123456"
• ✅ Change passwords every 3-6 months
• ❌ Never commit passwords to GitHub

Remote Access Security:

If your database is on a separate server:

1. Use SSH tunneling instead of opening MySQL port 3306
2. If you must expose 3306:
   • Use firewall to allow only specific IPs
   • Change default port 3306 to something obscure
   • Use SSL/TLS for connections

SQL Injection Prevention:

Always use prepared statements with oxmysql:

-- ❌ VULNERABLE (Don't do this!)
local query = string.format("SELECT * FROM users WHERE identifier = '%s'", identifier)
MySQL.query(query)

-- ✅ SAFE (Use this!)
MySQL.query('SELECT * FROM users WHERE identifier = ?', {identifier})

The ? placeholder is automatically sanitized by oxmysql, preventing SQL injection attacks.

Backup Strategies: Your Insurance Policy

The 3-2-1 Backup Rule:

• 3 copies of your data
• 2 different storage types
• 1 offsite backup

Automated Daily Backups (Windows)

Create a batch file to automate backups:

@echo off
REM backup_database.bat

REM Set variables
set MYSQL_PATH="C:\xampp\mysql\bin"
set DB_NAME=fivem_server
set DB_USER=root
set DB_PASS=your_password
set BACKUP_PATH=D:\FiveM_Backups
set TIMESTAMP=%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
set TIMESTAMP=%TIMESTAMP: =0%

REM Create backup directory if it doesn't exist
if not exist "%BACKUP_PATH%" mkdir "%BACKUP_PATH%"

REM Perform backup
%MYSQL_PATH%\mysqldump -u %DB_USER% -p%DB_PASS% %DB_NAME% > "%BACKUP_PATH%\backup_%TIMESTAMP%.sql"

REM Delete backups older than 30 days
forfiles /P "%BACKUP_PATH%" /S /M *.sql /D -30 /C "cmd /c del @path"

echo Backup completed: backup_%TIMESTAMP%.sql

Schedule this with Windows Task Scheduler:

1. Open Task Scheduler
2. Create Basic Task
3. Set trigger: Daily at 4 AM (low traffic time)
4. Action: Run backup_database.bat
5. Test it manually first!

Automated Daily Backups (Linux)

#!/bin/bash
# /root/backup_mysql.sh

DB_NAME="fivem_server"
DB_USER="root"
DB_PASS="your_password"
BACKUP_DIR="/backups/mysql"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# Create backup directory
mkdir -p $BACKUP_DIR

# Perform backup
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/backup_$TIMESTAMP.sql.gz

# Delete backups older than 30 days
find $BACKUP_DIR -name "*.sql.gz" -type f -mtime +30 -delete

echo "Backup completed: backup_$TIMESTAMP.sql.gz"

Add to crontab:

# Run daily at 4 AM
0 4 * * * /root/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

Offsite Backup Options:

• Google Drive - Use rclone to auto-sync backups
• Dropbox - Same, rclone integration
• AWS S3 - Enterprise option, pay-per-use
• BackBlaze B2 - Cheaper than S3, still reliable

Testing Your Backups:

A backup you haven't tested is not a backup.

Once a month, restore from backup to a test database:

mysql -u root -p test_database < backup_20250101_040000.sql

If it works, your backups are good. If not, fix your backup process NOW.

Performance Optimization

Slow Query Log:

Enable slow query logging to find performance bottlenecks:

# Add to my.ini / my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 2   # Log queries taking longer than 2 seconds

Review the log weekly and optimize slow queries.

Query Optimization Tips:

1. Use LIMIT for Large Result Sets:

-- ❌ Returns 50,000 rows (slow)
SELECT * FROM transactions;

-- ✅ Returns only what you need
SELECT * FROM transactions ORDER BY created_at DESC LIMIT 100;

2. Avoid SELECT *:

-- ❌ Returns 20 columns you don't need
SELECT * FROM users WHERE id = 1;

-- ✅ Returns only what you need
SELECT id, username, money FROM users WHERE id = 1;

3. Use JOIN Instead of Multiple Queries:

-- ❌ Two separate queries (slow)
local user = MySQL.query('SELECT * FROM users WHERE id = ?', {userId})
local vehicles = MySQL.query('SELECT * FROM vehicles WHERE owner_id = ?', {userId})

-- ✅ One query with JOIN (fast)
local result = MySQL.query([[
    SELECT u.*, v.plate, v.model 
    FROM users u
    LEFT JOIN vehicles v ON u.id = v.owner_id
    WHERE u.id = ?
]], {userId})

4. Batch INSERT for Multiple Records:

-- ❌ Multiple INSERT queries
for i, item in pairs(items) do
    MySQL.insert('INSERT INTO inventory (item_name, quantity) VALUES (?, ?)', {item.name, item.qty})
end

-- ✅ Single batch INSERT
MySQL.insert([[
    INSERT INTO inventory (item_name, quantity) VALUES 
    ('bread', 5), ('water', 10), ('bandage', 3)
]])

Table Maintenance:

Run these commands monthly:

-- Analyze tables (updates statistics)
ANALYZE TABLE users, vehicles, inventory;

-- Optimize tables (defragment)
OPTIMIZE TABLE users, vehicles, inventory;

-- Check for corruption
CHECK TABLE users, vehicles, inventory;

Monitoring and Alerts

What to Monitor:

• Connection count: Are you hitting max_connections?
• Query time: Are queries getting slower?
• Database size: Running out of disk space?
• Error logs: Any unusual errors?

Simple Monitoring Query:

-- Check current connections
SHOW STATUS WHERE Variable_name = 'Threads_connected';

-- Check slow queries
SHOW STATUS WHERE Variable_name = 'Slow_queries';

-- Check database size
SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES 
GROUP BY table_schema;

Discord Webhook Alerts:

Create a simple script to alert you when backups fail:

-- Add to your backup script
if [ $? -eq 0 ]; then
    curl -H "Content-Type: application/json" \
    -d '{"content":"✅ Database backup successful"}' \
    YOUR_DISCORD_WEBHOOK_URL
else
    curl -H "Content-Type: application/json" \
    -d '{"content":"❌ DATABASE BACKUP FAILED - CHECK IMMEDIATELY"}' \
    YOUR_DISCORD_WEBHOOK_URL
fi

Disaster Recovery Plan

When Shit Hits the Fan:

Database corrupted? Follow this checklist:

1. Stop the server immediately - Prevent further corruption
2. Notify players - Be transparent about the issue
3. Check backups - Find the most recent clean backup
4. Create a test environment - Never restore directly to production
5. Restore backup to test database
6. Verify data integrity - Spot check critical tables
7. Import to production - Only if test succeeds
8. Document what happened - Prevent future occurrences

Restoring from Backup:

# Create new database
mysql -u root -p -e "CREATE DATABASE fivem_server_restored;"

# Restore from backup
mysql -u root -p fivem_server_restored < backup_20250101_040000.sql

# If successful, swap databases
mysql -u root -p -e "DROP DATABASE fivem_server_old;"
mysql -u root -p -e "RENAME DATABASE fivem_server TO fivem_server_old;"
mysql -u root -p -e "RENAME DATABASE fivem_server_restored TO fivem_server;"

Point-in-Time Recovery:

If you need to recover to a specific time (not just the latest backup):

1. Enable binary logging in my.ini:

log-bin = mysql-bin
expire_logs_days = 7

2. Restore from the last backup before the incident
3. Apply binary logs up to the point before corruption:

mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p fivem_server

This is advanced, but can save you when you need to recover data from between backups.

Common Issues and Solutions

Issue: "Too many connections" Error

Cause: Your server is hitting the max_connections limit.

Solution:

-- Temporarily increase limit
SET GLOBAL max_connections = 300;

-- Make permanent in my.ini
max_connections = 300

Also check for connection leaks in your scripts. Every query should properly close connections.

Issue: Database Performance Degrades Over Time

Cause: Fragmentation and missing table optimization.

Solution:

-- Run monthly maintenance
OPTIMIZE TABLE users, vehicles, inventory, transactions;
ANALYZE TABLE users, vehicles, inventory, transactions;

Issue: "Lock wait timeout exceeded"

Cause: Long-running transactions blocking other queries.

Solution:

-- Find blocking queries
SHOW PROCESSLIST;

-- Kill problematic query
KILL [process_id];

-- Increase timeout temporarily
SET GLOBAL innodb_lock_wait_timeout = 120;

Issue: Sudden Disk Space Full

Cause: Binary logs or old backups filling disk.

Solution:

# Check MySQL data directory size
du -sh /var/lib/mysql/

# Purge old binary logs
mysql -u root -p -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"

# Delete old backups
find /backups -name "*.sql*" -mtime +30 -delete

Advanced Topics

Replication for High Availability

For servers with 200+ concurrent players, consider MySQL replication:

Benefits:

• Automatic failover if main database crashes
• Read queries can use replica (faster performance)
• Zero-downtime backups from replica

Basic Setup:

Master Server (my.ini):

server-id = 1
log-bin = mysql-bin
binlog-do-db = fivem_server

Replica Server (my.ini):

server-id = 2
relay-log = mysql-relay-bin

This is a complex topic that deserves its own guide, but it's worth knowing about for large servers.

Database Partitioning

For tables with millions of rows (like transaction logs), partitioning can dramatically improve performance:

-- Partition transactions by month
ALTER TABLE transactions
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

This allows MySQL to scan only relevant partitions, making queries 10x faster on large datasets.

Read/Write Splitting

For maximum performance, use separate connections for reads and writes:

-- Write connection (master)
local writeDb = 'mysql://user:pass@master-server/db'

-- Read connection (replica or same server)
local readDb = 'mysql://user:pass@read-server/db'

-- Heavy read queries use read connection
MySQL.query('SELECT * FROM users WHERE identifier = ?', {identifier}, readDb)

-- Writes use master
MySQL.insert('INSERT INTO logs ...', params, writeDb)

The Bottom Line

Your database is the heart of your FiveM server. Treat it with respect:

Daily: Automated backups running without fail
Weekly: Review slow query logs and optimize
Monthly: Test backup restoration, run table maintenance
Quarterly: Review security, update passwords, audit permissions

If you take away just ONE thing from this guide, make it this:

SET UP AUTOMATED BACKUPS RIGHT NOW.

Not tomorrow. Not next week. Right now.

Your future self will thank you when—not if—something goes wrong.

---

Quick Reference Checklist

Initial Setup:

• ☐ MySQL/MariaDB installed and configured
• ☐ Dedicated FiveM database user created (not root)
• ☐ oxmysql installed and configured
• ☐ Connection string secured (not in public repos)

Security:

• ☐ Strong password (16+ characters)
• ☐ Limited user permissions (no DROP/CREATE)
• ☐ Firewall configured (if remote database)
• ☐ All queries use prepared statements

Performance:

• ☐ Indexes on all foreign keys
• ☐ innodb_buffer_pool_size optimized
• ☐ Slow query log enabled
• ☐ Regular table optimization scheduled

Backups:

• ☐ Daily automated backups
• ☐ Backups stored in 3 locations
• ☐ Monthly restoration tests
• ☐ Discord alerts on backup failures

Monitoring:

• ☐ Disk space alerts set up
• ☐ Connection count monitored
• ☐ Error logs reviewed weekly
• ☐ Performance metrics tracked

---

Need help? Join the FiveM development Discord communities. Database issues are common, and experienced developers are usually happy to help.

Found this guide useful? Share it with other server owners. The FiveM community thrives when we help each other avoid catastrophic mistakes.

Good luck, and may your backups always be recent! 🚀