运行着Innodb 引擎的MySQL 在遇到硬件故障，内核bug 或者是突然断电，MySQL本身的bug，可能会造成Innodb表空间的损坏，提示的信息大致如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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):
… A LOT OF
HEX
AND BINARY DATA…
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
|
提示出现断言错误（assertion failure）；
对于损坏的表，大致可分为三种情况，Data corrupted In Secondary Index， Data corrupted In clustered key，Data corrupted In table dictionary，恢复难度依次增加；
下面我们来做一个简单恢复实验，我手动编辑test.ibd 文件，只修改一小部分，此时，我手动check 一下：
|
1
2
3
4
5
6
7
8
9
|
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)
|
第一次运行的时候，Innodb发现checksum error并出现crash的情况（运行check 的时候同样会发生crash），第二次运行的时候我设置了 innodb_force_recovery=1,但是check 的结果却是ok，对于check功能还是不要太过相信啊，
在Innodb_force_recovery=1 的情况下，我们可以建一张引擎为MyISAM同样表结构的表，对新表导入数据，然后去掉force recovery选项，重启MyISAM,并将引擎转换为INNODB，：
|
1
2
3
4
5
6
7
8
9
|
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
|
以上的操作确实有点简单，下面我们尝试把test.ibd 的page header部分全部擦除，这次甚至在innodb_force_recovery=1 的情况下，运行check table 同样会出现断言的错误(Assertion failure)：
|
1
2
3
4
5
|
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 参数值，也还是可能出现下面的情况:
|
1
2
|
mysql> insert into test2 select
*
from
test;
ERROR
2013
(HY000): Lost connection to MySQL server during query
|
对于上面这个语句，我们可能认为 当扫描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，一部分一部分的导入：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
|
以上我们可以得出，损坏的row就在200行-300行之间，下面我们可以使用二分查找，在次缩小查找范围，
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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
|
当跳过80行的时候，才insert成功；如果是 directory page 损坏，我们可能需要跳过更多page，如果是损坏的 higher level btree，可能会跳过更多，，，
在一些个别场景中像Data dictionary 或者root page 损坏，上面的方式可能不会奏效，此时可以尝试一把 innodb recovery toolkit 。。。
本文转自 位鹏飞 51CTO博客，原文链接：http://blog.51cto.com/weipengfei/1565194，如需转载请自行联系原作者
![[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响](https://ucc.alicdn.com/pic/developer-ecology/c2f83387419e42d79388f316eb675ec7.png?x-oss-process=image/resize,h_160,m_lfit)
![[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率](https://ucc.alicdn.com/pic/developer-ecology/cdf8a87bb36b46ebb0177d093c480bd4.png?x-oss-process=image/resize,h_160,m_lfit)