恢复损坏的InnoDB表格

原作者：Peter Zaitsev

翻译：Alan Gao @ cgaolei.iteye.com

英文原文：http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/

 

译者序：

MySQL性能博客(MySQL Performance Blog)是非官方的专注于MySQL性能方面问题的博客性网站。

他们的代表作：<<High Performance MySQL>>

这篇是此书的作者之一在2008年7月4日发表的博文。因为它帮助了我即时的解决了几周前发生在公司的数据库事故。事故原因至今未解，公司一台主数据库服务器（使用MySQL 5.0 InnoDB），一个表中的数据莫名损坏。单那一个表中就有4GB左右数据，使用修复命令无效。使用了文章中的方法，恢复了决大部分数据，只损失了200多条记录，后从备份中取出。

 

特将此文进行了翻译，希望对遇到了同样问题的朋友有所帮助。

翻译正文：

 

假设你正在运行使用InnoDB表格的MySQL,糟糕的硬件设备，驱动程序错误，内核错误，不幸的电源故障或某些罕见的MySQL错误使你的InnoDB表空间被损坏了。在这种情况下， InnoDB的一般会出现这样的输出：

写道

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
... 这里省略很多二进制和十六进制编码...
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index "PRIMARY" of table "test"."test")
InnoDB: Database page corruption on disk or a failed

还有，在尝试访问时导致崩溃。

你会如何恢复这样的数据库表格？

有多种因素可能导致数据损坏，我将在本文中详细讲述一个例子:数据页面的集群关键字索引(clustered key index)被损坏。这种情况和数据的二级索引(secondary indexes)被损坏相比要糟很多，因为后者可以通过使用OPTIMIZE TABLE命令来修复，但这和更难以恢复的表格目录(table dictionary)被破坏的情况来说要好一些。

在这个例子中，我其实先通过手动修改test.ibd数据文件，替换代几个字节，来制造表格轻微损坏。

首先，我应该提醒的是CHECK TABLE命令在InnoDB数据库中基本上是没有用的。通过手动损坏的表格，我会得到：

mysql> CHECK TABLE test;
ERROR 2013 (HY000): Lost connection TO MySQL server during query
       
mysql> CHECK TABLE test;
+-----------+-------+----------+----------+
| TABLE     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | CHECK | STATUS   | OK       |
+-----------+-------+----------+----------+
1 row IN SET (0.69 sec)

第一次在正常操作模式下执行CHECK TABLE命令:在这种情况下，InnoDB如果遇到校验错误(checksum error)就会崩溃（即使我们正在运行检查操作） 。

第二次我修改了设置，使用innodb_force_recovery = 1，正如你所看到的，即使日志文件中有校验失败的记录，但CHECK TABLE还是说表格是正确的。这意味着你不能太依赖CHECK TABLE在InnoDB上执行的结果。

在这个例子中，被破坏的地方只在数据的部分，所以当使用innodb_force_recovery = 1运行InnoDB时，您可以运行如下语句：

mysql> CREATE TABLE `test2` (
    ->   `c` char(255) DEFAULT NULL,
    ->   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)
       
mysql> INSERT INTO test2 SELECT * FROM test;
Query OK, 229376 rows affected (0.91 sec)
Records: 229376  Duplicates: 0  Warnings: 0

现在，所有的数据都被转到了MyISAM表格中，你只需要做的就是drop原来的表格,并将MyISAM表的引擎更改为InnoDB，然后去掉innodb_force_recovery选项重新启动。（也可以将旧表重新命名，以便以后来查阅）。

另一个方法是使用mysqldump将表格导出，然后再导回到InnoDB表中。这两种方法的结果是相同的。 这里我使用的MyISAM表格的原因会在后面看到。

你可能会想为什么不直接用OPTIMIZE TABLE命令重建表格？这是因为InnoDB在innodb_force_recovery运行模式中，数据就变成为只读，因此你无法插入或删除任何数据（尽管可以创建或删除InnoDB表格）：

