MySQL数据库页损坏怎么办，innodb_force_recovery参数帮你解决问题

忘是亡心i 2022-11-09 14:15 305阅读 0赞

MySQL数据库页损坏怎么办，innodb\_force\_recovery参数帮你解决问题

有些时候，你的MySQL数据库是单机，没有主从和高可用，如果宕机，或者其它异常情况，导致你的ibd文件损坏，这个时候，你的MySQL数据库实例无法启动了，而你又需要导出MySQL数据库中关键数据，这时innodb\_force\_recovery参数能救你一命，首先你需要了解innodb\_force\_recovery作用。

innodb\_force\_recovery默认为0，innodb\_force\_recovery可以设置1到6。较大的值包括较小值所有功能。例如，3包含1和2的所有功能。

设置innodb\_force\_recovery值等于或小于3，MySQL数据库的表是相对安全，此时仅丢失了损坏的单个页面上的某些数据。设置成4或更大的值是非常危险的，此时可能会导致页数据永久损坏。

为保护数据，InnoDB会在innodb\_force\_recovery大于 0 时阻止INSERT，UPDATE或DELETE操作。innodb\_force\_recovery设置为 4 或更大时会将InnoDB置于只读模式。

下面来介绍一下innodb\_force\_recovery设置各种值的作用  
innodb\_force\_recovery=1 ( SRV\_FORCE\_IGNORE\_CORRUPT )  
此时MySQL数据库即使检测到损坏的page也可以运行。可以尝试使SELECT \* FROM tab跳过损坏的索引记录和页面，可以恢复没有损坏的业务数据。

innodb\_force\_recovery=2 ( SRV\_FORCE\_NO\_BACKGROUND )  
阻止master thread和任何purge threads运行。如果在purge操作期间发生崩溃，则此恢复值将阻止它。

innodb\_force\_recovery=3 ( SRV\_FORCE\_NO\_TRX\_UNDO )  
在crash recovery之后不执行事务rollbacks。

innodb\_force\_recovery=4 ( SRV\_FORCE\_NO\_IBUF\_MERGE )  
防止insert buffer合并操作，不计算 tablestatistics。此时可能会永久损坏数据文件，需要删除并重新创建所有二级索引。

innodb\_force\_recovery=5 ( SRV\_FORCE\_NO\_UNDO\_LOG\_SCAN )  
启动数据库时不检查undo logs：InnoDB甚至将未完成的事务都视为已提交。此值可能会永久损坏数据文件。将InnoDB设置为只读。

innodb\_force\_recovery=6 ( SRV\_FORCE\_NO\_LOG\_REDO )  
不进行与恢复有关的redo log前滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态，从而可能导致 B 树和其他数据库结构遭受更多破坏。将InnoDB设置为只读。

**创建模拟表**

mysql> show create table t_test\G;
    *************************** 1. row ***************************
           Table: t_test
    Create Table: CREATE TABLE `t_test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `k` int(12) unsigned NOT NULL DEFAULT '0',
      `c` char(120) NOT NULL DEFAULT '',
      `pad` char(60) NOT NULL DEFAULT '',
      `paymont` double(10,2) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `k_1` (`k`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
    1 row in set (0.00 sec)
    
    mysql> select * from t_test;
    +----+---------+-------------+-------------+-----------+
    | id | k       | c           | pad         | paymont   |
    +----+---------+-------------+-------------+-----------+
    |  1 | 4992833 | 83868641912 | 67847967377 | 200000.00 |
    |  2 | 5019684 | 38014276128 | 23183251411 |      NULL |
    |  3 | 5042604 | 33973744704 | 38615512647 |      NULL |
    |  4 | 5020133 | 37002370280 | 63947013338 |      NULL |
    |  5 | 4998122 | 44257470806 | 34551750492 |      NULL |
    |  6 | 5005844 | 37216201353 | 05161542529 |      NULL |
    |  7 | 5013709 | 33071042495 | 91798303270 |      NULL |
    |  8 | 4999376 | 73754818686 | 76460662325 |      NULL |
    |  9 | 5272683 | 26482547570 | 30508501104 |      NULL |
    | 10 | 5033749 | 05677017559 | 29489382504 |      NULL |
    | 11 | 5495590 | 69916792160 | 87387995487 |      NULL |
    | 12 | 4877585 | 06636928111 | 89292458800 |      NULL |
    +----+---------+-------------+-------------+-----------+
    12 rows in set (0.00 sec)

**模拟表的页损坏**  
要模拟t\_test表页损坏，其实很简单，可以通过vi工具，在t\_test.ibd文件开始行添加N个！就可以

**重启MySQL服务**  
模拟损坏之后，就重启MySQL数据库实例，同时看MySQL数据库错误日志

2020-09-01T12:58:25.207448Z 0 [Note] InnoDB: Loading buffer pool(s) from /u02/log/3308/iblog/ib_buffer_pool
    2020-09-01T12:58:25.209575Z 0 [ERROR] InnoDB: Space ID in fsp header is 134217728, but in the page header it is 660067840.
    2020-09-01T12:58:25.331762Z 0 [ERROR] [FATAL] InnoDB: Tablespace id is 48 in the data dictionary but in file ./sbtest/t_test.ibd it is 18446744073709551615!
    2020-09-01 20:58:25 0x7ffb8d022700  InnoDB: Assertion failure in thread 140718379247360 in file ut0ut.cc line 942
    InnoDB: We intentionally generate a memory trap.
    InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
    InnoDB: If you get repeated assertion failures or crashes, even
    InnoDB: immediately after the mysqld startup, there may be
    InnoDB: corruption in the InnoDB tablespace. Please refer to
    InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
    
    InnoDB: about forcing recovery.

看到没，此时数据库实例已经无法正常启动了，原因是因为t\_test.ibd文件头部记录的Tablespace id与数据字典不一致。怎么办

**设置innodb\_force\_recovery**  
因为此时t\_test.ibd文件头部记录的Tablespace id与数据字典不一致，只能在my.cnf文件中添加innodb\_force\_recovery=4，然后启动MySQL数据库实例

mysql> show tables;
    +------------------+
    | Tables_in_sbtest |
    +------------------+
    | sbtest1          |
    | t_pay_test       |
    | t_test           |
    +------------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from t_test;
    ERROR 1812 (HY000): Tablespace is missing for table `sbtest`.`t_test`.
    mysql>
    mysql> drop table t_test;
    
    Query OK, 0 rows affected (0.02 sec)

由于此时已经无法识别t\_test.ibd文件了，此时只能drop掉t\_test表了。  
如果只是损坏了页数据，可以用下面的sql语句恢复业务数据

insert ignore into t_test_recovery select * from t_test limit 5;
    insert ignore into t_test_recovery select * from t_test limit 10;

MySQL数据库页损坏怎么办，innodb\_force\_recovery参数帮你解决问题  
https://mp.weixin.qq.com/s/l6KZP4glnwwuDQxU98A0Mw