mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移

问题概述

可以在数据库发生部分文件损坏，又没有相关备份时，利用mysqlfrm工具，读取数据库中表的.frm文件提取建表语句，结合.ibd文件做数据恢复。

一、相关概念

1、mysqlfrm工具

mysqlfrm是一个恢复性质的工具，可以用来读取.frm文件并从该文件中找到表定义，生成ddl语句，生成的ddl语句多用于在另一台服务器上创建表或进行诊断等。但有些信息不存在于.frm文件中，这些信息将会被忽略，如自增长序列。

2、.frm文件

frm的意思为“表定义”，是描述数据表结构的文件。frm文件是用来保存每个数据表的元数据信息，包括表结构的定义等。frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必须有frm文件，命名方式为“数据表名.frm”。

3、.ibd文件

专属于InnoDB存储引擎的数据库文件，当配置Innodb_file_per_table=1时，每个表都有其对应的独享表空间，即单独的.ibd文件，其中存放有表数据以及索引。如使用共享表空间时则InnoDB表的数据存放在.ibdata中。

二、创建测试表及模拟数据库损坏

1、现有数据库配置

cat /mysql/my.cnf
[mysqld]
basedir=/mysql/mysqld/
datadir=/mysql/data/
port=3306

socket=/mysql/run/mysql.sock
pid-file=/mysql/run/mysqld.pid
log-error=/mysql/log/mysqld.log

[mysql]
socket=/mysql/run/mysql.sock

2、创建测试库及测试表

/mysql/mysqld/bin/mysql --defaults-file=/mysql/my.cnf  -uroot -p'Root#123'
create database test;
use test;
create table tables as select * from information_schema.tables;
create table views as select * from information_schema.views;
exit;

查看其对应的数据文件

cd /mysql/data/test/
ls -l

3、模拟数据库损坏

注：基于.frm及.ibd文件做恢复，其本质可以理解为跨实例或跨数据库恢复，我这里为展示意外删除数据文件时显示的报错，模拟了数据库损坏。实际也可以根据后续的方法，在两个正常的数据库之间，通过.frm及.ibd文件，做数据的恢复。

cd /mysql/data/
ls
rm -rf sys/

关闭数据库

/mysql/mysqld/bin/mysql --defaults-file=/mysql/my.cnf  -uroot -p'Root#123'
shutdown;

启动数据库

/mysql/mysqld/bin/mysqld_safe --defaults-file=/mysql/my.cnf  >>/mysql/log/mysqld.log &

再次连接

/mysql/mysqld/bin/mysql --defaults-file=/mysql/my.cnf  -uroot -p'Root#123'
use test;
show tables;
select count(*) from tables;

此时再次查询上述创建的表时，已提示表不存在。查看mysql日志，提示无法从数据字典中打开表相关信息

三、mysqlfrm工具安装

官方下载链接: https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz

cd /mysqlfrm/
tar -xf mysql-utilities-1.6.5.tar.gz
ls
cd mysql-utilities-1.6.5/
python ./setup.py build
python ./setup.py install

查看安装结果

mysqlfrm --version

模式介绍
–basedir：需指定服务器的基本目录，相当于再生一个数据库实例，还需指定prot，user及.frm文件位置等信息，指定的prot不能与在运行的数据库冲突，可在原数据库无法打开情况下使用，在读取.frm文件后，再生的实例将被关闭，所有的临时文件将被删除。

例：mysqlfrm --basedir=/mysql/mysqld/ --port=3333 --user=mysql /mysql/data/test/tables.frm --show-stats

–server：需指定数据库的连接字符串，需在原数据库可以打开的情况下使用，指定数据库用户名，密码，端口号及.frm文件位置等信息。

例：mysqlfrm --server=root:Root#123@localhost:3306 /mysql/data/test/tables.frm --port=3310 --user=mysql

如数据库还可以正常连接，推荐使用–server模式
更多使用说明，可mysqlfrm --help查看帮助，其中还有诊断相关参数–diagnostic等

四、数据恢复

1、读取frm文件内容

tables表：

mysqlfrm --server=root:Root#123@localhost:3306 /mysql/data/test/tables.frm  --port=3310 --user=mysql

views表：

mysqlfrm --server=root:Root#123@localhost:3306 /mysql/data/test/views.frm  --port=3310 --user=mysql

如执行失败，在最后添加–diagnostic参数

2、配置新的数据库服务

注：可选，也可以在原数据库上操作
新数据库配置：

cat /mysql2/my.cnf
[mysqld]
basedir=/mysql2/mysqld/
datadir=/mysql2/data/
port=3308

socket=/mysql2/run/mysql.sock
pid-file=/mysql2/run/mysqld.pid
log-error=/mysql2/log/mysqld.log

[mysql]
socket=/mysql2/run/mysql.sock

数据库初始化，启动过程省略

3、连接新数据库及进行数据恢复

连接新数据库

/mysql2/mysqld/bin/mysql --defaults-file=/mysql2/my.cnf  -uroot -p'Root#123'

创建test数据库

create database test;

依据上述建表语句创建tables及views表

use test;

丢弃上述表对应的表空间

ALTER TABLE test.tables DISCARD TABLESPACE;
ALTER TABLE test.views DISCARD TABLESPACE;

拷贝原数据库两张表的.ibd文件至新数据库相应目录，并检查权限

cd /mysql2/data/test/
ls
cp -a /mysql/data/test/*.ibd ./
ll

数据库重新导入.ibd文件

ALTER TABLE test.tables IMPORT TABLESPACE; SHOW WARNINGS;
ALTER TABLE test.views IMPORT TABLESPACE; SHOW WARNINGS;

提示InnoDB尝试在没有模式验证的情况下导入文件，可以忽略。

验证表数据

select count(*) from tables;
select count(*) from views;

数据恢复成功