您的位置:首页 > 数据库 > > 正文

mysql云数据库数据恢复(MySQL 利用frm文件和ibd文件恢复表数据)

更多 时间:2021-10-14 00:03:02 类别:数据库 浏览量:204

mysql云数据库数据恢复

MySQL 利用frm文件和ibd文件恢复表数据

frm文件和ibd文件简介

   在mysql中,如果我们使用了默认的存储引擎innodb创建一张表,那么在文件夹下面就会出现表名.frm和表名.ibd两个文件,如果我们使用的是myisam存储引擎,那么就会出现三个文件,这里我们给出例子:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • [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",这个表的结构是这样的:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 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.看看重启之后错误日志输出的结果吧,如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 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,我们看看最终的结果:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 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,可以看到结果如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 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的方法来恢复表中的数据。整个过程看着比较复杂,其实完全可以按照步骤抽象出来一个脚本,这样在下次恢复的时候,只需要输入要恢复的表的名称,就可以快速的恢复表结构和数据,不失为一种应急的数据恢复预案。

    以上就是mysql 利用frm文件和ibd文件恢复表数据的详细内容,更多关于mysql 恢复表数据的资料请关注开心学习网其它相关文章!

    原文链接:https://cloud.tencent.com/developer/article/1533746

    您可能感兴趣