mysql datapump_了解mysqlpump工具

Ⅰ、功能分析

1.1 多线程介绍

mysqlpump是MySQL5.7的官方工具,用于取代mysqldump,其参数与mysqldump基本一样

mysqlpump是多线程备份,但只能到表级别,单表备份还是单线程

mysqldump备份时,有个默认队列(default),队列下开N个线程去备份数据库/数据库中的表

支持开多个队列(对应不同库/表),然后每个队列设置不同线程,进行备份

1.2 优缺点

优点：

官方工具,听着牛逼

缺点：

只能并行到表级别,如果表特别大,开多线程和单线程是一样的,并行度不如mydumper

无法获取当前备份对应的binlog位置

MySQL5.7.11之前的版本不要使用,并行导出和single-transaction是互斥的

1.3 重要参数

--default-parallelism指定线程数,默认开2个线程进行并发备份

--parallel-schemas指定哪些数据库进行并发备份

--set-gtid-purged=OFF   5.7.18后加入的参数,

Ⅱ、演示一手

[root@VM_0_5_centos ~]# mysqlpump --single-transaction --set-gtid-purged=OFF --parallel-schemas=2:employees --parallel-schemas=4:dbt3 -B employees dbt3 > /tmp/backup.sql

mysqlpump: [Warning] Using a password on the command line interface can be insecure.

Dump progress: 1/5 tables, 0/7559817 rows

Dump progress: 3/15 tables, 286750/12022332 rows

Dump progress: 3/15 tables, 686750/12022332 rows

Dump progress: 3/15 tables, 1042250/12022332 rows

...

Dump completed in 43732 milliseconds

新开一个会话看下情况

(root@172.16.0.10) [(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)

可以看到138268和138269在备份employees库,138270,138271,138272,138273在备份dbt3,这里没打印全,不过这是真的,不吹牛逼

Ⅲ、看下备份过程吧

session1:

(root@localhost) [(none)]> truncate mysql.general_log;

Query OK, 0 rows affected (0.10 sec)

(root@localhost) [(none)]> set global log_output = 'table';

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> set global general_log = 1;

Query OK, 0 rows affected (0.03 sec)

session2:

[root@VM_0_5_centos ~]# mysqlpump --single-transaction abc > /tmp/backup.sql

Dump completed in 592 milliseconds

(root@localhost) [(none)]> select thread_id,left(argument, 64) from mysql.general_log order by event_time;

省略部分输出：

+-----------+------------------------------------------------------------------+

| 7 | root@localhost 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 | root@localhost on using Socket |

| 8 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |

| 8 | SHOW WARNINGS |

| 8 | START TRANSACTION WITH CONSISTENT SNAPSHOT |

| 8 | SHOW WARNINGS |

| 9 | root@localhost 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.线程7 进行 FLUSH TABLES WITH READ LOCK 。对表加一个读锁

2.线程7、8、9分别开启一个事务(RR隔离级别)去备份数据,由于之前锁表了,所以这三个线程备份出的数据是具有一致性的

3.线程7 解锁 UNLOCK TABLE

整个过程没有获取二进制位置点

Ⅳ、compress-output

mysqlpump支持压缩输出,支持LZ4和ZLIB(ZLIB压缩比相对较高,但是速度较慢)

[root@VM_0_5_centos tmp]# mysqlpump --single-transaction --compress-output=lz4 abc > /tmp/backup_abc.sql

Dump completed in 511 milliseconds

Ⅴ、备份恢复

未压缩的备份

mysql < backup.sql

压缩过的备份

先解压

zlib_decompress

lz4_decompress

lz4_decompress backup_abc.sql backup.sql

再导入

mysql < backup.sql

可以看出来,这个导入是单线程

tips：

mysqlpump备份的数据恢复时会先插入数据,再建索引,而mysqldump备份的数据恢复是在建立表的时候就把索引加上了,所以前者备份的数据恢复时速度要快一点

总结：后续关注,现在用不上