MySQL备份文件.ibd、.frm、.MYD、.MYI的恢复教程

SQL herman 116浏览
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xmtxtt,发送下载链接帮助你免费下载!
本博客日IP超过1800,PV 2600 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog,之前的微信号好友位已满,备注:返现
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序

昨天,微信群里一位网友问到:“备份的 frm文件恢复,有的提示表不存在为什么”。关于这个问题,今天我们一起讨论一下!

根据网友的截图得知是他在恢复数据文件 .frm 时,报出的异常。error: 1146: Table ‘xxx’ doesn’t exist

error: 1146: Table ‘xxx’ doesn’t exist

这个的原因其实很简单,我先从头给你说起。

MySQL 数据库是具有持久化能力的。也就是说它的所有数据,其实最终都是存储在文件中的。如果你用的是 MyIsAM,则一张表数据文件有 3 个。

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

如果是 InnoDB,则一张表有 2 个数据文件。

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

且这些文件,一般在你安装 MySQL 目录中的 Data 目录中。

datadir = /usr/local/var/mysql/

如果你忘记了安装的位置,或者忘记了配置的 Data 目录在哪里,则可以根据下面的语句,找出数据文件的存放目录。

show global variables like "%datadir%";
show global variables like “%datadir%”

上面这个截图,就是我的测试数据库对应的数据文件的存放目录。进入到我的 /usr/local/var/mysql/ 目录后,可以看到我的 xttblog 和 test 数据库目录。

.frm 和 .ibd 文件

根据列表文件中的后缀名都可以判断出,我使用的是 InnoDB 存储引擎。下面我们就以 InnoDB 为例,来说说数据文件的恢复教程。

首先,我们可以在其他测试环境,创建一个 test 数据库,并在里面创建 test_2019 数据表。里面插入一些测试数据,包含建立的索引等。然后,我们将 test_2019.ibd 和 test_2019.frm 两个文件复制到本地的其他目录。

要恢复 test_2019 这张表,我们先要用 SQL 创建出这种表。

create table test_2019(id bigint not null);

主要是创建出一个同名的表,方便接下来的替换。字段内容无所谓。

表创建出来之后,我们就可以到 /usr/local/var/mysql/test/ 替换 test_2019.ibd 和 test_2019.frm 两个文件了。

接着重启 MySQL 服务即可了。

有些人可能少了,上面创建表这一步。直接复制表数据文件进行替换。结果就会导致上面的这个错误。虽然,你能通过 show tables 看到这张表,但是当你 select 这张表的时候,确报这张表不存在,error: 1146: Table ‘xxx’ doesn’t exist。

这也说明了针对 InnoDB 存储引擎,这样的直接 copy 复制的方式不能恢复数据表。但如果是 MyIsAM 存储引擎的话,直接复制是可以的(当然数据库版本必须要兼容)。这也是为什么一些文章说可以,一些文章说不可以。那是因为针对的存储引擎不对!

但是,我也很纳闷啊,数据文件都存在,为啥就提示表不存咋呢?

其实是可以通过 mysql-utilities 工具来恢复表结构!我是 Mac 电脑,安装命令如下:

brew install mysql-utilities

恢复表结构的 SQL 就很简单了,看下面的命令:

mysqlfrm --server=root:123@localhost:3306 test_2019.frm --port=3309 --user=root | tail -n +12 >> test_2019.sql

注意,上面有两个端口。–port=3309 这个指定的端口是你本地未被占用的端口。–user=root 表示以 root 用户运行。tail -n +12 表示跳过文件开头的前 12 行。因为前 12 行都是注释。

最后,我要提示的一点是,注意数据库的版本。如果版本不一致,也有可能导致恢复失败!

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加QQ1群:135430763(2000人群已满),QQ2群:454796847(已满),QQ3群:187424846(已满)。QQ群进群密码:xttblog,想加微信群的朋友,之前的微信号好友已满,请加博主新的微信号:xttblog,备注:“xttblog”,添加博主微信拉你进群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作可添加助理微信进行沟通!

本文原文出处:业余草: » MySQL备份文件.ibd、.frm、.MYD、.MYI的恢复教程