• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL备份恢复:多线程mydumper工具

MySQL 彭东稳 6年前 (2017-12-21) 22717次浏览 已收录 1个评论

一、mydumper&myloader

mydumper&myloader是用于对MySQL数据库进行多线程备份和恢复的开源 (GNU GPLv3)工具。开发人员主要来自MySQL、Facebook和SkySQL公司,目前由Percona公司开发和维护,是Percona Remote DBA项目的重要组成部分,包含在Percona XtraDB Cluster中。mydumper的第一版0.1发布于2010.3.26,最新版本0.9.5发布于2018.5月,每个版本都有详细的change log,使用时尽可能详细看看是否满足自己的需求。比如,0.9.5版本支持了JSON及生成列。

mydumper特点

1. C语言写的,轻量级。并且开源 (GNU GPLv3),目前持续维护中。

2. 多线程备份,执行速度比mysqldump快N倍,并支持行级chunk备份。

3. 事务性和非事务性表一致的快照(适用于0.2.2以上版本),维护所有线程的快照,提供准确的主从日志位置等。

4. 快速的文件压缩。

5. 支持导出binlog。

6. 多线程恢复(适用于0.2.1以上版本)。

7. 以守护进程的工作方式,定时快照和连续二进制日志(适用于0.5.0以上版本)。

8. 支持从库备份时记录主库的position,且不需要关闭slave sql线程(mysqldump需要关闭sql线程);并且支持同时记录主从position(mysqldump不支持)。

9. 更容易管理输出(每表单独文件,导出元数据等,易于查看/解析数据)

导入导出测试

下图是在SSD和HDD存储介质上将mydumper和同为逻辑备份的MySQL官方mysqldump所做的性能对比测试:

MySQL备份恢复:多线程mydumper工具

可以发现,在备份时间上,mydumper性能比mysqldump好将近1倍,通过分析不难发现,两者性能的差距主要跟备份实例的IO性能以及mydumper开启的线程数有关系,如果外部实例的IO性能很差,那么可能mysqldump单线程就能够将IO吃满,那改为使用mydumper也无法带来性能提升,但如果实例的IO性能较好,通过增加备份线程数(测试所用为2线程),就可能带来成倍的性能提升。导入时间分别使用与mydumper配合使用的myloader多线程sql导入工具以及mysqldump对应的source命令得到。相应的,在IO性能较好的SSD上,myloader的多线程优势体现明显,比source的单线程执行时间上节省将近2倍(测试所用为4线程)。不过在HDD上,由于其本身性能原因,myloader带来的效益变小。

二、多线程导出原理

mydumper原理与mysqldump原理类似,一致快照如何工作呢?

  • 作为预防措施,在服务器上运行缓慢的查询要么中止dump,要么被Kill
  • 读取各种元数据(“SHOW SLAVE STATUS”,“SHOW MASTER STATUS”)
  • 其他线程连接并建立快照(“START TRANSACTION WITH CONSISTENT SNAPSHOT”)
  • 一旦所有工作线程都宣布快照建立完成后,master将执行“UNLOCK TABLES”并开始排队作业。

当然,与mysqldump最大的区别是引入了多线程备份,每个备份线程备份一部分表,当然并发粒度可以到行级,达到多线程备份的目的。这里要解决最大一个问题是,如何保证备份的一致性,其实关键还是在于FTWRL。对于非Innodb表,在释放锁之前,需要将表备份完成。对于Innodb表,需要确保多个线程都能拿到一致性位点,这个动作同样要在持有全局锁期间完成,因为此时数据库没有读写,可以保证位点一致。

另外,在MySQL 5.7版本中,官方发布了一种新的备份工具mysqlpump,也是多线程的,其实现方式给人耳目一新的感觉,但遗憾的是其仍为表级别的并行,且不支持position点的记录。而mydumper能够实现记录级别的并行备份,其备份框架由主线程和多个工作线程组成,备份流程可见下图:

MySQL备份恢复:多线程mydumper工具

主线程负责建立数据一致性备份点、初始化工作线程和为工作线程推送备份任务:

  • 对备份实例加全局读锁,阻塞写操作以建立一致性数据备份快照点,记录备份点BinLog信息;
  • 创建工作线程,初始化备份任务队列,并向队列中推送数据库元数据(schema)、非InnoDB表和InnoDB表的备份任务;

工作线程负责将备份任务队列中的任务按顺序取出并完成备份:

  • 分别建立与备份实例连接,将session的事务级别设置为repeatable-read,用于实现可重复读;
  • 在主线程仍持有全局读锁时开启事务进行快照读,这样保证了读到的一致性数据与主线程相同,实现了备份数据的一致性;
  • 按序从备份任务队列中取出备份任务,工作线程先进行MyISAM等非InnoDB表备份,再完成InnoDB表备份;这样可以在完成非InnoDB表备份通知后主线程释放读锁,尽可能减小对备份实例业务的影响;

