Your backup job exits zero every night. That is a fact about a file. It is not a claim that the file contains a database, that the database will load, or that what loads is what you had.

Most people find out which of those are true on the one morning it matters. This is the drill that tells you beforehand, run against a real system — 156 tables, 110,725 rows, MySQL 8.0 — and the interesting part is not whether the backup passed. It did. The interesting part is that the check itself reported 65 problems that did not exist.

The drill

Three steps, none of which may touch the machine you are protecting:

1. dump the live database
2. start a THROWAWAY server that holds nothing of yours
3. restore into it, and compare the two

Step two is the critical one. A restore into a system you already run is a meaningless test: you cannot tell what came from the dump and what was already there. A throwaway container has no history to contaminate the result.

mysqldump --single-transaction --quick --routines --triggers --events db > dump.sql
docker run -d --name drill -e MYSQL_ROOT_PASSWORD=drill mysql:8.0
docker exec -i drill mysql -uroot -pdrill restored < dump.sql

On our database that produced a 49.8 MB file in about a second and restored in five. Those two numbers are worth writing down every time you run this, because a restore time is the number you will be asked for when the room is tense, and it is the only one you cannot estimate afterwards.

Comparing properly

Three checks, each stronger than the last:

tables      did every table arrive
rows        does every table hold the same number
checksums   is the DATA the same, not merely the amount

Row counts cannot see a restore that loaded the right number of wrong rows. CHECKSUM TABLE catches that by hashing the contents. If the checksums match, the data matches.

Our result was clean — 156 tables to 156, 110,725 rows to 110,725, and zero checksum mismatches across all 156.

Then the check lied

Rather than running COUNT(*) against 156 tables one at a time, use a single query:

SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'yourdb';

Instant, one round trip, no table scans. Run it on both sides and it reported 54 tables mismatched — on the restore that had just proved byte-identical on every checksum.

A second check went the same way. A restore can replace every row correctly and still reset the AUTO_INCREMENT counter that decides the next id. Neither a row count nor a checksum will see it, so the counters have to be checked directly. Comparing them reported 11 counters drifted. Both numbers were wrong.

Why, and it is two separate problems

The first is staleness. information_schema serves cached statistics, and the default expiry is 86400 — twenty-four hours. Your verification query can be reading yesterday.

SET SESSION information_schema_stats_expiry = 0;

That fixed the counters. The 11 reported differences dropped to one, a table the scheduler had written to between the dump and the check. The drift was an illusion produced by comparing a live database against day-old statistics.

Turning off the cache solves the staleness problem and not the accuracy one. table_rows is an estimate. InnoDB samples index pages rather than counting, so turning off the cache makes the number newer, not correct. With the cache off, on the same database:

105 non-empty tables
 57 where the estimate disagrees with COUNT(*)   (54.3%)
    median error 1.7%, worst 100%
whole database: 102,860 estimated vs 110,729 actual

A median error under two percent sounds tolerable until you notice what the worst case is. Several tables holding exactly one row report zero:

api_clients             estimate 0   actual 1
maintenance_status      estimate 0   actual 1
newsletter_subscribers  estimate 0   actual 1
image_provider_secrets  estimate 0   actual 1

Those are not obscure tables. That is the row holding your only API client, and the row deciding whether the site is in maintenance. A verification built on this number will tell you they are empty, in the backup and in the original alike, and agree with itself perfectly while describing neither.

What to run instead

Count with COUNT(*), in one union rather than 156 queries:

SELECT 'posts' t, COUNT(*) n FROM posts
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL ...

Generate that list from information_schema — the table names there are exact; it is only the statistics that are guesses. On our database the union scans 156 tables and returns in about a second, which is a fair price for an answer that is true.

Then CHECKSUM TABLE in batches, and compare the AUTO_INCREMENT counters with the statistics cache disabled. Four checks, none of them expensive, and between them they cover arrival, quantity, content and continuity.

What the drill cannot see

This drill compares the restore against the dump file. An internally inconsistent dump — one that captured table A before a transaction and table B after it — restores perfectly and passes every check, faithfully reproducing a database state that never existed.

We tried to produce one. A writer committed paired rows into two related tables, continuously, while four dumps ran: two with --single-transaction and two without. Then we counted rows on each side of the relationship that had lost their partner.

without --single-transaction   0 torn rows
with    --single-transaction   0 torn rows

The zero was honest for two reasons. First, the direction matters. mysqldump reads tables in name order, so in this schema child was read before parent, and a tear would create parents without children rather than orphans. A check looking only for orphans would have missed a torn dump.

The second is that an earlier run of this same experiment also returned zero — because the writer had died on startup and nothing was writing at all. The counts never moved and the result looked like evidence. Now the script measures the writer before trusting the zero, and refuses to continue if it is not committing.

The tearing did not reproduce, and not for the reason usually given. For a single-database dump, mysqldump defaults to --lock-tables, which takes a read lock on every table at once. The default dump is already consistent. What it is not is concurrent:

writes landing, per second
  291   no dump running
   32   during a default dump
   93   during --single-transaction

Roughly three times as many writes get through. Both numbers sit far below the baseline because the dump competes for the same disk either way, so the comparison worth reading is between the two modes rather than against an idle server. Use the flag — but use it knowing you are buying uptime during the backup, not correctness of it.

How this was measured, and what it does not prove

One database, 156 tables, 110,725 rows, MySQL 8.0 in Docker, dumped and restored on the same host. Every figure is a count from that run.

A drill that passes proves the backup you tested restores. It says nothing about tomorrow's, which is why this belongs in a schedule rather than in a memory of the day someone ran it. It also says nothing about a backup that is corrupted after it is written — the restore is the test, so a file that has rotted in storage since fails this drill exactly as it should, and only when you next run it.

The estimate figures are for InnoDB. Other engines maintain exact row counts and would not show this at all, which is worth knowing before you assume the number is broken everywhere.

We restored on the same machine. That deliberately sidesteps the failures most likely to bite in an emergency: the backup is on a host you cannot reach, in a format the new machine cannot read, or behind a credential nobody has. Restoring to a separate machine is the harder drill; get this one working first.

And a restored database is not a restored site. What the dump does not contain is a separate measurement, and on our system it is larger than the dump.