基于Xtrabackup及可传输表空间实现多源数据恢复

最新推荐文章于 2022-10-06 21:14:28 发布
最新推荐文章于 2022-10-06 21:14:28 发布
阅读量319 收藏
点赞数
分类专栏： MySQL 文章标签： 开源 数据库 MySQL 表空间 数据传输
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ActionTech/article/details/88370023
版权
本文目录一、使用背景

1.可传输表空间基本流程
2.使用前提条件/限制
二、技术要点
三、实施步骤
1.实验环境
2.源端操作
1) 造测试数据并模拟小压力
2) 备份单库数据
3) 备份表结构
4) 批量生成可传输表空间命令
3.目标端操作
1) 备份数据恢复准备
2) 恢复表结构至目标端
3) 舍弃目标端对应表空间文件
4) 拷贝表数据及配置文件到目标端sbtest库数据目录
5) 执行导入表空间文件操作
4.建立复制(多源复制)
四、参考链接

Keywords：
MySQL xtrabackup Transportable Tablespaces 
MySQLdump multi-source replication

一、使用背景 

MySQL在5.7以后引入了多源复制(Multi-Source)功能，可用于将多个单独的数据库汇聚到一个数据库实例下，方便用户进行数据分析、汇总或推送至其他数据库平台。早期针对汇聚场景初始化各源端数据到汇聚库，为了提升效率通常会使用使用开源并行逻辑导入导出工具myloader/mydumper进行数据导入/导出，当源端多实例多库数据量较大(100G以上)情况下，使用myloader/mydumper花费的时间则可能无法满足时间要求，需要考虑是否有更高效的方式进行数据初始化同步操作，以及当复制通道异常时更便捷快速修复。

ps：多源复制使用物理备份(xtrabackup)做数据初始化时，常规方式只有第一个通道可做覆盖还原，后续通道需逻辑还原或用其他方式还原，如该方案所描述的方式。

MySQL在5.6以后支持了可传输表空间,以及Xtrabackup针对该功能在备份时提供了一个对应参数export,该参数支持对InnoDB存储引擎表的备份数据export转换，且与常规备份操作一样可生成备份时间点的binlog信息(GTID信息),结合这两特性可以更高效和快速的方式实现多库数据汇聚的初始导入操作。

▽场景架构图如下▽
 



1.可传输表空间基本流程
先简单梳理一下可传输表空间基本流程：
 

  • 在源端将InnoDB表进行ibd数据文件导出处理(export tablespace))
  • 在目标端创建与源端相同表结构的表
  • 将目标端的表数据文件舍弃(discard tablespace)
  • 用源端的相对应ibd文件覆盖到目标端
  • 在目标端执行表空间文件的导入/置换(import tablespace)


单纯使用可传输表空间功能无法记录对应表的事务点，也就是如果需要进行汇聚复制同步，无法知道从binlog哪个文件的哪个position点进行数据同步，这也就是需要引入xtrabackup及export功能的原因。备份元数据信息本身会记录复制同步点信息。

2.使用前提条件/限制

  • MySQL须为5.6以上版本
  • 表存储引擎须为InnoDB存储引擎(支持可传输表空间)
  • 导入完成后建议执行ANALYZE TABLE更新统计信息


二、技术要点

  • sysbench 源端造测试数据并模拟压力
  • mysqldump 备份源端表结构
  • concat()拼接批量可传输表空间SQL
  • xtrabackup 备份源端单库(部分库)数据
  • Transportable Tablespace可传输表空间功能
  • Multi-source Replication 多源复制
  • mysql-error.log 错误信息日志校验
  • ANALYZE TABLE更新统计信息


三、实施步骤 

1.实验环境 


2.源端操作 

  • 1) 造测试数据并模拟小压力
使用sysbench创建4张各100W记录的测试表，并使用2个并发持续模拟业务压力：
 
## 造数据
shell> /opt/sysbench-0.9/sysbench/sysbench --test=/opt/sysbench-0.9/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --oltp-tables-count=4 --mysql-user=sysbench --mysql-password=sysbench --mysql-host=10.186.60.16 --mysql-port=3333 prepare

