InnoDB is generally pretty robust. It is built to be reliable, and it has a lot of built-in
sanity checks to prevent, find, and fix corrupted data—much more so than some
other storage engines. However, it can’t protect itself against everything.
At a minimum, InnoDB relies on unbuffered I/O calls and fsync( ) calls not return-
ing until the data is safely written to physical media. If your hardware doesn’t deliver
these, InnoDB can’t protect your data, and a crash can cause corruption.
Many InnoDB corruption problems are hardware-related (e.g., corrupted page writes
caused by power failures or bad memory). However, misconfigured hardware is a
much bigger source of problems in our experience. Common misconfigurations
include enabling the writeback cache on a RAID card that doesn’t have a battery
backup unit, or enabling the writeback cache on hard drives themselves. These mis-
takes will cause the controller or drive to lie and say the fsync( ) completed, when
the data is in fact only in the writeback cache, not on disk. In other words, the hard-
ware doesn’t provide the guarantees InnoDB needs to keep your data safe.
Sometimes machines are configured this way by default, because it gives better per-
formance—which may be fine for some purposes, but not for a transactional data-
base server. You should always check a machine if you didn’t set it up yourself.
You can also get corruption if you run InnoDB on network-attached storage (NAS),
because completing a fsync( ) to such a device just means the device received the
data. The data is safe if InnoDB crashes, but not necessarily if the NAS device
crashes.
Sometimes the corruption is worse than other times. Severe corruption can crash
InnoDB or MySQL, but less severe corruption might just mean some transactions are
lost because the log files weren’t really synced to disk.
How to recover corrupted InnoDB data
There are three major types of InnoDB corruption, and each requires a different level
of effort to recover the data:
Secondary index corruption
You can often fix a corrupt secondary index with OPTIMIZE TABLE; alternatively,
you can use SELECT INTO OUTFILE, drop and recreate the table, then use LOAD DATA
INFILE. These processes fix the corruption by building a new table, and hence
rebuilding the affected index.
Clustered index corruption
You may need to use the innodb_force_recovery settings to dump the table
(more on this later). Sometimes the dump process crashes InnoDB; if this hap-
pens, you may need to dump ranges of rows to skip the corrupted pages that are
causing the crash. A corrupt clustered index is more severe than a corrupt sec-
ondary index because it affects the data rows themselves, but it’s still possible to
fix just the affected tables in many cases.
Corrupt system structures
System structures include the InnoDB transaction log, the undo log area of the
tablespace, and the data dictionary. This type of corruption is likely to require a
complete dump and restore, because much of InnoDB’s inner workings may be
affected.
You can usually repair a corrupted secondary index without losing any data. How-
ever, the other two scenarios often involve at least some data loss. If you have a
backup, you’re probably better off restoring that backup rather than trying to extract
data from corrupt files.
If you must try to extract the data from the corrupted files, the general process is to
try to get InnoDB up and running, then use SELECT INTO OUTFILE to dump the data. If
your server has already crashed and you can’t even start InnoDB without crashing it,
you can configure it to prevent the normal recovery and background processes from
running. This might let you start the server and make a logical backup with reduced
or no integrity checking.
The innodb_force_recovery parameter controls which kinds of operations InnoDB
will do at startup and during normal operation. The normal value is 0, and you can
increase it up to 6. The MySQL manual documents