Automate MySQL/MariaDB Backups With Rotation and a Restore Test

Before you run this

Two scripts here. The first (mysql-backup.sh) makes a gzipped mysqldump of every database on the server, drops the file in /var/backups/mysql, and deletes any dump older than a retention window. The second (mysql-restore-test.sh) proves a backup is usable by loading the newest dump for one database into a throwaway database, counting the tables, and dropping that throwaway again.

• Privileges: both run as root. I rely on MariaDB's default unix_socket authentication on Debian/Ubuntu, so root on the OS connects as root in MariaDB with no password. That's why there's no password in these scripts. The backup itself only reads; the restore test needs CREATE/DROP/INSERT, which is why it runs as an admin.
• What changes, and what's irreversible:
  • The backup script only writes dump files — safe. But its rotation step deletes old dumps permanently (find … -delete). Get RETENTION_DAYS right before you schedule it.
  • The restore-test script creates and then DROPs a database named restore_test_<db>. A DROP DATABASE is irreversible. Make certain you have no real database with that name — the script would destroy it.
• Test first: read both scripts, then run them by hand on a test VM or a non-production replica, not on your live primary the first time. Run the restore test against one small database before you trust it.

Assumptions: Debian 12 or Ubuntu 22.04, MariaDB 10.x from the distro packages, bash, and cron. On RHEL/Alma the ideas are identical; the package is mariadb-server and you may need to create /var/backups yourself. On modern MariaDB mysqldump is a symlink to mariadb-dump — either name works.

The backup script

Save this as /usr/local/sbin/mysql-backup.sh and chmod 700 it (it runs as root and writes credentials-free dumps, so keep it and its output directory tight):

#!/usr/bin/env bash
set -euo pipefail   # fail on error, unset var, or a broken pipe (so a failed dump fails the script)

BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=14                     # delete dumps older than this many days
DATE="$(date +%F_%H%M%S)"

mkdir -p "$BACKUP_DIR"
chmod 700 "$BACKUP_DIR"

# List real databases, skipping the virtual/system schemas mysqldump can't dump normally.
DBS="$(mysql -N -e 'SHOW DATABASES;' \
  | grep -Ev '^(information_schema|performance_schema|sys)$')"

for db in $DBS; do
  out="${BACKUP_DIR}/${db}_${DATE}.sql.gz"
  # --single-transaction: consistent snapshot for InnoDB without locking the whole server.
  # --routines/--triggers/--events: include stored procedures, triggers, and scheduled events.
  mysqldump --single-transaction --quick \
    --routines --triggers --events "$db" \
    | gzip -c > "${out}.tmp"
  mv "${out}.tmp" "$out"              # promote to the real name only after a clean run
  echo "Dumped ${db} -> ${out}"
done

# Rotation: remove dumps older than the retention window. This deletion is permanent.
find "$BACKUP_DIR" -name '*.sql.gz' -type f -mtime +"$RETENTION_DAYS" -delete

echo "Backup run complete."

Two deliberate choices worth calling out. I dump each database with its bare name as a positional argument rather than --databases "$db". That produces a dump without CREATE DATABASE/USE statements, so I can later load it into any target database — which is exactly what makes the restore test safe. And I write to ${out}.tmp first and mv into place only on success, so a dump that dies halfway (disk full, network blip on a remote server) never leaves a half-file that looks like a good backup.

--single-transaction gives you a consistent snapshot for InnoDB tables. If you have MyISAM tables their dump won't be point-in-time consistent; that's a property of MyISAM, not a bug here.

Make it executable:

sudo chmod 700 /usr/local/sbin/mysql-backup.sh

Schedule it with cron

Create /etc/cron.d/mysql-backup:

# m h dom mon dow user command
30 2 * * * root /usr/local/sbin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

That runs at 02:30 daily as root and appends both output and errors to a log. A systemd timer is the other mainstream option if you prefer it — it gives you systemctl status and journald logging — but I'm keeping cron here since it's one file.

The restore test

A backup you've never restored is a guess. This script takes one database name, finds its newest dump, loads it into a scratch database, checks that tables actually landed, and cleans up. Save it as /usr/local/sbin/mysql-restore-test.sh:

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
SOURCE_DB="${1:?Usage: mysql-restore-test.sh <database-name>}"
SCRATCH_DB="restore_test_${SOURCE_DB}"   # throwaway target — must NOT match a real database

# Newest dump for this database.
LATEST="$(ls -1t "${BACKUP_DIR}/${SOURCE_DB}_"*.sql.gz | head -n1)"
echo "Testing restore of: $LATEST"

# Verify the gzip stream itself before we bother restoring.
gunzip -t "$LATEST"

# Fresh, empty scratch database.
mysql -e "DROP DATABASE IF EXISTS \`${SCRATCH_DB}\`; CREATE DATABASE \`${SCRATCH_DB}\`;"

# Load the dump into the scratch database (dump has no CREATE DATABASE/USE, so this is safe).
gunzip -c "$LATEST" | mysql "$SCRATCH_DB"

# Sanity check: how many tables actually restored?
COUNT="$(mysql -N -e \
  "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='${SCRATCH_DB}';")"
echo "Restored ${COUNT} table(s) into ${SCRATCH_DB}."

# Clean up the throwaway database.
mysql -e "DROP DATABASE \`${SCRATCH_DB}\`;"
echo "Restore test passed; scratch database dropped."

Run it against one database by hand:

sudo /usr/local/sbin/mysql-restore-test.sh yourdbname   # replace yourdbname

A table count of zero, or a non-zero exit, means the newest dump is bad — investigate before you need it. Because the scratch database is dropped at the end, this is repeatable and leaves nothing behind. It does briefly consume disk and I/O equal to the restored data, so run it off-hours on a busy box, or point it at a replica or a spare instance.

If you want this to run automatically, schedule it the same way as the backup, a while after it (say 03:30), and have it email or alert on a non-zero exit. I'd keep the automated version scoped to your most important database rather than looping every schema, so a routine test can't accidentally hammer the server.

Verify it worked

After the first scheduled run:

# Files exist, are recent, and non-trivially sized:
ls -lh /var/backups/mysql/

# The run logged success:
tail -n 20 /var/log/mysql-backup.log

# Each archive is a valid gzip stream (0 = good):
for f in /var/backups/mysql/*.sql.gz; do gunzip -t "$f" && echo "OK  $f"; done

Then run the restore test above for at least one database. That end-to-end check — dump → gzip integrity → actual reload → table count — is the only proof that matters.

Confirm rotation once a dump ages past RETENTION_DAYS: the oldest files should disappear. To dry-run the deletion without removing anything, replace the -delete in a copy of the command with -print and eyeball the list first:

find /var/backups/mysql -name '*.sql.gz' -type f -mtime +14 -print

Undo / roll back

• Stop the schedule: sudo rm /etc/cron.d/mysql-backup (or disable the systemd timer). The scripts stay on disk; nothing further runs.
• Remove accumulated backups: delete the files under /var/backups/mysql — but only when you're sure you no longer need them, since that's permanent.
• A failed restore test cleans up after itself; if you interrupted it mid-run, the scratch database may linger. Confirm the name, then drop it: sudo mysql -e "DROP DATABASE \restore_test_yourdbname`;"`.

For the exact behaviour of every flag, see the MariaDB Knowledge Base pages for mariadb-dump/mysqldump and the mysql client; both document --single-transaction, --routines, --triggers, and --events in full.