## 模拟小压力
shell> /opt/sysbench-0.9/sysbench/sysbench --test=/opt/sysbench-0.9/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --oltp-tables-count=4 --mysql-user=sysbench --mysql-password=sysbench --mysql-host=10.186.60.16 --mysql-port=3333 --num-threads=2 --max-requests=0 --max-time=0 --report-interval=1 run
  • 2) 备份单库数据


使用xtrabackup备份工具对源端sbtest库进行单库备份，保存至/data/mysql/backup/目录下：

shell> innobackupex --databases=sbtest /data/mysql/backup/
  • 3) 备份表结构


为可传输表空间做准备，将源端表结构备份并后续在目标端导入：

shell> cd /data/mysql/backup
shell> mysqldump --no-data --set-gtid-purged=off sbtest>sbtest_schema.sql

 

  • 4) 批量生成可传输表空间命令

 

  • discard


使用concat函数拼接出批量DISCARD TABLESPACE的SQL：

shell> cd /data/mysql/backup
shell> mysql -ssre "select concat('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DISCARD TABLESPACE;') from information_schema.tables where TABLE_SCHEMA='sbtest';" >discard_tbs.sql

## 输出文件如下所示
shell> cat discard_tbs.sql
ALTER TABLE sbtest.sbtest1 DISCARD TABLESPACE;
ALTER TABLE sbtest.sbtest2 DISCARD TABLESPACE;
ALTER TABLE sbtest.sbtest3 DISCARD TABLESPACE;
ALTER TABLE sbtest.sbtest4 DISCARD TABLESPACE;
  • import


使用concat函数拼接出批量IMPORT TABLESPACE的SQL：

shell> cd /data/mysql/backup
shell> mysql -ssre "select concat('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' IMPORT TABLESPACE;') from information_schema.tables where TABLE_SCHEMA='sbtest';">import_tbs.sql

## 输出文件如下所示
shell> cat import_tbs.sql
ALTER TABLE sbtest.sbtest1 IMPORT TABLESPACE;
ALTER TABLE sbtest.sbtest2 IMPORT TABLESPACE;
ALTER TABLE sbtest.sbtest3 IMPORT TABLESPACE;
ALTER TABLE sbtest.sbtest4 IMPORT TABLESPACE;


拷贝源端/data/mysql/backup目录下生成的所有相关文件到目标端/data/mysql/backup下
 

3.目标端操作

  • 1) 备份数据恢复准备


对备份数据进行apply-log日志应用及将数据进行export转换生成配置文件：

shell> innobackupex --apply-log --export /data/mysql/backup/2019-02-22_15-26-20/

## export执行完后sbtest库下备份文件如下所示
## exp结尾的文件为Percona针对Percona XtraDB做export的配置文件
## cfg结尾的文件为Percona针对MySQL可传输表空间export的配置文件

shell>  ll /data/mysql/backup/2019-02-22_15-26-20/sbtest/
总用量 999556
-rw-r----- 1 root root        67 2月  22 15:40 db.opt
-rw-r--r-- 1 root root       569 2月  22 15:53 sbtest1.cfg
-rw-r----- 1 root root     16384 2月  22 15:53 sbtest1.exp
-rw-r----- 1 root root      8632 2月  22 15:40 sbtest1.frm
-rw-r----- 1 root root 255852544 2月  22 15:53 sbtest1.ibd
-rw-r--r-- 1 root root       569 2月  22 15:53 sbtest2.cfg
-rw-r----- 1 root root     16384 2月  22 15:53 sbtest2.exp
-rw-r----- 1 root root      8632 2月  22 15:40 sbtest2.frm
-rw-r----- 1 root root 255852544 2月  22 15:53 sbtest2.ibd
-rw-r--r-- 1 root root       569 2月  22 15:53 sbtest3.cfg
-rw-r----- 1 root root     16384 2月  22 15:53 sbtest3.exp
-rw-r----- 1 root root      8632 2月  22 15:40 sbtest3.frm
-rw-r----- 1 root root 255852544 2月  22 15:53 sbtest3.ibd
-rw-r--r-- 1 root root       569 2月  22 15:53 sbtest4.cfg
-rw-r----- 1 root root     16384 2月  22 15:53 sbtest4.exp
-rw-r----- 1 root root      8632 2月  22 15:40 sbtest4.frm
-rw-r----- 1 root root 255852544 2月  22 15:53 sbtest4.ibd

 

  • 2) 恢复表结构至目标端


