MySQL uses forcing InnoDB Recovery to recover the database

Environment:

CentOS Release 6.6 (Final)

Server Version:5.5.42-37.1-log Percona Server (GPL), Release 37.1, Revision 39acee0

Phenomenon:

Database hangs, restart error

The server quit without updating PID file (/var/lib/mysql/cm.data.dingkai.com.pid). Failed

Log:

150721 12:38:37 Innodb:database was wasn't shut down normally!

Innodb:starting crash recovery.

innodb:reading tablespace information from the. ibd files ...

innodb:restoring possible Half-written data pages from the Doublewrite

Innodb:buffer ...

Innodb:doing recovery:scanned up to log sequence number 9044491443

150721 12:38:37 InnoDB:Error:page 7 log sequence number 17979997238

Innodb:is in the future! Current system log sequence number 9044491443.

Innodb:your database may corrupt or we have copied the InnoDB

This means that the library needs to be restored after the reboot, but the log of the database has a bad block.

Solve:

Use the forcing InnoDB Recovery to start the library and back up the data to rebuild the library.

A description of the Innodb_force_recovery parameter values is as follows:

As a safety measure,InnoDBPreventsINSERT,UPDATE, orDELETEOperations wheninnodb_force_recoveryis greater than 0.

• 1(SRV_FORCE_IGNORE_CORRUPT)

  lets the server run even if it detects a corrupt page. Tries to Make select * from 

• 2   (srv_force_no_background )

  prevents the master Thread and any purge threads from running. If a crash would occur during the purgeoperation, 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 tablestatistics. This value can permanently corrupt data files. After using the this value, is prepared to drop and recreate all secondary indexes.

• 5   (srv_force_no_undo_log_scan )

  does not look At undo Logs when starting the Database: 

• 6   (srv_force_no_log_redo )

  Does not does the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in a obsolete state, which in turn could introduce more corruption to b-trees and other database St Ructures.

Configuration parameters in the My.cnf file

Innodb_force_recovery = 6

Innodb_purge_thread=0

Note: The Innodb_purge_thread parameter is set to 0, so that the database startup will be error innodb:waiting for the background threads to start after the configuration of the other values.

[[Email protected] ~]# service MySQL start

Starting MySQL (Percona Server): Determine

Start successfully, then dump the data, then rebuild the library is OK!

This article is from the "Bug" blog, please be sure to keep this source http://worms.blog.51cto.com/969144/1676738

MySQL uses forcing InnoDB Recovery to recover the database