Database backups and migrations

Take a full dump on a schedule

A dump is one file that holds the whole database. Export the database to a .sql file on a schedule. Daily is right for an active server.

In HeidiSQL: right-click the database, choose Export database as SQL, and save it to a file with today's date in the name. The command line does the same thing.

Run the command below on the machine where MariaDB is installed. On Linux that is a shell. On Windows, open Command Prompt (search cmd), NOT PowerShell, because that is where the mariadb-dump program lives and where the > redirection writes a clean file. Make sure the MariaDB bin folder is on your PATH.

mariadb-dump -u root -p fivem > fivem-2026-07-02.sql

Here is what each part means. -u root is the database username. Use your own DB user, not always root. -p makes it prompt you for that user's password. Type the password when asked. Nothing shows on screen as you type, and that is normal. fivem is the database name to back up, so use yours. > fivem-2026-07-02.sql writes the dump into a file with today's date in the current folder.

On a MariaDB box the dump program is mariadb-dump (the tools were renamed in MariaDB 10.5). The old mysqldump name still works, because MariaDB ships it as an alias of the same binary. If mysqldump says command not found, just use mariadb-dump. They take the same flags.

To make daily backups real instead of a good intention, put the command on a schedule. On Linux you do that with cron, the built-in job scheduler you edit with crontab -e. This line dumps every night at 4am:

0 4 * * * /usr/bin/mariadb-dump --defaults-extra-file=/root/.mariadb-backup.cnf --single-transaction --routines fivem > /backups/fivem-$(date +\%F).sql

Read it left to right. 0 4 * * * is the cron schedule, meaning minute 0 of hour 4, every day. --defaults-extra-file=... points at a credentials file so no password is typed on the line. --single-transaction takes a consistent snapshot without blocking your players' writes (a consistent snapshot for InnoDB tables, which framework tables are by default). --routines includes stored procedures and functions. fivem is the database. > /backups/fivem-$(date +\%F).sql writes to a dated file. The backslash in \%F is required, because cron treats a bare % as a newline. Escaping it lets date +%F insert today's date as YYYY-MM-DD.

The line above uses a dedicated backup_user. For your very first backups it is fine to use root; a read-only backup user is the production habit. To create one, run this once in an interactive MariaDB session:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

Keep the password out of the command. Put the backup user's credentials in a plain-text option file at /root/.mariadb-backup.cnf in MariaDB option-file format (the [mariadb-dump] group is read by mariadb-dump, which also reads [client]):

[mariadb-dump]
user=backup_user
password=your-backup-user-password

Then lock the file down so only its owner can read it:

chmod 600 /root/.mariadb-backup.cnf

Never put a password directly in a cron line or in your command history. On Windows, use Task Scheduler to run the same dump on a schedule:

1. Open Task Scheduler and choose Create Basic Task. Give it a name like "MariaDB daily backup".
2. Set the trigger to Daily at 04:00.
3. For the action pick Start a program, set the program to cmd and the arguments to a full backup command, for example /c "mariadb-dump --defaults-extra-file=C:\backups\mariadb-backup.cnf --single-transaction --routines fivem > C:\backups\fivem-backup.sql".

Point --defaults-extra-file at a protected .cnf in C:\backups\ so the password never sits in the task itself. txAdmin only schedules server restarts and events, not general operating-system scripts, so do not rely on it to run a backup.

Store backups off the server box

Copy your dumps somewhere other than the server machine. Another disk, or a private cloud bucket, both work. A backup that dies with the server is not a backup.

Migrate schema safely

A migration is a change to the shape of the database, like adding a column. Three rules keep migrations safe. Take a fresh dump first. Run the change on a throwaway copy before you run it for real. Prefer additive changes like ADD COLUMN over destructive ones. Never run an untested ALTER or DROP against live data.

First build the copy from your latest dump and run the migration there. On Windows, run these in Command Prompt (cmd.exe) so the < redirect works:

mariadb -u root -p -e "CREATE DATABASE fivem_migrate_test;"
mariadb -u root -p fivem_migrate_test < fivem-2026-07-02.sql
mariadb -u root -p fivem_migrate_test -e "ALTER TABLE players ADD COLUMN bank INT NOT NULL DEFAULT 0;"

Only once that runs clean on the copy do you touch production. Stop the resources that write to the affected tables (from the txAdmin Resources page), or schedule the migration right after a restart announcement, so your scripts cannot race the migration. Then run the change. Bring the resources back only after you have checked the result.

ALTER TABLE players ADD COLUMN bank INT NOT NULL DEFAULT 0;

Run a restore test

Make a throwaway database and import a dump into it. This is the step everyone skips, and the step everyone regrets skipping.

Run these in Command Prompt (cmd.exe) on Windows, so the < redirect works:

mariadb -u root -p -e "CREATE DATABASE fivem_restore_test;"
mariadb -u root -p fivem_restore_test < fivem-2026-07-02.sql

Prefer a GUI? HeidiSQL does the same restore: right-click the session, choose Create new > Database and name it fivem_restore_test, then use File > Run SQL file, pick the dump, and run it against that test database.

Do not eyeball it. Prove the restore by comparing the row count of one table between production and the restore.

Open an interactive client session:

mariadb -u root -p

It prompts for your password, then shows a MariaDB [(none)]> prompt. Type each query there and press Enter:

SELECT COUNT(*) FROM fivem.players;
SELECT COUNT(*) FROM fivem_restore_test.players;

The interactive client prints a boxed table like this. The number 1487 is only an example. Yours will be your own row count.

Note: if you instead run these with the -e "..." flag from the shell, as in the exercise below, the client prints plain tab-separated output. You get a COUNT(*) header line, then the number, instead of the boxed table. The value is the same. Only the formatting differs.

The two counts must match. If they differ, the dump is incomplete or the import errored, and that backup is not safe to rely on.