高级DBA亲自带你解决mysql数据库表数据ibd文件破坏数据库服务无法正常启动实战方法全网唯一

高级DBA亲自带你解决mysql数据库表数据ibd文件破坏数据库无法正常启动实战方法全网唯一

一、事故描述自述

本人在从事软件行业10余年的漫长工作生活过程中，应用最多的就是Mysql数据库、postgresql数据库！（作者的师傅从事软件20来年，跟他取经，也是师傅带我处理的）经常遇到Mysql数据库会经常出现表ibd文件破坏、或者索引文件破坏了，或者索引文件找不到了。这类问题都是硬盘或者是操作系统层面的问题，比如硬盘坏道、或者是固态数据碎片，总之就是数据库的数据文件直接破坏了，操作系统从硬盘上读不出来，这类问题要怎么解决？笔者就直接来个真实的例子带大家解决一下！

二、mysql日志实际案例

我先说说实际的情况，一般就是当你打开那个表，或者读取那个表，MYSQL服务就会直接宕掉，服务挂掉，重启之后，似乎又好了！然后再有客户端或者程序只要一读这个问题表，就反复！！

步骤1 找到MYSQL错误日志位置

查看my.cnf或者my.ini判断运行日志跟错误日志的位置，如果没有配置，则可以配置上，再重启数据库。

[mysqld]
log-error=/home/gs/mysql-8.0.18/log/error.log
log=/home/gs/mysql-8.0.18/log/mysql.log

在 MySQL 中，可以使用以下 SQL 语句来查看数据库文件当前存放路径：

SHOW VARIABLES LIKE 'datadir';

windows默认就在数据库文件存放路径的.error

步骤2 解读mysql错误日志

主要找[ERROR]

2024-04-25T01:16:01.009604Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.25'  socket: ''  port: 3306  MySQL Community Server - GPL.
2024-04-25T01:16:17.246130Z 8 [ERROR] [MY-012218] [InnoDB] Cannot read first page of '.\某个数据库\某个表#p#p34.ibd' I/O error
2024-04-25T01:16:17.246979Z 8 [ERROR] [MY-012224] [InnoDB] Cannot read first page in datafile: .\某个数据库\某个表#p#p34.ibd, Space ID:4294967295, Flags: 16417. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2024-04-25T01:16:17.248370Z 8 [ERROR] [MY-012611] [InnoDB] Operating system error number 38 in a file operation.
2024-04-25T01:16:17.248876Z 8 [ERROR] [MY-012131] [InnoDB] Could not find a valid tablespace file for `某个数据库/某个表#p#p34`. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

日志核心内容，你看到这种错误，那就代表某个表某个数据库块的文件坏了，笔者这个是一个表分区的文件索引文件破坏了！

Operating system error number 38 in a file operation.
Could not find a valid tablespace file for

.ibd 文件是 InnoDB 存储引擎的数据文件。
InnoDB 表的数据、索引等信息都存储在这些文件中。

步骤3 解决问题方法思路

所有的核心解决方法，就是找到坏的表，删除重建表、通过数据备份恢复！

当前有2种情况，一种情况是mysql服务起的来（能启动就把表删除重建），还可以操作，第2种情况是直接无法正常启动！
mysql有类似windows操作系统的安全模式，我们要进入安全模式，然后把表删除重建操作！或者是进入安全模式把现存的没有破坏的存量数据导出！
作者这次遇到问题是文件破坏，重启服务不操作那个问题表是可以操作的，直接删除重建解决了！
下面是第2种情况，当前服务已经无法正常启动了，先进入安全模式！
按照模式配置方法如下：

[mysqld]
innodb_force_recovery = 1  #我们从1到6依次的启动，按级别的高级顺序。

MySQL在转储文件或者导入数据的过程中，出现中断、失败或者异常，造成数据无法回滚，可以通过innodb_force_recovery强力迫使InnoDB存储引擎运行，同时阻止后台操作运行，以便转储表数据。

