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。