To investigate database page corruption, you might dump your
tables from the database with
SELECT ... INTO
OUTFILE. Usually, most of the data obtained in this way
is intact. Serious corruption might cause
SELECT * FROM
InnoDB background operations to crash or
assert, or even cause
recovery to crash. In such cases, you can use the
innodb_force_recovery option to
InnoDB storage engine to start up
while preventing background operations from running, so that you
can dump your tables. For example, you can add the following line
[mysqld] section of your option file
before restarting the server:
[mysqld] innodb_force_recovery = 1
to a value greater than 0 in an emergency situation, so that you
InnoDB and dump your tables. Before
doing so, ensure that you have a backup copy of your database in
case you need to recreate it. Values of 4 or greater can
permanently corrupt data files. Only use an
of 4 or greater on a production server instance after you have
successfully tested the setting on separate physical copy of
your database. When forcing
you should always start with
only increase the value incrementally, as necessary.
innodb_force_recovery is 0 by
default (normal startup without forced recovery). The permissible
nonzero values for
innodb_force_recovery are 1 to 6.
A larger value includes the functionality of lesser values. For
example, a value of 3 includes all of the functionality of values
1 and 2.
If you are able to dump your tables with an
innodb_force_recovery value of 3
or less, then you are relatively safe that only some data on
corrupt individual pages is lost. A value of 4 or greater is
considered dangerous because data files can be permanently
corrupted. A value of 6 is considered drastic because database
pages are left in an obsolete state, which in turn may introduce
more corruption into B-trees
and other database structures.
Lets the server run even if it detects a corrupt
page. Tries to make
SELECT * FROM
corrupt index records and pages, which helps in dumping
Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.
Does not look at undo
logs when starting the database:
InnoDB treats even incomplete transactions
as committed. This value can permanently corrupt data files.
Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.
SELECT from tables to dump
tables even if forced recovery is used. If you know that a given
table is causing a crash on rollback, you can drop it. You can
also use this to stop a runaway rollback caused by a failing mass
ALTER TABLE: kill the
mysqld process and set
3 to bring the database up without the
DROP the table that is causing
the runaway rollback.
If corruption within the table data prevents you from dumping the
entire table contents, a query with an
be able to dump the portion of the table after the corrupted part.
If a high
value is required to start
InnoDB, there may be
corrupted data structures that could cause complex queries
BY, or other clauses) to fail. In this case, you may
only be able to run basic
SELECT * FROM t