If there is database page corruption, you may want to dump your
tables from the database with
SELECT ... INTO
OUTFILE. Usually, most of the data obtained in this
way is intact. However, it is possible that the corruption might
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
are able to dump your tables. For example, you can add the
following line to the
[mysqld] section of
your option file before restarting the server:
[mysqld] innodb_force_recovery = 1
innodb_force_recovery 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
recovery, 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.
Let the server run even if it detects a corrupt page. Try to
SELECT * FROM
corrupt index records and pages, which helps in dumping
Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.
Do not run transaction rollbacks after recovery.
Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.
Do not look at undo logs when starting the database:
InnoDB treats even incomplete
transactions as committed. This value can permanently
corrupt data files.
Do not do the 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 them, or
CREATE 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 import or
ALTER TABLE. You can kill the
mysqld process and set
3 to bring the database up without the
DROP the table that is causing
the runaway rollback.