mysqlpump使用说明
下面介绍 MySQL5.7 之后新添加的备份工具 mysqlpump。
mysqlpump 是 mysqldump 的一个衍生，mysqldump 备份功能这里就不多说了，现在看看 mysqlpump 到底有了哪些提升，详细可以查看官网文档。
mysqlpump 和 mysqldump 一样，属于逻辑备份，备份以 SQL 形式的文本保存。逻辑备份相对物理备份好处是不关心 log 的大小，直接备份数据即可。
¶mysqlpump 主要特点
- 并行备份数据库和数据库中的对象的，加快备份过程。
- 更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
- 备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到 MySQL 的系统数据库。
- 备份出来直接生成压缩后的备份文件。
- 备份进度指示（估计值）。
- 重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。
- 备份可以排除或则指定数据库。
¶mysqlpump 缺点
https://theme-next.js.org/highlight/
- 只能并行到表级别, 如果表特别大, 开多线程和单线程是一样的, 并行度不如 mydumper；
- 无法获取当前备份对应的 binlog 位置；
- MySQL5.7.11 之前的版本不要使用, 并行导出和 single-transaction 是互斥的；
¶参数说明
mysqlpump 绝大部分参数使用和 Mysqldump 一致，下面顺便重温一下。注意对于 mysqlpump 专有参数会用加粗标记出来。
1) --add-drop-database： 在建立库之前先执行删库操作DROP DATABASE IF EXISTS `...`;
- –add-drop-table：在建表之前先执行删表操作
DROP TABLE IF EXISTS `...`.`...`; |
3) --add-drop-user：在 CREATE USER 语句之前增加 DROP USER。 注意：这个参数需要和 --users 一起使用，否者不生效。DROP USER 'backup'@'172.16.60.%';
- –add-locks：备份表时，使用 LOCK TABLES 和 UNLOCK TABLES。注意：这个参数不支持并行备份，需要关闭并行备份功能：–default-parallelism=0
LOCK TABLES `...`.`...` WRITE; |
–all-databases：备份所有库，即 -A。
–bind-address：指定通过哪个网络接口来连接 Mysql 服务器（一台服务器可能有多个 IP），防止同一个网卡出去影响业务。
–complete-insert：dump 出包含所有列的完整 insert 语句。
–compress： 压缩客户端和服务器传输的所有的数据，即 -C。
–compress-output：默认不压缩输出，目前可以使用的压缩算法有 LZ4 和 ZLIB
[root@localhost ~]# mysqlpump --compress-output=LZ4 > dump.lz4 |
–databases：手动指定要备份的库，支持多个数据库，用空格分隔，即 - B。
–default-character-set：指定备份的字符集。
**12) --default-parallelism：指定并行线程数，默认是 2，如果设置成 0，表示不使用并行备份。**注意：每个线程的备份步骤是：先 create table 但不建立二级索引（主键会在 create table 时候建立），再写入数据，最后建立二级索引。
**13) --defer-table-indexes：延迟创建索引，直到所有数据都加载完之后，再创建索引，默认开启。**若关闭则会和 mysqldump 一样：先创建一个表和所有索引，再导入数据，因为在加载还原数据的时候要维护二级索引的开销，导致效率比较低。关闭使用参数：–skip–defer-table-indexes。
- –events：备份数据库的事件，默认开启，关闭使用 --skip-events 参数。
15) --exclude-databases：备份排除该参数指定的数据库，多个用逗号分隔。类似的还有 --exclude-events、–exclude-routines、–exclude-tables、–exclude-triggers、–exclude-users[root@localhost ~]# mysqlpump --exclude-databases=mysql,sys -p123456 --set-gtid-purged=off >/root/db.sql #备份过滤mysql和sys数据库
[root@localhost ~]# mysqlpump --exclude-tables=rr,tt -p123456 --set-gtid-purged=off > /root/db.sql #备份过滤所有数据库中rr、tt表
[root@localhost ~]# mysqlpump -B test --exclude-tables=tmp_ifulltext,tt -p123456 --set-gtid-purged=off >/root/db.sql #备份过滤test库中的rr、tt表
...
**注意：**要是只备份数据库的账号，需要添加参数 –users，并且需要过滤掉所有的数据库，如备份除dba和backup的所有账号。
[root@localhost ~]# mysqlpump --users --exclude-databases=sys,mysql,db1,db2 --exclude-users=dba,backup -p123456 --set-gtid-purged=off >/root/db.sql
16) --include-databases：指定备份数据库，多个用逗号分隔，类似的还有 --include-events、–include-routines、–include-tables、–include-triggers、–include-users，大致方法使用同 15。
–insert-ignore：备份用 insert ignore 语句代替 insert 语句。
–log-error-file：备份出现的 warnings 和 erros 信息输出到一个指定的文件。
–max-allowed-packet：备份时用于 client/server 直接通信的最大 buffer 包的大小。
–net-buffer-length：备份时用于 client/server 通信的初始 buffer 大小，当创建多行插入语句的时候，mysqlpump 创建行到 N 个字节长。
–no-create-db：备份不写 CREATE DATABASE 语句。要是备份多个库，需要使用参数 - B，而使用 - B 的时候会出现 create database 语句，该参数可以屏蔽 create database 语句。
–no-create-info：备份不写建表语句，即不备份表结构，只备份数据，即 -t。
–hex-blob： 备份 binary 字段的时候使用十六进制计数法，受影响的字段类型有 BINARY、VARBINARY、BLOB、BIT。
–host ：备份指定的数据库地址，即 -h。
25) --parallel-schemas=[N：]db_list：指定并行备份的库，多个库用逗号分隔，如果指定了 N，将使用 N 个线程的地队列，如果 N 不指定，将由 --default-parallelism 才确认 N 的值，可以设置多个 --parallel-schemas4个线程备份vs和aa，3个线程备份pt。通过show processlist 可以看到有7个线程。
[root@localhost ~]# mysqlpump --parallel-schemas=4：vs,aa --parallel-schemas=3：pt -p123456 --set-gtid-purged=off > /root/db.sql
默认2个线程，即2个线程备份vs和abc，2个线程备份pt
[root@localhost ~]# mysqlpump --parallel-schemas=vs,abc --parallel-schemas=pt -p123456 --set-gtid-purged=off > /root/db.sql
当然要是硬盘IO不允许的话，可以少开几个线程和数据库进行并行备份
–password**：**备份需要的密码。
–port ：备份数据库的端口。
–protocol={TCP|SOCKET|PIPE|MEMORY}：指定连接服务器的协议。
–replace：备份出来 replace into 语句。
–routines：备份出来包含存储过程和函数，默认开启，需要对 mysql.proc 表有查看权限。生成的文件中会包含 CREATE PROCEDURE 和 CREATE FUNCTION 语句以用于恢复，关闭则需要用 --skip-routines 参数。
–triggers：备份出来包含触发器，默认开启，使用 --skip-triggers 来关闭。
–set-charset：备份文件里写 SET NAMES default_character_set 到输出，此参默认开启。 – skip-set-charset 禁用此参数，不会在备份文件里面写出 set names…
–single-transaction**：**该参数在事务隔离级别设置成 Repeatable Read，并在 dump 之前发送 start transaction 语句给服务端**。这在使用 innodb 时很有用，因为在发出 start transaction 时，保证了在不阻塞任何应用下的一致性状态。对 myisam 和 memory 等非事务表，还是会改变状态的，当使用此参的时候要确保没有其他连接在使用 ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE 等语句，否则会出现不正确的内容或则失败。–add-locks 和此参互斥，在 mysql5.7.11 之前，–default-parallelism 大于 1 的时候和此参也互斥，必须使用 --default-parallelism=0。5.7.11 之后解决了 --single-transaction 和 --default-parallelism 的互斥问题。
–skip-definer：忽略那些创建视图和存储过程用到的 DEFINER 和 SQL SECURITY 语句，恢复的时候，会使用默认值，否则会在还原的时候看到没有 DEFINER 定义时的账号而报错。**
–skip-dump-rows：只备份表结构，不备份数据，即 -d。注意：mysqldump 支持 --no-data，mysqlpump 不支持 --no-data**
–socket：对于连接到 localhost，Unix 使用套接字文件，在 Windows 上是命名管道的名称使用，即 -S。
–ssl**：**–ssl 参数将要被去除，用 --ssl-mode 取代**。关于 ssl 相关的备份。
–tz-utc：备份时会在备份文件的最前几行添加 SET TIME_ZONE=‘+00：00’。注意：如果还原的服务器不在同一个时区并且还原表中的列有 timestamp 字段，会导致还原出来的结果不一致。默认开启该参数，用 --skip-tz-utc 来关闭参数。
–user：备份时候的用户名，即 -u。
–users：备份数据库用户，备份的形式是 CREATE USER…，GRANT…，只备份数据库账号可以通过如下命令**
过滤掉所有数据库 |
- –watch-progress：定期显示进度的完成，包括总数表、行和其他对象。该参数默认开启，用 --skip-watch-progress 来关闭。
mysqlpump 的多线程架构图如下
- mysqlpump 是 MySQL5.7 的官方工具, 用于取代 mysqldump, 其参数与 mysqldump 基本一样；
- mysqlpump 是多线程备份, 但只能到表级别, 单表备份还是单线程；
- mysqldump 备份时, 有个默认队列（default）, 队列下开 N 个线程去备份数据库 / 数据库中的表；
- 支持开多个队列 (对应不同库 / 表), 然后每个队列设置不同线程, 进行备份；
mysqlpump 支持基于库和表的并行导出，mysqlpump 的并行导出功能的架构为：队列 + 线程，允许有多个队列（–parallel-schemas），每个队列下有多个线程（N），而一个队列可以绑定 1 个或者多个数据库（逗号分隔）。mysqlpump 的备份是基于表并行的，对于每张表的导出只能是单个线程的，这里会有个限制是如果某个数据库有一张表非常大，可能大部分的时间都是消耗在这个表的备份上面，并行备份的效果可能就不明显。这里可以利用 Mydumper 其是以 chunk 的方式批量导出，即 Mydumper 支持一张表多个线程以 chunk 的方式批量导出。但相对于 Mysqldump 有很大提升。
¶对比测试如下
mysqlpump压缩备份kevin数据库 三个并发线程备份，消耗时间：222s |
从上面看出，mysqlpump 的备份效率是最快的，mydumper 次之，mysqldump 最差。所以在 IO 允许的情况下，能用多线程就别用单线程备份。并且 mysqlpump 还支持多数据库的并行备份，而 mydumper 要么备份一个库，要么就备份所有库。可以看出，在 mysql 数据库备份方面，mysqlpump 比 mysqldump 的测试结果要好。由于实际情况不同，测试给出的速度提升只是参考。到底开启多少个并行备份的线程，这个看磁盘 IO 的承受能力，若该服务器只进行备份任务，可以最大限制的来利用磁盘。
测试中发现 mysqlpump 和 mysqldump 对比：
- mysqldump 默认是不会有建库命令， 但是默认会有 drop table 的命令;
- mysqlpump 默认是有建库命令，但是不会有 drop table 的命令，所以 mysqlpump 恢复的时候不要直接 < file.sql ;
- mysqldump 恢复时会先创建表及其所有索引，然后再导入数据；mysqlpump 恢复时会先创建表，然后再导入数据，最后建索引;
- mysqlpump 可以指定多线程并发备份，默认是 2 个；备份时会有进度指示，虽然只是估计值，但不会再想 mysqldump 备份时那么枯燥，看不到过程.
注意：mysqlpump 备份的几个重要参数--default-parallelism 指定线程数,默认开2个线程进行并发备份
--parallel-schemas 指定哪些数据库进行并发备份
--set-gtid-purged=OFF 这个是5.7.18版本后加入的参数,
--set-gtid-purged=OFF这个参数很重要，如果备份命令里不加上，则备份可能会报错：
Warning： A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed
suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass
--all-databases --triggers --routines --events.
¶备份演示
¶备份命令
[root@localhost ~]# mysqlpump --single-transaction --set-gtid-purged=OFF --parallel-schemas=2：kevin --parallel-schemas=4：dbt3 -B kevin dbt3 -p123456 > /tmp/backup.sql |
¶备份过程如下
终端会话1： |
¶compress-output
mysqlpump 支持压缩输出, 支持 LZ4 和 ZLIB（ZLIB 压缩比相对较高, 但是速度较慢）[root@localhost tmp]# mysqlpump --single-transaction --compress-output=lz4 kevin --set-gtid-purged=OFF -p123456 > /tmp/backup_kevin.sql
Dump completed in 511 milliseconds
¶备份恢复
未压缩的备份mysql < source /tmp/backup.sql;
压缩过的备份先解压
[root@localhost ~]# lz4_decompress /tmp/backup_kevin.sql /tmp/kevin.sql
再导入
mysql < source /tmp/kevin.sql;
可以看出来, 这个导入是单线程。mysqlpump 备份的数据恢复时会先插入数据, 再建索引, 而 mysqldump 备份的数据恢复是在建立表的时候就把索引加上了, 所以前者备份的数据恢复时速度要快一点！
总体来说 mysqlpump 还是很好用的，尤其是多数据库表的备份。不过如果有一张表格外大，那么备份的大部分时间还是要消耗在这张表上，因为 mysqlpump 的备份是基于表并行的，对于每张表的导出只能是单个线程的。另外**注意 mysqlpump 备份时并发线程的数量还是要看自身服务器的 IO 负载能力，并不是说一味的增加并发线程数量就可以加快速度。**mysqldump 和 mysqlpump 的使用方法绝大部分一致，mysqlpump 新的参数文章上已经标明，到底用那种工具备份数据库这个要在具体的环境下才能做出选择，有些时候可能用物理备份更好（xtrabackup），总之根据需要进行测试，最后再决定使用哪种备份工具进行备份。