作者：李彬，爱可生 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 文件。
[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，其余参数可以提升回放效率），重启临时实例。
[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 建立复制通道并开启复制线程
[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_INFO\G
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 导出，并在目标端进行导入:
# 逻辑导出
[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 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 文件拷贝至目标库目录
[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 恢复数据
# 1. 丢弃表空间
[root@localhost tmp]$ /data/mysql/3311/base/bin/mysql -uroot -p -h127.0.0.1 -P3311 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。
[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 CHANGE REPLICATION FILTER REPLICATE_DO_DB = (test);
Query OK, 0 rows affected (0.01 sec)
启动复制线程，到误删除那个事务停止。
# 启动复制线程，到误删除那个事务停止
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 扩展 VARCHAR 长度遭遇问题的总结
故障分析 | MySQL 无法修改主键？原来是因为这个参数
故障分析 | MySQL 全文索引触发 OOM 一例
技术分享 | 详解 MySQL 三表 JOIN
技术分享 | 八怪：再谈 MySQL 8 这两个精准的时间戳
技术分享 | 细说 MySQL 用户安全加固策略
技术分享 | 你知道 MySQL 函数 sysdate() 与 now() 的区别么？
技术分享 | 基于 MySQL 多通道主主复制的机房容灾方案
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。
SQLE 获取
🔗 Github https://github.com/actiontech/sqle📚 文档 https://actiontech.github.io/sqle-docs/💻 官网 https://opensource.actionsky.com/sqle/👥 微信技术交流群：添加管理员微信 ActionOpenSource📊 点击 原文链接 查看社区版和企业版功能对比：https://actiontech.github.io/sqle-docs/docs/support/compare
