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, starting from MySQL 3.23.44,
you can use the
innodb_force_recovery option to
InnoDB storage engine to start up,
and you can also prevent 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 = 4
Before MySQL 4.0, use this syntax instead:
[mysqld] set-variable = innodb_force_recovery=4
innodb_force_recovery is 0 by
default (normal startup without forced recovery) The permissible
nonzero values for
innodb_force_recovery follow. A
larger number includes all precautions of smaller numbers. If
you are able to dump your tables with an option value of at most
4, then you are relatively safe that only some data on corrupt
individual pages is lost. A value of 6 is more drastic because
database pages are left in an obsolete state, which in turn may
introduce more corruption into B-trees and other database
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.
Do not look at undo logs when starting the database:
InnoDB treats even incomplete
transactions as committed.
Do not do the log roll-forward in connection with recovery.
The database must not otherwise be used with any
nonzero value of
As a safety measure,
InnoDB prevents users
DELETE operations when
greater than 0.
Starting from MySQL 3.23.53 and 4.0.4, you can
SELECT from tables to dump them,
CREATE a table
even if forced recovery is used. If you know that a certain
table is causing a crash in rollback, you can drop it. You can
use this also 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.