MySQL-innodb引擎故障

https://www.hyouit.com?aff=418

1、故障现象

MySQL数据库无法启动，日志信息如下：

2023-01-18 04:00:39 7f0d83cc2700 InnoDB: Error: Write to file ./ib_logfile2 failed at offset 123145216.
InnoDB: 69632 bytes should have been written, only 66560 were written.
InnoDB: Operating system error number 22.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 22 means 'Invalid argument'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2023-01-18 04:00:39 7f0d83cc2700  InnoDB: Assertion failure in thread 139696022497024 in file fil0fil.cc line 5708
InnoDB: Failing assertion: ret
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.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
20:00:39 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
.......
2023-01-18 04:37:28 9040 [ERROR] InnoDB: Tried to read 65536 bytes at offset 123210752. Was only able to read 1024.
2023-01-18 04:37:28 7f6098d5f720  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2023-01-18 04:37:28 9040 [ERROR] InnoDB: File (unknown): 'read' returned OS error 71. Cannot continue operation

2、故障分析

ib_logfile2文件写入异常，innodb引擎出现故障

3、故障处理

3.1 调整参数

innodb_force_recovery = 1

如果不生效，尝试 2 - 6 几个数字，重启 mysqld 服务。

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):不执行前滚的操作。

3.2 数据库备份

mysqldump --all-databases > /tmp/all.sql

3.3 删除数据

• 当数据导出成功后，删除原数据库中的数据时，如果提示不能删除，可在命令行进入 MySQL 的数据目录，手动删除相关数据库的文件夹或者数据库文件夹下的数据表文件，前提是数据一定导出或备份成功。
• 备份好 ib_logfile0、ib_logfile1、ibdata1 这三个文件，然后删除；

3.4 恢复参数设置，或注释掉

innodb_force_recovery = 0

3.5 重启数据库，导入备份的数据