mydumper的记录级备份由主线程负责任务拆分,由多个工作线程完成。主线程通过将表数据拆分为多个chunk,每个chunk作为一个备份任务。表数据拆分方式如下所述:mydumper优先选择主键索引的第一列作为chunk划分字段,若不存在主键索引,则选择第一个唯一索引作为划分依据,若还不存在,则选择区分度(Cardinality)最高的任意索引。前提是字段类型是整型,且不能是复合索引。如果都无法满足,则只能进行表级的并行备份。在确定了chunk划分字段后,先获取该字段的最大和最小值,再通过执行“explain select field from db.table”来估计该表的记录数,最后根据所设的每个任务(文件)记录数来将该表划分为多个chunk。如下图所示:

MySQL备份恢复:多线程mydumper工具

以上描述可知,mydumper并不能保证记录级备份时,每个备份任务中的记录数是相同的。另外,目前记录级备份存在一个bug:所用索引字段为负数时主线程会进入死循环无法退出,导致备份失败。https://bugs.launchpad.net/mydumper/+bug/1418355

与mysqldump另一个不同是, mydumper为每个备份任务建立至少一个备份文件。在0.9.1版本中,文件类型包括schema-create、schema、schema-post文件等表元数据文件分别用保存建数据库语句、建表语句(包括触发器)、函数/存储过程/事件定义语句等;数据文件可以根据用户设置为固定大小或固定记录数的文件。这样便以进行更细粒度的数据恢复和数据删除,比如可以在myloader的时候仅选择某几个数据库/表进行恢复。

三、网易对mydumper的改进

mydumper是一款优秀的备份工具,但也存在不足,包括多线程导出数据对实例业务的影响、逻辑备份方式对热点数据污染和持锁时对业务的阻塞等。网易RDS在mydumper实践中对其进行了多方面的优化。

多线程备份固然好,但在进行备份时往往数据库还在正常提供对外服务,多线程全表select数据会占用很大部分的系统IO能力,导致正常的业务IO性能下降,严重时甚至会使数据库连接爆掉。通过为mydumper增加负载自适应能力来最大限度缓解对线上业务影响:工作线程在每次数据导出前,都会首先观察实例的当前负载情况,举MySQL状态Thread_connected为例,其反映的是目前已连接到该实例的请求数,如果该数值大于设定的阈值,则本次导出操作会暂停,直到数值小于阈值才会恢复,这样就起到了根据实例业务负载情况,灵活调整用于数据导出的线程数来适应线上业务负载的作用。

逻辑备份的全表select不可避免会污染InnoDB Buffer Pool的热点数据,缓存的热点数据被换出,降低了命中率的同时增大了业务的IO量,在使用mydumper时应尽量减小对Buffer Pool的影响;通过调整Buffer Pool的热点算法,使得热点数据尽可能不被换出。修改innodb_old_blocks_time和innodb_old_blocks_pct,用于将全表select进入Buffer Pool放在其old sublist中,同时减小old sublist块在Buffer Pool中的比例,起到最小化污染的作用。其原理详见http://dev.mysql.com/doc/innodb-plugin/1.0/en/idm47548325357504.html

在进行数据备份时,由于MyISAM表是非事务的,为了得到一致性的数据,导出MyISAM表需要全程持有读锁。在通常的MySQL实例中,MyISAM表数据都是很少的,所以持锁时间很短,但若有实例存在大量的MyISAM表数据,那么就会因持锁时间过长对业务的数据更新和插入造成影响。通过为mydumper增加持锁超时时间来避免该问题,所在数据备份过程中,持锁时间超过所设置时间,则mydumper返回失败,通过将MyISAM表转化为InnoDB表后再开始导出。

此外,在对大数据量数据库进行备份时,往往需要耗费较长时间,如果能够实时了解备份进度,相信是一个很好的体验,为此,给mydumper增加了进度查询功能,能够查询mydumper所需执行的所有备份任务数、当前已经完成的备份任务数及每个备份任务所花费时间。

四、安装mydumper&myloader

Github上面下载即可,有源码包及RPM包。

如果编译安装,需要先安装依赖,然后进行编译。不想嫌麻烦可以直接使用打好的包即可。

解压安装

安装完成后会生成2个工具:mydumper(备份),myloader(导入),你可以直接复制放入到bin目录下。

五、mydumper&myloader语法

mydumper(备份工具)

myloader(恢复工具)

六、测试案例

案例一:备份单个事务表

备份sbtest数据库下的一个事务表到/backup目录中,是否多线程(开启3个),备份日志如下:

