恢复Innodb损坏的表

运行着Innodb 引擎的MySQL 在遇到硬件故障，内核bug 或者是突然断电，MySQL本身的bug，可能会造成Innodb表空间的损坏，提示的信息大致如下：

<code>InnoDB: Database page corruption on disk </code><code>or</code> <code>a failed</code>

<code>InnoDB: </code><code>file</code> <code>read of page </code><code>7.</code>

<code>InnoDB: You may have to recover </code><code>from</code> <code>a backup.</code>

<code>080703</code> <code>23</code><code>:</code><code>46</code><code>:</code><code>16</code> <code>InnoDB: Page dump </code><code>in</code> <code>ascii </code><code>and</code> <code>hex</code> <code>(</code><code>16384</code> <code>bytes):</code>

<code>… A LOT OF </code><code>HEX</code> <code>AND BINARY DATA…</code>

<code>080703</code> <code>23</code><code>:</code><code>46</code><code>:</code><code>16</code> <code>InnoDB: Page checksum </code><code>587461377</code><code>, prior</code><code>-</code><code>to</code><code>-</code><code>4.0</code><code>.</code><code>14</code><code>-</code><code>form checksum </code><code>772331632</code>

<code>InnoDB: stored checksum </code><code>2287785129</code><code>, prior</code><code>-</code><code>to</code><code>-</code><code>4.0</code><code>.</code><code>14</code><code>-</code><code>form stored checksum </code><code>772331632</code>

<code>InnoDB: Page lsn </code><code>24</code> <code>1487506025</code><code>, low </code><code>4</code> <code>bytes of lsn at page end </code><code>1487506025</code>

<code>InnoDB: Page number (</code><code>if</code> <code>stored to page already) </code><code>7</code><code>,</code>

<code>InnoDB: space </code><code>id</code> <code>(</code><code>if</code> <code>created with ></code><code>=</code> <code>MySQL</code><code>-</code><code>4.1</code><code>.</code><code>1</code> <code>and</code> <code>stored already) </code><code>6353</code>

<code>InnoDB: Page may be an index page where index </code><code>id</code> <code>is</code> <code>0</code> <code>25556</code>

<code>InnoDB: (index “PRIMARY” of table “test”.”test”)</code>

提示出现断言错误（assertion failure）；

对于损坏的表，大致可分为三种情况，Data corrupted In Secondary Index， Data corrupted In clustered key，Data corrupted In table dictionary，恢复难度依次增加；

下面我们来做一个简单恢复实验，我手动编辑test.ibd 文件，只修改一小部分，此时，我手动check 一下：

<code>mysql> check table test;</code>

<code>ERROR </code><code>2013</code> <code>(HY000): Lost connection to MySQL server during query</code>

第一次运行的时候，Innodb发现checksum error并出现crash的情况（运行check 的时候同样会发生crash），第二次运行的时候我设置了 innodb_force_recovery=1,但是check 的结果却是ok，对于check功能还是不要太过相信啊，

在Innodb_force_recovery=1 的情况下，我们可以建一张引擎为MyISAM同样表结构的表，对新表导入数据，然后去掉force recovery选项，重启MyISAM,并将引擎转换为INNODB，：

<code>mysql> CREATE TABLE `test2` (</code>

<code> </code><code>-</code><code>> `c` char(</code><code>255</code><code>) DEFAULT NULL,</code>

<code> </code><code>-</code><code>> `</code><code>id</code><code>` </code><code>int</code><code>(</code><code>10</code><code>) unsigned NOT NULL AUTO_INCREMENT,</code>

<code> </code><code>-</code><code>> PRIMARY KEY (`</code><code>id</code><code>`)</code>

<code> </code><code>-</code><code>> ) ENGINE</code><code>=</code><code>MYISAM;</code>

<code>Query OK, </code><code>0</code> <code>rows affected (</code><code>0.03</code> <code>sec)</code>

<code>mysql> insert into test2 select </code><code>*</code> <code>from</code> <code>test;</code>

<code>Query OK, </code><code>229376</code> <code>rows affected (</code><code>0.91</code> <code>sec)</code>

<code>Records: </code><code>229376</code> <code>Duplicates: </code><code>0</code> <code>Warnings: </code><code>0</code>

以上的操作确实有点简单，下面我们尝试把test.ibd 的page header部分全部擦除，这次甚至在innodb_force_recovery=1 的情况下，运行check table 同样会出现断言的错误(Assertion failure)：

