|
參數 |
說明 |
|---|---|
|
--default-parallelism=# |
設置并行導出的并發度，與 single-transaction 沖突 |
|
--single-transaction |
創建一個單獨的事務來導出所有的表 |
|
--exclude-databases=name |
導出時排除掉某些庫，多個庫以逗號分隔 |
|
--exclude-tables=name |
導出時排除掉某些表，多個表以逗號分隔 |
|
--include-databases=name |
導出時包含某些庫，多個庫以逗號分隔 |
|
--include-tables=name |
導出時包含某些表，多個表以逗號分隔 |
這里對 mysqlpump 做一次簡單的試用，目標實例選擇 MySQL 5.7，參數中同時采用了single-transaction和default-parallelism，試試看這個沖突的效果。
mysqlpump 側的輸出參考如下信息：
root@VM-64-10-debian:~# mysqlpump -h172.100.10.10 -uroot -p --single-transaction --default-parallelism=16 --set-gtid-purged=OFF -B sbtest > sbtest.sql Dump progress: 0/1 tables, 250/987400 rows Dump progress: 0/5 tables, 117250/3946600 rows Dump progress: 1/5 tables, 258750/3946600 rows Dump progress: 1/5 tables, 385500/3946600 rows Dump progress: 1/5 tables, 516750/3946600 rows Dump progress: 1/5 tables, 639250/3946600 rows Dump progress: 1/5 tables, 757000/3946600 rows Dump progress: 1/5 tables, 885000/3946600 rows Dump progress: 1/5 tables, 1005750/3946600 rows Dump progress: 1/5 tables, 1114250/3946600 rows Dump progress: 1/5 tables, 1223250/3946600 rows Dump progress: 2/5 tables, 1312500/3946600 rows Dump progress: 2/5 tables, 1430750/3946600 rows Dump progress: 2/5 tables, 1553000/3946600 rows Dump progress: 2/5 tables, 1680250/3946600 rows Dump progress: 2/5 tables, 1809500/3946600 rows Dump progress: 2/5 tables, 1940750/3946600 rows Dump progress: 2/5 tables, 2060000/3946600 rows Dump progress: 2/5 tables, 2175250/3946600 rows Dump progress: 2/5 tables, 2295250/3946600 rows Dump progress: 3/5 tables, 2413500/3946600 rows Dump progress: 3/5 tables, 2554500/3946600 rows Dump progress: 3/5 tables, 2693500/3946600 rows Dump progress: 3/5 tables, 2818750/3946600 rows Dump progress: 3/5 tables, 2941500/3946600 rows Dump progress: 4/5 tables, 3056000/3946600 rows Dump progress: 4/5 tables, 3172750/3946600 rows Dump progress: 4/5 tables, 3280000/3946600 rows Dump progress: 4/5 tables, 3372000/3946600 rows Dump progress: 4/5 tables, 3444750/3946600 rows Dump completed in 126555 milliseconds
可以看到當這兩個參數同時啟用的時候，mysqlpump 實際上還是在一個一個表的導出。single-transaction的優先級會高于default-parallelism。
去掉single-transaction再進行測試的時候，會發現一個比較有意思的現象，觀察 MySQL 的 processlist，會有如下結果：
mysql> show processlist; +---------+------+--------------------+------+---------+------+-------------------+----------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+------+--------------------+------+---------+------+-------------------+----------------------------------------------------+ | 2763496 | root | 172.100.10.10:49086 | NULL | Query | 0 | starting | show processlist | | 2763585 | root | 172.100.10.10:49192 | NULL | Sleep | 126 | | NULL | | 2763586 | root | 172.100.10.10:49194 | NULL | Sleep | 126 | | NULL | | 2763587 | root |172.100.10.10:49196 | NULL | Sleep | 126 | | NULL | | 2763588 | root | 172.100.10.10:49198 | NULL | Sleep | 126 | | NULL | | 2763589 | root | 172.100.10.10:49200 | NULL | Sleep | 126 | | NULL | | 2763590 | root | 172.100.10.10:49202 | NULL | Sleep | 126 | | NULL | | 2763591 | root | 172.100.10.10:49204 | NULL | Sleep | 126 | | NULL | | 2763592 | root | 172.100.10.10:49206 | NULL | Sleep | 126 | | NULL | | 2763593 | root | 172.100.10.10:49208 | NULL | Sleep | 126 | | NULL | | 2763594 | root | 172.100.10.10:49210 | NULL | Sleep | 126 | | NULL | | 2763595 | root | 172.100.10.10:49212 | NULL | Query | 125 | Sending to client | SELECT `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest5` | | 2763596 | root | 172.100.10.10:49214 | NULL | Query | 125 | Sending to client | SELECT `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest4` | | 2763597 | root | 172.100.10.10:49216 | NULL | Query | 125 | Sending to client | SELECT `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest3` | | 2763598 | root | 172.100.10.10:49218 | NULL | Query | 125 | Sending to client | SELECT `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest2` | | 2763599 | root | 172.100.10.10:49220 | NULL | Query | 125 | Sending to client | SELECT `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest1` | | 2763600 | root | 172.100.10.10:49222 | NULL | Sleep | 125 | | NULL | | 2763601 | root | 172.100.10.10:49224 | NULL | Sleep | 125 | | NULL | +---------+------+--------------------+------+---------+------+-------------------+----------------------------------------------------+ 18 rows in set (0.00 sec) mysql>
可以很明顯的看出來，mysqlpump 的“并行導出”實際上只是基于表級別的并行導出，當存在單個大表的時候，導出的時間會被嚴重的影響，存在短板效應。
額外的疑問：如果default-parallelism和single-transaction有沖突的話，那么并行導出的時候是不是無法確認數據一致性？
實踐出真實，打開 general_log 看一下導出時的操作：
2021-05-12T11:54:09.033215Z 75 Connect root@172.100.10.10 on using SSL/TLS 2021-05-12T11:54:09.075347Z 75 Query FLUSH TABLES WITH READ LOCK //開始鎖表 2021-05-12T11:54:09.103132Z 75 Query SHOW WARNINGS 2021-05-12T11:54:09.106382Z 75 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2021-05-12T11:54:09.106553Z 75 Query SHOW WARNINGS 2021-05-12T11:54:09.106640Z 75 Query START TRANSACTION WITH CONSISTENT SNAPSHOT 2021-05-12T11:54:09.108115Z 75 Query SHOW WARNINGS 2021-05-12T11:54:09.127277Z 76 Connect root@172.100.10.10 on using SSL/TLS 2021-05-12T11:54:09.127452Z 76 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2021-05-12T11:54:09.127590Z 76 Query SHOW WARNINGS 2021-05-12T11:54:09.127680Z 76 Query START TRANSACTION WITH CONSISTENT SNAPSHOT 2021-05-12T11:54:09.127790Z 76 Query SHOW WARNINGS ...... 2021-05-12T11:54:10.018813Z 90 Connect root@172.100.10.10 on using SSL/TLS 2021-05-12T11:54:10.018944Z 90 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2021-05-12T11:54:10.019047Z 90 Query SHOW WARNINGS 2021-05-12T11:54:10.019150Z 90 Query START TRANSACTION WITH CONSISTENT SNAPSHOT 2021-05-12T11:54:10.019226Z 90 Query SHOW WARNINGS 2021-05-12T11:54:10.025833Z 91 Connect root@172.100.10.10 on using SSL/TLS 2021-05-12T11:54:10.025934Z 91 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2021-05-12T11:54:10.026048Z 91 Query SHOW WARNINGS 2021-05-12T11:54:10.026141Z 91 Query START TRANSACTION WITH CONSISTENT SNAPSHOT 2021-05-12T11:54:10.026219Z 91 Query SHOW WARNINGS 2021-05-12T11:54:10.026293Z 75 Query UNLOCK TABLES //結束鎖表 2021-05-12T11:54:10.026406Z 75 Query SHOW WARNINGS
可以看到并行導出之前，有一個線程加上了全局讀鎖，然后等所有的并發線程打開事務之后才解鎖了表，因此并行導出的時候也是數據一致的。
盡管 mysqlpump 還有非常多的不足，但是相比較于原始的 mysqldump 已經有了非常大的進步，從這個工具的發布也可以看出來 Oracle 終于開始重視 MySQL 的生態工具了，期待官方提供更多的更優秀的生態工具。
以上就是MySQL官方導出工具mysqlpump的使用的詳細內容，更多關于mysqlpump的使用的資料請關注腳本之家其它相關文章！
