(转)MySQL InnoDB修复笔记

转自：https://xuanwobbs.com.cn/archives/2017-02/mysql-innodb-frm-ibd-repair.html

 

容我先说一句：千万不要直接拷贝数据库data目录备份  千万不要直接拷贝数据库data目录备份  千万不要直接拷贝数据库data目录备份（重要的事情重复三遍）

当然如果你是从搜索引擎搜到这篇文章的，恐怕上面那句话也没什么用了.

某人丢了一个用r1soft直接备份data目录数据库名文件夹的WordPress数据库备份文件给我（不含ibdata和log只有一个数据库名目录），问有没有救，我一打开...满眼的frm和ibd文件 差点没昏过去...

照经验 InnoDB这么直接复制粘贴要蛋疼死，但据说数据很重要让我想尽办法修。。没法子硬着头皮上吧

阅读本文需要一定运维知识

需要frm和ibd文件，frm文件用于提取表定义 （CREATE TABLE语句） 。frm文件丢失要有备份的表定义，如果这个也没有，至少必须有ibdata1

第一阶段：使用自动化工具让数据库“接受”frm和ibd文件

此阶段需要innodb_file_per_table=1之设定，也就是独立表空间（File-Per-Table tablespace）。共享表空间请直接跳转第二阶段对应段落

MySQL之所以无法像MyISAM直接读取独立表空间的tablespace，是因为InnoDB内部维护了一个Table counter，如果表的tablespace id与数据库内部的counter不合，启动会报错并且 innodb_force_recovery也无效， 传统解决方式我会在本文的杂记提及。 （MYSQL5.6后不会再有此错误）

这里先用来自zcgonvh的工具（在此表示感谢 减少了很多工作量）进行批量导入。备份下载 密码zcgonvh

此工具需要Windows环境 .net4.x版本、MySQL5.6的最新版本。需要一个专用于修复的环境否则可能损坏现有数据库！

工具使用方式：

InnoDBRestore <username> <password> <port> <srcdir> <destDB>

例如

InnoDBRestore root pass 3306 c:\dbcopy my_database

祝好运吧。如果运气好，无报错， c:\dbcopy 下的MyISAM和InnoDB数据都会被导入 my_database（my_database不需要提前创建）。

然而你可能会和我一样，遇上了数据错误（天知道为什么），导入过程可能会报错。这些报错很可能是连接被关闭

restoring : wp_comments.frm

unknown error:MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. ---> System.IO.IOException: 无法从传输连接中读取数据: 远程主机强迫关闭了一个现有的连接。。 ---> System.Net.Sockets.SocketException: 远程主机强迫关闭了一个现有的连接。

然后同时，查看数据库的err日志，可能会有类似以下报错：

InnoDB: Error: trying to access page number 1372160 in space 1,
InnoDB: space name recovery1/wp_comments,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.

由于InnoDB引擎遇上了异常，MySQL崩溃退出，导致连接断开。这种情况下，到MySQL的data目录下，删除刚才导入的数据库的文件夹以及ib_logfile0、ib_logfile1、ibdata1（也就是重置所有InnoDB引擎相关数据）。然后再启动MySQL。

将引发故障的表文件(ibd、frm)单独移出来，留作阶段2修复使用，再次执行InnoDBRestore，如再遇上故障重复以上步骤，直到工具不再报错为止。

使用mysqldump将表导出来。建议添加--skip-extended-insert参数以便数据检查，如果上面一切都顺利，无论是导入还是导出都没有任何报错，也需要仔细检查恢复出来的数据是否有异常（很大的负数、数据参杂乱码、不合理的日期等），有些情况下会有隐性损坏情况。如果没有，那么恭喜你数据就恢复完成了，不需要继续向下阅读了

......然而你可能像我一样，导出时再次遇上相似故障

ERROR 2013 (HY000): Lost connection to MySQL server during query

查看err日志后，发现另一个原先看起来成功导入的ibd文件dump时由于数据错误也失败了，修改my.cnf，添加innodb_force_recovery=6。然后再重新启动，再次尝试dump查看是否成功，如成功需要仔细检查是否数据正确。如果连 innodb_force_recovery=6 也无法获得正确数据的话，只能跳过出故障的表，将其余正常的表导出。出故障的表通过第二阶段进行修复。

第二阶段：修复异常的表

需要环境：Linux【此处使用CentOS6】、MySQL5.6+、编译套件、undrop-for-innodb

本步骤均以 wp_comments.ibd为例

安装undrop-for-innodb工具，只需要执行make命令进行编译，很简单也非常快。

该工具可用于很多 InnoDB 灾难性数据丢失场景的数据库救援。救援的意思是尽量恢复数据，通常需要这个工具的场合都是很糟糕的，运气好的情况下你或许能全部提取出。因此无论如何依然不能直接拷贝InnoDB数据库。p.s.今年1月此工具停止进一步开发了，很可惜

