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
For information about using option files, see Section 22.214.171.124, “Using Option Files”.
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 a 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.
As a safety measure,
DELETE operations when
innodb_force_recovery is greater
than 0. As of MySQL 5.6.15, an
innodb_force_recovery setting of
4 or greater places
InnoDB in read-only mode.
Lets the server run even if it detects a corrupt page. Tries to make
SELECT * FROMjump over corrupt index records and pages, which helps in dumping tables.
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. As of MySQL 5.6.15, sets
Does not look at undo logs when starting the database:
InnoDBtreats even incomplete transactions as committed. This value can permanently corrupt data files. As of MySQL 5.6.15, sets
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. As of MySQL 5.6.15, sets
SELECT from tables to dump
them. With an
innodb_force_recovery value of 3
or less you can
CREATE tables. As of MySQL 5.6.27,
DROP TABLE is also supported with
greater than 3.
If you know that a given table is causing a crash on rollback, you
can drop it. If you encounter a runaway rollback caused by a
failing mass import or
you can kill the mysqld process and set
3 to bring the database up without the
rollback, and then
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