恢复Innodb损坏的表

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

运行着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数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
14天前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之如何用InnoDB引擎创建Federated表
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
17 1
|
9月前
|
存储 关系型数据库 MySQL
MySQL使用InnoDB引擎时一张表最大可以储存数据大小为 64TB,那为什么当表中的单行数据达到16KB时,这张表只能储存一条数据?
MySQL使用InnoDB引擎时一张表最大可以储存数据大小为 64TB,那为什么当表中的单行数据达到16KB时,这张表只能储存一条数据?
|
关系型数据库 MySQL 测试技术
只有.frm和.ibd文件时如何批量恢复InnoDB的表---发表到爱可生开源社区
很多时候因为MySQL数据库不能启动而造成数据无法访问,但应用的数据通常没有丢失,只是系统表空间等其它文件损坏了,或者遇到MySQL的bug。
106 0
|
存储 关系型数据库 MySQL
MySQL InnoDB表和索引之聚簇索引与第二索引
MySQL InnoDB表和索引之聚簇索引与第二索引
71 0
|
存储 SQL 缓存
【MySQL从入门到精通】【高级篇】(六)MySQL表的存储引擎,InnoDB与MyISAM的对比
上一篇文章介绍了MySQL中SQL语句的执行流程 【MySQL从入门到精通】【高级篇】(五)MySQL的SQL语句执行流程,在介绍执行流程时提到了InnoDB和MyISAM两种存储引擎。这篇文章将来详细介绍下这两种存储引擎。
240 0
【MySQL从入门到精通】【高级篇】(六)MySQL表的存储引擎,InnoDB与MyISAM的对比
|
存储 运维 安全
数据库运维之InnoDB存储引擎表损坏修复方法
InnoDB存储引擎表的损坏可能是多种因素导致的,比如服务器断电、系统崩溃、硬盘损坏、写数据过程中mysqld进程被kill掉。
983 0
|
SQL 关系型数据库 MySQL
[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响
[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响
113 0
[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响
|
存储 Oracle 关系型数据库
[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率
[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率
139 0
[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率
|
存储 关系型数据库 MySQL
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
|
存储 关系型数据库 MySQL
InnoDB表聚集索引层高什么时候发生变化(1)
InnoDB表聚集索引层高什么时候发生变化