14.2.8.1. Forcing InnoDB Recovery
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. Even so, the corruption may cause SELECT
* FROM tbl_name statements
or InnoDB background operations to crash or
assert, or even make InnoDB roll-forward
recovery crash. However, you can force the
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 = 4
The allowable non-zero 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 structures.
-
1
(SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to
make SELECT * FROM
tbl_name jump over
corrupt index records and pages, which helps in dumping
tables.
-
2
(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 also 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:
InnoDB treats even incomplete
transactions as committed.
-
6
(SRV_FORCE_NO_LOG_REDO)
Do not do the log roll-forward in connection with recovery.
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.
The database must not otherwise be used with any
non-zero 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.