mysql> OPTIMIZE TABLE test;
+-----------+----------+----------+----------------------------------+
| TABLE     | Op       | Msg_type | Msg_text                         |
+-----------+----------+----------+----------------------------------+
| test.test | OPTIMIZE | error    | Got error -1 FROM storage engine |
| test.test | OPTIMIZE | STATUS   | Operation failed                 |
+-----------+----------+----------+----------------------------------+
2 rows IN SET, 2 warnings (0.09 sec)

这很容易，不是吗？

我也这样想，所以我再次编辑test.ibd文件，这次去掉一整个页。现在甚至使用innodb_force_recovery = 1运行时，连CHECK TABLE命令也会崩溃掉。

写道

080704 0:22:53 InnoDB: Assertion failure in thread 1158060352 in file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
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_force_recovery运行模式(译者注：通过改变innodb_force_recovery的值，1~6为6种不同模式)也不会有任何帮助(当损坏数据在系统的其它不同区域时，其它模式可能会有帮助，但它们都不能真正改变InnoDB处理页面数据的方式)。

下面的尝试将是错误的做法：

mysql> INSERT INTO test2 SELECT * FROM test;
ERROR 2013 (HY000): Lost connection TO MySQL server during query

你可能想对数据表进行扫描直到第一个被损坏的行，然后从MyISAM表中得到结果？ 不幸的是，运行之后的test2表格是空的。 在运行的同时，我看到一些数据被选中输出。 出现这种问题的原因是因为当MySQl崩溃时，一些数据是保存在缓存当中的，MySQL里还没有足够的数据可用来恢复MyISAM表格。

在这种情况下，我们可以通过使用一系列限制(LIMIT)查询语句，可以方便的进行手动恢复：

mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 10;
Query OK, 10 rows affected (0.00 sec)

Records: 10  Duplicates: 0  Warnings: 0
       
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20  Duplicates: 10  Warnings: 0

mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100  Duplicates: 20  Warnings: 0

mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200  Duplicates: 100  Warnings: 0

mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 300;
ERROR 2013 (HY000): Lost connection TO MySQL server during query

正如你所看到的，我可以从表中取出数据，直到使崩溃的MySQL的那一行。

在这种情况下，我们可以猜到，被损坏的数据应在200行和300行之间，我们可以使用二分搜索的方法再运行几个相同的语句。

注意：即使你不使用MyISAM表，而是想提取数据到脚本中，请确定使用LIMIT限制或主键(Primary Key)范围，因为当MySQL崩溃时，你会因网络中的数据包存在缓冲而丢失一些应当收到的数据。

所以，现在我们发现数据表中的损坏部分，我们需要以某种方式来跳过这部分。

要做到这一点，我们还需要找到损坏部分之后主键最大值，以便我们试一下更高的值。

mysql> SELECT max(id) FROM test2;
+---------+
| max(id) |
+---------+
|     220 |
+---------+
1 row IN SET (0.00 sec)

mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>250;
ERROR 2013 (HY000): Lost connection TO MySQL server during query

mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>300;
Query OK, 573140 rows affected (7.79 sec)
Records: 573140  Duplicates: 0  Warnings: 0

我试图跳过30行，但是它太少，而跳过80行是可行的。

再次重申，使用二分搜索，可以精确找到需要跳过有多少行，这样才能尽可能多恢复数据。 记录的长度值会比较有用，在这个例子中，每条记录大约有280个字节，每页有大约50条记录，这就不奇怪什么试图跳过30行是不够的：有时如果页目录被损坏，你需要跳过一个整页。如果页在较高的BTREE水平上被损坏了，你在使用此恢复方法时可能需要跳过了很多页（甚至整个子树）。你也可能需要跳过多个损坏的页，而不是这个例子中的一个页。

