MySQL 恢复表数据 MySQL 利用frm文件和ibd文件恢复表数据
AsiaYe 人气:0frm文件和ibd文件简介
在MySQL中，如果我们使用了默认的存储引擎innodb创建一张表，那么在文件夹下面就会出现表名.frm和表名.ibd两个文件，如果我们使用的是Myisam存储引擎，那么就会出现三个文件，这里我们给出例子：
[root@ /data/yeyz]#ll total 580 -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 a.frm -rw-rw---- 1 mysql mysql 0 Apr 3 17:44 a.MYD -rw-rw---- 1 mysql mysql 1024 Apr 3 17:44 a.MYI -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 b.frm -rw-rw---- 1 mysql mysql 98304 Apr 3 17:45 b.ibd -rw-rw---- 1 mysql mysql 61 Nov 23 09:54 db.opt -rw-rw---- 1 mysql mysql 8556 Apr 29 21:37 tbl_test_2.frm -rw-rw---- 1 mysql mysql 98304 Apr 29 21:37 tbl_test_2.ibd -rw-rw---- 1 mysql mysql 8556 Apr 29 21:33 tbl_test.frm -rw-rw---- 1 mysql mysql 98304 Apr 29 21:33 tbl_test.ibd -rw-rw---- 1 mysql mysql 8614 Apr 29 21:40 test.frm -rw-rw---- 1 mysql mysql 98304 Apr 29 21:43 test.ibd -rw-rw---- 1 mysql mysql 8666 Apr 2 15:13 unstandard_ins.frm -rw-rw---- 1 mysql mysql 98304 Apr 3 11:46 unstandard_ins.ibd -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 yeyz.frm -rw-rw---- 1 mysql mysql 28 Apr 3 17:44 yeyz.MYD -rw-rw---- 1 mysql mysql 2048 Apr 3 17:44 yeyz.MYI
其中ibd文件是innodb的表数据文件，而frm文件是innodb的表结构文件，mysiam存储引擎的表中，frm是表结构，MYI文件是索引文件，而MYD文件是数据文件，从这里也可以看出，innodb存储引擎的索引和数据是在一起的，而Myisam存储引擎索引和数据是分开的。
需要注意的是，这个frm文件和ibd文件都是不能直接打开的。
考虑这样一种需求，数据库需要快速恢复一个表中的数据，而这个表所在的库的数据量非常大，恢复起来可能耗费的时间也比较长，那么全库恢复肯定不是最佳的选择。那这种情况下怎么办呢？我们可以使用frm文件盒ibd文件来对数据进行恢复。下面我们分析分析这个过程。
frm文件恢复表结构
当然，表结构需要使用frm文件来恢复。我们第一反应想到的是，可以把这两个文件直接拷贝到一个新的数据库实例中，然后直接启动实例，这样可以么？当然是不行的。侄儿要是能行，估计DBA都可以下岗了。哈哈，废话不多说，来看操作过程。
首先，我们创建一个新的实例专门用来恢复数据，如果你使用线上的某一台机器来执行恢复，那你必须承担数据库重启的风险以及DML阻塞的风险，所以最好的方法还是使用一台专门的实例来进行恢复。那么我们如何从frm文件中拿到我们想要的表结构呢？
我拿线上的一个记录慢日志的表举个例子，为了写着方便，表名称我写成了"aaa",这个表的结构是这样的：
mysql--root@localhost:test_recover 12:08:43>>show create table aaa\G *************************** 1. row *************************** Table: aaa Create Table: CREATE TABLE `aaa` ( `maintain_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增列', `slowquery_filename` varchar(50) DEFAULT NULL COMMENT '慢日志文件名', `slowquery_path` varchar(150) DEFAULT NULL COMMENT '慢日志全路径', `slowquery_process` tinyint(20) unsigned NOT NULL DEFAULT '0' COMMENT '慢日志是否被解析', `slowquery_uploadtime` datetime DEFAULT CURRENT_TIMESTAMP, `slowquery_analyzetime` date DEFAULT NULL COMMENT '慢日志解析时间', `slowquery_starttime` date DEFAULT NULL, `slowquery_endtime` date DEFAULT NULL, `instance_ip` varchar(15) DEFAULT NULL COMMENT '慢日志IP地址', `instance_port` int(11) DEFAULT NULL COMMENT '慢日志端口号地址', PRIMARY KEY (`maintain_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set, 1 warning (0.01 sec)
要从frm文件中得到这样的一个表，我们要做的步骤如下：
1、在实例上创建一个同名的表aaa，由于我们不知道这个表的结构，我们可以给它设定只有一个字段id，也就是
create table aaa (id int);
我们知道，这个时候会在对应的data目录下生成新的aaa.frm和aaa.ibd文件，然后我们使用我们备份的aaa.frm来替代之前的aaa.frm,然后重启数据库。
是的，你没有看错，我们使用备份的表结构文件来替代它生成的表结构文件。
2.看看重启之后错误日志输出的结果吧，如下：
2019-03-22T03:17:28.652390Z 16 [Warning] InnoDB: Table test_recover/store_goods_price contains 1 user defined columns in InnoDB, but 12 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2019-04-02T07:56:31.558461Z 41 [Warning] InnoDB: Table test_recover/dv_control contains 1 user defined columns in InnoDB, but 14 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2019-05-23T03:14:10.161122Z 92 [Warning] InnoDB: Table test_recover/aaa contains 1 user defined columns in InnoDB, but 10 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
可以看到，10-12行的错误日志里面提示我们这个表aaa只包含1个字段，但是frm中包含10个字段，字段的数量不符。
这和我们预料的结果符合，因为我们在创建表aaa的时候，只给了他1个字段id，而我们要恢复的aaa表有10个字段，肯定是无法从frm中读取的。此时你可能很容易就能想到，如果我们把这个aaa表的字段调成10个，那么最终的结果是什么呢？
3.将aaa表的字段数量升级成10个，然后重新拷贝frm文件，修改配置文件中的参数innodb_force_recovery=6，我们看看最终的结果：
mysql--root:(none) 12:04:20>>use test_recover; Database changed mysql--root:test_recover 12:04:25>>create table aaa (id1 int,id2 int,id3 int,id4 int,id5 int,id6 int,id7 int,id8 int,id9 int,id10 int); Query OK, 0 rows affected (0.03 sec) mysql--root@localhost:test_recover 12:05:08>>show create table aaa\G *************************** 1. row *************************** Table: aaa Create Table: CREATE TABLE `aaa` ( `id1` int(11) DEFAULT NULL, `id2` int(11) DEFAULT NULL, `id3` int(11) DEFAULT NULL, `id4` int(11) DEFAULT NULL, `id5` int(11) DEFAULT NULL, `id6` int(11) DEFAULT NULL, `id7` int(11) DEFAULT NULL, `id8` int(11) DEFAULT NULL, `id9` int(11) DEFAULT NULL, `id10` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
然后我们重启实例，再次查看表aaa，可以看到结果如下：
mysql--root:test_recover 12:08:43>>show create table aaa\G *************************** 1. row *************************** Table: aaa Create Table: CREATE TABLE `aaa` ( `maintain_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增列', `slowquery_filename` varchar(50) DEFAULT NULL COMMENT '慢日志文件名', `slowquery_path` varchar(150) DEFAULT NULL COMMENT '慢日志全路径', `slowquery_process` tinyint(20) unsigned NOT NULL DEFAULT '0' COMMENT '慢日志是否被解析', `slowquery_uploadtime` datetime DEFAULT CURRENT_TIMESTAMP, `slowquery_analyzetime` date DEFAULT NULL COMMENT '慢日志解析时间', `slowquery_starttime` date DEFAULT NULL, `slowquery_endtime` date DEFAULT NULL, `instance_ip` varchar(15) DEFAULT NULL COMMENT '慢日志IP地址', `instance_port` int(11) DEFAULT NULL COMMENT '慢日志端口号地址', PRIMARY KEY (`maintain_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set, 1 warning (0.01 sec)
可以看到，我们想要的表结构已经从frm文件中恢复出来了，需要注意的是，这个过程中我们并没有使用ibd文件。
总结一下利用frm文件恢复表结构的步骤：
1、首先创建一个同名的表，然后启动实例
2、使用备份的frm文件替代生成的frm文件，重启实例
3、查看错误日志，从错误日志中获取到备份的frm文件中的字段数量m
4、重新创建同名表，保证字段数量为m，与备份表保持一致，然后重新拷贝备份的frm文件到对应目录
5、修改实例的配置文件中的参数innodb_force_recovery=6，然后重启数据库，就可以看到对应的表结构创建语句，我们把它保存下来，下一步恢复数据的时候要用。这一步相当重要
6、将参数innodb_force_recovery=6注释掉，重新使用默认的值，然后重启数据库，准备恢复表数据。
至此，表结构恢复完毕。
解释一下innodb_force_recovery参数，这个参数的最大值是6，在该等级下，仅支持一部分查询功能，DML都不支持，从名称就可以看出来，这是在一些强行恢复的场景下才会使用的参数，一般情况下这个参数可以不要，使用默认值就行。有兴趣更深了解的同学可以参考官方文档。
ibd文件恢复表数据
上一步执行完成之后，我们已经获取了对应的表结构，现在我们看看如何恢复表数据。
恢复表数据的方法比较简单，大体步骤如下：
1、利用我们上一步中获取的建表语句，重新创建一张表，然后执行：
flush table aaa for export;
这个语法是将表里面的数据落盘，并获取该表的锁，为后面恢复做好准备。
2、然后我们使用如下语句：
alter table aaa discard tablespace;
这个语句会删除当前的ibd文件。
3、然后我们使用我们之前备份的ibd文件，将其拷贝到对应的实例目录下面
4、最后在将ibd文件重新加载进来，使用如下语句：
alter table aaa import tablespace;
重启数据库，这样，我们的数据就恢复成功了。
简单总结一下
整个恢复的流程算是介绍完了，其中比较巧妙的地方就是从frm文件中获取表结构信息，我们使用了两次拼凑表创建语句的方法，最终得到了待恢复的表的表结构，然后使用alter table discard tablespace和alter table import tablespace的方法来恢复表中的数据。整个过程看着比较复杂，其实完全可以按照步骤抽象出来一个脚本，这样在下次恢复的时候，只需要输入要恢复的表的名称，就可以快速的恢复表结构和数据，不失为一种应急的数据恢复预案。
加载全部内容