MySQL崩溃-修复损坏的innodb:innodb_force_recovery

liuian 2024-12-17 15:03 54 浏览

Windows上安装了XMAPP-controller之后间歇性出现MySQL无法启动，查看日之后发现是innodb的报错，报错信息如下：

22-01-04 17:21:38 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-01-04 17:21:38 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-01-04 17:21:38 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-01-04 17:21:38 0 [Note] InnoDB: Waiting for purge to start
2022-01-04 17:21:38 0 [Note] InnoDB: 10.4.21 started; log sequence number 49263; transaction id 9584
2022-01-04 17:21:38 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-01-04 17:21:38 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Page [page id: space=0, page number=9] log sequence number 4150152 is in the future! Current system log sequence number 49272.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Page [page id: space=0, page number=243] log sequence number 4151094 is in the future! Current system log sequence number 49272.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Page [page id: space=0, page number=306] log sequence number 4150564 is in the future! Current system log sequence number 49272.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Page [page id: space=0, page number=309] log sequence number 4150665 is in the future! Current system log sequence number 49272.
2022-01-04 17:21:38 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-01-04 17:21:38 0 [Note] InnoDB: Buffer pool(s) load completed at 220104 17:21:38
2022-01-04 17:21:38 0 [Note] Server socket created on IP: '::'.

度娘上各种答案无法解决，后来直接看官方文档，直接上解决方案：

踩坑指南 - - 操作配置前需要做这些操作：

# 1. 将三个文件进行备份后删除：ib_logfile0、ib_logfile1、ibdata1
# 2. 一定要确认元数据导出成功了
# 3. 当数据导出成功后，删除原数据库中的数据是，如果提示不能删除，可在命令进入MySQL的数据目录，
手动删除相关数据库文件夹或者数据库文件夹下的数据表文件，前提是数据一定导出或备份成功。

1、配置my.cnf 配置innodb_force_recovery = 1 到 6 试到正确为止，重启MySQL

2、导出数据脚本 mysqldump -uroot -p123456 test > test.sql 导出SQL脚本。或者用Navicat将所有数据库/表导入到其他服务器的数据库中。注意：这里的数据一定要备份成功。然后删除原数据库中的数据。

3、删除ib_logfile0、ib_logfile1、ibdata1 备份MySQL数据目录下的ib_logfile0、ib_logfile1、ibdata1三个文件，然后将这三个文件删除

4、配置my.cnf 将my.cnf中innodb_force_recovery 这行配置删除或者配置为innodb_force_recovery = 0，重启MySQL服务

5、将数据导入MySQL数据库 mysql -uroot -p123456 test < test.sql;

或者用Navicat将备份的数据导入到数据库中。如果在导入数据过程中发生tablespace不存在的问题，请删除data目录相应database下的文件。

xampp怎么打开mysql