一次现网一线运维人员反馈一套mysql访问某个表的时候,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): 表引擎没有这个选项。
DISCARD TABLESPACE 命令不支持有父子关系的表。如果 FOREIGN_KEY_CHECKS 被设置成1. 在使用命令之前我们可以将这一参数设置为0. FOREIGN_KEY_CHECKS=0.
ALTER TABLE ... IMPORT TABLESPACE 命令在导入表时不会检查主外键关系。
如果是实时复制的时候, innodb_file_per_table 必需在主服务和从服务上设置为ON。
原文链接：https://blog.csdn.net/weixin_39884144/article/details/113221216