MySQL for your FiveM server: setting up oxmysql properly
A freeroam server remembers nothing, and that's fine. But as soon as you build a real roleplay server with a framework like ESX or QBCore, everything has to be persisted: characters, bank balances, vehicles, items in pockets and trunks. For that, virtually every FiveM server uses a MySQL database, and the bridge between your server and that database is nowadays called oxmysql. In this article you'll set up that connection properly, and make sure your players' data stays safe too.
Why an RP server can't do without a database
Every time a player logs out, your framework writes away their progress: position, money, job, inventory. When the player logs in again tomorrow, the framework reads it all back. Without a database everyone starts from scratch every session, and for roleplay that's fatal. The database is quite literally your city's memory, and every script that needs to remember anything (garages, houses, phones) taps into that same database.
oxmysql: the current standard
For years mysql-async was the standard connector, later joined by ghmattimysql. Neither is actively maintained anymore. The successor is oxmysql: actively developed, and the connector that modern frameworks and scripts expect these days. If you still come across mysql-async in a tutorial, that's a sign the tutorial is outdated. Good to know: oxmysql has a compatibility layer, so many older scripts that ask for mysql-async simply keep working.
Installing is little work beyond that: oxmysql is an ordinary resource you put in your resources folder and start with an ensure line. If you use a txAdmin recipe for ESX or QBCore, oxmysql is often already included and you only need to fill in the database details correctly.
The connection string in server.cfg
oxmysql needs to know where your database lives and how it may log in. You handle that with a single convar in server.cfg, and it has to come before the ensure of oxmysql:
set mysql_connection_string "mysql://user:[email protected]/database_name?charset=utf8mb4"
ensure oxmysql
ensure es_extendedYou get the details (user, password, host and database name) when you create your database. If you order a FiveM server from MC-Node, database management is simply part of the panel; the knowledge base explains how to create a database and how to connect it to your server.
One warning we can't repeat often enough: this line contains your database password. So never casually share your complete server.cfg when asking for help somewhere, but cut the connection string out first. And stick to the order: first the connection string, then ensure oxmysql, and only after that your framework. If oxmysql isn't started first, your framework comes up with database errors and players can't even log in.
How ESX and QBCore use your database
You don't need to become a database administrator, but a rough picture helps enormously when solving problems. When installing your framework you import a bundled .sql file that creates the base tables. ESX, for example, stores players in a users table and vehicles in owned_vehicles; QBCore uses players and player_vehicles, among others. Every larger script you install afterwards (garages, houses, jobs) often ships its own .sql file with extra tables. Import those properly, or you'll get console errors about missing tables the moment the script tries to save something.
If you want to look at the data yourself, you can via your panel's database management; the knowledge base explains how to create and manage a database. Looking is always fine, but be cautious with manual edits. Changing money or items directly in a table while the server is running gets overwritten again at the next save, or worse, leaves you with inconsistent data. For those kinds of interventions, use your framework's admin tools instead.
Recognising slow queries
A database that responds slowly feels like server lag: stutters when logging in, items appearing sluggishly in your inventory, a city that feels heavy while the processor has little to do. Here's how to find out whether the database is the culprit:
- txAdmin metrics. The txAdmin dashboard shows how your server is performing and which resources eat up time. If spikes coincide with moments when a lot gets saved, such as scheduled saves or busy periods, that points towards the database.
- The console. oxmysql can report slow queries in your server console, including the resource that fired them. That's worth gold: you immediately see which script is causing the problem.
- Testing after every installation. If you notice stutters right after adding a new script, you've already found your suspect. So add big scripts one at a time and keep an extra eye on the metrics for the first few days.
The cause is almost never "MySQL is slow", but almost always a script that writes too much or too often, for example saving complete inventories every few seconds. The solution then lies in the script: a more generous save interval, or a better-written alternative. A heavier plan only masks a problem like that for a while.
Backups: the most boring topic, until you need one
Your database contains everything your community has built up. One failed framework update or one script that empties a table, and months of characters and possessions are gone. Therefore:
- Make a backup before every big change. Framework update, new inventory script, big migration: export first, install after.
- Automate recurring backups via your panel's backup feature, and regularly download one to your own computer.
- Test a restore every now and then. A backup you've never restored is an assumption, not a certainty.
It sounds excessive, until it goes wrong. In an RP community the database isn't just data: it's the progress of all your players, and they trust you to take good care of it.