前言
研发自己搭建了一套 MySQL 没有设置双一参数,机房异常断电,导致数据页出现损坏,本篇文章介绍此类问题处理思路。
1. 备份恢复
如果该集群有完整的备份和 Binlog 备份,那么可以直接选择通过备份恢复数据。
2. 强制 InnoDB 恢复
MySQL 提供 innodb_force_recovery
参数,可在紧急情况使用,因为当数据页发生损坏时,数据库通常无法启动或频繁重启,可以设置 innodb_force_recovery 参数,表示即使发现了损坏页也可以继续让服务运行,这样我们就可以读取数据表,并且对当前损坏的数据表进行分析和备份。
innodb_force_recovery 参数一共有 7 种状态,除了默认的 0 以外,还可以为 1-6 的取值,分别代表不同的强制恢复措施。
通常 innodb_force_recovery 参数设置为 1,只要能正常读取数据表即可。但如果参数设置为 1 之后还无法读取数据表,我们可以将参数逐一增加,比如 2、3 等。一般来说不需要将参数设置到 4 或以上,因为这有可能对数据文件造成永久破坏。
另外当 innodb_force_recovery 设置为大于 0 时,相当于对 InnoDB 进行了写保护,会阻止 INSERT、UPDATE、DELETE 操作。只能进行 SELECT 操作。
接下来我们模拟一次数据页面损坏。
2.1 损坏数据页
直接使用 vi 打开一张测试表的 mgr_test.ibd 的数据文件,然后随机删除一段。
mgr_test 为测试表,有 10 行记录。
2.2 观察错误日志
此时如果有查询访问 mgr_test 表,数据库会直接报错,并重启。
[ERROR] [MY-011906] [InnoDB] Database page corruption on disk or a failed file read of page [page id: space=19, page number=4]. You may have to recover from a backup.
2.3 设置参数
将 innodb_force_recovery 调整为 1 然后重启数据库。
2.4 定位表信息
我们做实验的时候,是故意损坏一张表,所以是知道哪张表坏了,如果是真实发生的案例,往往是不知道是哪张表的数据页损坏的,需要定位分析。
[ERROR] [MY-011906] [InnoDB] Database page corruption on disk or a failed file read of page [page id: space=19, page number=4]. You may have to recover from a backup.
根据错误日志中的信息:[page id: space=19, page number=4] 发现是 space=19 page number=4 的数据页损坏。
select * from information_schema.INNODB_TABLES where SPACE = 19;
+----------+---------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+---------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| 1081 | test/mgr_test | 33 | 6 | 19 | Dynamic | 0 | Single | 0 | 0 |
+----------+---------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
由此,可以看出是 test 库下的 mgr_test 表的数据页发生损坏。
select
b.INDEX_ID,
a.NAME as TableName,
a.SPACE as Space,
b.NAME as IndexName
from
information_schema.INNODB_TABLES a,
information_schema.INNODB_INDEXES b
where
a.SPACE = b.SPACE
and a.SPACE = 19;
+----------+---------------+-------+-----------+
| INDEX_ID | TableName | Space | IndexName |
+----------+---------------+-------+-----------+
| 180 | test/mgr_test | 19 | PRIMARY |
+----------+---------------+-------+-----------+
根据上面的查询结果,确定损坏的页是属于主键还是辅助索引,如果属于主键索引,因为在 MySQL 中索引即数据,则可能会导致数据丢失,如果是二级索引,删除索引重建即可。
我们当前测试,损坏的就是 PRIMARY 主键索引,所以如果没有备份数据很可能会丢失。
2.5 分析处理
通过上一步骤的分析,已经定位到是 test/mgr_test 的主键索引发生数据页面的损坏,且没有备份,现在能做的就是尽可能的恢复部分数据。数据页损坏的表是不支持 WHERE、ORDER BY 等条件过滤的,只支持 LIMIT。
select * from mgr_test limit 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query
因为读取的数据中,包含损坏的页面,所以会直接报错,可以采用二分查找判断数据页损坏的位置。
select * from mgr_test limit 4;
经过测试,我们发现数据页发生损坏的是第 5 行记录,那么前 4 行记录还是可以保留下来的。
2.6 恢复数据
经过分析,只有前 4 行记录可以正常读取出来,那么此时需要将这部分数据备份出来。
mysqldump -uroot -p --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF test mgr_test --where="true limit 4" > ./resover.sql
记录表结构,删除改表,重新启动数据库后重建该表。
drop table mgr_test;
数据页损坏的表,已经被删除掉了,能恢复的数据也已备份出来了,现在需要设置 innodb_force_recovery 为 0 重启数据库。
创建 mgr_test 表,然后 source 备份文件写入该表,数据恢复完成,虽然丢失了一部分。
总结
本篇文章介绍了人工恢复 ibd 文件中的数据,虽然没有全部找回,但是相比于束手无措来说,已经是不幸中的万幸,至少我们还可以把正确的数据页中的记录成功备份出来,尽可能恢复原有的数据表。需要注意的是,生产环境不能有任何侥幸心理,一定要有完善的备份恢复机制。