容我先说一句:千万不要直接拷贝数据库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文件数据库数据异常无法读取,这个传统方式数据库引擎依然会崩溃报错,报错完完全全一样。这个原始做法写在这里只是为了记录