利用innodb_force_recovery修复MySQL数据页损坏

前言

早上了发现数据库连接不了，查询也有问题，检查发现报错日志如下：

2021-05-11T03:15:17.830648Z 2 [Note] InnoDB: Uncompressed page, stored checksum in field1 541880161, calculated checksums for field1: crc32 2510287112/3406680281, innodb 1824071848, none 3735928559, stored checksum in field2 2032165740, calculated checksums for field2: crc32 2510287112/3406680281, innodb 2124309173, none 3735928559,  page LSN 2037603642 538992996, low 4 bytes of LSN at page end 1768255092, page number (if stored to page already) 1682071916, space id (if created with >= MySQL-4.1.1 and stored already) 808333877
2021-05-11T03:15:17.830662Z 2 [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.
2021-05-11T03:15:17.830675Z 2 [ERROR] [FATAL] InnoDB: Unable to read page [page id: space=532, page number=1] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/5.7/en/ for more details. Aborting...

百度一波，发现是MySQL数据页损坏尝试通过innodb_force_recovery参数来恢复

innodb_force_recovery

innodb_force_recovery参数有6个选项具体如下

(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。

(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。

(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。

(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。

(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。

(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

注意: 当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的

# 尽量用最小的参数尝试启动，大的包含小的
innodb_force_recovery=1

修改参数以后重启

修复

可以尝试使用MySQL的bin目录下的mysqlcheck.exe工具来尝试发现并修复MyISAM表的损坏数据页。

如果仅是MyISAM数据页损坏，修复后，删除my.ini中的innodb_force_recovery参数，尝试启动。

[root@mysql data]# mysqlcheck -A -uroot -p
Enter password:
ht.tb OK
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK

如果是InnoDB数据表发生了数据页损坏，需要导出数据，重新建库。