默认情况下,由于没有检测到非事务表,所以在一致性快照开启后就释放了全局锁(在下个案例可以看出这个效果)。然后进行事务表的备份,这样整个数据就是一致的了。 另外,可以看到进行了多线程备份,由于没有任务 Thread-3 启动后就被关闭了。

查看整个备份产生的执行语句(开启general log):

如果你使用mysqldump的话可以看到整个过程都只有一个线程,而使用mydumper备份单个库或多个库时就会发现使用多线程,SELECT阶段根据我们设置的线程数同时工作了。这里由于我们备份单表,所以只有一个线程。

分析:mydumper把数据和表结构分开备份,并且把二进制日志备份出来单独放到一个文件中。

各个文件说明:

metadata:元数据记录备份开始和结束时间,以及master binlog position点(默认记录),如果在slave上备份默认会记录master binlog position及slave binlog position点,并且不会关闭SQL线程,这个很友好,比mysqldump及mysqlpump都强。

table.sql:每个表一个文件。

table-schema.sql:表结构文件。

db-schema.sql:库结构文件。

如何进行表级别多线程备份呢?当表中有主键或唯一索引时,加上 -r 或者 -F 可以执行 chunk 大小,此时就可以对表级别进行多线程备份了。关于行的多线程备份原理和要求在上面的“多线程导出原理”部分已经介绍了。

比如,这里指定以行为单位,1000000为一个chunk进行多线程备份。

基于行拆分的语句如下:

这个可以更好地提升数据库备份速度,不管数据库里是否有大表,而MySQL官方的mysqlpump虽说也是多线程但是只能针对多表多线程,遇到大表时也是无能为力。

案例二:备份事务表和非事务表

备份sbtest数据库下的一个事务表和一个非事务表备份到/backup目录中,备份日志如下:

可以看到,当事务表和非事务表混合情况下,在默认参数下,释放锁是非事务表备份完成后,也就是说锁持有时间取决于非事务表大小。对比上面的单事务表备份,同样在默认参数下,可以看出mydumper在默认情况下就是保证了数据的绝对一致性。

查看整个备份产生的执行语句(开启general log):

案例三:--trx-consistency-only

你可以将此视为mysqldump的--single-transaction参数,但仍然具有binlog位置。显然,这个位置仅适用于事务表(包括TokuDB)。使用此选项的一个优点是全局读锁仅用于线程协调,因此一旦启动事务就会释放它。

备份sbtest数据库下的一个事务表和一个非事务表备份到/backup目录中,查看备份日志:

从日志可以看出,在所有线程都获取到一致性快照后,就释放了锁。然后备份非事务表和事务表,这不就跟--single-transaction参数效果一样。但同时也会有一个问题,就是对于非事务表数据可能是不一致的,使用时需要确保没有非事务表。

查看整个备份产生的执行语句(开启general log):

可以看到加了--trx-consistency-only参数后,锁的释放确实在所有线程事务开启后就进行了。

进一步测试:测试一些常用的参数

1)备份指定表(-T),并且不要导出表结构(-m)

2)压缩备份文件(-c),备份binlog(-b),正则表达式备份表(-x)

如上所示,备份文件已经是压缩的了(用gzip -d 解压),并且备份出了以sbtest开头匹配出来的所有表,二进制日志也被备份到了binlog_snapshot文件中,并且也是被压缩的。这里说明下备份指定数据库的方法:--regex正则匹配

3)恢复数据,表存在先删除(-o):这里需要注意,使用该参数,备份目录里面需要有表结构的备份文件。

最后再来说一下,mydumper这个神器,支持在从库备份并且同时记录master binlog position及slave binlog position,并且不需要关闭SQL线程,很强。有了这个功能就可以大胆在从库备份,从而达到随意做从库,无论是依赖master还是slave都可以做从库。相关的信息都存在metadata文件中,一般信息如下:

关于mydumper的基本使用到这里就结束了。

七、守护模式

Mydumper具有守护进程模式,该模式将经常对 dump data 进行快照,同时不断地检索二进制日志文件(不太明白这句话的意思)。这样可以提供持续一致的备份,直到数据库服务器出现故障。

在以下示例中,mydumper将使用守护程序模式,每半小时创建一个快照并记录到输出文件:

产生的文件如下:

这两个目录会来回进行覆盖轮回,而last_dump就是指向最近一次目录,而目录里面的数据与我们直接备份数据相同。需要注意的是每次都是全量的,并没有增量的概念。

<参考>

https://github.com/maxbube/mydumper

https://www.percona.com/blog/2015/11/12/logical-mysql-backup-tool-mydumper-0-9-1-now-available/


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!

(1)个小伙伴在吐槽