mysql表空间损坏（innodb_force_recovery）-摩杜云开发者社区

一次现网一线运维人员反馈一套mysql访问某个表的时候,mysql会断连重启.经过问题定位,单表表空间损坏

一、问题定位

1.查看mysql的error日志,发现如下记录

2020-10-20T07:43:03.297962Z 0 [ERROR] InnoDB: Checksum mismatch in datafile: ./data_2020/bc_data_co#P#P20201221.ibd, Space ID:12883, Flags: 33. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2020-10-20T07:43:03.298007Z 0 [ERROR] InnoDB: Operating system error number 25 in a file operation.
2020-10-20T07:43:03.298020Z 0 [ERROR] InnoDB: Error number 25 means ‘Inappropriate ioctl for device’
2020-10-20T07:43:03.298025Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-10-20T07:43:03.298031Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for data_2020/bc_data_co#P#P20201221. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2020-10-20T07:43:03.298040Z 0 [Warning] InnoDB: Ignoring tablespace data_2020/bc_data_co#P#P20201221 because it could not be opened.

bc_data_co表的一个分区表空间文件不能打开

2.查找文件

ll /data/mysql/data/workdbs/data_2020/bc_data_co#P#P20201221

文件存在

分析:这个分区表空间文件已经损坏.

二、解决过程

1.操作之前,做数据文件备份

cp /data/mysql/data/workdbs/data_2020/bc_data_co#P* /data/mysql/data/backup/bc_data_co/
cp /data/mysql/data/workdbs/data_2020/bc_data_co.frm /data/mysql/data/backup/bc_data_co/

2.忽略表空间错误启动

vi /data/mysql/app/mysql-files/my.cnf
添加innodb_force_recovery=X

mysql.server restart

参数innodb_force_recovery说明:
innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作

经过测试, innodb_force_recovery=4 正常启动mysql.

3.导出表结构

mysqldump -uroot -p --set-gtid-purged=off -d data_2020 bc_data_co>bc_data_co.sql

4.清除参数innodb_force_recovery ,重启

vi /data/mysql/app/mysql-files/my.cnf
注销#innodb_force_recovery=4
mysql.server restart

5.创建一个同样表结构的新表

mysql -uroot -pRoot_001 -A(加这个-A可以跳过加载表定义数据，适用场景特别多表或者分区)
create database test;
use test;
source /data/mysql/bc_data_co.sql

6.卸载bc_data_co表空间

use data_2020;
alter table `bc_data_co` DISCARD tablespace;

7.把备份的表所有分区表空间文件复制回来,删除已损坏的分区表空间文件,用新的空文件代替

cp /data/mysql/data/backup/bc_data_co/*.ibd /data/mysql/data/workdbs/data_2020/
rm /data/mysql/data/workdbs/data_2020/bc_data_co#P#P20201221.ibd
cp /data/mysql/data/workdbs/test/bc_data_co#P#P20201221.ibd /data/mysql/data/workdbs/data_2020/

8.加载bc_data_co 所有表空间

alter table `bc_data_co` IMPORT tablespace;

9.访问bc_data_co 表

select * from bc_data_co limit 1;

正常访问. mysql 错误日志没有报错,也不再重复重启

三、总结

1.mysql无法启动,通过mysql error日志查看错误记录

2.表空间错误,通过在my.cnf设置参数innodb_force_recovery 值1-6,启动mysql

3.通过DISCARD TABLESPACE和IMPORT TABLESPACE 替换表空间文件

4.可迁移表空间的限制：

innodb_file_per_table 一定要打开成 ON. 在共享表空间上的表不能使用这个特性。

当表处理静默状态时,只有只读语句可以使用这张表。

当导入表空间时，目的库的页尺寸要和源库的页尺寸相匹配。

DISCARD TABLESPACE 不支持分区表。如果你在分区表上使用命令 ALTER TABLE ... DISCARD TABLESPACE 你会看到如下错误: ERROR 1031 (HY000): 表引擎没有这个选项。

FOREIGN_KEY_CHECKS=0.

ALTER TABLE ... IMPORT TABLESPACE 命令在导入表时不会检查主外键关系。

如果是实时复制的时候, innodb_file_per_table 必需在主服务和从服务上设置为ON。