Learn about Mysqlpump Tools

Ⅰ, functional analysis 1.1 multi-Threading Introduction

  +-----------+ | mysqlpump |        +-----+-----+                          |                 +-----------------------------------+        |                 |    |         +---v---+ +---v---+ +---v---+ |default|  |         DB1 |  |    DB2 | |         Queue | |         Queue | |    Queue |                 +---+---+         +---+---+         +---+---+        |                 |        | V v v+-------+-------+ +-------+-------+ +-------+-------+|+----+ +----+| |+----+ +--- -+| |+----+   +----+||| thd1|...| thd3| | || thd1|...| thd3| | || thd1|...| thd3| | | +----+   +----+| |+----+   +----+| |+----+   +----+|+---------------+ +---------------+ +---------------+

• Mysqlpump is the official tool of MySQL5.7, used to replace Mysqldump, whose parameters are essentially the same as mysqldump
• Mysqlpump is a multithreaded backup, but only at the table level, single-table backup or single-threaded
• Mysqldump Backup, there is a default queue, the queue is open n threads to back up the database/database table
• Support to open multiple queues (corresponding to different libraries/tables), and then set up different threads for each queue for backup

1.2 Advantages and Disadvantages

Advantages:

• Official tools, listen up.

Disadvantages:

• Only parallel to the table level, if the table is particularly large, open multi-threaded and single-threaded is the same, the degree of parallelism is inferior to mydumper
• Unable to get the Binlog location for the current backup
• Do not use versions prior to MySQL5.7.11, parallel export and single-transaction are mutually exclusive

1.3 Important parameters

--default-parallelism   指定线程数,默认开2个线程进行并发备份--parallel-schemas  指定哪些数据库进行并发备份--set-gtid-purged=OFF   5.7.18后加入的参数,

Ⅱ, Presentation

[[Email protected]_0_5_centos ~]# mysqlpump--single-transaction--set-gtid-purged=off--parallel-schemas=2: Employees--parallel-schemas=4:dbt3-b Employees DBT3 >/tmp/backup.sqlmysqlpump: [Warning] Using a password on the COM Mand line interface can is insecure. Dump progress:1/5 tables, 0/7559817 rowsdump progress:3/15 tables, 286750/12022332 rowsdump progress:3/15 tables, 68675 0/12022332 rowsdump progress:3/15 tables, 1042250/12022332 rows ... Dump completed in 43732 milliseconds a new session look at the situation ([email protected]) [(none)]> show processlist;+--------+------ +------------------+------+---------+------+-------------------+----------------------------------------------- -------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+--- ---------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+| 138199 | Root | 172.16.0.5:39238 | NULL |    Query | 0 | Starting | Show Processlist | | 138267 | Root | 172.16.0.5:39776 | NULL |    Sleep |                   2 | | NULL | | 138268 | Root | 172.16.0.5:39778 | NULL |    Query | 2 | Sending to Client | SELECT Sql_no_cache ' emp_no ', ' dept_no ', ' from_date ', ' to_date ' from ' employees '. ' Dept_emp ' | | 138269 | Root | 172.16.0.5:39780 | NULL |    Query | 2 | Sending to Client | SELECT Sql_no_cache ' emp_no ', ' birth_date ', ' first_name ', ' last_name ', ' gender ', ' hire_date ' from ' Emplo | | 138270 | Root | 172.16.0.5:39782 | NULL |    Query | 2 | Sending to Client | SELECT Sql_no_cache ' O_orderkey ', ' o_custkey ', ' o_orderstatus ', ' o_totalprice ', 'O_orderdate ', ' O_ORDERPR | | 138271 | Root | 172.16.0.5:39784 | NULL |    Query | 2 | Sending to Client | SELECT Sql_no_cache ' P_partkey ', ' p_name ', ' p_mfgr ', ' p_brand ', ' p_type ', ' p_size ', ' p_container ', ' P_retai | | 138272 | Root | 172.16.0.5:39786 | NULL |    Query | 2 | Sending Data | SELECT Sql_no_cache ' L_orderkey ', ' l_partkey ', ' l_suppkey ', ' l_linenumber ', ' l_quantity ', ' l_extendedpric | | 138273 | Root | 172.16.0.5:39788 | NULL |    Query | 2 | Sending to Client | SELECT Sql_no_cache ' C_custkey ', ' c_name ', ' c_address ', ' c_nationkey ', ' c_phone ', ' c_acctbal ', ' C_mktsegme | | 138274 | Root | 172.16.0.5:39790 | NULL |    Sleep |                   2 | | NULL | | 138275 | Root | 172.16.0.5:39792 | NULL |    Sleep |                   1 | | NULL |+--------+------+--- ---------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------- -----------+10 rows in Set (0.00 sec) can see 138268 and 138269 in Backup Employees Library, 138270,138271,138272,138273 in Backup dbt3, here not printing full, But it's true, don't blow it.

