基于 frm 和 ibd 文件恢复表数据

说明

  • MySQL 如果单表过大，使用逻辑备份复制表时用时会比较长，可基于 ibd 文件进行物理备份复制、恢复表。要基于 frm 和 ibd 复制表，需要开启独立表空间
  • ibd 文件是 innodb 的表数据文件，frm 是 innodb 的表结构文件。
    • 安装 MySQL 官方 mysql-utilities 工具包，使用 mysqlfrm 工具可直接从 frm 中提取表结构
  • 如果基于正在运行的 MySQL 复制 ibd，需先执行 flush table $TBL_NAME for export，使脏页数据落盘
  • 如果基于 xtrabackup 全备恢复数据，直接使用 ibd 文件恢复，会存在脏页数据未落盘的情况。推荐建新库恢复后 flush table 使脏页落盘后复制 ibd 文件

使用 mysqlfrm 提取表结构

  • 如果知道对应表的表结构，可忽略此步骤。当误删除表需要恢复时，可从备份文件中的 frm 文件提取表结构。

安装

  • 下载安装 mysql-connector-python 和 mysql-utilities
    • mysql-connector-python 下载地址：https://downloads.mysql.com/archives/c-python/
    • mysql-utilities 下载地址：https://downloads.mysql.com/archives/utilities/
  • Centos7
1
2
3
4

$ wget https://cdn.mysql.com/archives/mysql-connector-python-2.1/mysql-connector-python-2.1.7-1.el7.x86_64.rpm
$ wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5-1.el7.noarch.rpm
$ sudo yum -y install mysql-connector-python-2.1.7-1.el7.x86_64.rpm
$ sudo yum -y install mysql-utilities-1.6.5-1.el7.noarch.rpm

提取建表语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

$ mysqlfrm  --diagnostic  xxl_job_log.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for xxl_job_log.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `xxl_job_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `job_group` int(11) NOT NULL comment '执行器主键ID',
  `job_id` int(11) NOT NULL comment '任务，主键ID',
  `executor_address` varchar(1020) DEFAULT NULL comment '执行器地址，本次执行的地址',
  `executor_handler` varchar(1020) DEFAULT NULL comment '执行器任务handler',
  `executor_param` varchar(2048) DEFAULT NULL comment '执行器任务参数',
  `executor_sharding_param` varchar(80) DEFAULT NULL comment '执行器任务分片参数，格式如 1/2',
  `executor_fail_retry_count` int(11) NOT NULL comment '失败重试次数',
  `trigger_time` datetime DEFAULT NULL comment '调度-时间',
  `trigger_code` int(11) NOT NULL comment '调度-结果',
  `trigger_msg` text DEFAULT NULL comment '调度-日志',
  `handle_time` datetime DEFAULT NULL comment '执行-时间',
  `handle_code` int(11) NOT NULL comment '执行-状态',
  `handle_msg` text DEFAULT NULL comment '执行-日志',
  `alarm_status` tinyint(4) NOT NULL comment '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败',
PRIMARY KEY `PRIMARY` (`id`),
KEY `I_trigger_time` (`trigger_time`),
KEY `I_handle_code` (`handle_code`)
) ENGINE=InnoDB;

#...done.

基于 ibd 文件恢复

  • 如果基于全备恢复，推荐使用 xtrabackup 先建库恢复然后再导出 ibd 进行恢复。
    • 如果直接基于全备 ibd 文件恢复，可能会由于脏页数据未落盘导致数据丢失。
    • 参照：https://blog.xiangy.cloud/post/mysql5.7-flashback/#%E5%9F%BA%E4%BA%8E%E5%85%A8%E5%A4%87%E5%A2%9E%E5%A4%87%E7%9A%84%E6%95%B0%E6%8D%AE%E6%81%A2%E5%A4%8D

恢复数据

  • 备份库是指基于全备恢复数据的库
  • 恢复库是指要恢复表的数据库
  • 恢复库如果无原表，需使用先执行 CREATE ，生成 frm 和 ibd 文件
    • frm 文件无法直接拷贝其他库 frm 文件使用，需通过 Create 创建，然后替换 ibd 文件
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

mysql> CREATE TABLE `xxl_job_log` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `job_group` int(11) NOT NULL comment '执行器主键ID',
    ->   `job_id` int(11) NOT NULL comment '任务，主键ID',
    ->   `executor_address` varchar(1020) DEFAULT NULL comment '执行器地址，本次执行的地址',
    ->   `executor_handler` varchar(1020) DEFAULT NULL comment '执行器任务handler',
    ->   `executor_param` varchar(2048) DEFAULT NULL comment '执行器任务参数',
    ->   `executor_sharding_param` varchar(80) DEFAULT NULL comment '执行器任务分片参数，格式如 1/2',
    ->   `executor_fail_retry_count` int(11) NOT NULL comment '失败重试次数',
    ->   `trigger_time` datetime DEFAULT NULL comment '调度-时间',
    ->   `trigger_code` int(11) NOT NULL comment '调度-结果',
    ->   `trigger_msg` text DEFAULT NULL comment '调度-日志',
    ->   `handle_time` datetime DEFAULT NULL comment '执行-时间',
    ->   `handle_code` int(11) NOT NULL comment '执行-状态',
    ->   `handle_msg` text DEFAULT NULL comment '执行-日志',
    ->   `alarm_status` tinyint(4) NOT NULL comment '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败',
    -> PRIMARY KEY `PRIMARY` (`id`),
    -> KEY `I_trigger_time` (`trigger_time`),
    -> KEY `I_handle_code` (`handle_code`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
  • 恢复库摘除 ibd 文件
1
2

mysql> alter table `xxl_job_log` discard tablespace;
Query OK, 0 rows affected (0.12 sec)
  • 此时查看 mysql data 目录，可以看到 xxl_job_log 数据仅有 frm 文件，没有 ibd 文件
1
2
3
4

[root@dbsl test]# ll -h
total 32K
-rw-r-----. 1 mysql mysql  67 Apr 11 19:19 db.opt
-rw-r-----. 1 mysql mysql 26K Apr 11 19:19 xxl_job_log.frm
  • 备份库刷新该表脏页，并添加只读锁
1
2

mysql> flush  table `xxl_job_log` for export;
Query OK, 0 rows affected (0.01 sec)
  • 发送 ibd 文件至恢复库，放至指定位置并配置权限
1
2
3

$ scp  xxl_job_log.ibd root@192.168.212.11:
$ mv ~/xxl_job_log.ibd /usr/local/platform/mysql_3309/mysql_data/data_3309/test/
$ chown mysql.mysql xxl_job_log.ibd
  • 恢复库重新加载表 ibd 文件
1
2

mysql> alter table `xxl_job_log` import tablespace;
Query OK, 0 rows affected, 1 warning (0.40 sec)
  • 备份库解锁表
1
2

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
  • 验证恢复库内恢复的表的数据
1
2
3
4
5
6
7

mysql> select count(*) from `xxl_job_log` ;
+----------+
| count(*) |
+----------+
|    23207 |
+----------+
1 row in set (0.00 sec)

参考