Servers Tutorials • • 5-8 minutes

The backup restore test: how to know your MySQL backups actually work

Diego Cortés
Diego Cortés
Full Stack Developer & SEO Specialist
Share:
The backup restore test: how to know your MySQL backups actually work
Image generated with AI

"Backup completed" only says the command exited without errors. A backup restore test tells you whether your data comes back, how long it takes and whether the RTO you promised is real.

Why "Backup Completed" Proves Nothing

A 3 a.m. cron job that runs mysqldump and writes "ok" to a log is the most common backup in the world and also the most fragile. As long as the file grows and the process exits clean, nobody looks inside. The problem shows up on incident day, which is the worst day to find out the dump was incomplete.

The Four Failures That Only Show Up on Restore

A truncated dump because the disk filled mid-write. Missing objects: routines, triggers and events that were never requested. Encoding: a dump restored under a different sql_mode or charset inserts corrupted text and fails on unique indexes. And time: the backup is valid and restoring it takes six hours when the runbook promised one.

What RPO and RTO Mean in One Sentence

RPO is how much data you can afford to lose: with a daily dump, up to 24 hours. RTO is how long it takes to be operational again. The first is planned; the second is only known by measuring it, and measuring it is what this tutorial is about.

The Drill Rule: One Restore a Month, Into a Database That Is Not Production

Once a month, and always after changing MySQL versions, servers or schemas. The drill is not a theatrical simulation: it is the same dump you would use in a real emergency, restored into a lab database, verified with numbers and timed.

Step 1: The Dump That Can Actually Be Restored

The mysqldump Flags That Matter (and Why)

mysqldump \
  --single-transaction \
  --quick \
  --routines --triggers --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  --source-data=2 \
  --set-gtid-purged=OFF \
  --no-tablespaces \
  -h 127.0.0.1 -u backup_ro -p produccion \
  > /srv/backups/produccion-$(date +%F).sql

None of them is decorative and every one has a concrete reason.

--single-transaction: InnoDB's Consistent Snapshot

It opens a transaction with repeatable read isolation and reads every table from the same instant without blocking writes. Without it, the dump can capture the orders table before a payment and the payments table after it: it restores a reality that never existed.

--no-tablespaces and the PROCESS Privilege Since MySQL 8.0.21

Since MySQL 8.0.21, mysqldump tries to read tablespace information and needs the PROCESS privilege for that. A backup user should not have it: pass --no-tablespaces and the Access denied; you need (at least one of) the PROCESS privilege(s) error disappears. The privileges you do need are SELECT, SHOW VIEW for views and TRIGGER for triggers.

Routines, Triggers and Events: What You Lose If You Do Not Ask for Them

mysqldump does not include procedures, functions, triggers or events by default. A dump without --routines --triggers --events restores tables stripped of logic and the application fails in odd places: a calculation that never appears, an audit row that is never written, a scheduled task that does not exist.

The Binary Log Next to the Dump: --source-data=2

--source-data=2 is the new name for --master-data since MySQL 8.0.26 and it writes the binary log position into a comment in the dump; that comment is what later allows recovery to a specific instant. If the server uses GTID, add --set-gtid-purged=OFF: the GTID state injected by default gets in the way when restoring on another server.

Step 2: Restore Into a Lab Database

Create the Drill Database and Give It Its Own User

CREATE DATABASE drill_20261119
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'drill'@'localhost' IDENTIFIED BY 'a-long-password';
GRANT ALL PRIVILEGES ON drill_20261119.* TO 'drill'@'localhost';

The drill_ prefix is not superstition: it is the marker that stops someone from running the script against the real schema. The drill user only has rights on that database, so a wrong name fails instead of wiping production.

Restore Without Touching Production

time mysql -u drill -p drill_20261119 < /srv/backups/produccion-2026-11-19.sql

If the file carries USE statements or old database names, strip them instead of adapting the server. And check with df -h that at least twice the dump size is free: disk space is the first limit you hit and the most common cause of a failed drill with a perfectly good backup.

Step 3: Verify With Data You Cannot Fake

Row Counts: COUNT(*) and Not table_rows

information_schema.TABLES.table_rows is an optimizer estimate, not a count. Real verification means running COUNT(*) table by table on both databases.

mysql -N -B -u drill -p -e "SELECT table_name
  FROM information_schema.tables
  WHERE table_schema='produccion' AND table_type='BASE TABLE'" > /tmp/tables.txt

while read t; do
  src=$(mysql -N -B -u drill -p produccion -e "SELECT COUNT(*) FROM \`$t\`")
  dst=$(mysql -N -B -u drill -p drill_20261119 -e "SELECT COUNT(*) FROM \`$t\`")
  [ "$src" = "$dst" ] && echo "ok $t $src" || echo "FAIL $t $src/$dst"
done < /tmp/tables.txt

CHECKSUM TABLE on Tables That Do Not Change

