Ⅰ、功能分析

1.1 多線程介紹

• mysqlpump是MySQL5.7的官方工具,用于取代mysqldump,其參數與mysqldump基本一樣
• mysqlpump是多線程備份,但只能到表級別,單表備份還是單線程
• mysqldump備份時,有個默認隊列（default）,隊列下開N個線程去備份數據庫/數據庫中的表
• 支持開多個隊列(對應不同庫/表),然后每個隊列設置不同線程,進行備份

1.2 優缺點

優點：

• 官方工具,聽著牛逼

缺點：

• 只能并行到表級別,如果表特別大,開多線程和單線程是一樣的,并行度不如mydumper
• 無法獲取當前備份對應的binlog位置
• MySQL5.7.11之前的版本不要使用,并行導出和single-transaction是互斥的

1.3 重要參數

--default-parallelism 指定線程數,默認開2個線程進行并發備份--parallel-schemas 指定哪些數據庫進行并發備份--set-gtid-purged=OFF ? 5.7.18后加入的參數,

Ⅱ、演示一手

[root@VM_0_5_centos ~]# mysqlpump --single-transaction --set-gtid-purged=OFF --parallel-schemas=2:employees --parallel-schemas=4:dbt3 -B employees dbt3 > /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新開一個會話看下情況 (root@172.16.0.10) [(none)]> show processlist; +--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+ | 138199 | root | 172.16.0.5:39238 | NULL | Query | 0 | starting | show processlist | | 138267 | root | 172.16.0.5:39776 | NULL | Sleep | 2 | | NULL | | 138268 | root | 172.16.0.5:39778 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`dept_no`,`from_date`,`to_date` FROM `employees`.`dept_emp` | | 138269 | root | 172.16.0.5: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.0.5: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.0.5: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.0.5: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.0.5: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.0.5:39790 | NULL | Sleep | 2 | | NULL | | 138275 | root | 172.16.0.5:39792 | NULL | Sleep | 1 | | NULL | +--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec)可以看到138268和138269在備份employees庫,138270,138271,138272,138273在備份dbt3,這里沒打印全,不過這是真的,不吹牛逼

Ⅲ、看下備份過程吧

session1: (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)session2: [root@VM_0_5_centos ~]# mysqlpump --single-transaction abc > /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 | +-----------+------------------------------------------------------------------+ 1.線程7 進行 FLUSH TABLES WITH READ LOCK 。對表加一個讀鎖 2.線程7、8、9分別開啟一個事務（RR隔離級別）去備份數據,由于之前鎖表了,所以這三個線程備份出的數據是具有一致性的 3.線程7 解鎖 UNLOCK TABLE 整個過程沒有獲取二進制位置點

Ⅳ、compress-output

mysqlpump支持壓縮輸出,支持LZ4和ZLIB（ZLIB壓縮比相對較高,但是速度較慢）

[root@VM_0_5_centos tmp]# mysqlpump --single-transaction --compress-output=lz4 abc > /tmp/backup_abc.sql Dump completed in 511 milliseconds

Ⅴ、備份恢復

未壓縮的備份

mysql < backup.sql

壓縮過的備份

先解壓 zlib_decompress lz4_decompress lz4_decompress backup_abc.sql backup.sql 再導入 mysql < backup.sql

可以看出來,這個導入是單線程

tips：

mysqlpump備份的數據恢復時會先插入數據,再建索引,而mysqldump備份的數據恢復是在建立表的時候就把索引加上了,所以前者備份的數據恢復時速度要快一點

總結：后續關注,現在用不上

轉載于:https://www.cnblogs.com/---wunian/p/8992858.html

總結

以上是生活随笔為你收集整理的了解mysqlpump工具的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。