mysql5.7逻辑备份工具mysqlpump

mysqlpump是mysql5.7.8版本后特有的逻辑备份工具，相对于mysqldump和mysqldumper，mysqlpump拥有更多特性，官方文档的描述如下：

mysqlpump features include:
 Parallel processing of databases, and of objects within databases, to speed up the dump process
 Better control over which databases and database objects (tables, stored programs, user accounts)
to dump
 Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather than
as inserts into the mysql system database
 Capability of creating compressed output
 Progress indicator (the values are estimates)
 For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes after rows are inserted

1、支持多线程备份数据库和数据库对象，加快备份进程。

2、有更好的控制策略去备份数据库和数据库对象（表、存储过程、账户）。

3、备份用户通过账户管理SQL（create user,grant）而不再是往mysql系统库插入记录的方式

4、能创建压缩文件

5、显示进度（估算值）

6、重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。

注：mysql5.7.11版本后，mysqlpump才解决了一致性备份的问题，如果早于这个版本，逻辑备份最好采用mysqldump解决一致性备份问题。

Before MySQL 5.7.11, use of the --single-transaction option is mutually
exclusive with parallelism. To use --single-transaction, disable parallelism
by setting --default-parallelism to 0 and not using any instances of --
parallel-schemas:
shell> mysqlpump --single-transaction --default-parallelism=0

为了测试下mysqlpump性能，在自己虚拟机上做了小测试，

    测试虚拟机环境：

    主机：

    CPU：Intel(R) Core(TM) i5-6400 CPU @ 2.70GHz 4核

    内存：1G

    磁盘：SCSI硬盘 10G

    数据库：

    版本：5.7.16

用sysbench准备测试数据，以下是测试数据准备脚本：

[root@mysql1 shell]# cat test_sysbench.sh 
#!/bin/sh
sysbench --test=oltp --mysql-host=192.168.110.100 --mysql-port=3307 --mysql-user=root --mysql-password=root --mysql-db=sbtest2 --oltp-num-tables=4 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=off --max-time=30 --num-threads=16 prepare 
sysbench --test=oltp --mysql-host=192.168.110.100 --mysql-port=3307 --mysql-user=root --mysql-password=root --mysql-db=sbtest3 --oltp-num-tables=4 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=off --max-time=30 --num-threads=16 prepare 
sysbench --test=oltp --mysql-host=192.168.110.100 --mysql-port=3307 --mysql-user=root --mysql-password=root --mysql-db=sbtest4 --oltp-num-tables=4 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=off --max-time=30 --num-threads=16 prepare

测试结果如下：（注意每次测试都需要重启mysql服务，以避免内存缓冲对测试结果有所影响）

shell>time mysqlpump -uroot -proot -S /mysqldata/mysql/mysql3307/mysql3307.sock -A --single-transaction --default-parallelism=1 > pump.sql
real    0m46.317s
user    0m13.602s
sys     0m3.949s
shell>time mysqlpump -uroot -proot -S /mysqldata/mysql/mysql3307/mysql3307.sock -A --single-transaction --default-parallelism=2 > pump.sql
real    0m48.707s
user    0m15.087s
sys     0m3.965s
shell>time mysqlpump -uroot -proot -S /mysqldata/mysql/mysql3307/mysql3307.sock -A --single-transaction --default-parallelism=4 > pump.sql
real    0m55.529s
user    0m14.783s
sys     0m4.143s
shell>time mysqldump  -uroot -proot -S /mysqldata/mysql/mysql3307/mysql3307.sock --set-gtid-purged=OFF -A --single-transaction > dump.sql
real    0m57.089s
user    0m8.870s
sys     0m3.441s
++++++++++++++++++++++++++++++++++++++++++分割线+++++++++++++++++++++++++++++++++++++++++++
shell>time mysqlpump -uroot -proot -S /mysqldata/mysql/mysql3307/mysql3307.sock -A --single-transaction --default-parallelism=1 --compress_output=LZ4 --skip-watch-progress > pump.sql 
real    1m6.681s
user    0m19.521s
sys     0m4.041s

shell>time mysqlpump -uroot -proot -S /mysqldata/mysql/mysql3307/mysql3307.sock -A --single-transaction --default-parallelism=1 --compress_output=ZLIB --skip-watch-progress > pump.sql   
real    1m8.877s
user    0m27.315s
sys     0m4.693s

shell>time mysqldump  -uroot -proot -S /mysqldata/mysql/mysql3307/mysql3307.sock --set-gtid-purged=OFF -A --single-transaction |gzip> dump.sql.gz
real    1m8.567s
user    0m20.797s
sys     0m8.082s

从测试结果上可以发现：

1、mysqlpump确实比mysqldump效率高。

2、但是由于测试机磁盘IO有限，导致并发线程数从1提升到2和4的时候，由于IO的瓶颈，备份的时间反而增加。

3、由于测试的库表数据较小，测试数据有所失真，建议准备多几个库和大表的数据进行测试，效果会更明显。

4、mysqlpump的LZ4压缩算法压缩效率高于ZLIB压缩算法。

mysqlpump用法同mysqldump用法高度一致，最为有看点的就是：

1、支持基于表的多线程导出功能(--default-parallelism，默认为2，--parallel-schemas，控制并行导出的库)
2、导出的时候带有进度条（--watch-progress，默认开启）
3、支持直接压缩导出导入（参数--compress-output，而且支持ZLIB和LZ4）

详细的用法可以通过查找官方文档or mysqlpump --help。