之前詳細介紹了Mysqldump備份工具使用，下面說下MySQL5.7之後新新增的備份工具mysqlpump。mysqlpump是mysqldump的一個衍生，mysqldump備份功能這裡就不多說了，現在看看mysqlpump到底有了哪些提升，詳細可以檢視官網文件。mysqlpump和mysqldump一樣，屬於邏輯備份，備份以SQL形式的文字儲存。邏輯備份相對物理備份好處是不關心log的大小，直接備份資料即可。
Mysqlpump主要特點
- 並行備份資料庫和資料庫中的物件的，加快備份過程。
- 更好的控制資料庫和資料庫物件（表，儲存過程，使用者帳戶）的備份。
- 備份使用者賬號作為帳戶管理語句（CREATE USER，GRANT），而不是直接插入到MySQL的系統資料庫。
- 備份出來直接生成壓縮後的備份檔案。
- 備份進度指示（估計值）。
- 重新載入（還原）備份檔案，先建表後插入資料最後建立索引，減少了索引維護開銷，加快了還原速度。
- 備份可以排除或則指定資料庫。
Mysqlpump缺點
- 只能並行到表級別,如果表特別大,開多執行緒和單執行緒是一樣的,並行度不如mydumper；
- 無法獲取當前備份對應的binlog位置；
- MySQL5.7.11之前的版本不要使用,並行匯出和single-transaction是互斥的；
引數說明：Mysqlpump絕大部分引數使用和Mysqldump一致，下面順便重溫一下。注意對於mysqlpump 專有引數會用背景色 標記出來。
1) --add-drop-database: 在建立庫之前先執行刪庫操作
DROP DATABASE IF EXISTS `...`;
2) --add-drop-table：在建表之前先執行刪表操作
DROP TABLE IF EXISTS `...`.`...`;
3) --add-drop-user：在CREATE USER語句之前增加DROP USER。 注意：這個引數需要和--users一起使用，否者不生效。
DROP USER 'backup'@'172.16.60.%';
4) --add-locks：備份表時，使用LOCK TABLES和UNLOCK TABLES。注意：這個引數不支援並行備份，需要關閉並行備份功能：--default-parallelism=0
LOCK TABLES `...`.`...` WRITE; ... UNLOCK TABLES;
5) --all-databases：備份所有庫，即 -A。
6) --bind-address：指定通過哪個網路介面來連線Mysql伺服器（一臺伺服器可能有多個IP），防止同一個網路卡出去影響業務。
7) --complete-insert：dump出包含所有列的完整insert語句。
8) --compress： 壓縮客戶端和伺服器傳輸的所有的資料，即 -C。
9) --compress-output：預設不壓縮輸出，目前可以使用的壓縮演算法有LZ4和ZLIB
[root@localhost ~]# mysqlpump --compress-output=LZ4 > dump.lz4 [root@localhost ~]# lz4_decompress dump.lz4 dump.txt [root@localhost ~]# mysqlpump --compress-output=ZLIB > dump.zlib [root@localhost ~]# zlib_decompress dump.zlib dump.txt
10) --databases：手動指定要備份的庫，支援多個資料庫，用空格分隔，即-B。
11) --default-character-set：指定備份的字符集。
12) --default-parallelism：指定並行執行緒數，預設是2，如果設定成0，表示不使用並行備份。注意：每個執行緒的備份步驟是：先create table但不建立二級索引（主鍵會在create table時候建立），再寫入資料，最後建立二級索引。
13) --defer-table-indexes：延遲建立索引，直到所有資料都載入完之後，再建立索引，預設開啟。若關閉則會和mysqldump一樣：先建立一個表和所有索引，再匯入資料，因為在載入還原資料的時候要維護二級索引的開銷，導致效率比較低。關閉使用引數：--skip--defer-table-indexes。
14) --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。
17) --insert-ignore：備份用insert ignore語句代替insert語句。
18) --log-error-file：備份出現的warnings和erros資訊輸出到一個指定的檔案。
19) --max-allowed-packet：備份時用於client/server直接通訊的最大buffer包的大小。
20) --net-buffer-length：備份時用於client/server通訊的初始buffer大小，當建立多行插入語句的時候，mysqlpump 建立行到N個位元組長。
21) --no-create-db：備份不寫CREATE DATABASE語句。要是備份多個庫，需要使用引數-B，而使用-B的時候會出現create database語句，該引數可以遮蔽create database 語句。
22) --no-create-info：備份不寫建表語句，即不備份表結構，只備份資料，即 -t。
23) --hex-blob： 備份binary欄位的時候使用十六進位制計數法，受影響的欄位型別有BINARY、VARBINARY、BLOB、BIT。
24) --host ：備份指定的資料庫地址，即 -h。
25) --parallel-schemas=[N:]db_list：指定並行備份的庫，多個庫用逗號分隔，如果指定了N，將使用N個執行緒的地佇列，如果N不指定，將由 --default-parallelism才確認N的值，可以設定多個--parallel-schemas
#4個執行緒備份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不允許的話，可以少開幾個執行緒和資料庫進行並行備份
26) --password：備份需要的密碼。
27) --port ：備份資料庫的埠。
28) --protocol={TCP|SOCKET|PIPE|MEMORY}：指定連線伺服器的協議。
29) --replace：備份出來replace into語句。
30) --routines：備份出來包含儲存過程和函式，預設開啟，需要對 mysql.proc表有檢視許可權。生成的檔案中會包含CREATE PROCEDURE 和 CREATE FUNCTION語句以用於恢復，關閉則需要用--skip-routines引數。
31) --triggers：備份出來包含觸發器，預設開啟，使用--skip-triggers來關閉。
31) --set-charset：備份檔案裡寫SET NAMES default_character_set 到輸出，此參預設開啟。 -- skip-set-charset禁用此引數，不會在備份檔案裡面寫出set names...
32) --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的互斥問題。
33) --skip-definer：忽略那些建立檢視和儲存過程用到的 DEFINER 和 SQL SECURITY 語句，恢復的時候，會使用預設值，否則會在還原的時候看到沒有DEFINER定義時的賬號而報錯。
34) --skip-dump-rows：只備份表結構，不備份資料，即-d。注意：mysqldump支援--no-data，mysqlpump不支援--no-data
35) --socket：對於連線到localhost，Unix使用套接字檔案，在Windows上是命名管道的名稱使用，即 -S。
36) --ssl：--ssl引數將要被去除，用--ssl-mode取代。關於ssl相關的備份。
37) --tz-utc：備份時會在備份檔案的最前幾行新增SET TIME_ZONE='+00:00'。注意：如果還原的伺服器不在同一個時區並且還原表中的列有timestamp欄位，會導致還原出來的結果不一致。預設開啟該引數，用 --skip-tz-utc來關閉引數。
38) --user：備份時候的使用者名稱，即 -u。
39) --users：備份資料庫使用者，備份的形式是CREATE USER...，GRANT...，只備份資料庫賬號可以通過如下命令
#過濾掉所有資料庫 [root@localhost ~]# mysqlpump --exclude-databases=% --users -p123456 --set-gtid-purged=off >/root/db.sql
40) --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 [root@localhost ~]# mysqlpump -uroot -p123456 -h172.16.60.211 --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B kevin > /data/db_backup/kevin_db.sql.lz4 mysqldump備份壓縮kevin資料庫 單個執行緒備份，消耗時間：900s，gzip的壓縮率比LZ4的高 [root@localhost ~]# mysqldump -uroot -p123456 -h172.16.60.211 --default-character-set=utf8 -P3306 --skip-opt --add-drop-table --create-options --quick --extended-insert --single-transaction -B kevin | gzip > /data/db_backup/kevin.sql.gz mydumper備份kevin資料庫 三個併發執行緒備份，消耗時間：300s，gzip的壓縮率比LZ4的高 [root@localhost ~]# mydumper -u root -p123456 -h 172.16.60.211 -P 3306 -t 3 -c -l 3600 -s 10000000 -B kevin -o /data/db_backup/kevin/ mydumper備份kevin資料庫，五個併發執行緒備份，並且開啟對一張表多個執行緒以chunk的方式批量匯出，-r。消耗時間：180s [root@localhost ~]# mydumper -u root -p123456 -h 172.16.60.211 -P 3306 -t 5 -c -r 300000 -l 3600 -s 10000000 -B kevin -o /data/db_backup/kevin/ 注意： 如果是開啟了GTID功能的資料庫，備份時還需要新增"--set-gtid-purged=off"引數，否則可能會報錯！
從上面看出，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 mysqlpump: [Warning] Using a password on the command line interface can be insecure. Dump progress: 1/5 tables, 0/7559817 rows Dump progress: 3/15 tables, 286750/12022332 rows Dump progress: 3/15 tables, 686750/12022332 rows Dump progress: 3/15 tables, 1042250/12022332 rows ... Dump completed in 43732 milliseconds 接著另外開啟一個終端會話，登入mysql看下情況 (root@172.16.0.10) [(none)]> show processlist; +--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+ | 138199 | root | 172.16.60.50:39238 | NULL | Query | 0 | starting | show processlist | | 138267 | root | 172.16.60.50:39776 | NULL | Sleep | 2 | | NULL | | 138268 | root | 172.16.60.50:39778 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`dept_no`,`from_date`,`to_date` FROM `kevin`.`dept_emp` | | 138269 | root | 172.16.60.50:39780 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date` FROM `emplo | | 138270 | root | 172.16.60.50:39782 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `o_orderkey`,`o_custkey`,`o_orderstatus`,`o_totalprice`,`o_orderDATE`,`o_orderpr | | 138271 | root | 172.16.60.50:39784 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `p_partkey`,`p_name`,`p_mfgr`,`p_brand`,`p_type`,`p_size`,`p_container`,`p_retai | | 138272 | root | 172.16.60.50:39786 | NULL | Query | 2 | Sending data | SELECT SQL_NO_CACHE `l_orderkey`,`l_partkey`,`l_suppkey`,`l_linenumber`,`l_quantity`,`l_extendedpric | | 138273 | root | 172.16.60.50:39788 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `c_custkey`,`c_name`,`c_address`,`c_nationkey`,`c_phone`,`c_acctbal`,`c_mktsegme | | 138274 | root | 172.16.60.50:39790 | NULL | Sleep | 2 | | NULL | | 138275 | root | 172.16.60.50:39792 | NULL | Sleep | 1 | | NULL | +--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) 可以看到138268和138269在備份kevin庫,138270,138271,138272,138273在備份dbt3,這裡沒列印全。
- 備份過程如下:
終端會話1: (root@localhost) [(none)]> truncate mysql.general_log; Query OK, 0 rows affected (0.10 sec) (root@localhost) [(none)]> set global log_output = 'table'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> set global general_log = 1; Query OK, 0 rows affected (0.03 sec) 終端會話2: [root@VM_0_5_centos ~]# mysqlpump --single-transaction kevin --set-gtid-purged=OFF -p123456> /tmp/backup.sql Dump completed in 592 milliseconds (root@localhost) [(none)]> select thread_id,left(argument, 64) from mysql.general_log order by event_time; ................ ................ +-----------+------------------------------------------------------------------+ | 7 | root@localhost on using Socket | | 7 | FLUSH TABLES WITH READ LOCK | | 7 | SHOW WARNINGS | | 7 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | | 7 | SHOW WARNINGS | | 7 | START TRANSACTION WITH CONSISTENT SNAPSHOT | | 7 | SHOW WARNINGS | | 8 | root@localhost on using Socket | | 8 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | | 8 | SHOW WARNINGS | | 8 | START TRANSACTION WITH CONSISTENT SNAPSHOT | | 8 | SHOW WARNINGS | | 9 | root@localhost on using Socket | | 9 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | | 9 | SHOW WARNINGS | | 9 | START TRANSACTION WITH CONSISTENT SNAPSHOT | | 9 | SHOW WARNINGS | | 7 | UNLOCK TABLES | | 7 | SHOW WARNINGS | | 9 | SET SQL_QUOTE_SHOW_CREATE= 1 | | 9 | SHOW WARNINGS | | 9 | SET TIME_ZONE='+00:00' | | 8 | SET SQL_QUOTE_SHOW_CREATE= 1 | | 8 | SHOW WARNINGS | | 8 | SET TIME_ZONE='+00:00' | | 3 | set global general_log = 0 | +-----------+------------------------------------------------------------------+ 根據上面資訊，可以看出： - 執行緒7 進行 FLUSH TABLES WITH READ LOCK 。對錶加一個讀鎖 - 執行緒7、8、9分別開啟一個事務（RR隔離級別）去備份資料,由於之前鎖表了,所以這三個執行緒備份出的資料是具有一致性的 - 執行緒7 解鎖 UNLOCK TABLE - 整個過程都沒有獲取二進位制位置點
- 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），總之根據需要進行測試，最後再決定使用哪種備份工具進行備份。