MySQL并行导入导出工具——mysqlpump

一、 mysqlpump简介

mysql官方从5.7开始推出了mysqlpump工具，它和mysqldump一样属于逻辑备份。

1. 优点

• 基于表并行备份数据库和数据库中对象，加快备份过程。（--default-parallelism）
• 更好地控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
• 备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
• 支持直接压缩导出
• 导出可显示进度（估计值）。
• 导出时可以排除或指定数据库。
• 导入备份文件时，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。

2. 不足

• 官方表示在5.7.11之前无法保证数据的一致性，所以5.7.11之前该工具基本无法使用
• 5.7.11之前，--defaut-parallelism>0时与--single-transaction互斥，无法使用并行。直到5.7.11才解决了--single-transaction和--default-parallelism互斥的问题
• mysqlpump的并行是基于表的，对db中只有几个超大表其余表都很小的场景，并行备份基本无用，甚至速度还不如mysqldump

二、mysqlpump备份原理

并行导出的架构为：队列+线程。允许有多个队列（--parallel-schemas），每个队列下有多个线程（N），每个队列可以绑定1个或者多个数据库（逗号分隔）。mysqlpump的备份是基于表并行的，对于每张表的导出只能是单个线程的。如果某个库有一张表非常大，可能大部分的时间都是消耗在这个表的备份上面，mysqlpump并行备份的效果就不明显，此时可以利用mydumper导出。mydumper是以chunk方式批量导出，即它支持对表的并行导出。

三、mysqlpump重要参数

mysqlpump参数与mysqldump基本类似，下面只介绍重要及常用的参数，其余参数参考官方文档

MySQL :: MySQL 5.7 Reference Manual :: 4.5.6 mysqlpump — A Database Backup Program

参数	含义	备注
-A, --all-databases	备份所有数据库
--add-locks	备份表时使用LOCK TABLES和UNLOCK TABLES	这个参数不支持并行备份，需要关闭并行备份功能：default-parallelism=0
--character-sets-dir=name	指定备份数据导出字符集
--compress-output=name	直接压缩导出	目前支持LZ4和ZLIB压缩算法
-B, --databases	指定备份数据库，多个库之间用逗号分隔
--default-parallelism	默认备份并行线程数，设为0表示默认不使用并行备份	默认为2
--defer-table-indexes	延迟创建索引，将全部数据备份结束后再创建索引，默认开启。	mysqldump导入先创建表和索引，然后加载原数据，资源消耗不仅有备份还有对二级索引的维护
--exclude-databases=name	备份时排除该参数指定的数据库，多个数据库之前使用,分隔
--exclude-tables=name	备份时排除该参数指定的表，多个表之前使用,分隔
--include-databases=name	备份指定数据库，多个数据库之前使用,分隔
--include-tables=name	备份指定表，多个表之前使用,分隔
--parallel-schemas=[N:]db_list	指定要并行备份的库及并行度N，多个库之间用逗号分隔。（注意是指定的库一共用N个线程，而不是每个用N个线程）	若不指定并行度则由-default-parallelism参数决定，默认为2。
-d, --skip-dump-rows	只备份表结构，不备份数据
--users	备份数据库用户，备份形式为create user ... ,grant ....	如果只需要备份数据库账号可以使用 mysqlpump --exclude-databases=% --users
--add-drop-user	在CREATE USER语句之前增加DROP USER	这个参数需要和--users一起使用，否则不生效
--watch-progress	显示备份进度，默认开启
--single-transaction	对于innodb表，在备份开始的时候会开启一个事务，并且设置隔离级别为RR，保证备份数据的一致性。备份期间应避免DDL。	5.7.11后解决了--single-transaction才能与--default-parallelism共用

四、 常见用法

1.   指定库导出：（-B, --databases）

#单库导出
mysqlpump -B webplus > backup.sql

#多库导出
mysqlpump --databases webplus1webplus2 webplus3 > backup.sql

2.   全库导出：（-A, --all-databases）

mysqlpump --all-databases > backup.sql

3.   导出指定表：

mysqlpump 库名 表1 表2 > backup.sql

mysqlpump webplus T_ARTICLE T_SITEARTICLE > backup.sql

4.   导出时排查指定库、表：

mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2 > backup.sql

mysqlpump --user=root --password --exclude-databases=db1,db2 > backup.sql

5.   指定错误日志

mysqlpump --log-error-file=/opt/mysqlerror.logwebplus > backup.sql

6.   不锁表导出
--single-transaction参数使用时mysql不锁表热备份，但是备份时需要避免使用DDL语句

mysqlpump --single-transactionwebplus > backup.sql

7.   多线程导出

默认线程（--default-parallelism）和指定线程（--parallel-schemas）

mysqlpump --default-parallelism=5 --parallel-schemas=4:webplusA --parallel-schemas=3:webplusB,webplusC webplusD >backup.sql

此时webplusA调用4线程，webplusB、C调用3线程，webplusD调用5线程（默认值），show processlist将会看到4+3+5=12线程正在进行pump。图为pump 5线程与dump 单线程比较，运行前均重启数据库

8.   压缩导出

使用zlib/lz4进行压缩导出（导出时同时压缩，含有--compress-output时--compress为默认值，--compress可省略）

mysqlpump --compress --compress-output=LZ4> backup.lz4
mysqlpump --compress --compress-output=ZLIB> backup.zlib

实际压缩时间及文件大小：

• dump不压缩     57秒             大小约1800MB
• dump+tar：    2分05秒        压缩文件约333M 约占原文件18.5%
• pump zlib：     1分33秒       压缩文件约346M 约占原文件19.2%
• pump lz4：      30秒            压缩文件约509M 约占原文件28.2%

 

参考

MySQL mysqlpump并行备份_数据库技术_Linux公社-Linux系统门户网站

文章-阿里云开发者社区-云计算社区-阿里云

统一身份认证登录

MySQL 5.7 新备份工具mysqlpump 使用说明 - 运维小结 - 散尽浮华 - 博客园