Databases Reference
In-Depth Information
Clustered index corruption
In the event of clustered index corruption, you might need to use the
innodb
_force_recovery
settings to dump the table (more on this later). Sometimes the
dump process crashes InnoDB; if this happens, you might need to dump ranges of
rows to skip the corrupted pages that are causing the crash. A corrupt clustered
index is a more severe problem than a corrupt secondary 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 might be
affected.
You can usually repair a corrupted secondary index without losing any data. However,
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 run-
ning. 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 the exact behavior of each option; we won't
duplicate that information here, but we will note that you can increase the value to as
high as
4
with little danger. At this setting, you might lose some data on pages that have
corruption; if you go higher, you might extract bad data from corrupted pages, or
increase the risk of a crash during the
SELECT INTO OUTFILE
. In other words, levels up
to 4 do no harm to your data, but they might miss opportunities to fix problems; levels
5 and 6 are more aggressive at fixing problems but risk doing harm.
When you set
innodb_force_recovery
to a value greater than
0
, InnoDB is essentially
read-only, but you can still create and drop tables. This prevents further corruption,
and it makes InnoDB relax some of its normal checks so it doesn't intentionally crash
when it finds bad data. In normal operations, this is a safeguard, but you don't want it
when you're recovering. If you need to force InnoDB recovery, it's a good idea to con-
figure MySQL not to allow normal connection requests until you're finished.
If InnoDB's data is so corrupt that you can't start MySQL at all, you can use Percona's
InnoDB Recovery Toolkit to extract data directly from the files. These tools are freely
available at
http://www.percona.com/software/
. Percona Server also has an option that