mysql官方提供了6个等级
Innodb事务型存储引擎，通过redo，undo，double write这些特性保证数据的完整，针对硬件故障，内核bug，突然断电的事件，需要手动对Innodb进行恢复；
可以将Innodb page 损坏分为几类，data page 损坏，secondary_index page 损坏， root index 损坏,data dictionary 损坏，恢复的难度依次增加；与朋友一起恢复innodb的时候，重新认识了下innodb_force_recovery;
最初对innodb_force_recovery 的认识只是错误的停留在 它只针对无法启动的时候使用，1-6的参数，对损坏数据只在启动的时候不去检查；后来才明白启用该参数后，MySQL redo only 就是为了保证对应参数里面的值，不启用后台thread的任何检查直至设置innodb_force_recovery=0才可以，同时跟大家分享下， check table 的结果对于innodb 是不可信的（明明error log报page错误，但检测结果仍是ok） （以下内容多参考官网）
innodb_force_recovery 在使用的时候，能尽量从1-6依次递增，=3的时候，已经包括 =1 和=2的处理情况，一般 = 1-3的时候，数据的完整性相对来说还是可以保证的（除了已经损坏的部分），>=4 的时候可能造成 page处于一种相对“过时”（obsolete state）,(如果不进行重建损坏的表)，可能造成B-trees and other database structures 的损坏，>0 的时候，INSERT,UPDATE,DELETE这些操作都是禁止的，下面介绍下各个参数的具体含义：
1 (SRV_FORCE_IGNORE_CORRUPT)：
强制忽略corrupt page并自动跳过，期间可以dump table；
2 (SRV_FORCE_NO_BACKGROUND)：
在前置忽略corrupt page 的基础上（包含=1的作用），阻塞 master thread 和 任何的 purge thread 运行（有效防止在purge的时候发生MySQL crash）
3 (SRV_FORCE_NO_TRX_UNDO)：
在忽略 corrupt page，阻塞 purge thread的基础上，不进行 transaction rollback；
4 (SRV_FORCE_NO_IBUF_MERGE)：
在忽略 corrupt page，阻塞 purge thread，禁止 transaction rollback 基础上，禁止 merge insert buffer，对 table statistics 不进行更新；（这样会损坏 data file，等恢复后最好重建所有的secondary index）；
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)：
在忽略 corrupt page ,阻塞purge thread，禁止 transaction rollback，禁止merge insert buffer，停止 table statistic 的基础上，在启动 MySQL的时候，不在扫描 undo logs，对待incomplete transactions as committed；
6 (SRV_FORCE_NO_LOG_REDO)：
在以上所有的基础上，redo log 不进行前滚（roll-forward）
这里再次提醒下，对Innodb_force_recovery的赋值最好是依次递增（除非自己做过严格测试）

然后反复重启服务，直到服务可以起来，然后把当前的存量数据备份导出！过滤掉那个问题的表！
拿到备份之后，再把问题表删除，重建！数据库删除重建都可以！

将生产数据库的该表直接drop掉，然后重建，再把数据从最近历史备份分离出来，恢复回去。这样破坏的文件会重建。

也可以将该数据库先DUMP下来，然后删除整个数据库重新恢复。所有的破坏的表文件，都会重建！
innodb_force_recovery >0是不能更新操作的。所以先要在>0的状态尽量DUMP将生产的数据备份出来，如果DUMP报错，则用历史的备份恢复。然后删除掉关联坏的表或者数据库。DROP的时候尽量按表、数据库的单位删除，因为会关联很多莫名的未知文件。整体思路就是找到坏的文件关联的表或者数据库，让MySql去删除操作，然后按常规的顺序重建，重新创建文件。
然后在innodb_force_recovery =0的状态下，再重新新增表操作并插入数据。

三、案例总结

作者把真实的日志报错案例给大家列举出来，我相信每个DBA都会经常遇到类似的问题，或者自己是软件开发过程中也会经常遇到表文件破坏的问题，我希望能帮助到每个开发者，通过作者的案例，及时的解决问题，或者是直接解决生产的灾难事故！大家跟着笔者我的思路与方法一定可以化险为夷，解决危机！现在数据安全是非常重要的，所以增量备份还是提前做好！
笔者还写过一篇文章，引入了其他mysql宕机的例子，作者将把遇到的所有mysql问题都一一列举出来，争取做到中国第一，我看中国没有人把数据库故障列举出来，而且出去培训也没有人将生产数据库事故要怎么解决！我希望能帮到大家！公益无求！

其他报错案例参考文章

https://blog.csdn.net/nasen512/article/details/130705957

笔者简介
国内某一线知名软件公司企业认证在职员工：任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验！现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起，挽回数据丢失所造成的灾难损失不计其数!并为某国家级大数据系统的技术方案（国家知识产权局颁布）专利权的第一专利发明人！