MySQL mysqlpump parallel backup

MySQL mysqlpump parallel backup

Preface:

MySQL 5.7 is a new Database Export method. The official calligraphy is to replace mysqldump with mysqlpump;

Mysqldump: the export process is very slow, but it is a single thread. [However, you can also use the system layer to concurrently implement mysqldump parallel backup to implement mysqlpump. However, the order of the exported SQL table structure is still different ];

Mysqlpump is multi-threaded. In the Community version, mydumper is multi-threaded. Mysqlpump may be faulty and should be used after 5.7.11 [because MySQL 5.7.11 solves the consistency backup problem]

The syntax of mysqlpump is highly compatible with mysqldump. It supports concurrent export based on databases and tables, which significantly improves the speed of mysqldump.

The architecture of the parallel export function of mysqlpump is queue + thread. multiple queues are allowed. Each queue has multiple threads, and one queue can be bound to one or more databases.

However, the export of each table can only be a single thread, which is different from the mydumper tool, because mydumper supports batch export of multiple threads in a table in the form of chunk;

This improves the export speed when the primary key is random. Shows the architecture of mysqlpump:

* ***** Experiment results

* ***** Mysqldump backup time is 80 minutes

* ***** Mysqlpump parallel 4 backup time is 40 minutes [because the largest table in the database is exported for 40 minutes separately, if the table sizes in the database are average, better performance .... (export mydumper in parallel from a single large table... still Lab )]

Key parameters

1. Supports table-based multi-threaded Export (-- default-parallelism; default value: 2; -- parallel-schemas; controls the parallel export Library)

2. a progress bar (-- watch-progress, enabled by default) is displayed during export)

3. Support for direct compression and export (parameters -- compress-output, and ZLIB and LZ4)

[Root @ localhost ~] # Mysqlpump-uroot-p123-A -- parallel-schemas = 4: db7 -- parallel-schemas = 1: db6 -- skip-watch-progress>/data/all. SQL

View parallelism in the background
Mysql> show processlist;
+ ---- + ------------- + ----------- + ------ + --------- + ------ + ------------------------------------------------- +
| Id | User | Host | db | Command | Time | State Info |
+ ---- + ------------- + ----------- + ------ + --------- + ------ + ------------------------------------------------- +
| 43 | root | localhost | NULL | Query | 10 | Sending data | SELECT SQL _NO_CACHE * FROM 'db7'. 'account _ bill_pool '|
| 44 | root | localhost | NULL | Query | 9 | Sending to client | SELECT SQL _NO_CACHE * FROM 'db7'. 'bill _ check_diff_dtl '|
| 45 | root | localhost | NULL | Query | 0 | Sending to client | SELECT SQL _NO_CACHE * FROM 'db7'. 'bill _ checkstk_inventory '|
| 46 | root | localhost | NULL | Query | 3 | Sending data | SELECT SQL _NO_CACHE * FROM 'db7'. 'bill _ checkstk_in_transit '|
| 72 | root | localhost | NULL | Query | 6 | Sending data | SELECT SQL _NO_CACHE * FROM 'db6'. 'account _ bill_pool '|
+ ---- + ------------- + ----------- + ------ + --------- + ------ + ------------------------------------------ +
Mysql>

View exported SQL content
Create table 'mysql'. 'Help _ keyword '(
'Help _ keyword_id 'int (10) unsigned not null,
'Name' char (64) not null,
Primary key ('help _ keyword_id ')
) ENGINE = InnoDB default charset = utf8 STATS_PERSISTENT = 0 COMMENT = 'help keyword ';
Insert into 'mysql'. 'Help _ keyword' VALUES (9, "MASTER_SSL_CA"), (367, "MASTER_SSL_CERT ");
Alter table 'mysql'. 'Help _ keyword' add unique key 'name' ('name ');
 

 

** It is better to create an index after creating a table and then inserting data. (mysqldump creates an index when creating a table and then inserts data. This is not as efficient as mysqlpump)

Extension:
Versions later than 5.7.9 -- default-parallelism multithreading can be used with -- single-transaction.
[Root @ localhost data] # mysqlpump-uroot-p123-B mysql -- single-transaction> aa. SQL
Mysqlpump: [ERROR] (1) Usage of -- single-transaction is mutually exclusive with parallelism.
Dump process encountered error and will not continue.
[Root @ localhost data] #
[Root @ localhost data] # mysqlpump-uroot-p123-B mysql -- single-transaction -- default-parallelism = 0> aa. SQL
Dump progress: 0/1 tables, 2/2 rows
Dump completed in 623 milliseconds
[Root @ localhost data] #

Detailed parameter introduction mysqlpump -- help

This article permanently updates the link address: