技术分享 | 如何缩短 MySQL 物理备份恢复时间？

作者：李彬，爱可生 DBA 团队成员，负责项目日常问题处理及公司平台问题排查。爱好有亿点点多，吉他、旅行、打游戏…

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 2600 字，预计阅读需要 7 分钟。

1背景

作为一名 DBA，数据库的备份与恢复是异常重要的，日常我们也许关注的仅仅是提升备份效率，但在真实的运维场景下，数据恢复的时间成本考量更为重要，过长的恢复时间可能满足不了 RTO 的要求。本文以 Xtrabackup 工具为例，分别基于以下三个场景，来探讨如何加快数据的恢复速度。

场景一：全备之后，数据库故障，需要恢复全备 Binlog 的所有数据。
场景二：全备之后，误删除了某个库，需要恢复该库的所有数据。
场景三：全备之后，误删除了某个表，需要恢复该表的所有数据。

前置条件：你已经拥有了完整的 Xtrabackup 全量备份和 Binlog。

2场景一

基于全备 Binlog 的恢复流程，实现恢复加速的妙招在于使用 SQL Thread 进行 Binlog 回放，这样做有以下几点好处:

可以用到并行复制特性，速度更快。
可以使用复制过滤功能，只回放相应库表的 Binlog（单库或单表恢复场景）。

假设你已经恢复了完整的 Xtrabackup 全量备份到临时实例，使用 SQL Thread 回放 Binlog 的操作过程见下:

2.1 生成 index 文件

将全备后的所有 Binlog 均拷贝到临时实例的 relay log 目录中并重命名，然后生成 index 文件。

代码语言：javascript复制

[root@localhost relaylog]$ rename mysql-bin mysql-relay mysql-bin*
[root@localhost relaylog]$ ls ./mysql-relay.0* > mysql-relay.index
[root@localhost relaylog]$ chown -R mysql.mysql mysql-relay.*

2.2 修改参数

修改 MySQL 参数（server_id 不能与原实例相同、relay_log_recovery 必须配置为 0，其余参数可以提升回放效率），重启临时实例。

代码语言：javascript复制

[root@localhost relaylog]$ vim ../my.cnf.3306
[root@localhost relaylog]$ less ../my.cnf.3306 | grep -Ei "server_id|relay_log_recovery|slave-para|flush_log_at|sync_binlog"
server_id                       = 4674
slave-parallel-type            = LOGICAL_CLOCK
slave-parallel-workers     = 8
sync_binlog                = 0
innodb_flush_log_at_trx_commit = 0
relay_log_recovery         = 0

[root@localhost relaylog]$ systemctl restart mysql_3306
[root@localhost relaylog]$ ps aux | grep 3306

2.3 建立复制通道并开启复制线程

代码语言：javascript复制

[root@localhost relaylog]$ cat /data/mybackup/recovery/186-60-42/xtrabackup_binlog_info
mysql-bin.000002 195862214 5af74703-a85e-11ed-a34e-02000aba3c2a:1-205
[root@localhost relaylog]$ mysql -S /data/mysql/3306/data/mysqld.sock -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='mysql-relay.000002',RELAY_LOG_POS=195862214;
mysql> SELECT * FROM MYSQL.SLAVE_RELAY_LOG_INFOG
mysql> START SLAVE SQL_THREAD;

看到这里，我们小结一下用到的加速技巧：

使用 SQL 线程回放 Binlog，并配置并行复制。
修改双一参数为双 0，进行复制加速。

3场景二

针对从全备中恢复单库的场景，又该如何加速呢？除了 SQL 线程回放 Binlog，还需要用到我们第二个加速恢复的妙招，可传输表空间。

老规矩，先贴出官方文档的说明：https://dev.mysql.com/doc/refman/5.7/en/innodb-table-import.html

注意：使用可传输表空间的方式是有限制的，官方提出了六点使用前提，大家可以自行研究。

对于大表，使用表空间传输来进行表迁移对比 SQL 恢复在效率上有很大的提升，且 Xtrabackup 也提供了 --export 参数支持，让我们在 Xtrabackup 恢复的 prepare 阶段就可以获取到 .cfg 等需要的文件。

以恢复 test 库为例（源库 3310，临时库 3311）：

3.1 准备表结构

首先我们需要有对应表的表结构，这里使用 mysqldump 导出，并在目标端进行导入:

代码语言：javascript复制

# 逻辑导出
[root@localhost 3310]$ /data/mysql/3310/base/bin/mysqldump -uroot -p -h127.0.0.1 -P3310 --set-gtid-purged=off --no-data --databases test > ./testdb_schema_bak.sql

# 目标端导入
[root@localhost 3311]$ /data/mysql/3311/base/bin/mysql -uroot -p -h127.0.0.1 -P3311 < /data/mysql/3310/testdb_schema_bak.sql

3.2 Prepare

在全备中使用 --export 进行 Prepare，生成用于表空间传输的相关文件:

代码语言：javascript复制