CHECKSUM TABLE computes a checksum of the contents and compares source and restore in a single query. The table takes a read lock during the operation, so use it on catalogs or during low-traffic hours, not on the orders table at peak time.

CHECKSUM TABLE produccion.countries, drill_20261119.countries;

Tables That Do Change: Comparing Them Without Losing Your Mind

On a table with constant writes the checksum will never match. What you can compare is a closed range: rows older than the dump instant, by maximum id and by the sum of a stable numeric column.

Step 4: Measure the Real RTO

Dump Time Is Not Restore Time

Dumping is usually faster than restoring, because restoring means building indexes, checking foreign keys and writing pages to disk. Measuring the dump and calling it RTO is the most frequent paperwork mistake in a recovery plan.

Time the Drill and Keep the Series

start=$(date +%s)
mysql -u drill -p drill_20261119 < /srv/backups/produccion-$TODAY.sql
end=$(date +%s)
echo "$TODAY,$((end-start))" >> /var/log/drill-rto.csv

A single number is useless; the monthly series is not: it shows whether RTO grows with the database, whether a schema change blew it up and how much headroom is left before breaking the promise.

Step 5: Automate It With an Alert

The Script: Dump, Restore, Verify and Exit Code

#!/usr/bin/env bash
set -euo pipefail
DB=drill_$(date +%Y%m%d)
failures=0

mysql -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB CHARACTER SET utf8mb4;"
timeout 7200 mysql -u drill -p "$DB" < "/srv/backups/produccion-$(date +%F).sql" || failures=1

for t in $(cat /tmp/tables.txt); do
  a=$(mysql -N -B produccion -e "SELECT COUNT(*) FROM \`$t\`")
  b=$(mysql -N -B "$DB" -e "SELECT COUNT(*) FROM \`$t\`")
  [ "$a" = "$b" ] || { echo "FAIL $t: $a vs $b"; failures=1; }
done

exit $failures

The value is at the end: it exits non-zero when something does not add up, and that turns the drill into a test. A script that always exits zero warns you about nothing.

Scheduling It in Laravel 13 or in cron

In Laravel 13 the scheduler runs it with Schedule::command('backup:drill')->monthlyOn(1, '03:30') and can email the output on failure. If the application is not the natural place, a cron entry with MAILTO does the same. What cannot happen is the drill running and nobody learning the result.

What Has to Happen for the Alarm to Fire

Three conditions: the restore exits with an error, any row count does not match, or the elapsed time crosses the defined threshold. The third is the one almost nobody configures and the one that prevents discovering on incident day that RTO doubled four months ago.

The Part Almost Nobody Tests: Recovering to a Specific Moment

Replaying the Binary Log Up to the Second Before the Disaster

Restoring the dump leaves you at the instant of the dump. To reach 03:14:59, just before the accidental DELETE, you apply the binary logs from the position recorded in the dump.

mysqlbinlog --stop-datetime="2026-11-19 03:14:59" /var/log/mysql/binlog.000042 \
  | mysql -u drill -p drill_20261119

Proving the Binary Log Chain Has No Gaps

Point-in-time recovery only works if the binary logs are consecutive. Compare SHOW BINARY LOGS with what is on disk and with expire_logs_days: if the server purges logs before your retention expires, the chain breaks and point-in-time recovery silently stops existing.

File Backups: restic and the Check That Actually Reads the Data

restic check Is Not restic check --read-data

restic check validates repository structure and indexes but does not read pack contents. Proving the data is intact requires --read-data or a subset: restic check --read-data-subset=10% inspects part of the repository without taking hours.

Restoring a Folder Into a Temporary Directory

restic restore latest --target /tmp/drill --include /var/www/app/storage

Restoring one path is quick and answers the only question that matters: the files are there and they open.

The Traps That Show Up When You Need Them

DEFINER, sql_mode and Charset: The Dump That Restores and Then Fails

Triggers created by a user that does not exist on the target fail because of DEFINER. A stricter sql_mode rejects rows production accepted with default zeros. A different charset breaks accents and unique indexes. All three are caught by the drill and none of them by reading the backup log.

Client and Server on Different Versions

A modern mysqldump against an old server, or the other way round, can emit statements the target does not understand. Pin the client version and write it next to the dump: a year from now that line is worth gold.

Partial Restore: Bringing Back One Table Without Dropping the Rest

mysqldump --single-transaction --no-create-info --no-tablespaces \
  produccion orders > /tmp/orders-data.sql

When only one table was lost, dumping its data without CREATE TABLE lets you reload it over the existing structure without touching the rest. That is the difference between a contained incident and a full restore at three in the morning.

Conclusion

A backup is judged by its restore, not by the log line. Set up a monthly drill, verify with row counts and checksums, measure the real RTO and make the script return an error when something does not add up: that separates a backup from a folder full of hope. If you want to keep tightening your server operations, this blog has more MySQL and production deployment guides.

Categories