Use of MySQL official export tool mysqlpump

Use of MySQL official export tool mysqlpump

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

Recommend

Tutorial on installing mysql-8.0.18-winx64 under Windows (with pictures and text)

1. Download the installation package The installa...

MySQL replication table details and example code

MySQL replication table detailed explanation If w...

The easiest way to make a program run automatically at startup in Linux

I collected a lot of them, but all ended in failu...

Summary of the minesweeping project implemented in JS

This article shares the summary of the JS mineswe...

Some data processing methods that may be commonly used in JS

Table of contents DOM processing Arrays method Su...

How to implement a password strength detector in react

Table of contents Preface use Component Writing D...

JavaScript Function Currying

Table of contents 1 What is function currying? 2 ...

Summary of Kubernetes's application areas

Kubernetes is the leader in the container orchest...

Tips for using top command in Linux

First, let me introduce the meaning of some field...

Vue+js click arrow to switch pictures

This article example shares the specific code of ...

Batch replace part of the data of a field in Mysql (recommended)

Batch replace part of the data of a field in MYSQ...

A tutorial on how to install, use, and automatically compile TypeScript

1. Introduction to TypeScript The previous articl...

Control the light switch with js

Use js to control the light switch for your refer...

MySQL 8.0.12 installation and environment variable configuration tutorial under win10

Installation, configuration, startup, login and c...

MySQL 5.7.17 winx64 installation and configuration method graphic tutorial

Windows installation mysql-5.7.17-winx64.zip meth...