您当前的位置:个人博客

Mysql数据恢复方法


博主: yanyanbin   2017-11-12 08:45

Mysql数据通过直接复制的方式备份后,如何通过备份文件还原数据?

   最近,得到一个数据库文件,里面的数据是直接复制Mysql数据库datadir指向的路径中的所有数据。最近尝试了很多方法,恢复了不少的数据,现在做个总结。

一、首先要判断该数据库文件中数据表的存储引擎是什么?因为不同存储引擎的表,存储数据的方式是不一样的,因而恢复数据的手段也是不同的。

   答:我看了自己数据库中的数据,发现有MyISAM类型的数据和InnoDB类型的数据。如果想看看这两种存储引擎的区别,可以参考:http://www.jb51.net/article/62457.htm

刚开始,我看了网上的很多的博客和帖子,得到的信息是:表存储结构为MyISAM的数据允许通过直接复制的方式进行备份,但是表存储结构为InnoDB类型的数据是不允许通过复制的方式进行备份的,就得通过其它的一些方法去恢复数据了。

 

二、表结构为MyISAM存储引擎的数据如何恢复?

   表结构为MyISAM存储引擎的数据恢复其实比较简单。MyISAM在创建表和插入数据等操作的时候会产生三种格式的文件*.FRM、*.MYD和*.MYI。*.frm是描述了表的结构,*.MYD保存了表的数据记录,*.MYI则是表的索引。恢复方法如下:

   (1)找到MySQL的安装路径,找到my.ini文件,打开找到datadir指向的路径,比如” datadir=F:/mysqlData/Data”

       也可以在Mysql命令行输入 show variables like ‘datadir’

  (2)在数据库中创建一个数据库,比如数据库名为test(最好和原名一样)

  (3)找到datadir指向路径的data文件夹,把备份的数据库(*.frm,*.myd,*.myi文件)复制到test目录下;

  (4)重启服务就可以了。

 

三、表结构为InnoDB存储引擎的数据如何恢复?

   表结构为InnoDB存储引擎的数据恢复就比较麻烦了,也有可能恢复不了。首先要判断该数据库中的表数据是共享表空间的还是独立表空间?

   InnoDB引擎的表数据存储在一个共享空间中和独立表空间中的方式又是不同的,想了解他们之间的区别,可以参考 http://blog.csdn.net/gaoxuefeng/article/details/7699400

3.1独立表空间的数据该如何恢复?

  如果当初设置把所有的数据库InnoDB引擎的表数据存储独立表空间中(数据库中会存在*.frm文件和*.ibd文件),那么数据是可能恢复的,只不过比较麻烦。方法如下:

 (1)先准备一台纯洁的mysql环境,也就是从启动到现在没有使用过INNODB的表结构的mysql环境(选择纯净的mysql环境,是为了避免后续操作中表id溢出导致的问题)

 (2)通过*.frm恢复表结构。

      1.首先创建一个数据库比如test2

      2.在test2数据库中创建表,表名要与恢复数据的表名一致表结构随意。例如:

          create table th_user (id int(11) NOT NULL ) ENGINE=InnoDB ;

      3. 关闭mysql, 用需要恢复的frm文件直接替换刚刚新建的frm文件;(数据在datadir指向路径下的test2文件中)

      4.在配置文件里添加innodb_force_recovery = 6;(这个步骤在很多文档中提到了,但是我并没有在配置文件中添加这个参数也恢复了表结构。)

      5.启动mysql,就能看到表结构了。

注:我在做实验的时候,有些表结构恢复不了,查看日志文件,试了很多方式也不行。所以这样的数据我没有恢复出来。

 (3)通过ibd文件来恢复数据(参考文献:http://www.chriscalender.com/recovering-an-innodb-table-from-only-an-ibd-file/

     注:现在拿到了表结构,重新建立了一个新的数据库,继续进行实验。(我没有在原来恢复了表结构的数据库中继续实验,怕出现问题,所有就重新建立了数据库来实验,有兴趣的可以尝试在原有的数据库上试试,或许可以。)

         1.创建数据库test3,创建表结构(通过步骤(2)已经得到了表结构数据),假设在这里创建了一个表th_user.

         2. 执行 alter table th_user discard tablespace;

         3.把备份文件中对应的ibd文件复制到数据库文件目录下(数据在datadir指向路径下的test3文件中)

         4.执行 alter table th_user import tablespace;

         5.此时百分之99.9999%会产生一个错误. ERROR 1030 (HY000): Got error -1 from storage engine。因为MySQL当前的table id和ibd文件中的table id是不匹配的。所以接下来要通过某些方法使得这两个table id相同

         6.查看错误日志文件:(一般是数据库下面以.err结尾的文件),会发现比如:2016-07-11 18:18:05  InnoDB: Error: tablespace id in file '.th_user.ibd' is 1193, but in the InnoDBInnoDB: data dictionary it is 1.(表明当前table id是1,而th_uesr.ibd文件中的table id是 1193)

         7.执行 drop table th_user;

         8.执行for ($1=1; $i<=1191; $1++) { CREATE TABLE t# (id int) ENGINE=InnoDB;}创建1193-2个表,也就是在mysql的表空间中创建了1191个table id,加上第一次创建的 table id,mysql的table id到了1192,下一个表的id就是1193了。

         9. drop db test3;

        10.重复步骤2-5即可.

 

3.2共享表空间的数据该怎么恢复呢?

    共享表空间的数据想要完全恢复有个前提:所有文件都完好无损(ibdata1文件、.frm文件和.ibd文件)。恢复方法如下:

    (1)先准备一台纯洁的mysql环境(最好版本一致)

    (2)创建相应的数据库名称

    (3)关闭mysql,把备份的*.frm和*.ibd文件拷贝到相应的数据库下

    (4)把备份的其它格式文件拷贝到datadir指向的目录下(最重要的数据是ibdata1文件数据)

   (5)启动mysql,查看结果。

评论请先 登录