前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >技术分享 | 如何缩短 MySQL 物理备份恢复时间？

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

作者头像
爱可生开源社区
发布于 2024-02-21 16:49:14
1700
发布于 2024-02-21 16:49:14
举报
文章被收录于专栏：爱可生开源社区爱可生开源社区

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

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

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

1背景

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

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

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

2场景一

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

  1. 可以用到并行复制特性，速度更快。
  2. 可以使用复制过滤功能，只回放相应库表的 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_INFO\G
mysql> START SLAVE SQL_THREAD;

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

  1. 使用 SQL 线程回放 Binlog，并配置并行复制。
  2. 修改双一参数为双 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 工具配合一些第三方脚本可助你一臂之力。

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

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

4其他技巧

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

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

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

本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2024-02-06，如有侵权请联系 cloudcommunity@tencent.com 删除
备份
数据
线程
mysql
root

本文分享自 爱可生开源社区 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

备份
数据
线程
mysql
root
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 1背景
  • 2场景一
    • 2.1 生成 index 文件
      • 2.2 修改参数
        • 2.3 建立复制通道并开启复制线程
        • 3场景二
          • 3.1 准备表结构
            • 3.2 Prepare
              • 3.3 准备 SQL
                • 3.4 将全备中对应的表文件与 SQL 文件拷贝至目标库目录
                  • 3.5 恢复数据
                    • 3.6 数据验证
                    • 4其他技巧
                    相关产品与服务
                    云数据库 MySQL
                    腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
                    免费体验产品介绍产品文档
                    云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
                    领券

                    腾讯云开发者

                    扫码关注腾讯云开发者

                    扫码关注腾讯云开发者

                    领取腾讯云代金券

                    热门产品

                    热门推荐

                    更多推荐

                    Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

                    深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

                    腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

                    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

                    Copyright © 2013 - 2024 Tencent Cloud.

                    All Rights Reserved. 腾讯云 版权所有

                    登录 后参与评论