Landigo's Garden
large picture of the cover

关于mysql数据库损坏的修复经验

背景

今天登博客发现err502，让我大为不解，终端还能有什么问题。

进去一看docker一片红，mysql容器一直在重启：

halo | 2025-08-24 12:58:57.606 | 2025-08-24T12:58:57.605+08:00 WARN 7 --- [ main] onfigReactiveWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'r2dbcScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/R2dbcInitializationConfiguration.class]: Failed to execute database script halo | 2025-08-24 12:58:57.615 | 2025-08-24T12:58:57.614+08:00 INFO 7 --- [ main] r.h.a.search.lucene.LuceneSearchEngine : Destroyed lucene search engine halo | 2025-08-24 12:58:57.748 | 2025-08-24T12:58:57.747+08:00 INFO 7 --- [ main] .s.b.a.l.ConditionEvaluationReportLogger : halo | 2025-08-24 12:58:57.748 | halo | 2025-08-24 12:58:57.748 | Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled. mysql | 2025-08-24 12:58:57.533 | 2025-08-24 04:58:57+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.42-1.el9 started. halo | 2025-08-24 12:58:57.786 | 2025-08-24T12:58:57.780+08:00 ERROR 7 --- [ main] o.s.boot.SpringApplication : Application run failed halo | 2025-08-24 12:58:57.786 | halo | 2025-08-24 12:58:57.786 | org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'r2dbcScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/R2dbcInitializationConfiguration.class]: Failed to execute database script halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1826) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:607) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:529) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:339) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:373) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:337) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.DefaultListableBeanFactory.instantiateSingleton(DefaultListableBeanFactory.java:1222) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingleton(DefaultListableBeanFactory.java:1188) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:1123) ~[spring-beans-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:987) ~[spring-context-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:627) ~[spring-context-6.2.9.jar:6.2.9] halo | 2025-08-24 12:58:57.786 | at org.springframework.boot.web.reactive.context.ReactiveWebServerApplicationContext.refresh(ReactiveWebServerApplicationContext.java:66) ~[spring-boot-3.5.4.jar:3.5.4] halo | 2025-08-24 12:58:57.786 | at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:752) ~[spring-boot-3.5.4.jar:3.5.4] halo | 2025-08-24 12:58:57.786 | at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:439) ~[spring-boot-3.5.4.jar:3.5.4]

咱又不懂代码，springboot干嘛的也不知道，重启试试

2025-08-23T21:01:29.270640Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2025-08-23T21:01:29.270689Z 0 [ERROR] [MY-010119] [Server] Aborting
2025-08-23T21:01:29.271654Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.42)  MySQL Community Server - GPL.
2025-08-23 21:02:29+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.42-1.el9 started.
2025-08-23 21:02:30+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2025-08-23 21:02:30+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.42-1.el9 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2025-08-23T21:02:30.579960Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2025-08-23T21:02:30.585945Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.42) starting as process 1
2025-08-23T21:02:30.590380Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2025-08-23T21:02:30.634480Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-08-23T21:02:31.089419Z 1 [ERROR] [MY-013089] [InnoDB] Invalid redo log header checksum.
2025-08-23T21:02:31.089475Z 1 [ERROR] [MY-013859] [InnoDB] Failed to read header of the redo log file ./#innodb_redo/#ib_redo97
2025-08-23T21:02:31.090045Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2025-08-23T21:02:31.353360Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2025-08-23T21:02:31.353590Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2025-08-23T21:02:31.353629Z 0 [ERROR] [MY-010119] [Server] Aborting
2025-08-23T21:02:31.354593Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.42)  MySQL Community Server - GPL.

完蛋，MySQL InnoDB redo log 文件坏了（常见于异常关机/断电/磁盘写入失败）。
所以 MySQL 启动时 数据字典无法初始化 → 容器直接崩溃重启，进都进不去。

直接贴解决办法

使用场景

MySQL 8.0默认使用独立表空间（innodb_file_per_table=ON），每个InnoDB表的.ibd文件存储了表数据和索引。当遇到以下场景时，直接操作.ibd文件恢复数据成为最后一根救命稻草

  • 误删表且无备份：DROP TABLE后无binlog可用。

  • 文件系统错误：磁盘损坏、文件系统元数据丢失。

  • MySQL服务崩溃：实例无法启动，但.ibd文件物理存在。

  • 表损坏：CHECK TABLE报错，但不想重建表结构。

  • 官方innodb_force_recovery无效

工具原理

元数据扫描：

  • 定位SYS_TABLES和SYS_COLUMNS的系统表.ibd文件，提取表结构。

  • 解析数据页的PAGE_HEADER和RECORD_HEADER，重建行数据。

数据导出：

  • 支持导出为SQL语句（含INSERT）或CSV文件。

  • 自动处理碎片页和已删除标记（DELETE_BIT）

安装与使用

准备.ibd文件

确保你已经从备份或者故障数据库中获取了需要恢复的.ibd文件。

运行恢复工具

1，下载mysql数据库恢复工具

密码:056jgl

2，在vscode extension安装go依赖

3，在vscode打开工具folder，并 ctrl+` 打开终端，根据提示安装go mod

开始恢复

使用以下命令运行工具，并指定要恢复的.ibd文件路径：

cd 你的go目录
go run .\cmd\mycmd.go G:\dock-project\mysql-data.bak\halo\extensions.ibd

这将解析.bd文件并生成相应的SQL文件。

输出在 output.txt，里面包含：

  • CREATE TABLE 语句

  • DISCARD TABLESPACE

  • IMPORT TABLESPACE.

确认表结构是否与 MySQL 现有表一致：

SHOW CREATE TABLE extensions\G

若不一致，需要先 DROP TABLE 再建。

1，登录 MySQL

docker exec -it halo-mysql mysql -uroot -p
# 输入 rootpassword

2，创建数据库和表（如果不存在）

CREATE DATABASE IF NOT EXISTS halo CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE halo;

CREATE TABLE `extensions` (
  `name` varchar(255) NOT NULL,
  `data` blob DEFAULT NULL,
  `version` bigint DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

3，丢弃现有表空间

ALTER TABLE extensions DISCARD TABLESPACE;

4，拷贝 .ibd 文件到容器

docker cp "G:\dock-project\mysql-data.bak\halo\extensions.ibd" halo-mysql:/var/lib/mysql/halo/extensions.ibd

5，修改文件权限

docker exec -it halo-mysql bash
chown mysql:mysql /var/lib/mysql/halo/extensions.ibd
exit

5， 导入表空间

ALTER TABLE extensions IMPORT TABLESPACE;

如果出现警告 *.cfg not found，只要表结构一致，数据依然可以读出。

6，验证数据

SELECT * FROM extensions LIMIT 10;
SHOW WARNINGS;

确认 BLOB、varchar 等字段内容正确。

恭喜找回！

记得勤备份qwq

meow~

site logo

"The Goddess of Time has been asleep since the beginning of time. She has never opened her eyes, not even once. Maybe... everything we know, everything we are... is just part of her dream."

© 2025 Landigo's Garden