How to verify MySQL backups

Why mysqldump backups need checking

A mysqldump that is interrupted still leaves a file behind. It may hold half your tables, or stop in the middle of a large insert. Because the output is plain SQL, nothing flags the problem until someone tries to restore it.

Step by step

1. Take a consistent dump

   For InnoDB tables, --single-transaction gives a consistent snapshot without locking writes. Include routines and triggers if your application uses them. Dumping a single database without --databases lets you restore it under a different name later.

   mysqldump --single-transaction --routines --triggers app > app.sql

2. Check the completion marker

   By default mysqldump writes a final comment when it finishes. If the last line is not the completion marker, the dump stopped early. The marker is missing when the dump uses --skip-comments or --compact.

   tail -n 1 app.sql
   # -- Dump completed on 2026-09-24 02:00:00

3. Count the tables in the dump

   Compare the number of CREATE TABLE statements with the number of tables in production.

   grep -c "^CREATE TABLE" app.sql

4. Restore into a scratch database

   Restore on a separate server when you can, or at least into a database that nothing else uses.

   mysql -e "CREATE DATABASE scratch_verify"
   mysql scratch_verify < app.sql

5. Compare row counts

   Use exact counts for critical tables. The TABLE_ROWS column in information_schema is only an estimate for InnoDB.

   mysql scratch_verify -e "SELECT COUNT(*) FROM orders;"

6. Drop the scratch database

   Delete the restored copy once the checks pass, since it contains production data.

   mysql -e "DROP DATABASE scratch_verify"

What a passing MySQL backup looks like

Treat a backup as verified only when all of these hold:

• The dump ends with the completion marker
• The CREATE TABLE count matches production
• The restore finishes without errors
• Critical tables have exact row counts close to production
• Stored routines and triggers your application needs are present

Compressed dumps

If you compress dumps, test the archive before anything else. gzip -t app.sql.gz reads the whole file and fails on corruption. Then check the completion marker with zcat app.sql.gz | tail -n 1.

How Senal Recover automates this

• Senal Recover creates mysqldump compatible backups on your schedule and inspects each file for the completion marker and its table definitions before it can be used for a restore.
• On the Business plan, tier two validation restores the dump into your own scratch database and compares tables and row counts, flagging missing, extra, and empty tables.
• Drift detection flags unusual changes in backup size or duration, and validation failures alert your team through email, Slack, or webhooks.