ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 解决mysql innodb page corrupt一例

解决mysql innodb page corrupt一例

原创 Linux操作系统 作者:tengrid 时间:2009-05-26 11:29:33 0 删除 编辑

在一台订单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会失败.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94384/viewspace-604185/,如需转载,请注明出处,否则将追究法律责任。

上一篇: yes
请登录后发表评论 登录
全部评论

注册时间:2009-05-18

  • 博文量
    136
  • 访问量
    375923