14.2.5.2 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. However, it is possible that the corruption might cause SELECT * FROM tbl_name statements or InnoDB 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 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
Warning

Before using innodb_force_recovery ensure that you have a backup copy of your database in case you need to start over. You should always begin by setting innodb_force_recovery to a lower value. Incrementally increase the setting as required. Only use an innodb_force_recovery setting of 3 or greater on a production server instance after you have successfully tested the setting on separate physical copy of your database.

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 1 and 2. If you are able to dump your tables with an option value of at most 3, then you are relatively safe that only some data on corrupt individual pages is lost. 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.

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.