MariaDB and MySQL Backup and Restore on a VPS

Published on

Back up MariaDB or MySQL on a VPS with mariadb-dump or mysqldump, keep users and grants, restore, use mariadb-backup and Docker, and test nightly dumps.

Overview page of a VPS control panel with disk usage, bandwidth, CPU and network graphs and a Backup item in the side menu

Your site, shop, bot or FiveM server keeps its data in MariaDB or MySQL on one VPS. After a failed update, a DELETE with the wrong WHERE or a dead disk, you need a copy you can load back, stored off that VPS. This guide sets it up on Ubuntu 24.04 and Debian 12, from the first dump to nightly runs, restore tests, mariadb-backup and Docker.

Quick answer: Dump each database with mariadb-dump --single-transaction --routines --events appdb | gzip > appdb.sql.gz (mysqldump on MySQL), and save the accounts with mariadb-dump --system=users --insert-ignore, because a database dump contains no users. To restore, create the database and pipe the file into mariadb appdb; on a new server, load the users file after the data. Run the dumps nightly from cron, delete files older than 14 days, copy them to another machine and load one into a scratch database every month. For large databases, mariadb-backup copies the data files while MariaDB runs.

Dump or Physical Copy?

MariaDB's backup overview separates logical backups (SQL statements) from physical ones (copies of the data files):

mariadb-dump or mysqldumpmariadb-backup
OutputSQL statementsA copy of the data directory
RestoresOne database or all, also into another version or MySQLThe whole server
While it runsInnoDB tables stay writableNearly non-blocking
Size and restore timeLarger and slowerSmaller and faster

Start with dumps. MariaDB calls mariadb-dump "a good choice when the data size is relatively small", and MySQL's manual warns that restoring a large one "can be very slow" because every SQL statement is replayed.

Step 1: Dump a Database

On Debian and Ubuntu, sudo replaces the database password: MariaDB's packages there create root@localhost with unix_socket authentication, and MySQL on Ubuntu uses auth_socket. Replace appdb with your database:

sudo mariadb -e 'SHOW DATABASES'
sudo mariadb-dump --single-transaction --routines --events appdb | gzip > appdb.sql.gz
zcat appdb.sql.gz | tail -n 1
  • --single-transaction starts a transaction before it reads, so InnoDB tables come out as they were at that moment, "without blocking any applications" (mariadb-dump). Meanwhile, no other connection should run ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE: they can make the dump fail or copy wrong contents.
  • Triggers come along by default; stored procedures, functions and events only with --routines and --events.
  • The file has no CREATE DATABASE line, so you choose the database when you restore.
  • A complete dump ends with a -- Dump completed on line. Anything else means it stopped early.

On MySQL, use the same flags with mysqldump, and mysql in place of mariadb.

--single-transaction only protects InnoDB: tables in other engines, such as MyISAM, "may still change state" during the dump. This lists the tables that aren't InnoDB:

sudo mariadb -e "SELECT table_schema, table_name, engine FROM information_schema.tables
  WHERE table_type = 'BASE TABLE' AND engine <> 'InnoDB'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')"

If it lists any, dump with --lock-all-tables instead. That holds a global read lock for the whole dump, and while it does, "all write access to tables is blocked" (FLUSH), so run it at a quiet hour.

--all-databases in place of the name dumps everything into one file, including the mysql system database as plain table rows. Separate files are easier to restore one at a time, and accounts move better as in Step 2.

Step 2: Save Users and Grants

A dump of appdb holds its tables, data and routines, not the accounts your app logs in with. MariaDB's --system=users writes every account as CREATE USER and GRANT statements:

sudo mariadb-dump --system=users --insert-ignore | gzip > users.sql.gz

With --insert-ignore, each account starts with CREATE USER IF NOT EXISTS (mysqldump.c), so accounts that already exist on a new server, such as root@localhost, are skipped instead of stopping the load with Operation CREATE USER failed for 'root'@'localhost'. The file contains password hashes, so guard it like the dumps.

MySQL's mysqldump has no --system option. List each app user's privileges with SHOW GRANTS and create the user again on the new server, as Step 5 of our website move guide shows.

Step 3: Restore a Dump

Stop the app first, so nothing writes while the dump loads.

Replace a damaged database:

sudo mariadb -e 'DROP DATABASE appdb; CREATE DATABASE appdb'
zcat appdb.sql.gz | sudo mariadb appdb

Privileges on a database survive DROP DATABASE, so the app logs in as before once the load finishes. To compare or copy rows back, load into a second database such as appdb_old instead.