make编译后会在其目录生成以下可执行工具：

c_parser
innochecksum_changer
stream_parser

1.使用

./stream_parser -f wp_comments.ibd

拆出ibd文件结构

2.使用mysqlfrm拆出包含表结构的CREATE TABLE语句，在第一阶段 zcgonvh的工具里有一个Windows的MySqlFrm.exe亦可使用，这里以该工具为例。Linux的 mysqlfrm可以参考下面本文后杂记

mysqlfrm <username> <password> <port> <srcdir>
例如：
mysqlfrm root pass 3306 c:\dbcopy

会在同目录下对每个frm文件生成一个.sql文件 内含创建表语句。注意该工具生成的CREATE TABLE语句不含分号，会对之后操作造成影响，需要在语句末尾添加一个分号

3.拆出的ibd文件结构会存储在pages-wp_comments.ibd里。包含以下子目录：

FIL_PAGE_INDEX：一般PAGE，依照其ID存放

FIL_PAGE_TYPE_BLOB： 如果遇上较大的数据（例如comments里有text类型的数据并且内容较多），InnoDB会使用BLOB类PAGE存储数据。需检查此目录是否有文件。如有，说明此表使用了BLOB，之后提取命令需要用-b参数指定此目录进行提取

这里有个问题，很显然我的情况没有原数据库的ibdata1，无从知晓主键index_id（该表主键索引的PAGE ID，存储于ibdata1的SYS_INDEXES内），因此只能瞎蒙主键所在的PAGE。

↓↓↓↓↓↓↓↓ 以下步骤假设你有 ibdata1 如没有请跳过 ↓↓↓↓↓↓↓↓

按照步骤1拆分ibdata1，然后编辑recover_dictionary.sh脚本里的mysql命令行 加入用户名密码（不然有可能脚本导出数据后脚本也无法将数据导回数据库）。然后执行此脚本，会将SYS系列表导入test数据库

使用mysql命令行进入test数据库后，执行：

mysql> select * from SYS_TABLES where NAME like "%/wp_comments";

+-----------------------+----+--------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-----------------------+----+--------+------+--------+---------+--------------+-------+
| recovery1/wp_comments | 16 | 15 | 1 | 0 | 80 | | 2 |
+-----------------------+----+--------+------+--------+---------+--------------+-------+
1 row in set (0.00 sec)

此table的ID为16，然后执行

mysql>  SELECT * FROM SYS_INDEXES where table_id=16;

+----------+----+---------------------------+----------+------+-------+---------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+----+---------------------------+----------+------+-------+---------+
| 16 | 22 | PRIMARY | 1 | 3 | 2 | 3 |
| 16 | 23 | comment_post_ID | 1 | 0 | 2 | 4 |
| 16 | 24 | comment_approved_date_gmt | 2 | 0 | 2 | 5 |
| 16 | 25 | comment_date_gmt | 1 | 0 | 2 | 6 |
| 16 | 26 | comment_parent | 1 | 0 | 2 | 7 |
| 16 | 27 | comment_author_email | 1 | 0 | 2 | 8 |
+----------+----+---------------------------+----------+------+-------+---------+
6 rows in set (0.00 sec)

即可获得主键的index_id为22，因此对应的page是：pages-wp_comments.ibd/FIL_PAGE_INDEX/0000000000000022.page。

注：如果是只有ibdata1（共享表空间）的情况，下文的数据提取则直接从pages-ibdata1/FIL_PAGE_INDEX/0000000000000022.page提取即可

↑↑↑↑↑↑↑↑ 以上步骤假设你有 ibdata1 如没有请跳过 ↑↑↑↑↑↑↑↑

可是我没有【上面的步骤是数据恢复以后模拟的，并非实际情况】只好一个个page试，使用c_parser命令对每个page尝试提取。此表数据结构是COMPACT，因而使用参数-5。如果是MYSQL5.6以上的格式用-6。不确定的话5和6都试下

本例BLOB目录下有文件，需要-b参数指定BLOB目录以确保数据完整。

步骤2得到的表结构定义（CREATE TABLE）放在 wp_comments.create.sql

将输出指向到less以便阅读：

[root@Test undrop-for-innodb]#./c_parser -5f ./pages-wp_comments.ibd/FIL_PAGE_INDEX/XXXXX.page -b ./pages-wp_comments.ibd/FIL_PAGE_TYPE_BLOB/ -t ./wp_comments.create.sql | less

XXXXX替换成具体的page编号，建议从头开始尝试（对两个有类似问题的表修复的结果似乎暗示第一个的成功概率最高），直到获得了明显正确的结果（日期正确 大部分数据正常 Records list为Valid）

c_parser会将tsv数据dump到标准输出管道，并且很贴心的将对应数据恢复SQL命令特意单独输出到了错误输出（某个N年未更新的工具还需要自行构建命令导入）。可以使用以下命令对含有正确数据索引的page做最终导出：

