MySQL官方导出工具mysqlpump的使用
16lz
2021-12-11
简介
mysqlpump 是 mysqldump 的一个衍生，本身也参考了 mydumper 的思路，支持了并行导出数据，因此导出数据的效率比 mysqldump 会高很多。
使用介绍
mysqlpump 的绝大多数参数与 mysqldump 是一样的，整体的使用方法和 mysqldump 没有太多的差异。这里列出一部分 mysqlpump 中比较重要且常用的参数。
| 参数 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.sqlDump progress: 0/1 tables, 250/987400 rowsDump progress: 0/5 tables, 117250/3946600 rowsDump progress: 1/5 tables, 258750/3946600 rowsDump progress: 1/5 tables, 385500/3946600 rowsDump progress: 1/5 tables, 516750/3946600 rowsDump progress: 1/5 tables, 639250/3946600 rowsDump progress: 1/5 tables, 757000/3946600 rowsDump progress: 1/5 tables, 885000/3946600 rowsDump progress: 1/5 tables, 1005750/3946600 rowsDump progress: 1/5 tables, 1114250/3946600 rowsDump progress: 1/5 tables, 1223250/3946600 rowsDump progress: 2/5 tables, 1312500/3946600 rowsDump progress: 2/5 tables, 1430750/3946600 rowsDump progress: 2/5 tables, 1553000/3946600 rowsDump progress: 2/5 tables, 1680250/3946600 rowsDump progress: 2/5 tables, 1809500/3946600 rowsDump progress: 2/5 tables, 1940750/3946600 rowsDump progress: 2/5 tables, 2060000/3946600 rowsDump progress: 2/5 tables, 2175250/3946600 rowsDump progress: 2/5 tables, 2295250/3946600 rowsDump progress: 3/5 tables, 2413500/3946600 rowsDump progress: 3/5 tables, 2554500/3946600 rowsDump progress: 3/5 tables, 2693500/3946600 rowsDump progress: 3/5 tables, 2818750/3946600 rowsDump progress: 3/5 tables, 2941500/3946600 rowsDump progress: 4/5 tables, 3056000/3946600 rowsDump progress: 4/5 tables, 3172750/3946600 rowsDump progress: 4/5 tables, 3280000/3946600 rowsDump progress: 4/5 tables, 3372000/3946600 rowsDump progress: 4/5 tables, 3444750/3946600 rowsDump completed in 126555 milliseconds 去掉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> 额外的疑问：如果default-parallelism和single-transaction有冲突的话，那么并行导出的时候是不是无法确认数据一致性？ 实践出真实，打开 general_log 看一下导出时的操作： 2021-05-12T11:54:09.033215Z 75 Connect root@172.100.10.10 on using SSL/TLS2021-05-12T11:54:09.075347Z 75 Query FLUSH TABLES WITH READ LOCK //开始锁表2021-05-12T11:54:09.103132Z 75 Query SHOW WARNINGS2021-05-12T11:54:09.106382Z 75 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-05-12T11:54:09.106553Z 75 Query SHOW WARNINGS2021-05-12T11:54:09.106640Z 75 Query START TRANSACTION WITH CONSISTENT SNAPSHOT2021-05-12T11:54:09.108115Z 75 Query SHOW WARNINGS2021-05-12T11:54:09.127277Z 76 Connect root@172.100.10.10 on using SSL/TLS2021-05-12T11:54:09.127452Z 76 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-05-12T11:54:09.127590Z 76 Query SHOW WARNINGS2021-05-12T11:54:09.127680Z 76 Query START TRANSACTION WITH CONSISTENT SNAPSHOT2021-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/TLS2021-05-12T11:54:10.018944Z 90 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-05-12T11:54:10.019047Z 90 Query SHOW WARNINGS2021-05-12T11:54:10.019150Z 90 Query START TRANSACTION WITH CONSISTENT SNAPSHOT2021-05-12T11:54:10.019226Z 90 Query SHOW WARNINGS2021-05-12T11:54:10.025833Z 91 Connect root@172.100.10.10 on using SSL/TLS2021-05-12T11:54:10.025934Z 91 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-05-12T11:54:10.026048Z 91 Query SHOW WARNINGS2021-05-12T11:54:10.026141Z 91 Query START TRANSACTION WITH CONSISTENT SNAPSHOT2021-05-12T11:54:10.026219Z 91 Query SHOW WARNINGS2021-05-12T11:54:10.026293Z 75 Query UNLOCK TABLES //结束锁表2021-05-12T11:54:10.026406Z 75 Query SHOW WARNINGS 优缺点
总结一下尽管 mysqlpump 还有非常多的不足，但是相比较于原始的 mysqldump 已经有了非常大的进步，从这个工具的发布也可以看出来 Oracle 终于开始重视 MySQL 的生态工具了，期待官方提供更多的更优秀的生态工具。 更多相关文章
随机推荐 |
|---|