MySQL数据损坏修复的一些踩坑和探索

发表于 分类于

事情是这样的。本周一，所里断电进行供电系统维护，电力恢复后服务器上的MySQL数据出现了损坏，无法正常登录。

后来花了很长时间解决这个问题，下面是踩坑记录。

问题描述

断电重启后MySQL无法登录。

首先，MySQL的docker容器运行状况如下。其状态为 Exited(255) ，且当我使用 docker start mysql_zhangwanyu 启动以后，再次登录MySQL，会经历 ”成功登录→连接数据库→进程崩溃闪退“ 的循环。

1
2
3
4
5
6
7
8
(base) [zhangwanyu@lih-svr01]~$ docker ps --all
CONTAINER ID   IMAGE       COMMAND                  CREATED        STATUS                     PORTS                                                  NAMES
5e43b24b4376   mysql:5.7   "docker-entrypoint.s…"   2 months ago   Exited (255) 3 hours ago   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql_zhangwanyu
(base) [zhangwanyu@lih-svr01]~$ docker exec -it mysql bash
Error response from daemon: No such container: mysql
(base) [zhangwanyu@lih-svr01]~$ docker exec -it mysql_zhangwanyu bash
Error response from daemon: container 5e43b24b43760a4b6f268a6eda048dc6225a74d1e4aab78d8835273a836ab49a is not running
(base) [zhangwanyu@lih-svr01]~$

使用 docker logs 指令查询docker容器的运行日志，其部分输出如下（部分敏感信息已用 * 星号打码）：

1
2
3
4
5
6
7
8
9
10
11
12
2024-11-18T04:50:57.454136Z 0 [Note] InnoDB: Buffer pool(s) load completed at 241118  4:50:57
2024-11-18T04:51:05.361318Z 3 [ERROR] InnoDB: Unable to lock ./********/*******.ibd error: 11
2024-11-18T04:51:05.361329Z 3 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2024-11-18 04:51:05 0x7f1284063700  InnoDB: Assertion failure in thread 139717501138688 in file fil0fil.cc line 922
InnoDB: Failing assertion: success
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

这表明在上次服务器或容器断电后，MySQL 数据库并未正常关闭，导致一些文件损坏， InnoDB 引擎需要进行崩溃恢复。如果恢复操作遇到问题或失败，可能会导致容器崩溃。

解决方法

关键点有两个：

  1. 使用innodb_force_recovery参数进入恢复模式
  2. 从文件系统底层出发直接删除损坏的文件，然后再次从头构建数据表

（一）使用 innodb_force_recovery参数进入恢复模式

MySQL的 innodb_force_recovery 参数有6个不同的级别，每个级别代表了不同程度的恢复操作。这些级别用于在MySQL数据库遇到严重的InnoDB表空间损坏或崩溃时进行数据恢复。每个级别逐步增加了恢复的力度和数据的保护措施。

不同level的含义可以参考这个网页： https://dev.mysql.com/doc/refman/8.4/en/forcing-innodb-recovery.html 。以下是每个级别的详细说明及其使用场景：

innodb_force_recovery=1（跳过故障页清理）

  • 描述：禁用后台的 purge 线程（负责删除已经提交的事务的undo日志）。
  • 使用场景：当数据库在崩溃恢复过程中出现问题或不能正常启动时，可以尝试使用该级别来跳过数据清理的步骤。
  • 风险：此级别对数据完整性的影响最小，通常是数据恢复的第一步。

innodb_force_recovery=2（跳过插入缓冲和合并）

  • 描述：除了级别1的动作外，还禁用插入缓冲（insert buffer）和二次写（doublewrite）。
  • 使用场景：当数据库在启动时因插入缓冲或合并操作出错而崩溃，可以使用此级别来绕过这些操作。
  • 风险：该级别对数据库数据安全的影响较小，但如果数据有缺失，可能不会立即发现。

innodb_force_recovery=3（禁用崩溃恢复）

  • 描述：进一步限制恢复操作，不执行崩溃恢复过程。
  • 使用场景：用于无法通过级别1和2恢复的情况，避免数据在崩溃恢复过程中因损坏数据页而导致服务器崩溃。
  • 风险：数据库只能以只读模式运行，数据一致性可能受到影响。