./c_parser -5f pages-wp_comments.ibd/FIL_PAGE_INDEX/0000000000224178.page -b pages-wp_comments.ibd/FIL_PAGE_TYPE_BLOB/ -t wp_comments.create.sql > wp_comments 2> wp_comments.sql

会得到

wp_comments：提取出来的tsv格式数据
wp_comments.sql：将tsv导入数据库的SQL命令

两个文件。我们需要将这两个文件挪到/tmp文件夹（规避权限问题），然后修改 wp_comments.sql 内的LOAD DATA LOCAL INFILE路径，使之符合新的tsv文件路径。

之后要做的就很简单了，在一个数据库用CREATE TABLE语句创建一个同名同定义的表，然后SOURCE /tmp/wp_comments.sql;就能将数据导进数据库了。

最后dump出来，就是标准的SQL格式了。

杂记

以下只是探索阶段的一些没啥用的记录，可以直接无视。有兴趣可以看看

第一阶段使用传统方式导入tablespace的方法：

环境要求： Linux【此处使用CentOS6】、MySQL5.6+、hexdump 需要专用于修复的环境

以wp_commentmeta数据恢复为例，有原生方式和简单方式通过frm文件获取表定义，选一即可

========原生方式获取表定义========

创建数据库略过，用以下命令创建同名的新表：

CREATE TABLE wp_commentmeta (id int(11) NOT NULL ) ENGINE=InnoDB ;

关闭数据库

用备份里的frm文件替换mysql data目录刚才新建的frm。添加 innodb_force_recovery=6到my.cnf

启动数据库，进入刚才新建的数据库，执行

show tables;

几乎可以肯定看不到任何表，此时查看mysql错误日志 会有以下信息

InnoDB: table recovery1/wp_commentmeta contains 1 user defined columns in InnoDB, but 4 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how to resolve it

意思是有4个 columns而我们只定义了1个。在恢复数据库内drop掉 wp_commentmeta。再关闭数据库 去掉 innodb_force_recovery，再启动

再创建一个有4个 columns的 wp_commentmeta：

CREATE TABLE ` wp_commentmeta `(
`weiboid` bigint(20),
`weiboid2` bigint(20),
`weiboid3` bigint(20),
`weiboid4` bigint(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

关闭MySQL，再次用备份里的frm文件替换mysql data目录刚才新建的frm ，添加 innodb_force_recovery=6，再启动，进入数据库，使用SHOW CREATE TABLE  wp_commentmeta;即可获得表定义。之后即可drop掉这个表然后去掉 innodb_force_recovery再启动

===========简单方式=============

使用mysqlfrm（需要额外安装） 设置的port必须没有被占用

mysqlfrm --server=root:pass@localhost:3306 --port 3307  /路径/到/wp_commentmeta.frm

===============================

使用hexdump获取table id

hexdump -C wp_commentmeta.ibd |head -n 3 |tail -n 1|awk '{print $6$7}'

ID会是16进制，转换成10进制即可。我获得了1b79 转换后得到7033

如果使用原生方式，由于创建过两次表（-2），然后又要腾出占位空间（-1），需要创建7030个表去“堆”Table counter：

for i in `seq 1 7030`; do mysql -u用户 -p密码 数据库 -e "CREATE TABLE iinser$i (id bigint(20) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id)) ENGINE=innodb "; done

如果使用简单方式，只需要腾出占位空间（-1），因此需要创建7032个表去 “堆”Table counter：

for i in `seq 1 7032`; do mysql -u用户 -p密码 数据库 -e "CREATE TABLE iinser$i (id bigint(20) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id)) ENGINE=innodb "; done

可能会有在命令行使用-p指定密码不安全的错误提示，无视即可（删除的步骤仅在MYSQL5.5及以下需要使用）

命令执行完后，还暂时不需要修改recovery模式，使用刚才获取的表定义，创建同名表：

CREATE TABLE `wp_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`meta_id`),
KEY `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

然后丢弃掉这个空白表的tablespace：

alter table wp_commentmeta discard tablespace;

将 wp_commentmeta.ibd文件复制到对应的mysql data目录下的数据库文件夹，然后import：

alter table wp_commentmeta import tablespace;

修改my.cnf 添加 innodb_force_recovery=6，重启mysql，即可将该表数据dump。如果要dump其他表，删除ib_logfile0、ib_logfile1、ibdata1以及刚才创建的临时数据库文件夹，然后重复上述步骤“即可”（不得不说实在是太太太太麻烦死了）

另外需要特别注意的是：这个方式与之前介绍的简便方式，得到的效果是完全一样的。

也就是说，如果ibd文件数据库数据异常无法读取，这个传统方式数据库引擎依然会崩溃报错，报错完完全全一样。这个原始做法写在这里只是为了记录