# 全备 prepare 之前的文件
[root@localhost test]$ ll
-rw-r----- 1 root root     8632 Dec 14 10:45 sbtest1.frm
-rw-r----- 1 root root 30408704 Dec 14 10:45 sbtest1.ibd
-rw-r----- 1 root root     8632 Dec 14 10:45 sbtest2.frm
-rw-r----- 1 root root 30408704 Dec 14 10:45 sbtest2.ibd
...

# --export 之后的文件，可以看到针对 MySQL 的 .cfg 文件已经自动生成，代替了 FLUSH TABLES ... FOR EXPORT
[root@localhost 3310]$ xtrabackup --prepare --export --use-memory=1024MB --target-dir=/data/mysql/3310/backup/3310_20231214_full_bak
[root@localhost 3310]$ ll /data/mysql/3310/backup/3310_20231214_full_bak/test/
-rw-r--r-- 1 root root      490 Dec 14 10:47 sbtest1.cfg
-rw-r----- 1 root root    16384 Dec 14 10:47 sbtest1.exp
-rw-r----- 1 root root     8632 Dec 14 10:45 sbtest1.frm
-rw-r----- 1 root root 30408704 Dec 14 10:45 sbtest1.ibd
-rw-r--r-- 1 root root      490 Dec 14 10:47 sbtest2.cfg
-rw-r----- 1 root root    16384 Dec 14 10:47 sbtest2.exp
-rw-r----- 1 root root     8632 Dec 14 10:45 sbtest2.frm
-rw-r----- 1 root root 30408704 Dec 14 10:45 sbtest2.ibd
...

3.3 准备 SQL

拼凑出多表 DISCARD TABLESPACE 和 IMPORT TABLESPACE 命令，当表存在时，可使用 SQL 配合 information_schema.tables 表进行语句拼接，这里以 Shell 实现进行举例：

代码语言：javascript复制

[root@localhost tmp]$ DATABASE='test'
[root@localhost tmp]$ for table in sbtest1 sbtest2 sbtest3 sbtest4 sbtest5
> do
>     echo "ALTER TABLE ${DATABASE}.${table} DISCARD TABLESPACE;" >> discard_ts.sql
>     echo "ALTER TABLE ${DATABASE}.${table} IMPORT TABLESPACE;"  >> import_ts.sql
> done
[root@localhost tmp]$ cat discard_ts.sql 
ALTER TABLE test.sbtest1 DISCARD TABLESPACE;
ALTER TABLE test.sbtest2 DISCARD TABLESPACE;
ALTER TABLE test.sbtest3 DISCARD TABLESPACE;
ALTER TABLE test.sbtest4 DISCARD TABLESPACE;
ALTER TABLE test.sbtest5 DISCARD TABLESPACE;
[root@localhost tmp]$ cat import_ts.sql 
ALTER TABLE test.sbtest1 IMPORT TABLESPACE;
ALTER TABLE test.sbtest2 IMPORT TABLESPACE;
ALTER TABLE test.sbtest3 IMPORT TABLESPACE;
ALTER TABLE test.sbtest4 IMPORT TABLESPACE;
ALTER TABLE test.sbtest5 IMPORT TABLESPACE;

3.4 将全备中对应的表文件与 SQL 文件拷贝至目标库目录

代码语言：javascript复制

[root@localhost test]$ cp sbtest*.{cfg,ibd} /data/mysql/3311/tmp/
[root@localhost tmp]$ ll
total 148508
-rw-r--r-- 1 root root      225 Dec 14 14:00 discard_ts.sql
-rw-r--r-- 1 root root      225 Dec 14 14:00 import_ts.sql
-rw-r--r-- 1 root root      490 Dec 14 13:59 sbtest1.cfg
-rw-r----- 1 root root 30408704 Dec 14 13:59 sbtest1.ibd
-rw-r--r-- 1 root root      490 Dec 14 13:59 sbtest2.cfg
-rw-r----- 1 root root 30408704 Dec 14 13:59 sbtest2.ibd
-rw-r--r-- 1 root root      490 Dec 14 13:59 sbtest3.cfg
-rw-r----- 1 root root 30408704 Dec 14 13:59 sbtest3.ibd
-rw-r--r-- 1 root root      490 Dec 14 13:59 sbtest4.cfg
-rw-r----- 1 root root 30408704 Dec 14 13:59 sbtest4.ibd
-rw-r--r-- 1 root root      490 Dec 14 13:59 sbtest5.cfg
-rw-r----- 1 root root 30408704 Dec 14 13:59 sbtest5.ibd
# 注意权限
[root@localhost tmp]$ chown mysql. ./*

3.5 恢复数据

代码语言：javascript复制

# 1. 丢弃表空间
[root@localhost tmp]$ /data/mysql/3311/base/bin/mysql -uroot -p -h127.0.0.1 -P3311 < discard_ts.sql
# 2. 拷贝.cfg和.ibd到目标端的test库目录下
[root@localhost tmp]$ cp -a sbtest*.{cfg,ibd} /data/mysql/3311/data/test/
# 确认权限
[root@localhost tmp]$ ll /data/mysql/3311/data/test/
# 3. 导入表空间(可通过查看mysql-error.log确认该过程是否有报错)
[root@localhost tmp]$ /data/mysql/3311/base/bin/mysql -uroot -p -h127.0.0.1 -P3311 < import_ts.sql

3.6 数据验证

代码语言：javascript复制

mysql> use test;
mysql> select count(*) from sbtest1;
 ---------- 
| count(*) |
 ---------- 
|   100000 |
 ---------- 
1 row in set (0.21 sec)
...

至此，我们已经恢复了全备中的表数据，那么 Binlog 中的数据如何恢复呢？

其实我们仅需在临时实例中配置 SQL 线程回放过滤复制，即可完成对表数据的全量恢复。与场景一不同，我们需要找到 DROP 操作的 GTID 或者 POS，配置过滤复制，并使 SQL 线程回放到 DROP 之前停止。

解析 binlog/relaylog，得到 DROP 操作的 GTID 或者 POS。

代码语言：javascript复制

[root@localhost relaylog]$
while read relaylogname
do
/data/mysql/3311/base/bin/mysqlbinlog --base64-output=decode-rows -vvv $relaylogname  | grep -Ei "drop" && echo "RELAYLOG位置: $relaylogname"
done</data/mysql/3311/relaylog/mysql-relay.index

# DROP DATABASE `test` /* generated by server */
# RELAYLOG位置: ./mysql-relay.000006

