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

一、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的架构如下图所示：

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

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

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

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

总结

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)