MySQL InnoDB表损坏恢复的逐步指南

・4 分钟阅读

InnoDB表不会轻易损坏,但一旦损坏,通常是由于硬件问题,断电或MySQL错误而发生。当MySQL崩溃时,会看到类似错误:


InnoDB: Assertion failure in thread 1129654592 in file ibuf0ibuf.c line 4231
InnoDB: Failing assertion: page_get_n_recs(page) > 1
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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
...
some backtrace
...
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
mysqld_safe Number of processes running now: 0
mysqld_safe mysqld restarted

从损坏的InnoDB表恢复

步骤1 -以恢复模式启动数据库

你应该把数据库关闭,以防它仍然在运行。为了恢复数据库,你需要用innodb_force_recovery启动它,

一定要检查你的MySQL日志,如果它循环出现:


InnoDB: Waiting for the background threads to start

应该将innodb_purge_threads=0添加到my.cnf。

为了修复数据库,需要在my.cnf中添加3个参数:


port = 8881
innodb_force_recovery=3
innodb_purge_threads=0

步骤2 - 检查哪些表已损坏,并生成列表

如果尝试,你会得到错误:


Got error -1 from storage engine

我们需要找出哪些表被损坏,执行:mysqlcheck --all-databases

检查表被损坏的行,写下所有导致错误的表/数据库。您需要以恢复模式mysqldump,并在恢复正常MySQL模式后,重新导入它们。

步骤3 -备份和删除损坏的表

mysqldump my_database table > database.table.sql

备份后,通过执行以下操作删除损坏的表:drop table database.table;

步骤4 -以正常模式重启MySQL

当我们的数据库中没有坏表时,应删除my.cnf中步骤1添加的设置。重新启动你的mysql。

步骤5 -导入备份.sql

将每个转储的.sql表导入数据库,从CLI中执行此操作:


mysql database < database.table.sql

导入完表后,重新启动MySQL。

Huangzhongbang profile image