How to recover a corrupted production database

TL;DR

• Stop writing to the database. Every write to a corrupted system risks turning recoverable damage into permanent loss.
• Snapshot everything now, file-level copy or volume snapshot, before any repair attempt, so every recovery path stays open.
• Don’t run repair tools blind. REPAIR TABLE, fsck, and friends can discard data to make errors go away. They’re last resorts, not first moves.
• Verify a backup before you need to trust it. Restore to a separate machine and check it, never restore over the damaged original.
• Corruption recovery is the one incident where a wrong first move is irreversible. If the data matters, get a senior engineer on it inside the first hour.

First: what corruption actually looks like

Corruption rarely announces itself politely. Common tells:

• Checksum or page-verification errors in the logs (invalid page in block…, InnoDB: Database page corruption…)
• A table that crashes the server or the session when queried
• Queries returning obviously wrong or inconsistent results, an index says a row exists, the table says it doesn’t
• The database refusing to start after a crash, power loss, or disk-full event
• Backups or dumps failing partway with read errors

Common causes: sudden power loss or kill -9 mid-write, disk or filesystem failure, running out of disk, faulty memory, bugs in the storage layer, and occasionally a human deleting the wrong files. The cause matters, because if the underlying disk is dying, everything below must happen on a copy, not in place.

Step 1, Stop the bleeding (first 15 minutes)

1. Quiesce writes. Put the application in maintenance mode or block writes at the load balancer or connection layer. Reads may be fine; writes compound damage.
2. Do not restart in a loop. Repeated crash-recovery attempts against damaged files can widen the damage. One controlled restart to observe the error is information; five hopeful ones are vandalism.
3. Preserve the logs. Copy out the database error log now, it identifies which files, pages, or tables are affected, and it may rotate away.

Step 2, Copy before you touch (next 30 minutes)

Take a file-level backup or volume snapshot of the database as it sits, corruption and all. On cloud volumes, snapshot the disk. On bare metal, rsync/copy the data directory with the server stopped.

This is the step people skip under pressure, and it’s the one that separates recoverable incidents from unrecoverable ones. With a pristine copy of the damaged state, every mistake from here is reversible. Without it, your first failed repair attempt is final.

Step 3, Assess the damage

Work from the copy or a read path, not with repair commands.

• Scope it. Is it one index, one table, or the whole cluster? A corrupted secondary index is a non-event (drop and rebuild it). A corrupted system catalog or WAL is a serious incident.
• Check the hardware. SMART status, kernel logs, filesystem errors. Restoring onto a failing disk just schedules the sequel.
• Inventory your recovery assets: latest full backup, WAL/binlog archives since that backup, any replicas. A healthy replica that stopped replicating before the corruption hit is often the fastest path back.

Step 4, Choose a recovery path (least to most invasive)

1. Rebuild what can be rebuilt. Corrupted secondary index → drop and recreate. Done, verify, move on.
2. Fail over to a clean replica. If a replica predates or escaped the corruption, promote it and rebuild the old primary from scratch. Check the replica first, corruption that arrived via the replication stream (bad writes, not bad disks) will be there too.
3. Point-in-time restore. Restore the last good full backup, then roll forward through WAL/binlogs to just before the corruption event. This is the gold standard when the damage came from a bad write or deletion, you keep everything up to the moment things went wrong.
4. Salvage from the damaged files. Dump table-by-table around the damage, use page-level inspection tools to extract rows, accept documented losses. Slow, delicate, and very much senior-engineer territory.
5. Repair tools, eyes open. MySQL’s REPAIR TABLE/innodb_force_recovery, PostgreSQL’s zero_damaged_pages: these buy readability by discarding damaged data. Use only on a copy, only to extract what other paths can’t reach, and never as step one.

Whatever path you take: restore to a fresh location and verify before cutover. Row counts against expectations, application smoke tests, integrity checks. Then switch traffic.

The pitfalls that turn bad into unrecoverable

• Running repair tools before taking a copy, the classic irreversible move.
• Restoring the backup over the damaged original, then discovering the backup was itself incomplete.
• Trusting a backup that has never been restore-tested. “We have backups” and “we can restore” are different sentences.
• Deleting “temporary” files (WAL, binlogs, undo logs) to free disk space, you may be deleting the roll-forward that would have saved you.
• Rebuilding on the same failing disk.

When to call for help

Call in a senior engineer immediately if any of these is true: the corruption touches system catalogs or the transaction log; there’s no verified backup; the disk itself is failing; the data has financial, legal, or medical weight; or the recovery path requires tools you’d be running for the first time, in production, today. One hour of expertise at the start routinely saves days, and sometimes the dataset.