innodb_force_recovery=4（跳过重做日志应用）

  • 描述：跳过事务日志的回放，即不应用重做日志来恢复数据。
  • 使用场景：适用于数据页可能有损坏、回放重做日志会导致崩溃的情况。
  • 风险：不执行重做日志意味着有未提交的事务数据可能丢失，但允许对数据库进行只读访问。

innodb_force_recovery=5（跳过未提交事务的回滚）

  • 描述：不回滚未提交的事务，防止崩溃恢复时处理损坏的undo日志。
  • 使用场景：适用于回滚未提交事务会导致崩溃的情况，可以用于数据导出或备份。
  • 风险：使用此级别可能会导致数据库中有不一致的数据，因为未提交事务不会被回滚。

innodb_force_recovery=6（禁用所有的InnoDB崩溃恢复机制）

  • 描述：跳过所有的InnoDB崩溃恢复过程，包括检查损坏的数据页。
  • 使用场景：这是最高级别的恢复方式，通常只在其他级别无法恢复数据库时使用。此时数据库可以进行只读访问。
  • 风险：这个级别风险最大，数据库中可能有数据丢失或不一致的情况。此模式下仅建议用于导出数据以进行备份或迁移，随后应重建数据库。

innodb_force_recovery 参数的级别是逐渐递增的，级别越高，对数据的一致性和完整性的影响越大。通常从级别1开始尝试，如果不能解决问题，再逐步增加级别。应注意，在较高级别（如4、5、6）下，数据库只能进行只读访问，修改数据可能会导致更多的问题。因此，在这些级别下通常建议导出数据，备份后重建数据库。

如果是docker启动，则innodb_force_recovery 参数可以直接传递给启动脚本。例如启动脚本可以改成下面这样（加上参数 --innodb_force_recovery=4 指定进入level4的恢复模式。参数 -v 设置了容器与宿主机的共享目录的挂载路径。参数 MYSQL_ROOT_PASSWORD 指定MySQL的登陆密码，可以根据情况自行修改）。

1
2
3
4
5
6
7
8
#!/bin/bash
docker run -p 3306:3306 --name mysql_zhangwanyu_recovery \
-v /home/zhangwanyu/var/mysql/log:/var/log/mysql \
-v /home/zhangwanyu/var/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-u $(id -u):$(id -g) \
-d mysql:5.7 \
--innodb_force_recovery=4

我尝试了 --innodb_force_recovery 的1到6的取值，最后发现只有level4以上的级别才能保证MySQL启动后不崩溃。考虑到level越高，功能限制就越多，因此最后我选择了level4启动。

然后就是，恢复模式并非长久之计，最好能够导出数据，然后在新环境下重建MySQL数据表。下面是导出数据的指令：

1
mysqldump -u root -p --all-databases > all_databases_backup.sql

(二）如果是某个数据表损坏且无法恢复，则从文件系统底层出发直接删文件

假设，我的数据表如下：

1
2
3
4
5
6
7
+----------------------+
| Tables_in_model_data |
+----------------------+
| information_tab1     |
| information_tab2     |
| information_tab3     |
+----------------------+

要判断哪一个表损坏，可以依次尝试对这些表中的数据进行query操作 （select * from information_tab1 limit 10 ，另外几张表以此类推）。一旦在query某一张表的过程中MySQL崩溃，基本就可以确定是这张表的文件损坏了。

例如，如果其中 information_tab3 损坏，无法select，也无法drop（DROP TABLE information_tab3; 提示 ERROR 1051 (42S02): Unknown table 'model_data.information_tab3' ）。此时，我们可以从文件系统定位到上述数据表所在位置，即 /var/lib/mysql/<databasename>/information_tab3.frm 和 /var/lib/mysql/<databasename>/information_tab3.ibd ：

删除上述文件，然后进入MySQL，一切就恢复正常了。至于被物理删除的那个表，需要再重新创建和写入一下。