mysql 使用 Forcing InnoDB Recovery 恢複資料庫

標籤：mysql 使用 forcing innodb recovery 恢複資料庫

環境：

CentOS release 6.6 (Final)

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

現象：

資料庫掛掉，重啟報錯

The server quit without updating PID file (/var/lib/mysql/cm.data.dingkai.com.pid).[失敗]

日誌：

150721 12:38:37  InnoDB: Database was not 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 be corrupt or you may have copied the InnoDB

這說明庫在重啟啟動後需要做恢複，但是資料庫的log出現壞塊。

解決：

使用Forcing InnoDB Recovery 啟動庫並備份資料，重建立庫。

關於innodb_force_recovery 參數值的描述如下：

As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

• 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 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 this value, be 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: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.

• 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.

在my.cnf檔案配置參數 

innodb_force_recovery = 6

innodb_purge_thread=0

注意： innodb_purge_thread 參數的設定為0 ，以免在配置別的值後，資料庫啟動會一直報錯 InnoDB: Waiting for the background threads to start。

[[email protected] ~]# service mysql start

Starting MySQL (Percona Server)..[確定]

啟動成功，接下來dump 資料，接著重建庫就OK了!

本文出自 “蟲子” 部落格，請務必保留此出處http://worms.blog.51cto.com/969144/1676738

mysql 使用 Forcing InnoDB Recovery 恢複資料庫