MySQL: backup

A MySQL backup that has never been restored is not a backup, it is a hope. The previous article covered the memento , this one covers automated backup and the restore test.

The backup script

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
MYSQL_USER="backup_user"
MYSQL_PASS="password"

mkdir -p "$BACKUP_DIR/$DATE"

# List databases
DATABASES=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES" | grep -v Database | grep -v information_schema | grep -v test)

# Dump each database
for db in $DATABASES; do
    mysqldump -u $MYSQL_USER -p$MYSQL_PASS \
        --add-drop-table \
        --single-transaction \
        "$db" > "$BACKUP_DIR/$DATE/$db.sql"
    gzip "$BACKUP_DIR/$DATE/$db.sql"
done

# Clean backups older than 7 days
find "$BACKUP_DIR" -type d -mtime +7 -exec rm -rf {} \;

The --single-transaction is crucial: it starts an InnoDB transaction and dumps from a consistent snapshot without locking tables. Without this flag, mysqldump locks each table and your production application waits.

The cron

0 2 * * * /usr/local/bin/mysql-backup.sh > /dev/null 2>&1

2am, every day. The > /dev/null prevents cron from sending a mail on every successful run (cron only sends if stdout/stderr is not empty).

The restore test

The test nobody does and that takes 30 seconds:

# Create a temp database
mysql -u root -p -e "CREATE DATABASE test_restore"

# Restore the dump
gunzip -c /backup/mysql/$(date +%Y%m%d)/my_schema.sql.gz | mysql -u root -p test_restore

# Check the tables
mysql -u root -p -e "SHOW TABLES" test_restore

# Count rows in a critical table
mysql -u root -p -e "SELECT COUNT(*) FROM users" test_restore

# Drop the test database
mysql -u root -p -e "DROP DATABASE test_restore"

If the COUNT(*) returns a number close to what the application sees in prod, the backup is good. If the gunzip fails, the backup is corrupted and you just found out BEFORE you needed it.

The restore nobody tests

The script above does three things many backup scripts forget: --single-transaction (no lock), old backup cleanup (otherwise the disk fills) and gzip (50 MB of dump becomes 8 MB of gzip). The fourth thing, the restore test, is not in the script: it is in the calendar, once a month, manually.

[edit: 2026] mysqldump is still there, but modern servers have snapshots (LVM, ZFS, cloud snapshots) that are faster than the dump. The restore test principle has not moved: an untested backup is not a backup.

[edit: 2026] The Ansible implementation of all this: the mariadb role .