mysql表损坏怎么修复

使用场景

有些时候，你的MySQL数据库是单机，没有主从和高可用，如果宕机，或者其它异常情况，导致你的ibd文件损坏，这个时候，你的MySQL数据库实例无法启动了，而你又需要导出MySQL数据库中关键数据，这时innodb_force_recovery参数能救你一命，首先你需要了解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 时阻止ｉｎｓｅｒｔ，ｕｐｄａｔｅ或ｄｅｌｅｔｅ操作。innodb_force_recovery设置为 4 或更大时会将InnoDB置于只读模式。

下面来介绍一下innodb_force_recovery设置各种值的作用

innodb_force_recovery=1 ( SRV_FORCE_IGNORE_CORRUPT )此时MySQL数据库即使检测到损坏的page也可以运行。可以尝试使ｓｅｌｅｃｔ * 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 )防止ｉｎｓｅｒｔ 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_testCreate Table: CREATE TABLE `t_test` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`k` int(12) unsigned NOT NULL DEFAULT ‘0‘,`c` ｃｈａｒ(120) NOT NULL DEFAULT ‘‘,`pad` ｃｈａｒ(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=10000001 row 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_pool2020-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 0x7ffb8d022700InnoDB: Assertion failure in thread 140718379247360 in file ut0ut.cc line 942InnoDB: 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, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.htmlInnoDB: 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> ｓｅｌｅｃｔ * from t_test;ERROR 1812 (HY000): Tablespace is missing for table `sbtest`.`t_test`.mysql>mysql> ｄｒｏｐ table t_test;Query OK, 0 rows affected (0.02 sec)

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

ｉｎｓｅｒｔ ignore into t_test_recovery ｓｅｌｅｃｔ * from t_test limit 5;ｉｎｓｅｒｔ ignore into t_test_recovery ｓｅｌｅｃｔ * from t_test limit 10;