另一个提示:在MySQL的崩溃后，你还可以通过检查(CHECK TABLE)MyISAM表，以确保索引没有被损坏。

至此，我们尝试了如何从一个简单的被损坏的InnoDB表格中恢复数据。

在较为复杂的情况下，虽然使用尽可能低的恢复模式来进行，恢复数据的效果会更好，但您可能需要同时使用较高innodb_force_recovery模式防止清除(purging activity)，插入合并缓冲(insert buffer merge)或恢复事件记录(transactional log)。

在某些情况下，例如，如果集合索引中数据字典(data dictionary)或“根页”(root page)被损坏，以上描述的方法不会管用。在这种情况下，你可能需要使用InnoDB的恢复工具(Innodb recovery toolket )，这个工具集也可帮你恢复已删除的数据行或表格。

我还要提及在Percona公司，我们提供MySQL恢复援助，包括恢复InnoDB中被损坏和删除的数据。

评论

3 楼 lhs295988029 2014-02-12  

非常好，上次恢复时，丢失2张损坏的表，这个更加精确，有机会实验下！

2 楼 cgaolei 2009-07-01  

回hzlypan朋友的问题：

首先，原作者写这篇文章的方式是一步一步的引导和分析问题，所以需要对整篇文章做完整的阅读和理解后才能找到最合适的修复方式，所以文章中有些部分是可以省略的。

答问题1：我也试着用了innodb_force_recovery = 1及更高的值，都没有起作用，说明问题比较严重，用正规方法无法恢复，只好用后面的方法。

答问题2，3：我是直接跳过了中间的部分，而直接试用最后的方法。因为我的InnoDB表中的foregin key,转成myISAM没多大意义，反正是要想办法把可用数据导出来，所以跳过去了。

答问题4：我遇到的情况也是一样的，原作者所说的也是一样的，注意这里：
ERROR 2013 (HY000): Lost connection TO MySQL server during query
当2013出现后，数据库的连接就断开了。

所以，你遇到的情况正是文中所描述的，一模一样。

1 楼 hzlypan 2009-06-30  

早上来公司，发现自己的测试环境，数据库中的一个表出现了跟这里一模一样的问题，现在通过这篇文章的方法已经将表成功修复，只是丢失了200多条数据。
同时对文中几个地方有点疑问（我是一个初学者）：
1. check table那里，我在my.cnf配置文件中增加了"innodb_force_recovery = 1"配置并重启后，使用check table还是提示同样的2013错误，不能正确显示表的状态（后来我没管，继续后面的操作）
2. 根据第1点，已经修改了配置文件，但是执行下面的语句时也没成功，还是提示原来的错误号：

 mysql> INSERT INTO videos2 SELECT * FROM videos;  
 ERROR 2013 (HY000): Lost connection TO MySQL server during query 

不过里面已经有近7W条数据（总数据13W多）
3. 通过第2点，videos2表的max(id)=69373，但是我通过一个id一个id去被损表videos去select，还得到14条数据，同时也通过下面的语句成功修复此14条数据：

 mysql> INSERT IGNORE INTO videos2 SELECT * FROM videos WHERE id=69374;
 ......
 mysql> INSERT IGNORE INTO videos2 SELECT * FROM videos WHERE id=69398 

这样修复是否有缺陷，此14条数据为什么需要具体的id才可以。
4. 还有一个细节，每次出现2013错误后，mysql都会gone away，文中未提及，不知道是不是因为和作者遇到的情况不一样？

mysql> INSERT IGNORE INTO videos2 SELECT * FROM videos WHERE id < 69398;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> INSERT IGNORE INTO videos2 SELECT * FROM videos WHERE id < 69397;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: $my_database_name  #这里是我操作的数据库名

Query OK, 23 rows affected (1.42 sec)
Records: 69282  Duplicates: 69259  Warnings: 0

以上几点是我的疑问，不知道译者碰到的情况是不是也是这样？