Back up and restore a MySQL/MariaDB database

A database is backed up with a logical dump, not by copying its files while it is writing. The dump must be consistent, compressed, stored off the VPS and, above all, restored at least once. This procedure uses the MariaDB tools in Debian 13 and also applies to MySQL.

Produce a consistent dump

For InnoDB, --single-transaction opens a consistent snapshot without blocking writes. --quick streams rows instead of loading a table into memory. --routines, --triggers and --events keep procedures, triggers and scheduled jobs.

sudo install -d -m 700 /var/backups/mysql
sudo sh -c 'mariadb-dump --defaults-extra-file=/etc/backup-mysql.cnf \
  --single-transaction --quick --routines --triggers --events \
  --default-character-set=utf8mb4 app \
  | gzip > /var/backups/mysql/app-$(date +%F).sql.gz'

mariadb-dump is the native tool; mysqldump remains an alias. Do not put the password on the command line: it is visible in ps. Create /etc/backup-mysql.cnf, readable by root only:

[client]
user=backup
password=strong-password

sudo chmod 600 /etc/backup-mysql.cnf. For a non-root user, use $HOME/.my.cnf with the same keys.

A logical dump is not atomic for MyISAM. If some tables still use that engine, plan a maintenance window or convert them to InnoDB before relying on --single-transaction.

Restore without overwriting production

sudo mariadb -e "CREATE DATABASE test_restore"
gunzip -c /var/backups/mysql/app-2026-11-07.sql.gz | sudo mariadb test_restore

Compare row counts for critical tables and run the application against this copy. Restoring directly into production while the application is writing creates duplicates or inconsistencies. For a real restore: stop the application, back up the current state, restore, verify, then restart.

Automate and retain

A short script beats an unreadable cron command:

#!/bin/sh
set -eu
umask 077
exec 9>/run/lock/backup-app.lock
flock -n 9 || exit 0
mariadb-dump --defaults-extra-file=/etc/backup-mysql.cnf \
  --single-transaction --quick --routines --triggers \
  --default-character-set=utf8mb4 app \
  | gzip > /var/backups/mysql/app-$(date +%F).sql.gz
find /var/backups/mysql -type f -name '*.sql.gz' -mtime +14 -delete

Schedule it with cron (30 2 * * *) and redirect output to a log. flock prevents two simultaneous runs; the credentials file avoids any prompt, which is essential from cron. Then copy the archives off site with rsync or rclone, and encrypt them before transfer with age or gpg. A dump on the same disk as the database does not survive the loss of the VPS.

Test the backup

A backup that has never been restored is an assumption. Test monthly: download the archive, decompress it, import it into a throwaway database, check CHECKSUM TABLE or a row count, and record the duration. Watch disk space: a full dump can exceed the size of the compressed database, and a truncated archive only fails when you need it.