mysql的page有损坏_解决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会失败.