【原创】大叔问题定位分享（32）mysql故障恢复

时间：2022-05-06 17:25:21

mysql启动失败，一直crash，报错如下：

2019-03-14T11:15:12.937923Z 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 1118110825, calculated checksums for field1: crc32 2889511119/2044155182, innodb 4005355497, none 3735928559, stored checksum in field2 1244127832, calculated checksums for field2: crc32 2889511119/2044155182, innodb 952099433, none 3735928559, page LSN 4 2819295388, low 4 bytes of LSN at page end 2486405135, page number (if stored to page already) 3971, space id (if created with >= MySQL-4.1.1 and stored already) 1840
InnoDB: Page may be an index page where index id is 6640
2019-03-14T11:15:12.937950Z 0 [Note] InnoDB: Index 6640 is `GEN_CLUST_INDEX` in table `zabbix`.`history_uint`
2019-03-14T11:15:12.937955Z 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

看起来是文件损坏了，我有备库，所以希望主库尽快恢复，然后再恢复数据，直接移走有问题的frm和ibd，再启动，报错：

2019-03-14T11:23:37.246589Z 0 [ERROR] InnoDB: Tablespace 1840 was not found at ./zabbix/history_uint.ibd.
2019-03-14T11:23:37.246594Z 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
2019-03-14T11:23:37.247018Z 0 [ERROR] InnoDB: Cannot continue operation.

修改my.cnf

innodb_force_recovery=1

再重启正常，尝试重新新建表，但是各种报错：

mysql> CREATE TABLE `history` (

    ->   `itemid` bigint(20) unsigned NOT NULL,

    ->   `clock` int(11) NOT NULL DEFAULT '',

    ->   `value` double(16,4) NOT NULL DEFAULT '0.0000',

    ->   `ns` int(11) NOT NULL DEFAULT '',

    ->   KEY `history_1` (`itemid`,`clock`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ERROR 1813 (HY000): Tablespace '`zabbix`.`history`' exists.

mysql> alter table history discard tablespace;

ERROR 1146 (42S02): Table 'zabbix.history' doesn't exist

mysql> drop tablespace history;

ERROR 1529 (HY000): Failed to drop TABLESPACE history

mysql> drop table history;

ERROR 1051 (42S02): Unknown table 'zabbix.history'

完全没有办法操作，这时的解决方法是：

  • CREATE TABLE bad_table ENGINE=MyISAM ...
  • rm bad_table.ibd
  • DROP TABLE bad_table
  • CREATE TABLE bad_table ENGINE=INNODB ...

然后再恢复数据

也有可能报错时并没有指出具体出错的文件或者表，类似

2019-11-25T12:54:59.258844Z 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=0, page number=593]. You may have to recover from a backup.

这时需要使用mysqlcheck命令来检查某个数据库

# mysqlcheck -uroot -proot $dbname

也可以检查所有的数据库

# echo 'show databases'|mysql -uroot -proot|xargs -i mysqlcheck -uroot -proot {}

输出结果为两列，第二列是status，正常为OK，其他值为异常，找到异常的表，删除或者将数据导出后再导入，则问题修复；

参考：
https://*.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp

相关文章