Backups You've Actually Tested: The Third Leg of a VPS Deploy

This finishes a trilogy that was designed as one. Two Fridays ago, the deploy guide got a backend running on a VPS and said out loud that your schema was your problem. Last Friday, the Alembic guide solved that and left a harder one hanging — because it ended on the sentence that makes this article necessary: MySQL DDL does not roll back. A migration that goes wrong halfway through doesn't rewind. Your rollback plan is a restore, whether you've admitted that or not.

So this is the third leg: backups you have actually tested. And because this blog has a habit of publishing its own ledger, we ran the full rehearsal against our four production databases while writing this. One of them does not restore. We'll get to that; the procedure comes first, because it's the procedure that found it.

The Thesis, In One Sentence

A backup nobody has restored is a belief, not a backup. Everything below follows from that. Backup jobs are easy to write, easy to schedule, and easy to watch succeed forever — the exit code is zero, the file is there, the log says OK. None of that tests the only property that matters, which is whether the file can be turned back into a working database. That question has exactly one honest answer, and it is a rehearsal.

Part One: Take a Dump That Can Be Trusted

Start with the dump itself, because two flags separate a consistent snapshot from a plausible-looking one:

mysqldump --single-transaction --routines --triggers \
          --default-character-set=utf8mb4 mydb | gzip > mydb_20260807.sql.gz

--routines and --triggers are there because their absence is invisible until the restore. Neither is included by default; a dump without them looks complete, restores without error, and silently drops every stored procedure and trigger you had. You find out when something stops firing in production, which is the worst possible moment.

Part Two: Verify the Artifact (Necessary, Not Sufficient)

Our tooling verifies every dump the moment it's written — file exists, non-zero size, gzip -t integrity, SHA-256 recorded to a manifest alongside the file. That is genuinely worth doing: it catches a truncated write, a full disk, a corrupted archive. It's also where most teams stop, and stopping here is the trap. Every one of those checks passed on the database that turned out not to restore. A verified archive proves the bytes survived. It says nothing about whether the SQL inside them executes.

Part Three: The Rehearsal (This Is the Article)

The rehearsal is four steps, and the whole point is that it is non-destructive — you restore into a scratch schema, never over the live one. Anyone can run this today against production without risk:

# 1. A scratch schema. Never restore over the live database to "test" it.
mysql -e "CREATE DATABASE restore_rehearsal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# 2. Restore into it, and TIME IT. The number is the deliverable.
S=$(date +%s)
gunzip -c mydb_20260807.sql.gz | mysql --default-character-set=utf8mb4 restore_rehearsal
E=$(date +%s); echo "restore took $((E-S))s"

# 3. Compare against live: table count first, then row counts on tables you care about.
mysql -N -B -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='restore_rehearsal';"
mysql -N -B -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mydb';"

# 4. Drop the scratch schema. The rehearsal leaves nothing behind.
mysql -e "DROP DATABASE restore_rehearsal;"

Three things about that sequence deserve emphasis.

Time it, because nobody knows their number. “How long does a restore take” is the question you will be asked during an outage, by yourself, while deciding whether to restore or keep debugging. Guessing is miserable. Measuring takes one afternoon and the answer is usually reassuring: ours came back at 20 seconds for a 215 MB database and a handful of seconds for the smaller ones. Knowing that changes how you behave in a crisis — a 20-second rollback is a decision you can make calmly.

Compare counts, don't eyeball it. Table count catches the catastrophic failure; row counts on a few meaningful tables catch the subtle one. And expect a small delta on the live side: our rehearsal showed 2,228 users restored against 2,229 live. That difference isn't an error — it's a person who signed up after the 03:00 dump, and it is your recovery point objective made visible. Restoring last night's backup means losing whatever happened since. Seeing that as a number rather than a concept is half the value of rehearsing.

Run the app's own health check against the restored schema if you can. A restore that produces the right row counts can still be missing a trigger or a stored routine. Pointing a health endpoint at the scratch schema is the strongest check available and costs almost nothing once the schema is sitting there.

What Our Rehearsal Found

Here's the ledger, run this week against the four databases behind our products:

Database	Restore	Result
Companion app	20s	Clean — 45/45 tables, row counts match live
Dating app	Seconds	Clean — 34/34 tables, zero errors
Licensing	Seconds	Clean — zero errors
Game platform	—	FAILED — dies at table 112 of 264

That backup had been running nightly, succeeding nightly, and passing verification nightly for months. It would have failed on the night it was needed. The only reason we know is that someone finally ran the restore — which is the entire thesis of this article arriving, uninvited, in our own infrastructure.

Two smaller findings came out of the same afternoon, and they generalize. First: our nightly job covers four databases and we have six. Two production schemas were never in the list; their most recent dumps were 77 and 81 days old. Second, and worse: the weekly verifier checks the same hardcoded four names, so it had been cheerfully reporting “all backups verified OK” the entire time. A monitor that only knows about the things you remembered to tell it about will confirm your assumptions forever. If that sounds familiar, it's the same shape as a claim that stopped being true without anyone touching it.

Where the Backup Lives Matters More Than Its Format

One structural point we have to make against ourselves. Our dumps are written to /opt/<project>/db_backups on the same VPS as the database, with the last five kept. That is useful — it covers the overwhelmingly common case, which is not a datacenter fire but a bad migration, a wrong DELETE, or a deploy that mangled a table. For that, a local dump from three hours ago is exactly the right tool.

But it is not disaster recovery, and calling it that would be a lie of the kind this blog tries not to tell. A backup on the same disk as the database dies with the disk; a backup on the same server dies with the server. The fix is boring and has no vendor loyalty in it: get a copy off the host, on a schedule, to somewhere with different failure modes than the machine you're protecting. Object storage, another provider's box, an encrypted copy pulled down to hardware you own — the destination matters far less than the fact that losing the origin doesn't take the copies with it. Ours is a gap. We're stating it rather than papering over it.

Tie It Back: The Restore Is the Rollback

Return to the sentence that started this. MySQL DDL does not roll back — there is no transaction wrapping an ALTER TABLE that you can abort halfway. So when a migration goes wrong in production, your options are to fix forward or to restore, and if the restore is untested then you have exactly one option and it's the stressful one. A tested restore is what turns "we can't roll this back" into "we can roll this back, and it takes 20 seconds." That is the difference between a migration you're afraid to run on a Friday and one you can run on a Tuesday afternoon.

We ended up with a broken backup, two unbacked databases, and a verifier that lied — from one afternoon of running four commands against a scratch schema. That is a good trade, and it was available at any point in the previous several months to anyone who asked the question. Ask it this week. The maintenance tax is cheapest when you pay it on purpose.