解決mysql innodb page corrupt一例

在一臺訂單slave上遇到以下報錯

InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 1133, file name deal_mysql-bin.000301
InnoDB: Starting in background the rollback of uncommitted transactions
090521 17:21:34  InnoDB: Rolling back trx with id 0 128093134, 1 rows to undo
090521 17:21:34  InnoDB: Started; log sequence number 48 925399944
InnoDB: !!! innodb_force_recovery is set to 1 !!!

InnoDB: Rolling back of trx id 0 128093134 completed
090521 17:21:35  InnoDB: Rollback of non-prepared transactions completed
090521 17:21:35 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.45-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 2096241.
InnoDB: You may have to recover from a backup.
090521 18:58:30  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex e3d3ad2
後面是page dump。。。。
InnoDB: End of page dump
090521 18:58:30  InnoDB: Page checksum 1674816815, prior-to-4.0.14-form. checksum 3310524232
InnoDB: stored checksum 3822300463, prior-to-4.0.14-form. stored checksum 3310524232
InnoDB: Page lsn 48 796923670, low 4 bytes of lsn at page end 796923670
InnoDB: Page number (if stored to page already) 2096241,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 31945
InnoDB: (index PRIMARY of table c2cdb/t_deal_info_ref_buyer_677)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 2096241.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.

隨後mysqld無法啟動--每次啟動時,在前滾,回滾完成之後，準備接受外部連線時丟擲上面的錯誤.

根據錯誤提示,先是重啟機器,以排除filesystem cache corrupt的可能
重啟後發現錯誤依舊, 於是根據連結提示,設定innodb_force_recovery=1後，強制啟動mysqld

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.

**表示即使mysqld發現有頁損壞，也會啟動

線上執行check table,發現所有表都提示 status ok

錯誤日誌中顯示,是某個主鍵索引頁壞了，於是將該表的資料匯出來後將表drop掉，再匯入表資料
mysql> alter table t_deal_info_ref_buyer_677 drop primary key;
ERROR 1030 (HY000): Got error -1 from storage engine

mysql> create table t_deal_info_ref_buyer_677_dmp as select * from t_deal_info_ref_buyer_677;
ERROR 1030 (HY000): Got error -1 from storage engine

mysql> select * from t_deal_info_ref_buyer_677 into outfile '/tmp/t_deal_info_ref_buyer_677.txt';
Query OK, 12813 rows affected (0.16 sec)

mysql> drop table t_deal_info_ref_buyer_677;
Query OK, 0 rows affected

作了上述操作後,將my.cnf中的innodb_force_recovery註釋掉，重新啟動mysqld成功

上述解決過程有個疑問

直接drop index 會讀索引頁，所以失敗
奇怪的是ctas也失敗 ,而select into outfile成功
同樣是select,ctas會失敗.