+1 (541) 238-9429
QueryKings

Your backups are untested: a point-in-time recovery drill for MySQL

Most teams we meet have backups. Fewer have restores. The gap between the two is where the bad afternoons live: the snapshot schedule is green for three years, and then someone runs a DELETE without a WHERE clause at 14:12 and the first question — "how far back can we go, and how long will it take?" — has never been answered with a number.

This is a drill you can run in an afternoon. It ends with two measured figures: your real recovery time objective (RTO, how long a restore takes) and your real recovery point objective (RPO, how much data a restore loses). Both are usually worse than the policy document says, and both are fixable once you know them.

Know which disaster you are insuring against

Backups cover three unrelated failure modes, and a tool that handles one may not handle the others:

  • Host or storage loss. A replica or a multi-AZ standby covers this in seconds. A backup is the slower fallback.
  • Logical corruption. A bad migration, an application bug, a truncated table. Replication faithfully copies this to every replica within milliseconds. Only a backup plus a binlog chain gets you back to 14:11:59.
  • Account or region loss. Ransomware, a deleted AWS account, a compromised credential. Only a copy outside the blast radius — a different account, different region, immutable or object-locked storage — survives this.

High availability is not backup. A three-node cluster with automatic failover protects against the first case and does nothing for the second or third. If your entire recovery story is "we have replicas," you are insured against one hazard out of three.

Pick the backup method deliberately

Four realistic options, each with a cost worth stating out loud.

Logical dumps — mysqldump. Portable, readable, diffable, and able to restore a single table. Restore speed is the problem: a logical restore replays every INSERT and rebuilds every index, so a dataset that dumps in an hour can take five or more to load. Fine up to tens of gigabytes; painful beyond that. Always use --single-transaction on an all-InnoDB schema so you get a consistent snapshot without locking writes, and --source-data=2 (--master-data=2 on 5.7) so the binlog coordinates are recorded in the dump header. Without those coordinates you have a backup but no anchor for point-in-time recovery.

Parallel logical dumps — MySQL Shell's util.dumpInstance() / util.loadDump(). Same portability, multi-threaded, chunked, compressed, and with a load path that can defer secondary index creation. In our experience it is several times faster than mysqldump on both ends for large schemas, and it writes to object storage directly. If you are on 8.0 or later and still scripting mysqldump for a multi-hundred-gigabyte instance, this is the cheapest single improvement available to you.

Physical backups — Percona XtraBackup-class tools, or a filesystem/EBS snapshot of a stopped or frozen volume. Restore is a file copy plus crash recovery, which is dramatically faster at size, and the time is roughly linear in bytes rather than in rows and indexes. The cost: the backup is tied to the storage layout and usually the major version, and you cannot pull one table out of it without restoring the whole instance somewhere first.

Managed-platform snapshots — RDS and Aurora automated backups. Genuinely good, and point-in-time restore is built in within the retention window. Two things to understand about them: a restore creates a new instance, so your RTO includes provisioning, warm-up on a cold buffer pool, and the DNS or connection-string change your application needs; and automated backups live in the same account, so they do not answer the account-loss case. Copy a snapshot to a second account or region on a schedule if that risk is real for you.

A common sound answer is a combination: nightly physical or snapshot backup for speed of full recovery, a weekly logical dump for portability and single-table extraction, and a retained binlog stream for everything between.

The binlog chain is the half people forget

Point-in-time recovery is a backup plus every binary log written since it. If the binlogs expire before you notice the damage, your recovery point is the backup itself and nothing finer.

Check three settings:

SHOW VARIABLES LIKE 'log_bin';                 -- must be ON
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- retention, in seconds
SHOW VARIABLES LIKE 'binlog_format';           -- ROW, in almost all cases
SHOW BINARY LOGS;                              -- the chain you actually have on disk

Retention defaults to 30 days on 8.0, but plenty of instances have it cranked down to a day or two because someone once filled the data volume. That is a legitimate concern with an illegitimate fix: ship binlogs off the server continuously (mysqlbinlog --read-from-remote-server --raw --stop-never, or the platform's equivalent) rather than shortening the window. Binlogs on the same volume as the data directory are also not a backup — they die with the volume.

With the chain intact, recovery to just before the bad statement is:

# 1. restore the full backup into a scratch instance
# 2. find the offending event's timestamp or GTID
mysqlbinlog --base64-output=DECODE-ROWS --verbose \
  --start-datetime="2026-05-14 14:00:00" binlog.000317 | less

# 3. replay from the backup's coordinates up to the moment before it
mysqlbinlog --start-position=194 --stop-datetime="2026-05-14 14:11:59" \
  binlog.000315 binlog.000316 binlog.000317 | mysql -h scratch-host

If you run GTIDs, prefer --exclude-gtids or a stop at a specific GTID over a wall-clock stop: timestamps have one-second resolution, and a busy instance can put thousands of transactions inside that second.

The drill

Schedule two hours. Use a scratch instance nobody depends on, and treat it as a rehearsal, not a test you are trying to pass.

  1. Pick a target. "The orders table as it was 90 minutes ago, on a fresh host." Not "restore the backup" — a specific state at a specific time.
  2. Start the clock. Someone writes down the wall-clock time and nothing else.
  3. Restore from the artifact only. No shelling into production for a missing file, no asking the one engineer who remembers the flag. If the runbook is incomplete, that is the finding — write it down and continue.
  4. Replay binlogs to the target time, using the coordinates recorded in the backup.
  5. Verify with a query, not a feeling. Row counts against a known-good figure, CHECKSUM TABLE against production for a static table, a business-level query ("yesterday's order total") that a human can confirm.
  6. Stop the clock and record both numbers: elapsed time to usable data (your real RTO) and the gap between the last recovered transaction and the incident (your real RPO).

Run it once a quarter, and once after every major version upgrade or platform move. Both events change the restore path, and both are the classic way a working procedure goes stale.

What the drill usually finds

The same handful of things, in our experience:

  • Restore takes three to ten times longer than anyone guessed, nearly always because the backup is logical and index rebuilds dominate.
  • Binlog retention is shorter than the time it takes to notice a logical corruption. Detection lag is the number that matters, and it is often measured in days.
  • The restore procedure depends on undocumented steps or a person who happens to be on holiday.
  • The backup verifies as present but not as loadable — a truncated dump, a snapshot of a volume that was not quiescent, a compression step that silently failed.
  • Nobody has tested restoring a single table, which is what the actual incident will require nine times out of ten.

None of these are expensive to fix once named. All of them are expensive to discover at 14:15 with the site down.

If you would rather not discover them alone, a restore drill is a well-bounded piece of work — we will run one against your environment, produce the measured RTO and RPO, and leave you the runbook that reproduces it.