On a new server, install MariaDB, load the data, then the accounts. mariadb-dump uses the same order when it writes both into one file, and a grant on a single table fails until that table exists (sql_acl.cc):

sudo mariadb -e 'CREATE DATABASE appdb'
zcat appdb.sql.gz | sudo mariadb appdb
zcat users.sql.gz | sudo mariadb

Docker: The Same Tools Through docker exec

The official mariadb image's entrypoint gives root@localhost a password, so use MARIADB_ROOT_PASSWORD inside the container, as MariaDB's container backup page does. For a container named mariadb:

sudo docker exec mariadb sh -c 'exec mariadb-dump --all-databases --single-transaction --routines --events -uroot -p"$MARIADB_ROOT_PASSWORD"' | gzip > all.sql.gz
zcat all.sql.gz | sudo docker exec -i mariadb sh -c 'exec mariadb -uroot -p"$MARIADB_ROOT_PASSWORD"'
  • The single quotes let the container fill in its own variable. The mysql image works the same way with mysqldump, mysql and MYSQL_ROOT_PASSWORD.
  • -i keeps standard input open for the restore (docker exec). Never add -t to a dump: a pseudo-TTY adds a carriage return to every line (moby #8513). docker compose exec allocates a TTY by default, so add -T there.

Our Docker volumes guide covers one-database dumps in Compose and the volumes around them.

Step 4: Nightly Dumps With Cron

Create a folder that only you and root can open:

sudo install -d -m 700 -o "$USER" /var/backups/mysql

Save this as /usr/local/bin/db-backup:

#!/bin/bash
set -euo pipefail
DIR=/var/backups/mysql
KEEP_DAYS=14
DAY=$(date +%F)
SQL=mariadb            # MySQL: SQL=mysql
DUMP=mariadb-dump      # MySQL: DUMP=mysqldump

DBS=$($SQL -N -B -e "SELECT schema_name FROM information_schema.schemata
  WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')")
for DB in $DBS; do
  $DUMP --single-transaction --routines --events "$DB" | gzip > "$DIR/$DB-$DAY.sql.gz.part"
  mv "$DIR/$DB-$DAY.sql.gz.part" "$DIR/$DB-$DAY.sql.gz"
done
# MariaDB only: on MySQL, delete the next two lines
$DUMP --system=users --insert-ignore | gzip > "$DIR/users-$DAY.sql.gz.part"
mv "$DIR/users-$DAY.sql.gz.part" "$DIR/users-$DAY.sql.gz"
find "$DIR" -type f -mtime +"$KEEP_DAYS" -delete

With set -e and pipefail, a failed dump stops the script (bash), so a broken night never deletes older files, and a file only gets its final name once its dump has finished. find ignores fractions of a day, so -mtime +14 removes files at least 15 days old.

Run it once, check the folder, then open root's crontab:

sudo chmod 700 /usr/local/bin/db-backup
sudo /usr/local/bin/db-backup
ls -l /var/backups/mysql
sudo crontab -e

Add 30 3 * * * /usr/local/bin/db-backup to run it at 03:30 every night as root (crontab(5)). Prefer a systemd timer? Our PostgreSQL backup guide has the two units; point ExecStart at this script.

Then get a copy onto another machine every day, with rsync as in Step 4 of the PostgreSQL guide or with restic over SSH: a dump on the VPS's own disk dies with that disk.

Step 5: Test a Restore Every Month

A dump you have never loaded is a guess. Check last night's file, load it into a scratch database, compare a row count, then drop it:

F=/var/backups/mysql/appdb-2026-10-30.sql.gz
gzip -t "$F" && zcat "$F" | tail -n 1
sudo mariadb -e 'CREATE DATABASE restore_test'
zcat "$F" | sudo mariadb restore_test && echo RESTORE OK
sudo mariadb -e 'SELECT COUNT(*) FROM restore_test.orders; SELECT COUNT(*) FROM appdb.orders'
sudo mariadb -e 'DROP DATABASE restore_test'

Use one of your own tables for orders; the live count may be higher by now. gzip -t is silent for a good file and prints unexpected end of file for a cut-off one. The test needs room for a second copy, and events in the dump now exist in restore_test too, so drop it straight away. Now and then, repeat the new-server restore from Step 3 on another machine.

Larger Databases: mariadb-backup

mariadb-backup copies the data files of a running server with a "nearly non-blocking method" (overview). It reads them from disk, so it runs on the database server and needs room for a full copy of the data directory; on a Space-Node VPS you can add extra NVMe storage on the order form or upgrade to a bigger plan from the client area. Check the sizes, then back up and prepare:

sudo du -sh /var/lib/mysql; df -h /var/backups
sudo apt install mariadb-backup
sudo mariadb-backup --backup --target-dir=/var/backups/mariadb-full
sudo mariadb-backup --prepare --target-dir=/var/backups/mariadb-full

The target folder must be empty or not exist yet. From 10.11.7, mariadb-backup takes the user name from the system when --user is missing (options), so sudo logs it in as root. --prepare makes the files consistent: they were copied at different moments, and InnoDB would otherwise crash "to protect you from corruption" (full backup and restore). Prepare with the same MariaDB version that took the backup.

To restore, stop MariaDB and start from an empty data directory:

sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.old
sudo mkdir /var/lib/mysql
sudo mariadb-backup --copy-back --target-dir=/var/backups/mariadb-full --datadir=/var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

/var/lib/mysql is the default on Debian and Ubuntu; sudo mariadb -e 'SELECT @@datadir' shows yours. Delete /var/lib/mysql.old once the app works. MariaDB's overview says Percona XtraBackup is not supported on MariaDB; for MySQL, Ubuntu's MySQL guide points to MySQL Shell's dump utility and Percona XtraBackup.

Version Notes

  • What the distributions ship. On 30 October 2026, Ubuntu 24.04 had MariaDB 10.11.14 and MySQL 8.0.46, Debian 12 had MariaDB 10.11.18 and Debian 13 had MariaDB 11.8.6 (Launchpad, Debian).
  • The old names. On 10.11, mysqldump and mysql are links to mariadb-dump and mariadb. From 11.0, mysqldump is deprecated and gone from the Docker image, and 11.8 prints Deprecated program name when you call a tool by its old name (my_init.c). Use the mariadb names in scripts.
  • The sandbox line. Since 10.11.8 (May 2024) and the matching 10.6.18, 11.0.6, 11.1.5, 11.2.4 and 11.4.2 releases, every dump starts with /*M!999999\- enable the sandbox mode */, which stops the client from running commands that access the file system (release notes). Older mariadb clients stop there with ERROR at line 1: Unknown command '\-'. Skip the line: zcat appdb.sql.gz | tail -n +2 | sudo mysql appdb.
  • MySQL 8.0 and 8.4 no longer keep routines and events in the mysql schema, so their manuals say an --all-databases dump only includes them with --routines and --events.

If a Restore Fails

  • ERROR 1049 (42000): Unknown database 'appdb': the dump has no CREATE DATABASE line. Create the database, then load again.
  • ERROR at line 1: Unknown command '\-'.: the client is older than the dump; see the sandbox line above.
  • Operation CREATE USER failed for 'root'@'localhost': the users file was made without --insert-ignore. Make a new one, or load it with mariadb --force, which carries on past the accounts that exist.
  • The user specified as a definer ('appuser'@'localhost') does not exist: a view, trigger or routine names an account this server lacks. Load users.sql.gz from Step 2.
  • unexpected end of file, or no -- Dump completed on line: the file is cut short and can't be restored in full. Take a new dump, with enough free disk space.

FAQ

Do I have to stop my app to make a dump? No, not for InnoDB tables. With --single-transaction, the dump reads a consistent snapshot "without blocking any applications"; only schema changes such as ALTER TABLE should wait until it ends. Do stop the app for a restore or a server move.

Is a VPS snapshot enough? Not on its own. MariaDB's backup overview warns that block-level snapshots such as LVM's are "database-blind" and capture "a crash-consistent state, identical to a sudden power failure". Keep dumps as well; our snapshot guide shows how to take a snapshot while MySQL or MariaDB holds a read lock.

How do I lose less than a day of data? Turn on the binary log (log_bin in the server config), which records the changes to your data. A dump made with --master-data=2 notes the binary log position it matches, and mariadb-binlog replays the changes after it up to a moment you choose (point-in-time recovery).

Can I load a MariaDB dump into MySQL or a newer MariaDB? Usually. MariaDB's overview says a dump loads into other MariaDB versions, MySQL or even another database system, "assuming there are no version or DBMS-specific statements in the dump". A mariadb-backup copy has to be prepared with the version that made it, so take a dump before you switch versions.

Are backups included with a Space-Node VPS? Not in the plan price; you can arrange them through a support ticket. Your own off-server dumps still matter: you decide how often they run and how long they stay.

Sources

All opened on 30 October 2026: