mysql只有.ibd和.frm文件恢复数据

mysql只有.ibd和.frm文件恢复数据

我们知道mysql数据库存储的数据实际上也是存储在文件里的,只是这文件只有mysql能读取,如果是Myisam引擎,则一张表有三个文件:

xxx.frm 表结构文件
xxx.MYD 数据文件
xxx.MYI 索引文件

而对于Innodb引擎,一张表只有两个文件:

xxx.ibd 数据和索引文件
xxx.frm 表结构文件

mysql的这些数据文件存储在mysql配置文件datadir指定的存储目录中

datadir = /usr/local/var/mysql/

以库名为文件夹,文件夹里面的文件即为各张表的表文件,比如我的本机的mysql数据目录在:/usr/local/var/mysql,里面有一个目录叫wordpress,说明我有一个数据库名叫wordpress

Xnip2018-12-13_03-04-38.jpg

再看wordpress目录中的文件:

Xnip2018-12-13_03-05-03.jpg

可以看出,wordpress这个库的表都是innodb引擎的,如果我直接把wordpress目录复制一份放到另一台机的mysql数据目录中,一般来说,那台机是无法读取出这个数据文件的表的,虽然你show tables能看到表,但你select数据的时候却会说表不存在,也就是说通过这种方法是无法恢复数据库的,严格来说是通过这种方法无法恢复innodb引擎的数据库,但如果是Myisam引擎,这样直接复制过去,是可以直接在那个数据库中使用这个库的(也分情况,比如数据库版本不对有可能无法恢复),因为innodb的表有些元数据是存储在ibdata1文件中的,这个文件就在mysql数据目录中,而我们拷的时候,并没有拷它,而是拷的某个数据库目录,所以另一台机无法识别这个库的表。

那要怎么恢复呢?毕竟表的结构存在.frm文件中,数据和索引存在.ibd文件中,这两个文件都在啊,理论上数据肯定可以恢复的,实际上,也是可以的。

安装mysql-utilities实用工具,因为我是mac所以我用brew安装,如果是linux应该可以用yum/apt-get安装:

brew install mysql-utilities

然后我们先使用mysql-utilities实用工具中的mysqlfrm命令来恢复表结构(导出建表语句),注意先把所有.frm和.ibd文件都备份一下,以免不小必误操作丢了:

mysqlfrm --server=root:[email protected]:3306 xxxx.frm --port=3309  --user=root | tail -n +12 >> structure.sql

其中『root:[email protected]:3306』为『mysql用户名:密码@数据库地址:端口』,xxxx.frm是你要恢复的表,--port=3307表示端口,注意如果你原数据库端口是3306,那这里就随便写一个其他未被占用的端口,这个端口不能跟原数据库端口相同,--user=root表示以root用户运行,否则可能无权限读取你的xxxx.frm数据,tail -n +12表示从第12行后面开始写入structure.sql文件中,因为前面12行都是注释的信息,没啥用所以我去掉了(而且第一行是没有注释的,提示直接使用密码不安全,但我们这里是自己使用,无所谓,而且为了后面的自动处理,我们必须把密码写到命令里)

这样是可以恢复,但是每次只能恢复一张表,如果有100多个表,每次都要修改一下表名再执行这句命令,那也是挺累的,所以我们写成自动化的(我是用php写的,我读取ibd文件是因为要排除Myisam引擎的.frm文件,因为Myisam引擎的数据不需要恢复,只要数据库文件复制到mysql数据目录直接就可用),反正都是循环,我就顺便把后边要用的语句也都循环拼接输入到文件中备用,把下面的代码保存到一个php文件中,我为了方便就直接保存到index.php中:

<?php
    $files = glob("*.ibd");
    $discardTablespace = '';
    $importTablespace = '';
    $createTable = '';
    foreach ($files as $key => $value) {
        $discardTablespace .= "ALTER TABLE `{$value}` DISCARD TABLESPACE;\n";
        $importTablespace .= "ALTER TABLE `{$value}` IMPORT TABLESPACE;\n";
        //恢复表建表语句
        $value = substr($value, 0, -3).'frm';
        $shell = 'mysqlfrm --server=root:[email protected]:3306 '.$value.' --port=3309  --user=root | tail -n +12';
        $output = shell_exec($shell);
        $lineCount =  substr_count($output, "\n");
        $arr = explode("\n", $output);
        $arrCount = count($arr);
        $arr = array_slice($arr, 0, $arrCount-3);
        $createTable .= join("\n", $arr).";\n\n";
    }
    file_put_contents('discard_tablespace.sql', $discardTablespace);
    file_put_contents('import_tablespace.sql', $importTablespace);
    file_put_contents('structure.sql', $createTable);

然后把index.php放到你要恢复的那个数据库的目录里面,然后执行,需要一定时间,看表数量的多少。注意执行命令的当前目录就是能直接看到.frm文件的目录,此时不需要在mysql的数据目录中,当然也可以在那里,你的.frm文件在哪里,就在哪时执行就行。

php index.php &

使用ps -ef | grep index.php来查看是否已经完成!

执行完成后,生成了三个文件

discard_tablespace.sql 用于删除表空间
import_tablespace.sql 用于导入新的表空间
structure.sql 用于建表

注意,由于数据库版本关系,上边的“structure.sql”文件可能是空的(全是分号),此时我们可以不用mysql服务器,直接用mysqlfrm命令读取表结构(其中./.frm文件所在目录,它会自动读取该目录中的所有.frm文件并输出表结构,我用们>>把输出重定向到structure.sql文件中):

mysqlfrm --diagnostic ./ >> structure.sql

但要注意的是,这种方法恢复的表结构,有些varchar里的数值非常大,感觉不正常,而且有时候会有CHARSET <UNKNOW>,手动把它删除即可。

我们先用structure.sql创建表,先登录mysql,然后use一下你要导入到哪个库,然后执行以下语句(当然你也可以用客户端导入更方便不用输命令):

source /path/to/structure.sql

注意,此时如果你原来那个库还在那要删除掉重新建一个(或者只删除它的所有表即可,因为我们要重建表)。

创建表之后,mysql数据目录中又重新生成了表结构文件.frm和表数据索引文件.ibd(但此时还没有数据),不过我也遇到过只生成了.ibd文件而没有生成.frm文件的(不知道是mysql8.0的问题还是其他问题),这样就只有把原来的.frm文件复制进去(实测这样是正常的)。

现在到了执行discard_tablespace.sql的时候了,如果是客户端,直接用导入方式即可,导入其实就是执行,执行后,数据库目录中的.ibd文件全部被删除,然后我们将之前的ibd文件拷进去,拷进去后,再导入import_tablespace.sql即可恢复数据。

打赏

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of

扫码在手机查看
iPhone请用自带相机扫
安卓用UC/QQ浏览器扫

mysql只有.ibd和.frm文件恢复数据