|
參數(shù) |
說(shuō)明 |
|---|---|
|
--default-parallelism=# |
設(shè)置并行導(dǎo)出的并發(fā)度，與 single-transaction 沖突 |
|
--single-transaction |
創(chuàng)建一個(gè)單獨(dú)的事務(wù)來(lái)導(dǎo)出所有的表 |
|
--exclude-databases=name |
導(dǎo)出時(shí)排除掉某些庫(kù)，多個(gè)庫(kù)以逗號(hào)分隔 |
|
--exclude-tables=name |
導(dǎo)出時(shí)排除掉某些表，多個(gè)表以逗號(hào)分隔 |
|
--include-databases=name |
導(dǎo)出時(shí)包含某些庫(kù)，多個(gè)庫(kù)以逗號(hào)分隔 |
|
--include-tables=name |
導(dǎo)出時(shí)包含某些表，多個(gè)表以逗號(hào)分隔 |
這里對(duì) mysqlpump 做一次簡(jiǎn)單的試用，目標(biāo)實(shí)例選擇 MySQL 5.7，參數(shù)中同時(shí)采用了single-transaction和default-parallelism，試試看這個(gè)沖突的效果。
mysqlpump 側(cè)的輸出參考如下信息：
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
可以看到當(dāng)這兩個(gè)參數(shù)同時(shí)啟用的時(shí)候，mysqlpump 實(shí)際上還是在一個(gè)一個(gè)表的導(dǎo)出。single-transaction的優(yōu)先級(jí)會(huì)高于default-parallelism。
去掉single-transaction再進(jìn)行測(cè)試的時(shí)候，會(huì)發(fā)現(xiàn)一個(gè)比較有意思的現(xiàn)象，觀察 MySQL 的 processlist，會(huì)有如下結(jié)果：
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>
可以很明顯的看出來(lái)，mysqlpump 的“并行導(dǎo)出”實(shí)際上只是基于表級(jí)別的并行導(dǎo)出，當(dāng)存在單個(gè)大表的時(shí)候，導(dǎo)出的時(shí)間會(huì)被嚴(yán)重的影響，存在短板效應(yīng)。
額外的疑問(wèn)：如果default-parallelism和single-transaction有沖突的話，那么并行導(dǎo)出的時(shí)候是不是無(wú)法確認(rèn)數(shù)據(jù)一致性？
實(shí)踐出真實(shí)，打開(kāi) general_log 看一下導(dǎo)出時(shí)的操作：
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 //開(kāi)始鎖表 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 //結(jié)束鎖表 2021-05-12T11:54:10.026406Z 75 Query SHOW WARNINGS
可以看到并行導(dǎo)出之前，有一個(gè)線程加上了全局讀鎖，然后等所有的并發(fā)線程打開(kāi)事務(wù)之后才解鎖了表，因此并行導(dǎo)出的時(shí)候也是數(shù)據(jù)一致的。
盡管 mysqlpump 還有非常多的不足，但是相比較于原始的 mysqldump 已經(jīng)有了非常大的進(jìn)步，從這個(gè)工具的發(fā)布也可以看出來(lái) Oracle 終于開(kāi)始重視 MySQL 的生態(tài)工具了，期待官方提供更多的更優(yōu)秀的生態(tài)工具。
以上就是MySQL官方導(dǎo)出工具mysqlpump的使用的詳細(xì)內(nèi)容，更多關(guān)于mysqlpump的使用的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
標(biāo)簽：無(wú)錫 西寧 龍巖 麗水 徐州 迪慶 南充 自貢
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL官方導(dǎo)出工具mysqlpump的使用》，本文關(guān)鍵詞 MySQL,官方,導(dǎo)出,工具,mysqlpump,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
