Database setup: MariaDB, HeidiSQL and oxmysql

Frameworks like QBCore, Qbox and ESX store characters, money, vehicles and inventories in a SQL database. FiveM talks to it through a resource, almost always oxmysql. A plain vMenu server does not need any of this.

How to Setup a Database for Your FiveM Server (TraseDev)

Use MariaDB, not XAMPP

• MariaDB is what the frameworks target. The oxmysql docs say MariaDB is “highly recommended for compatibility and improved performance” over MySQL 8 (which has reserved keyword and default value differences that break some scripts).
• Qbox requires at least MariaDB 10.9 and recommends a current LTS release. The ESX docs say to use MariaDB only.
• XAMPP bundles an old MariaDB with a web server you don’t need. Both the ESX and Qbox docs tell you not to use it. If you’re on XAMPP now, dump your database and move it to a real MariaDB install.

Install on Windows

1. Download the MSI from mariadb.org/download (pick the current LTS).
2. Run it. Set a root password and keep “Install as service” checked. Leave “Enable access from remote machines for root” unchecked.
3. The Windows installer includes HeidiSQL, a free GUI for the database. Keep it selected.
4. Default port is 3306. Keep it unless something else uses it.

Install on Linux

sudo apt install -y mariadb-server
sudo mariadb-secure-installation

Then create a dedicated user in the MariaDB shell (sudo mariadb):

CREATE USER 'fivem'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON *.* TO 'fivem'@'localhost';
FLUSH PRIVILEGES;

The broad grant lets the txAdmin deployer create a database. After deploying, you can restrict it to one database: GRANT ALL PRIVILEGES ON qbox_4f2a.* TO 'fivem'@'localhost'; and revoke the rest. Manage it from your PC with HeidiSQL over an SSH tunnel (HeidiSQL supports “MariaDB or MySQL (SSH tunnel)”) instead of opening port 3306.

HeidiSQL basics

1. Open HeidiSQL, click New, network type MariaDB or MySQL (TCP/IP), host 127.0.0.1, user root (or fivem), your password, port 3306. Save and Open.
2. Create a database: right click the connection > Create new > Database. Collation utf8mb4_unicode_ci or utf8mb4_general_ci.
3. Import a .sql file (a resource’s install SQL): select the database, then File > Run SQL file… and pick the file.
4. Browse data: click a table, then the Data tab. Handy for checking a player’s money or job while testing.
5. Back up: right click the database > Export database as SQL, tick “Create” for tables and “Insert” for data.

Let txAdmin create it

When you deploy a framework recipe in txAdmin, the deployer asks for host, port, user and password and creates the database for you if you leave the name empty (it generates a random name like QBCore_A1B2C3). It also imports the framework’s SQL and writes the connection string into server.cfg through the {{dbConnectionString}} placeholder. That’s the easiest path.

Connect with oxmysql

oxmysql reads one convar. Put it in server.cfg (or better, in a secrets.cfg you exec) before ensure oxmysql:

set mysql_connection_string "mysql://fivem:password@localhost:3306/qbox_4f2a?charset=utf8mb4"

ensure oxmysql

Both formats from the oxmysql docs work:

# URI
set mysql_connection_string "mysql://root:12345@localhost:3306/fivem"
# key=value
set mysql_connection_string "user=root;password=12345;host=localhost;port=3306;database=fivem"

Useful oxmysql convars:

set mysql_slow_query_warning 150      # warn about queries slower than 150 ms
set mysql_debug false                 # true, or a list like ["ox_inventory"] to log queries

Using the database in your own resource

Add oxmysql’s library to your manifest:

server_scripts {
    '@oxmysql/lib/MySQL.lua',
    'server/main.lua',
}
dependency 'oxmysql'

Then query from server scripts. Always use placeholders (?) for values, never string concatenation:

-- one row
local row = MySQL.single.await('SELECT money FROM my_accounts WHERE identifier = ?', { identifier })

-- one value
local count = MySQL.scalar.await('SELECT COUNT(*) FROM my_accounts')

-- many rows
local rows = MySQL.query.await('SELECT * FROM my_accounts WHERE money > ?', { 1000 })

-- insert, returns the new id
local id = MySQL.insert.await('INSERT INTO my_accounts (identifier, money) VALUES (?, ?)', { identifier, 500 })

-- update, returns affected rows
local changed = MySQL.update.await('UPDATE my_accounts SET money = money + ? WHERE identifier = ?', { 250, identifier })

Database code only runs on the server. Clients must never talk to the database directly. They ask the server with an event or callback, and the server checks and queries. See Client, server and events.

Backups

Set these up before you have players, not after the first disaster.

Windows: a scheduled task that runs mariadb-dump (or mysqldump, both ship with MariaDB) every night:

@echo off
set STAMP=%DATE:~-4%-%DATE:~3,2%-%DATE:~0,2%
"C:\Program Files\MariaDB 11.4\bin\mariadb-dump.exe" -u root -pYOURPASSWORD --single-transaction qbox_4f2a > C:\Backups\db-%STAMP%.sql

Adjust the MariaDB folder and date format to your system.

Linux: a cron job for the fivem user:

crontab -e
# every night at 04:30, keep 14 days
30 4 * * * mariadb-dump --single-transaction qbox_4f2a | gzip > /home/fivem/backups/db-$(date +\%F).sql.gz && find /home/fivem/backups -name 'db-*.sql.gz' -mtime +14 -delete

Put credentials in ~/.my.cnf (mode 600) so they don’t appear in the crontab:

[client]
user=fivem
password=a-long-random-password

Copy backups off the machine too (another server, object storage). A backup on the same disk doesn’t help when the disk dies.

Common database errors

Next: Common errors and fixes.