Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.5Mb
PDF (A4) - 39.5Mb
PDF (RPM) - 38.6Mb
HTML Download (TGZ) - 10.9Mb
HTML Download (Zip) - 11.0Mb
HTML Download (RPM) - 9.6Mb
Man Pages (TGZ) - 217.4Kb
Man Pages (Zip) - 327.1Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Forcing InnoDB Recovery

14.21.2 Forcing InnoDB Recovery

To investigate database page corruption, you might dump your tables from the database with SELECT ... INTO OUTFILE. Usually, most of the data obtained in this way is intact. Serious 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 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 = 1

For information about using option files, see Section 4.2.2.2, “Using Option Files”.

Warning

Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start 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 innodb_force_recovery setting of 4 or greater on a production server instance after you have successfully tested the setting on a separate physical copy of your database. When forcing InnoDB recovery, you should always start with innodb_force_recovery=1 and 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.

As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0. An innodb_force_recovery setting of 4 or greater places InnoDB in read-only mode.

  • 1 (SRV_FORCE_IGNORE_CORRUPT)

    Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

  • 2 (SRV_FORCE_NO_BACKGROUND)

    Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

  • 3 (SRV_FORCE_NO_TRX_UNDO)

    Does not run transaction rollbacks after crash recovery.

  • 4 (SRV_FORCE_NO_IBUF_MERGE)

    Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes. Sets InnoDB to read-only.

  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

    Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files. Sets InnoDB to read-only.

  • 6 (SRV_FORCE_NO_LOG_REDO)

    Does not do the redo 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. Sets InnoDB to read-only.

You can SELECT from tables to dump them. With an innodb_force_recovery value of 3 or less you can DROP or CREATE tables. DROP TABLE is also supported with an innodb_force_recovery value greater than 3, up to MySQL 5.7.17. As of MySQL 5.7.18, DROP TABLE is not permitted with an innodb_force_recovery value greater than 4.

If you know that a given table is causing a crash on rollback, you can drop it. If you encounter 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, and then DROP the table that is causing the runaway rollback.

If corruption within the table data prevents you from dumping the entire table contents, a query with an ORDER BY primary_key DESC clause might be able to dump the portion of the table after the corrupted part.

If a high innodb_force_recovery value is required to start InnoDB, there may be corrupted data structures that could cause complex queries (queries containing WHERE, ORDER BY, or other clauses) to fail. In this case, you may only be able to run basic SELECT * FROM t queries.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Todd Hall on October 17, 2018
Recently experienced the same issue, more than likely due to memory issues. Added innodb_force_recovery=1 to my.cnf file to start the database. Ran a mysqldump using: mysqldump --single-transaction --quick --flush-logs original_db > backup_db. This actually failed on the corrupted table that was the cause of all of the crashing. We removed the table -- it was a cache table anyway-- created the table and restarted mysql service. Database started up. Removed innodb_force_recovery=1 from my.cnf and restarted mysql service again. Everything has been working well, since. We stayed with the original database. Note: Dropping the table was not the only thing we had to do. You'll have to remove, or move the associated idb file to a different folder in order to recreate the table properly.