# 解析BINLOG/RELAYLOG日志确认位点或者GTID信息(POS信息: 20135899)
[root@localhost relaylog]$ /data/mysql/3311/base/bin/mysqlbinlog --base64-output=decode-rows -vvv mysql-relay.000006 | less

# at 20135872
#231213 17:53:07 server id 60423306  end_log_pos 20135899       Xid = 7982902
COMMIT/*!*/;
# at 20135899
#231213 17:53:27 server id 60423306  end_log_pos 20135960       GTID    last_committed=9207     sequence_number=9208    rbr_only=no
SET @@SESSION.GTID_NEXT= '5af74703-a85e-11ed-a34e-02000aba3c2a:399350'/*!*/;
# at 20135960
#231213 17:53:27 server id 60423306  end_log_pos 20136076       Query   thread_id=70    exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1675936407/*!*/;
SET @@session.pseudo_thread_id=70/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=46/*!*/;
DROP DATABASE `test` /* generated by server */
/*!*/;
# at 20136076

配置复制过滤。

代码语言：javascript复制

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (test);
Query OK, 0 rows affected (0.01 sec)

启动复制线程，到误删除那个事务停止。

代码语言：javascript复制

# 启动复制线程，到误删除那个事务停止
mysql> START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = '5af74703-a85e-11ed-a34e-02000aba3c2a:399350';
# 若为基于POS的复制，则使用下面的语句
mysql> START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'mysql-relay.000006', RELAY_LOG_POS = 20135899;

至此，大家应该对于在全备中如何快速恢复误删除库表有了一定的思路，场景三实际与场景二的思路一致。当然，有些小伙伴可能有一个疑问，如果是误删除操作，源端的库表已经不存在了，如何获取表结构呢？这里提供两个方法：

相关的表结构可以从测试或者性能环境中导出，当然你需要确保各个环境的表结构是一致的。
MySQL 8.0 之前，可以解析备份中的 .frm 文件获取表结构，如 mysqlfrm 工具。MySQL 8.0 之后，ibd2sdi 工具配合一些第三方脚本可助你一臂之力。

同样，我们小结一下用到的加速技巧：

配合 Xtrabackup 的 --export 参数，通过表空间传输只恢复对应的表，而无需恢复整个全备数据。在全备很大，但需要恢复的表很小时，节省了很多时间。
针对大表，可以直接使用表空间传输进行表迁移，对比逻辑恢复效率提升明显（注意限制）。
在场景一的基础上，使用过滤复制的功能，针对单库或单表选择性地进行回放，进一步缩减了恢复的时间。

4其他技巧

除了以上两个妙招，其实在恢复数据的整个流程中，还有一些节省时间的小技巧，如：

工具及其版本的选择。以 Xtrabackup 为例，8.0.33-28 版本针对 prepare 阶段进行了优化，效率提升明显。
结合实际的机器资源，合理配置工具的性能参数。如 Xtrabackup 的 --parallel 可以配合 --decompress 和 -–decrypt 选项来进行并行解压缩和解密操作，--use-memory 指定 Xtrabackup --prepare 或者 Xtrabackup --stats 时使用的内存大小，对恢复效率也有一定影响。
恢复流程控制。prepare 阶段是需要一定时间的，我们可以在备份完成后直接做 prepare，从而省掉大量时间。同样，用于恢复的临时机器如何快速拿到备份文件也是优化的方向之一。
机器性能因素。如 CPU、磁盘性能、网络带宽（传输备份相关文件）等。

本文关键字：#MySQL# #备份# #Xtrabackup#

备份数据线程 mysql root

0 人点赞