Use of MySQL official export tool mysqlpump

Introduction
Instructions
Actual experience
Pros and Cons
To sum up

Introduction

mysqlpump is a derivative of mysqldump. It also refers to the idea of mydumper and supports parallel data export. Therefore, the efficiency of exporting data is much higher than that of mysqldump.

Instructions

Most of the parameters of mysqlpump are the same as those of mysqldump, and the overall usage is not much different from mysqldump. Here are some of the more important and commonly used parameters in mysqlpump.

parameter	illustrate
--default-parallelism=#	Set the concurrency of parallel export, which conflicts with single-transaction
--single-transaction	Create a single transaction to export all tables
--exclude-databases=name	Exclude certain libraries when exporting. Multiple libraries are separated by commas.
--exclude-tables=name	Exclude certain tables when exporting. Multiple tables are separated by commas.
--include-databases=name	Include certain libraries when exporting. Multiple libraries are separated by commas.
--include-tables=name	Include certain tables when exporting. Multiple tables are separated by commas.

Actual experience

Here we will do a simple trial of mysqlpump, select MySQL 5.7 as the target instance, and use both single-transaction and default-parallelism in the parameters to see the effect of this conflict.

The output on the mysqlpump side refers to the following information:

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

You can see that when these two parameters are enabled at the same time, mysqlpump actually still exports tables one by one. The priority of single-transaction is higher than that of default-parallelism.

When removing single-transaction and testing again, you will find an interesting phenomenon. Observing MySQL processlist, you will get the following results:

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>

It can be clearly seen that the "parallel export" of mysqlpump is actually only based on table-level parallel export. When there is a single large table, the export time will be seriously affected, and there will be a short board effect.

Additional question: If default-parallelism and single-transaction conflict, will data consistency be unable to be confirmed during parallel export?

Practice makes perfect. Open general_log and take a look at the export operation:

2021-05-12T11:54:09.033215Z 75 Connect [email protected] on using SSL/TLS
2021-05-12T11:54:09.075347Z 75 Query FLUSH TABLES WITH READ LOCK //Start locking tables 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 [email protected] 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 [email protected] 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 [email protected] 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 //End lock table 2021-05-12T11:54:10.026406Z 75 Query SHOW WARNINGS

It can be seen that before the parallel export, one thread adds a global read lock, and then unlocks the table only after all concurrent threads open the transaction. Therefore, the data is consistent during parallel export.

Pros and Cons

advantage:
- Backs up databases and objects in them in parallel, which is more efficient than mysqldump.
- Better control over backups of databases and database objects (tables, stored procedures, user accounts).
- Backup progress visualization.
shortcoming:
- It can only be parallelized to the table level. If a table has a particularly large amount of data, there will be a very serious short board effect.
- The exported data is saved in a file, and the import is still single-threaded, which is less efficient.
- Unable to obtain the binlog position corresponding to the current backup.

To sum up

Although mysqlpump still has many shortcomings, it has made great progress compared to the original mysqldump. The release of this tool also shows that Oracle has finally begun to pay attention to MySQL's ecological tools. We look forward to the official providing more and better ecological tools.

The above is the detailed content of using MySQL official export tool mysqlpump. For more information about the use of mysqlpump, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

mysqldump parameters you may not know
Implementation of MySQL5.7 mysqldump backup and recovery
Detailed explanation of the idea of using mysqldump+expect+crontab to implement mysql periodic cold backup in linux
Summary of MySql import and export methods using mysqldump
Detailed explanation of the use of MySQL mysqldump
How to use mysqldump for full and point-in-time backups
Docker uses the mysqldump command to back up and export mysql data in the project
MySQL data migration using MySQLdump command
PHP scheduled backup MySQL and mysqldump syntax parameters detailed
Detailed explanation of how to use the mysql backup script mysqldump
Detailed explanation of Linux mysqldump exporting database, data, and table structure
Detailed discussion on the issue of mysqldump data export

<<: Explore VMware ESXI CLI common commands

>>: How to use Element in React project