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
cause SELECT * FROM
statements or
tbl_nameInnoDB background operations to crash or
assert, or even cause InnoDB roll-forward
recovery to crash. In such cases, you can use the
innodb_force_recovery option to
force the 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 to the [mysqld] section of your option
file before restarting the server:
[mysqld] 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 can 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
structures.
1(SRV_FORCE_IGNORE_CORRUPT)Let the server run even if it detects a corrupt page. Try to make
SELECT * FROMjump over corrupt index records and pages, which helps in dumping tables.tbl_name2(SRV_FORCE_NO_BACKGROUND)Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.
3(SRV_FORCE_NO_TRX_UNDO)Do not run transaction rollbacks after recovery.
4(SRV_FORCE_NO_IBUF_MERGE)Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.
5(SRV_FORCE_NO_UNDO_LOG_SCAN)Do not look at undo logs when starting the database:
InnoDBtreats even incomplete transactions as committed.6(SRV_FORCE_NO_LOG_REDO)Do not do the log roll-forward in connection with recovery.
With this value, you might not be able to do queries other than a basic
SELECT * FROM t, with noWHERE,ORDER BY, or other clauses. More complex queries could encounter corrupted data structures and fail.If corruption within the table data prevents you from dumping the entire table contents, a query with an
ORDER BYclause might be able to dump the portion of the table after the corrupted part.primary_keyDESC
The database must not otherwise be used with any
nonzero value of
innodb_force_recovery.
As a safety measure, InnoDB prevents users
from performing INSERT,
UPDATE, or
DELETE operations when
innodb_force_recovery is
greater than 0.
You can SELECT from tables to
dump them, or DROP 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
innodb_force_recovery to
3 to bring the database up without the
rollback, then DROP the table that is causing
the runaway rollback.