MySQL InnoDB Corruption: Before You Force Recovery

InnoDB corruption usually announces itself in the MySQL error log: a message about database page corruption on disk or a failed file read, an assertion failure during startup, or a server that starts, crashes and restarts in a loop. It can affect one table's tablespace file, the shared system tablespace, or the redo log. Which of those it is decides how much can be saved, and every change made to the files afterwards makes that answer harder to find.

A crash-looping server keeps trying to write to the files it is failing to read. Stop it, and before changing anything, copy the whole data directory — the system tablespace (ibdata1), the redo and undo files, every table's .ibd file, and the configuration file — to a separate volume. If the storage itself looks unhealthy, take a block-level image instead of a file copy. From this point, all experiments should run against the copy, not the original.

MySQL's innodb_force_recovery setting accepts values from 1 to 6, each one telling InnoDB to skip more of its normal startup and consistency work so that the server will start at all. Its purpose is narrow: to start the server just long enough to dump the data that can still be read. MySQL's own documentation warns that values of 4 or higher can permanently corrupt data files, and that the setting should only be used in an emergency, after taking a backup.

A sensible approach is to start at 1, raise it one level at a time only if the server still will not start, and stop at the lowest value that works. Once it starts, export what you need with mysqldump or a per-table export, set the option back to 0, and rebuild on clean storage from that export. A server left running with force recovery enabled is not a healthy server.

Deleting the redo log files to make the server start is a common suggestion online, and it can turn a recoverable situation into an unrecoverable one, because changes that were logged but not yet applied are lost. Other common mistakes are re-initializing the data directory over the damaged one, restoring an old backup on top of the damaged files before copying them, and running OPTIMIZE TABLE or ALTER TABLE against a table that is already reporting errors.

InnoDB corruption is most often a storage problem rather than a MySQL problem: a failing disk, a RAID controller fault, a power loss during a write, a full volume, or a VM or SAN snapshot taken while the database was writing. Faulty memory can also cause it. Rebuilding onto the same storage without finding the cause tends to reproduce the corruption, so check the disks and the system logs as part of the recovery, not after it.

If the server will not start even at the lower recovery levels, if the dump fails or is missing tables, or if the system tablespace itself is damaged, the recovery source becomes the data files. Recovering tables directly from the .ibd and system tablespace files is something we have done in many cases, and we tell you exactly what is recoverable after a free assessment. That is the point to stop making changes to the original files and work from the copy or image taken at the start.