注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

涅槃

文档收藏

 
 
 

日志

 
 
 
 

恢复损坏的InnoDB表格  

2013-05-25 15:10:44|  分类: mysql存储引擎 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

原作者: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数据库中基本上是没有用的。通过手动损坏的表格,我会得到:

Sql代码  收藏代码
  1. mysql> CHECK TABLE test;  
  2. ERROR 2013 (HY000): Lost connection TO MySQL server during query  
  3.          
  4. mysql> CHECK TABLE test;  
  5. +-----------+-------+----------+----------+  
  6. TABLE     | Op    | Msg_type | Msg_text |  
  7. +-----------+-------+----------+----------+  
  8. | test.test | CHECK | STATUS   | OK       |  
  9. +-----------+-------+----------+----------+  
  10. 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时,您可以运行如下语句:

Sql代码  收藏代码
  1. mysql> CREATE TABLE `test2` (  
  2.     ->   `c` char(255) DEFAULT NULL,  
  3.     ->   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,  
  4.     ->   PRIMARY KEY (`id`)  
  5.     -> ) ENGINE=MYISAM;  
  6. Query OK, 0 rows affected (0.03 sec)  
  7.          
  8. mysql> INSERT INTO test2 SELECT * FROM test;  
  9. Query OK, 229376 rows affected (0.91 sec)  
  10. Records: 229376  Duplicates: 0  Warnings: 0  

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

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

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

Sql代码  收藏代码
  1. mysql> OPTIMIZE TABLE test;  
  2. +-----------+----------+----------+----------------------------------+  
  3. TABLE     | Op       | Msg_type | Msg_text                         |  
  4. +-----------+----------+----------+----------------------------------+  
  5. | test.test | OPTIMIZE | error    | Got error -1 FROM storage engine |  
  6. | test.test | OPTIMIZE | STATUS   | Operation failed                 |  
  7. +-----------+----------+----------+----------------------------------+  
  8. 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处理页面数据的方式)。


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

Sql代码  收藏代码
  1. mysql> INSERT INTO test2 SELECT * FROM test;  
  2. ERROR 2013 (HY000): Lost connection TO MySQL server during query  

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


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

Sql代码  收藏代码
  1. mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 10;  
  2. Query OK, 10 rows affected (0.00 sec)  
  3.   
  4. Records: 10  Duplicates: 0  Warnings: 0  
  5.          
  6. mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 20;  
  7. Query OK, 10 rows affected (0.00 sec)  
  8. Records: 20  Duplicates: 10  Warnings: 0  
  9.   
  10. mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 100;  
  11. Query OK, 80 rows affected (0.00 sec)  
  12. Records: 100  Duplicates: 20  Warnings: 0  
  13.   
  14. mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 200;  
  15. Query OK, 100 rows affected (1.47 sec)  
  16. Records: 200  Duplicates: 100  Warnings: 0  
  17.   
  18. mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 300;  
  19. ERROR 2013 (HY000): Lost connection TO MySQL server during query  

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

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

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

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

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

Sql代码  收藏代码
  1. mysql> SELECT max(id) FROM test2;  
  2. +---------+  
  3. max(id) |  
  4. +---------+  
  5. |     220 |  
  6. +---------+  
  7. 1 row IN SET (0.00 sec)  
  8.   
  9. mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>250;  
  10. ERROR 2013 (HY000): Lost connection TO MySQL server during query  
  11.   
  12. mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>300;  
  13. Query OK, 573140 rows affected (7.79 sec)  
  14. 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中被损坏和删除的数据。

  评论这张
 
阅读(629)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018