Ⅲ, take a look at the backup process.

Session1: ([email protected]) [(none)]> truncate mysql.general_log; Query OK, 0 rows affected (0.10 sec) ([email protected]) [(none)]> set global log_output = ' table '; Query OK, 0 rows Affected (0.00 sec) ([email protected]) [(none)]> set global general_log = 1; Query OK, 0 rows affected (0.03 sec) Session2:[[email protected]_0_5_centos ~]# mysqlpump--single-transaction ABC &G T /tmp/backup.sqldump completed in 592 milliseconds ([email protected]) [(None)]> Select Thread_id,left (argument , mysql.general_log order by event_time; omit partial output: +-----------+-----------------------------------------------         -------------------+| 7 |         [email protected] on using Socket | | 7 |         FLUSH TABLES with READ LOCK | | 7 |         SHOW WARNINGS | | 7 |         SET SESSION TRANSACTION Isolation level repeatable READ | | 7 |SHOW WARNINGS | | 7 |         START TRANSACTION with consistent SNAPSHOT | | 7 |         SHOW WARNINGS | | 8 |         [email protected] on using Socket | | 8 |         SET SESSION TRANSACTION Isolation level repeatable READ | | 8 |         SHOW WARNINGS | | 8 |         START TRANSACTION with consistent SNAPSHOT | | 8 |         SHOW WARNINGS | | 9 |         [email protected] on using Socket | | 9 |         SET SESSION TRANSACTION Isolation level repeatable READ | | 9 |         SHOW WARNINGS | | 9 |         START TRANSACTION with consistent SNAPSHOT | | 9 |           SHOW WARNINGS                                         || 7 |         UNLOCK TABLES | | 7 |         SHOW WARNINGS | | 9 |         SET sql_quote_show_create= 1 | | 9 |         SHOW WARNINGS | | 9 |         SET time_zone= ' +00:00 ' | | 8 |         SET sql_quote_show_create= 1 | | 8 |         SHOW WARNINGS | | 8 |         SET time_zone= ' +00:00 ' | | 3 | Set global General_log = 0 |+-----------+------------------------------------------- -----------------------+1. Thread 7 is FLUSH TABLES with READ LOCK. Add a read lock to the table 2. Threads 7, 8, 9 open one thing (RR isolation level) to back up the data, because the previous lock table, so the data backed up by the three threads is consistent 3. Thread 7 unlocking UNLOCK table The entire process does not get a binary location point

Ⅳ, Compress-output

Mysqlpump supports compression output, supports LZ4 and zlib (zlib compression ratio is relatively high, but slower)

[[email protected]_0_5_centos tmp]# mysqlpump --single-transaction --compress-output=lz4 abc > /tmp/backup_abc.sqlDump completed in 511 milliseconds

Ⅴ, Backup Recovery

Non-compressed backup

mysql < backup.sql

Compressed backup

先解压zlib_decompresslz4_decompresslz4_decompress backup_abc.sql backup.sql再导入mysql < backup.sql

As you can see, this import is a single thread

Tips

Mysqlpump backup Data Recovery will be inserted first, and then indexed, and mysqldump backup data recovery is when the table is created when the index is added, so the former backup of the data recovery speed faster

Summary: Follow-up attention, now not used

Learn about Mysqlpump Tools