<code>080704</code> <code>0</code><code>:</code><code>22</code><code>:</code><code>53</code> <code>InnoDB: Assertion failure </code><code>in</code> <code>thread </code><code>1158060352</code> <code>in</code> <code>file</code> <code>btr</code><code>/</code><code>btr0btr.c line </code><code>3235</code>

<code>InnoDB: Failing assertion: page_get_n_recs(page) > </code><code>0</code> <code>|| (level </code><code>=</code><code>=</code> <code>0</code> <code>&& page_get_page_no(page) </code><code>=</code><code>=</code> <code>dict_index_get_page(index))</code>

<code>InnoDB: We intentionally generate a memory trap.</code>

<code>InnoDB: Submit a detailed bug report to http:</code><code>/</code><code>/</code><code>bugs.mysql.com.</code>

<code>InnoDB: If you get repeated assertion failures </code><code>or</code> <code>crashes, even</code>

这个时候，提高innodb_force_recovery 参数值，也还是可能出现下面的情况:

对于上面这个语句，我们可能认为当扫描table时第一次遇到corrupted的row时，会报错，并把已扫描的row存储在MyISAM（非事务型存储引擎）中，但实践是test2最终是没有记录的，原因是：there is some buffering taking place and as MySQL crashes it does not store all data it could recover to MyISAM table；（对这个理解还不是很懂）

对于这种情况，只能加上limit，一部分一部分的导入：

<code>mysql> insert ignore into test2 select </code><code>*</code> <code>from</code> <code>test limit </code><code>10</code><code>;</code>

<code>Query OK, </code><code>10</code> <code>rows affected (</code><code>0.00</code> <code>sec)</code>

<code>Records: </code><code>10</code> <code>Duplicates: </code><code>0</code> <code>Warnings: </code><code>0</code>

<code>mysql> insert ignore into test2 select </code><code>*</code> <code>from</code> <code>test limit </code><code>20</code><code>;</code>

<code>Records: </code><code>20</code> <code>Duplicates: </code><code>10</code> <code>Warnings: </code><code>0</code>

<code>mysql> insert ignore into test2 select </code><code>*</code> <code>from</code> <code>test limit </code><code>100</code><code>;</code>

<code>Query OK, </code><code>80</code> <code>rows affected (</code><code>0.00</code> <code>sec)</code>

<code>Records: </code><code>100</code> <code>Duplicates: </code><code>20</code> <code>Warnings: </code><code>0</code>

<code>mysql> insert ignore into test2 select </code><code>*</code> <code>from</code> <code>test limit </code><code>200</code><code>;</code>

<code>Query OK, </code><code>100</code> <code>rows affected (</code><code>1.47</code> <code>sec)</code>

<code>Records: </code><code>200</code> <code>Duplicates: </code><code>100</code> <code>Warnings: </code><code>0</code>

<code>mysql> insert ignore into test2 select </code><code>*</code> <code>from</code> <code>test limit </code><code>300</code><code>;</code>

以上我们可以得出，损坏的row就在200行-300行之间，下面我们可以使用二分查找，在次缩小查找范围，

<code>mysql> select </code><code>max</code><code>(</code><code>id</code><code>) </code><code>from</code> <code>test2;</code>

<code>mysql> insert ignore into test2 select </code><code>*</code> <code>from</code> <code>test where </code><code>id</code><code>></code><code>250</code><code>;</code>

<code>mysql> insert ignore into test2 select </code><code>*</code> <code>from</code> <code>test where </code><code>id</code><code>></code><code>300</code><code>;</code>

<code>Query OK, </code><code>573140</code> <code>rows affected (</code><code>7.79</code> <code>sec)</code>

<code>Records: </code><code>573140</code> <code>Duplicates: </code><code>0</code> <code>Warnings: </code><code>0</code>

当跳过80行的时候，才insert成功；如果是 directory page 损坏，我们可能需要跳过更多page，如果是损坏的 higher level btree，可能会跳过更多，，，

在一些个别场景中像Data dictionary 或者root page 损坏，上面的方式可能不会奏效，此时可以尝试一把 innodb recovery toolkit 。。。

本文转自位鹏飞 51CTO博客，原文链接：http://blog.51cto.com/weipengfei/1565194，如需转载请自行联系原作者

恢复Innodb损坏的表

继续阅读

转详解C#数据库存取图片三大方式

BMP文件结构及图像每行字节计算方法

磁盘结构及在Linux中的命名

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件