将源端表结构在目标端数据库创建：

shell> cd /data/mysql/backup

## 手工创建sbtest库
shell> mysql -e "create database sbtest;"

## 导入源端对应的表结构
shell> mysql sbtest< sbtest_schema.sql

## 验证
shell> mysql -e "show tables from sbtest;"

 

  • 3) 舍弃目标端对应表空间文件

 

shell> cd /data/mysql/backup
shell> mysql <discard_tbs.sql

## 执行后效果如下所示，ibd文件已被舍弃
shell>  ll /data/mysql/data/sbtest/
-rw-r----- 1 mysql mysql   67 2月  22 15:58 db.opt
-rw-r----- 1 mysql mysql 8632 2月  22 15:58 sbtest1.frm
-rw-r----- 1 mysql mysql 8632 2月  22 15:58 sbtest2.frm
-rw-r----- 1 mysql mysql 8632 2月  22 15:58 sbtest3.frm
-rw-r----- 1 mysql mysql 8632 2月  22 15:58 sbtest4.frm

 

  • 4) 拷贝表数据及配置文件到目标端sbtest库数据目录

 

## 拷贝ibd文件
shell> cp /data/mysql/backup/2019-02-22_15-26-20/sbtest/*.ibd /data/mysql/data/sbtest/

## 拷贝cfg文件
shell> cp /data/mysql/backup/2019-02-22_15-26-20/sbtest/*.cfg /data/mysql/data/sbtest/

## 修改文件权限为mysql用户
shell> chown -R mysql:mysql /data/mysql/data/sbtest

 

  • 5) 执行导入表空间文件操作
shell> cd /data/mysql/backup

## 出现Warning为可传输表空间正常输出，可忽略,详情可参考下图所示note说明
shell> mysql <import_tbs.sql
Warning (Code 1814): InnoDB: Tablespace has been discarded for table 'sbtest1'
Warning (Code 1814): InnoDB: Tablespace has been discarded for table 'sbtest2'
Warning (Code 1814): InnoDB: Tablespace has been discarded for table 'sbtest3'
Warning (Code 1814): InnoDB: Tablespace has been discarded for table 'sbtest4'

## 可通过MySQL错误日志查看import期间是否存在导入异常
shell> less /data/mysql/data/mysql-error.log



4.建立复制(多源复制) 

mysql> CHANGE MASTER TO 
MASTER_HOST='10.186.60.16', 
MASTER_USER='repl', 
MASTER_PORT=3333, 
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=149327998 FOR CHANNEL '10-186-60-16';

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('sbtest.%');

mysql> START SLAVE FOR CHANNEL '10-186-60-16';
mysql> SHOW SLAVE STATUS FOR CHANNEL '10-186-60-16'\G;



四、参考链接

  • Percona xtrabackup export功能介绍

https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/restoring_individual_tables.html

  • MySQL 可传输表空间功能说明

https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

  • 场景示例思路来源博客

http://www.cnblogs.com/xuanzhi201111/p/6609867.html
 

使用指南系列

| 使用指南
深度分析 | MyCat与DBLE的对比性能调优
开源分布式中间件 DBLE 快速入门指南
DBLE 自定义拆分算法
DBLE Server.xml 配置解析
DBLE Schema.xml 配置解析
DBLE rule.xml 配置解析
| 案例分析
DBLE和Mycat跨分片查询结果不一致案例分析
| 社区活动
DBLE核心研发主讲：MySQL分布式中间件公开课开课啦

 


开源分布式中间件DBLE
社区官网：https://opensource.actionsky.com/
GitHub主页：https://github.com/actiontech/dble
技术交流群：669663113

开源数据传输中间件DTLE
社区官网：https://opensource.actionsky.com/
GitHub主页：https://github.com/actiontech/dtle
技术交流群：852990221
 

爱可生开源社区
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用xtrabackup恢复备份数据
qq_28408457的博客
10-12 412
前言 本次演示使用xtrabackup插件恢复数据至mysql。xtrabackup是Percona公司CTO Vadim参与开发的一款基于InnoDB的在线热备工具，具有开源，免费，支持在线热备，备份恢复速度快，占用磁盘空间小等特点，并且支持不同情况下的多种备份形式。 准备环境 本次使用虚拟机安装 linux centos7作为演示环境，然后在虚拟机centos7中安装数据库mysql和xtrabackup（提示：本次仅演示全量备份数据恢复） 虚拟机：VMware® Workstation 15 Pro
Xtrabackup实现数据备份与恢复
Micky_Yang的博客
08-25 404
一、Xtrabackup工具的介绍 　　Xtrabackup工具是由percona开源的免费的数据库热备份软件，它能对InnoDB数据库存储引擎和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加上表级锁）；mysqldump工具的备份方式是采用的数据库逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，那么使用mysqldump工具来进行备份就不太合适了。 ...
Mysql5.7.12多源复制 xtrabackup
weixin_34304013的博客
03-13 136
 实验环境 系统环境查看系统版本命令：cat /etc/redhat-release 本实验是编译安装的Mysql ，坑太多，以后不建议采用编译安装的方式 启动Mysql前首先执行以下命令，创建链接ln -s /usr/local/mysql/bin/mysql /usr/bin Mysql初始的root密码存放在/root/.mysql_secret找到密码后连接数据库，一定要修改roo...
使用Xtrabackup对MySQL做主从复制
weixin_34240657的博客
09-03 196
为什么80%的码农都做不了架构师？>>> ...
数据库备份恢复—Xtrabackup备份与恢复
kang_01的博客
01-21 1721
目录一、安装工具与常用选项二、全量备份与恢复2.1 全量备份2.2 恢复1、保持数据一致：2、恢复：如报错查看mysql/data/下是否有不是mysql用户的文件，修改成mysql用户即可3、更改 data/ 目录权限并启动mysql：如报错查看mysql/data/下是否有不是mysql用户的文件，修改成mysql用户即可4、可以看到数据库和表均已恢复 一、安装工具与常用选项 [root@master tools]# yum install -y percona-xtrabackup-24-2.4.9-
实现数据高可用和负载均衡：MySQL数据库复制技术详解，保障数据库稳定运行
[实现数据高可用和负载均衡：MySQL数据库复制技术详解，保障数据库稳定运行](https://itcloudbd.com/wp-content/uploads/2022/09/1663143118-%E5%BE%AE%E4%BF%A1%E5%9B%BE%E7%89%87_20220914161033-1024x511.png) ...
MySQL数据库复制机制深入解析：实现数据高可用与负载均衡，让数据库稳定如磐石
![MySQL数据库复制机制深入解析：实现数据高可用与负载均衡，让数据库稳定如磐石]...它提供了数据高可用性、负载均衡和灾难恢复功能。 **复制的优点：** * **高可用性：**如果主服务器
MySQL在线主从复制（不锁表，不停数据库）.docx
05-11
Percona XtraBackup工具是实现这一目标的理想选择，因为它支持在线无锁表同步复制，同时具备高效备份和恢复功能。 Percona XtraBackup是一个免费的开源工具，专为InnoDB存储引擎设计，能够在不影响MySQL服务的情况...
MySQL高频面试题及答案
最新发布
02-23
- 数据库复制实现高可用性和故障转移，主从复制、半同步复制、多源复制各有特点。 14. **存储过程与函数**： - 可重用的SQL代码集合，提升效率，减少网络传输。 15. **窗口函数**： - 在SQL查询中进行排名、...
【MySQL数据库同步工具指南】：全方位剖析常见同步工具及最佳实践
MySQL数据库同步是一种在两个或多个数据库之间复制数据的方法，以保持它们之间的数据一致性。它在以下场景中至关重要： - **数据冗余：**在多个数据库中存储相同的数据，以提高可用性和容错性。 - **数据集
Xtrabackup数据库备份与恢复
一个骚年的博客
02-19 744
​ Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。 安装 测试环境：CentOS 7 ，MySQL 5.7 有多种安装方式，以下使用rmp包安装方式进行演示安装 #下载rpm包# wget https://www.percona.com/
mysql xtrabackup 主从_使用 Xtrabackup 在线对MySQL做主从复制
weixin_39879219的博客
01-29 154
1. 说明1.1 xtrabackupmysqldump对于导出10G以下的数据库或几个表，还是适用的，而且更快捷。一旦数据量达到100-500G，无论是对原库的压力还是导出的性能，mysqldump就力不从心了。Percona-Xtrabackup备份工具，是实现MySQL在线热备工作的不二选择，可进行全量、增量、单表备份和还原。(但当数据量更大时，可能需要考虑分库分表，或使用 LVM 快照来加...
通过XtraBackup进行数据库表备份和表空间传输实例
m0_38130105的博客
07-09 383
一．安装教程      1.XtraBackup简介XtraBackup有很多功能和优点：例如支持全备、增量备份、部分备份；支持压缩备份；备份不影响数据读写、事务等，但是也有缺陷不足：例如不支持脱机备份、不支持直接备份到磁带设备、不支持Cloud Back，MyISAM的备份也会阻塞。XtraBackup只支持Linux平台。      2.安装包将percona-xtrabackup-2.4.9...
mysql多源复制详解
weixin_34295316的博客
07-04 266
大家应该知道，mysql比起其他数据库，最大的特色是就是主从复制，不过5.7之前的版本最多就只支持一主多从的复制方式，对于一些统计类的需求，就需要跨库，这是比较麻烦的事情，以往只能交由数据库中间件（mycat等）去做这种事情，还有备份操作，也只能一个个库的用脚本去轮询或并发备份，不可谓不费时费力。然后，多源复制的概念出来了。多源复制的概念最早是mariadb社区提出的，后来m...
MySQL 5.7.9的多源复制
__Yoon
01-29 533
什么是多源复制? 首先，我们需要清楚 multi-master 与multi-source 复制不是一样的. Multi-Master 复制通常是环形复制，你可以在任意主机上将数据复制给其他主机。 5.7.9的多源复制" /> Multi-source 是不同的. MySQL在这个新版本中修复了一个复制限制 , 这限制是一个从站只能有一个主站. 这是一个在我们设计复制环境中的限制因素，
Mysql5.5通过xtrabackup工具迁移表空间
lz13553182650的专栏
10-31 585
1.用innobackupex备份需要迁移的表 Percona XtraBackup工具部分备份，备份单表 总的来说部分备份总共有三种方式，分别是：1. 用正则表达式表示要备份的库名及表名（参数为--include）；2. 将要备份的表名或库名都写在一个文本文件中（参数为--tables-file）以及 3. 将要备份表名或库名完整的写在命令行中（参数为：--databas
基于GTID多源复制扩展
weixin_33873846的博客
08-01 83
对一个运行很久的库做备份恢复建同步 不能使用xtrabackup 使用mysqldump导数据： mysqldump -S /data/mysql/3307/tmp/3307.sock --single-transaction --master-data=2 --databases lgj3307 > lgj3307.sql 在从库上执行： change maste...
Mysql备份恢复（xtrabackup）
weixin_53150440的博客
10-06 1869
Mysql三种备份，恢复流程（xtrabackup）
MySQL XtraBackup：高效数据备份恢复详解及实战教程
对于大型数据库系统，确保数据安全性和可恢复性至关重要。然而，传统的mysqldump工具可能无法满足高可用性和性能需求，尤其是在处理大量数据时。XtraBackup作为Percona Server的一部分，提供了更为专业的备份解决...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值