恢复损坏的InnoDB表格

最新推荐文章于 2021-09-04 22:50:54 发布

cgaolei

最新推荐文章于 2021-09-04 22:50:54 发布

阅读量200

点赞数

分类专栏： Technical 文章标签： MySQL SQL Server SQL 搜索引擎 performance

Technical 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

原作者：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中被损坏和删除的数据。