<返回更多

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

2022-01-05    桌面运维小技巧
加入收藏

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:xamppmysqldataibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-01-04 17:21:38 0 [Note] InnoDB: File 'C:xamppmysqldataibtmp1' 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:xamppmysqldataib_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 = 到 试到正确为止,重启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下的文件。

声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多资讯 >>>