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

MySQL 5.7:多线程备份工具mysqlpump使用

MySQL 5.7 彭东稳 7年前 (2016-12-13) 38898次浏览 已收录 0个评论

一、mysqlpump介绍

MySQL在备份方面包含了自身的mysqldump工具,但其只支持单线程工作,这就使得它无法迅速的备份数据。所以就有人写了mydumper工具,支持多线程,并且是行级粒度,支持正则匹配多数据库。MySQL官方当然也意识到了这个问题,所以从MySQL 5.7开始就推出了mysqlpump工具,mysqlpump相对于之前的逻辑备份工具mysqldump来说,总结的优势如下:

  • 支持基于表的并行导出功能(参数--default-parallelism ,默认为2,参数--parallel-schemas ,控制并行导出的库)
  • 导出的时候带有进度条(参数--watch-progress ,默认开启)
  • 支持直接压缩导出导入(参数--compress-output ,支持ZLIB和LZ4)
  • 重新加载(还原)备份文件,先建表后插入数据最后建立索引,减少了索引维护开销,加快了还原速度。
  • 更好的控制数据库和数据库对象(表,存储过程,用户帐户)的备份。

说完优势,再来说说缺点:

  • 导出数据时尽然没有position,如果想用这个玩意儿搭建个备库,岂不是奢望?(暂时不支持--master-data--dump-slave参数了)
  • 在MySQL 5.7.11之前无法保证数据一致性,所以基本没法使用,好在之后修复了这个问题。
  • 由于是对表的多线程,所以在大表比例大的时候其速度也不是很乐观。

但mysqlpump真正可以在生产使用,直到MySQL 5.7.11版本发布,其中最有意义的部分在于官方修复了之前mysqlpump工具一致性备份的问题,使得mysqlpump工具在生产环境中有了用武之地。而在之前MySQL 5.7的文档中明确写着,mysqlpump是不支持一致性的备份,如下:

注意:mysqlpump的多线程备份是基于表的,所以当只有一张表或99张是小表,1张是超级大表,mysqlpump备份速度还不如mysqldump(实际测试单表一千万数据,mysqlpump在7秒,mysqldump在5秒多),甚至更慢。

mysqlpump的并行导出功能的架构为:队列+线程,允许有多个队列,每个队列下有多个线程,而一个队列可以绑定1个或者多个数据库。但是,对于每张表的导出只能是单个线程的,这和mydumper工具是不一样的,因为mydumper支持一张表多个线程以chunk的方式批量导出,能够实现记录级别的并行备份,这在主键是随机的情况下,导出速度还能有提升。

mysqlpump的架构如下图所示:

MySQL 5.7:多线程备份工具mysqlpump使用

接着对比了mysqldump与mysqlpump的导出速度,选择的数据库大小为7.8G,每次备份测试时都重启数据库,清空缓冲池中的内容。其中各表的大小如下所示:

由于只有tpcc单个数据库,这里mysqlpump测试采用默认单队列2个线程和单队列4个线程测试,mysqlpump测试语句如下:

最后的测试结果如下所示:

MySQL 5.7:多线程备份工具mysqlpump使用

测试在网易云环境下,这时可以发现默认配置下mysqlpump的速度比起mysqldump快了39.04%,4个线程下快了有48.89%。接着测试多个数据库备份的场景,这里选择7.8G大小的tpcc库和2.4G大小的dbt3数据库,最后得到的结果mysqlpump比起mysqldump最高快了70%多的时间:

MySQL 5.7:多线程备份工具mysqlpump使用

总结

mysqlpump的语法与mysqldump高度兼容,支持基于库和表的并行导出,对比mysqldump速度提升非常明显。MySQL 5.7.11版本解决了一致性备份问题,推荐线上环境使用。由于每个数据库大小,架构不同,测试给出的速度提升只是参考,或许在你的环境会没有任何差别,也可能速度提升更大。anyway,是时候好好测试mysqlpump,看看对你的生产环境是否会有极大的速度提升。

二、mysqlpump备份工具使用

mysqlpump语法跟mysqldump语法大部分都相同(除mysqldump支持而mysqlpump不支持的选项外),另外除了多线程部分和显示进度条外,其他部分的实现原理也都差不多,可以看MySQL备份工具:单线程mysqldump工具使用

-h, --host

指定备份连接的主机地址。

-P, --port

指定备份连接的主机端口。

-u, --user

指定备份用户。

-p, --password

指定备份用户密码。

-S, --socket

指定备份socket。

--character-sets-dir

指定备份数据导出字符集。

-C, --compress

压缩客户端和服务器传输的所有的数据。

--compress-output

默认不压缩输出,目前可以使用的压缩算法有LZ4和ZLIB(ZLIB压缩比更加高效)。

--default-parallelism

指定并行线程数,默认是2,如果设置成0,表示不使用并行备份。注意:每个线程的备份步骤是:先create table但不建立二级索引(主键会在create table时候建立),再写入数据,最后建立二级索引。

--defer-table-indexes

延迟创建索引,直到所有数据都加载完之后,再创建索引,默认开启。若关闭则会和mysqldump一样:先创建一个表和所有索引,再导入数据,因为在加载还原数据的时候要维护二级索引的开销,导致效率比较低。关闭使用参数:--skip--defer-table-indexes

--exclude-databases

备份排除该参数指定的数据库,多个用逗号分隔。类似的还有--exclude-events--exclude-routines--exclude-tables--exclude-triggers--exclude-users

--include-databases

指定备份数据库,多个用逗号分隔。类似的还有--include-events--include-routines--include-tables--include-triggers--include-users,大致方法使用同15。

--exclude-tables

备份排除该参数指定的表,多个用逗号分隔。

--include-tables

指定备份表,多个用逗号分隔。

--parallel-schemas

指定并行备份的库,多个库用逗号分隔,如果指定了N,将使用N个线程的队列,如果N不指定,将由--default-parallelism才确认N的值,可以设置多个--parallel-schemas

####当然要是硬盘IO不允许的话,可以少开几个线程和数据库进行并行备份。

-d, --skip-dump-rows

只备份表结构,不备份数据。注意:mysqldump支持--no-data,mysqlpump不支持--no-data

--users

备份数据库用户,备份的形式是CREATE USER…,GRANT…,只备份数据库账号可以通过如下命令。

--watch-progress

定期显示进度的完成,包括总数表、行和其他对象。该参数默认开启,用--skip-watch-progress来关闭。

--single-transaction

该参数适用于InnoDB表,与–lock-tables参数互斥,备份期间不锁表(但会有一个瞬间需执行FLUSH TABLE WITH READ LOCK),即热备份。此参数在备份开始前,会开启事务,并且会设置隔离级别为RR,利用可重复读特性来获得备份的一致性。当启用该参数并进行备份时,为确保得到有效的备份文件,使用该参数备份期间应避免使用DDL(ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.)语句,因为连续性的读并没有对这些语句进行隔离,备份期间使用这些DDL语句会导致潜在的select获取到的返回的数据不一致或错误,比如数据读出一半表被删了。

--login-path

可以支持免密码备份数据,参考:mysqlpump免密码备份

三、mysqlpump备份实践

mysqlpump压缩备份vs数据库,三个并发线程备份,消耗时间:222s。

mysqldump备份压缩vs数据库,单个线程备份,消耗时间:900s,gzip的压缩率比LZ4的高。

待补充。。。


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

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