问题概述

可以在数据库发生部分文件损坏,又没有相关备份时,利用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;

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

查看其对应的数据文件

cd /mysql/data/test/
ls -l

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

3、模拟数据库损坏

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

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

mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_03


关闭数据库

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

mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_04


启动数据库

/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;

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


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

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

三、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工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_07


mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_08

查看安装结果

mysqlfrm --version

mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_09

模式介绍
–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

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


views表:

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

mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_11

如执行失败,在最后添加–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;

mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_12

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

use test;

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


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

丢弃上述表对应的表空间

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

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

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

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

mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_16

数据库重新导入.ibd文件

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

mysqlfrm工具使用及基于.frm和.ibd文件的数据恢复和.ibd迁移_故障处理_17


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

验证表数据

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

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

数据恢复成功