MySQL5.7新备份⼯具mysqlpump使⽤说明-运维⼩结
之前详细介绍了使⽤，下⾯说下MySQL5.7之后新添加的备份⼯具mysqlpump。mysqlpump是mysqldump的⼀个衍⽣，mysqldump备份功能这⾥就不多说了，现在看看mysqlpump到底有了哪些提升，详细可以查看。mysqlpump和mysqldump⼀样，属于逻辑备份，备份以SQL形式的⽂本保存。逻辑备份相对物理备份好处是不关⼼log的⼤⼩，直接备份数据即可。
Mysqlpump主要特点
- 并⾏备份数据库和数据库中的对象的，加快备份过程。
- 更好的控制数据库和数据库对象（表，存储过程，⽤户帐户）的备份。
- 备份⽤户账号作为帐户管理语句（CREATE USER，GRANT），⽽不是直接插⼊到MySQL的系统数据库。
- 备份出来直接⽣成压缩后的备份⽂件。
- 备份进度指⽰（估计值）。
-
重新加载（还原）备份⽂件，先建表后插⼊数据最后建⽴索引，减少了索引维护开销，加快了还原速度。
- 备份可以排除或则指定数据库。
Mysqlpump缺点
- 只能并⾏到表级别，如果表特别⼤，开多线程和单线程是⼀样的，并⾏度不如mydumper；
- ⽆法获取当前备份对应的binlog位置；
- MySQL5.7.11之前的版本不要使⽤，并⾏导出和single-transaction是互斥的；
参数说明：Mysqlpump绝⼤部分参数使⽤和Mysqldump⼀致，下⾯顺便重温⼀下。注意对于mysqlpump 专有参数会⽤背景⾊标记出来。
1) --add-drop-database: 在建⽴库之前先执⾏删库操作
DROP DATABASE IF EXISTS `...`;
2) --add-drop-table：在建表之前先执⾏删表操作
DROP TABLE IF EXISTS `...`.`...`;
3) --add-drop-user：在CREATE USER语句之前增加DROP USER。注意：这个参数需要和--users⼀起使⽤，否者不⽣效。
DROP USER 'backup'@'172.16.60.%';
4) --add-locks：备份表时，使⽤LOCK TABLES和UNLOCK TABLES。注意：这个参数不⽀持并⾏备份，需要关闭并⾏备份功能：--default-parallelism=0
LOCK TABLES `...`.`...` WRITE;
...
之前下过mysql现在重新下载mysqlUNLOCK TABLES;
5) --all-databases：备份所有库，即 -A。
6) --bind-address：指定通过哪个⽹络接⼝来连接Mysql服务器（⼀台服务器可能有多个IP），防⽌同⼀个⽹卡出去影响业务。
7) --complete-insert：dump出包含所有列的完整insert语句。
8) --compress：压缩客户端和服务器传输的所有的数据，即 -C。
9) --compress-output：默认不压缩输出，⽬前可以使⽤的压缩算法有LZ4和ZLIB
[root@localhost ~]# mysqlpump --compress-output=LZ4 > dump.lz4
[root@localhost ~]# lz4_decompress dump.
[root@localhost ~]# mysqlpump --compress-output=ZLIB > dump.zlib
[root@localhost ~]# zlib_decompress dump.
10) --databases：⼿动指定要备份的库，⽀持多个数据库，⽤空格分隔，即-B。
11) --default-character-set：指定备份的字符集。
12) --default-parallelism：指定并⾏线程数，默认是2，如果设置成0，表⽰不使⽤并⾏备份。注意：每个线程的备份步骤是：先create table但不建⽴⼆级索引（主键会在create table时候建⽴），再写⼊数据，最后建⽴⼆级索引。
13) --defer-table-indexes：延迟创建索引，直到所有数据都加载完之后，再创建索引，默认开启。若关闭则会和mysqldump⼀样：先创建⼀个表和所有索引，再导⼊数据，因为在加载还原数据的时候要维护⼆级索引的开销，导致效率⽐较低。关闭使⽤参数：--skip--defer-table-indexes。
14) --events：备份数据库的事件，默认开启，关闭使⽤--skip-events参数。
15) --exclude-databases：备份排除该参数指定的数据库，多个⽤逗号分隔。类似的还有--exclude-events、--exclude-routines、--exclude-tables、--exclude-triggers、--exclude-users
[root@localhost ~]# mysqlpump --exclude-databases=mysql,sys -p123456 --set-gtid-purged=off >/root/db.sql #备份过滤mysql和sys数据库
[root@localhost ~]# mysqlpump --exclude-tables=rr,tt -p123456 --set-gtid-purged=off > /root/db.sql #备份过滤所有数据库中rr、tt表
[root@localhost ~]# mysqlpump -B test --exclude-tables=tmp_ifulltext,tt -p123456 --set-gtid-purged=off >/root/db.sql #备份过滤test库中的rr、tt表
...
注意：要是只备份数据库的账号，需要添加参数，并且需要过滤掉所有的数据库，如
#备份除dba和backup的所有账号。
[root@localhost ~]# mysqlpump --users --exclude-databases=sys,mysql,db1,db2 --exclude-users=dba,backup -p123456 --set-gtid-purged=off >/root/db.sql
16) --include-databases：指定备份数据库，多个⽤逗号分隔，类似的还有--include-events、--include-routines、--include-tables、--include-triggers、--include-users，⼤致⽅法使⽤同15。
17) --insert-ignore：备份⽤insert ignore语句代替insert语句。
18) --log-error-file：备份出现的warnings和erros信息输出到⼀个指定的⽂件。
19) --max-allowed-packet：备份时⽤于client/server直接通信的最⼤buffer包的⼤⼩。
20) --net-buffer-length：备份时⽤于client/server通信的初始buffer⼤⼩，当创建多⾏插⼊语句的时候，mysqlpump 创建⾏到N个字节长。
21) --no-create-db：备份不写CREATE DATABASE语句。要是备份多个库，需要使⽤参数-B，⽽使⽤-B的时候会出现create database语句，该参数可以屏蔽create database 语句。
22) --no-create-info：备份不写建表语句，即不备份表结构，只备份数据，即 -t。
23) --hex-blob：备份binary字段的时候使⽤⼗六进制计数法，受影响的字段类型有BINARY、VARBINARY、BLOB、BIT。
24) --host：备份指定的数据库地址，即 -h。
25) --parallel-schemas=[N:]db_list：指定并⾏备份的库，多个库⽤逗号分隔，如果指定了N，将使⽤N个线程的地队列，如果N不指定，将由 --default-parallelism才确认N的值，可以设置多个--parallel-schemas
#4个线程备份vs和aa，3个线程备份pt。通过show processlist 可以看到有7个线程。
[root@localhost ~]# mysqlpump --parallel-schemas=4:vs,aa --parallel-schemas=3:pt -p123456 --set-gtid-purged=off > /root/db.sql
#默认2个线程，即2个线程备份vs和abc，2个线程备份pt
[root@localhost ~]# mysqlpump --parallel-schemas=vs,abc --parallel-schemas=pt -p123456 --set-gtid-purged=off > /root/db.sql
#当然要是硬盘IO不允许的话，可以少开⼏个线程和数据库进⾏并⾏备份
26) --password：备份需要的密码。
27) --port：备份数据库的端⼝。
28) --protocol={TCP|SOCKET|PIPE|MEMORY}：指定连接服务器的协议。
29) --replace：备份出来replace into语句。
30) --routines：备份出来包含存储过程和函数，默认开启，需要对 mysql.proc表有查看权限。⽣成的⽂件中会包含CREATE PROCEDURE 和 CREATE FUNCTION语句以⽤于
恢复，关闭则需要⽤--skip-routines参数。
31) --triggers：备份出来包含触发器，默认开启，使⽤--skip-triggers来关闭。
31) --set-charset：备份⽂件⾥写SET NAMES default_character_set 到输出，此参默认开启。 -- skip-set-charset禁⽤此参数，不会在备份⽂件⾥⾯写出
32) --single-transaction：该参数在事务隔离级别设置成Repeatable Read，并在dump之前发送start transaction 语句给服务端。这在使⽤innodb时很有⽤，因为在发出start
transaction时，保证了在不阻塞任何应⽤下的⼀致性状态。对myisam和memory等⾮事务表，还是会改变状态的，当使⽤此参的时候要确保没有其他连接在使⽤ALTER
TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等语句，否则会出现不正确的内容或则失败。--add-locks和此参互斥，在mysql5.7.11之
前，--default-parallelism⼤于1的时候和此参也互斥，必须使⽤--default-parallelism=0。5.7.11之后解决了--single-transaction和--default-parallelism的互斥问题。
33) --skip-definer：忽略那些创建视图和存储过程⽤到的 DEFINER 和 SQL SECURITY 语句，恢复的时候，会使⽤默认值，否则会在还原的时候看到没有DEFINER定义时的账
号⽽报错。
34) --skip-dump-rows：只备份表结构，不备份数据，即-d。注意：mysqldump⽀持--no-data，mysqlpump不⽀持--no-data
35) --socket：对于连接到localhost，Unix使⽤套接字⽂件，在Windows上是命名管道的名称使⽤，即 -S。
36) --ssl：--ssl参数将要被去除，⽤--ssl-mode取代。关于ssl相关的备份。
37) --tz-utc：备份时会在备份⽂件的最前⼏⾏添加SET TIME_ZONE='+00:00'。注意：如果还原的服务器不在同⼀个时区并且还原表中的列有timestamp字段，会导致还原出来的
结果不⼀致。默认开启该参数，⽤ --skip-tz-utc来关闭参数。
38) --user：备份时候的⽤户名，即 -u。
39) --users：备份数据库⽤户，备份的形式是，，只备份数据库账号可以通过如下命令
#过滤掉所有数据库
[root@localhost ~]# mysqlpump --exclude-databases=% --users -p123456 --set-gtid-purged=off >/root/db.sql
40) --watch-progress：定期显⽰进度的完成，包括总数表、⾏和其他对象。该参数默认开启，⽤--skip-watch-progress来关闭。
Mysqlpump的多线程架构图如下
- mysqlpump是MySQL5.7的官⽅⼯具,⽤于取代mysqldump，其参数与mysqldump基本⼀样；
- mysqlpump是多线程备份，但只能到表级别，单表备份还是单线程；
- mysqldump备份时，有个默认队列（default），队列下开N个线程去备份数据库/数据库中的表；
- ⽀持开多个队列(对应不同库/表)，然后每个队列设置不同线程，进⾏备份；
Mysqlpump⽀持基于库和表的并⾏导出，Mysqlpump的并⾏导出功能的架构为：队列+线程，允许有多个队列（--parallel-schemas），每个队列下有多个线程（N），⽽⼀个队
列可以绑定1个或者多个数据库（逗号分隔）。Mysqlpump的备份是基于表并⾏的，对于每张表的导出只能是单个线程的，这⾥会有个限制是如果某个数据库有⼀张表⾮常⼤，
可能⼤部分的时间都是消耗在这个表的备份上⾯，并⾏备份的效果可能就不明显。这⾥可以利⽤Mydumper其是以chunk的⽅式批量导出，即Mydumper⽀持⼀张表多个线程以
chunk的⽅式批量导出。但相对于Mysqldump有很⼤提升。
对⽐测试如下
mysqlpump压缩备份kevin数据库三个并发线程备份，消耗时间：222s
[root@localhost ~]# mysqlpump -uroot -p123456 -h172.16.60.211 --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B kevin > /data/db_backup/kevin_db.sql.lz4
mysqldump备份压缩kevin数据库单个线程备份，消耗时间：900s，gzip的压缩率⽐LZ4的⾼
[root@localhost ~]# mysqldump -uroot -p123456 -h172.16.60.211 --default-character-set=utf8 -P3306 --skip-opt --add-drop-table --create-options --quick --extended-insert --single-transaction -B kevin | gzip > /data/db_backup/ mydumper备份kevin数据库三个并发线程备份，消耗时间：300s，gzip的压缩率⽐LZ4的⾼
[root@localhost ~]# mydumper -u root -p123456 -h 172.16.60.211 -P 3306 -t 3 -c -l 3600 -s 10000000 -B kevin -o /data/db_backup/kevin/
mydumper备份kevin数据库，五个并发线程备份，并且开启对⼀张表多个线程以chunk的⽅式批量导出，-r。消耗时间：180s
[root@localhost ~]# mydumper -u root -p123456 -h 172.16.60.211 -P 3306 -t 5 -c -r 300000 -l 3600 -s 10000000 -B kevin -o /data/db_backup/kevin/
注意：如果是开启了GTID功能的数据库，备份时还需要添加"--set-gtid-purged=off"参数，否则可能会报错！
从上⾯看出，mysqlpump的备份效率是最快的，mydumper次之，mysqldump最差。所以在IO允许的情况下，能⽤多线程就别⽤单线程备份。并且mysqlpump还⽀持多数据库的
并⾏备份，⽽mydumper要么备份⼀个库，要么就备份所有库。可以看出，在mysql数据库备份⽅⾯，mysqlpump⽐mysqldump的测试结果要好。由于实际情况不同，测试给出
的速度提升只是参考。到底开启多少个并⾏备份的线程，这个看磁盘IO的承受能⼒，若该服务器只进⾏备份任务，可以最⼤限制的来利⽤磁盘。
测试中发现mysqlpump和mysqldump对⽐:
- mysqldump默认是不会有建库命令，但是默认会有drop table的命令;
- mysqlpump默认是有建库命令，但是不会有drop table的命令，所以mysqlpump恢复的时候不要直接< file.sql ;
- mysqldump恢复时会先创建表及其所有索引，然后再导⼊数据；mysqlpump恢复时会先创建表，然后再导⼊数据，最后建索引;
- mysqlpump可以指定多线程并发备份，默认是2个；备份时会有进度指⽰，虽然只是估计值，但不会再想mysqldump备份时那么枯燥，看不到过程.
注意：mysqlpump备份的⼏个重要参数
--default-parallelism 指定线程数，默认开2个线程进⾏并发备份
--parallel-schemas 指定哪些数据库进⾏并发备份
--set-gtid-purged=OFF 这个是5.7.18版本后加⼊的参数,
--set-gtid-purged=OFF这个参数很重要，如果备份命令⾥不加上，则备份可能会报错:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed
suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass
--all-databases --triggers --routines --events.
备份演⽰如下
- 备份命令如下
[root@localhost ~]# mysqlpump --single-transaction --set-gtid-purged=OFF --parallel-schemas=2:kev
in --parallel-schemas=4:dbt3 -B kevin dbt3 -p123456 > /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
接着另外打开⼀个终端会话，登录mysql看下情况
(root@172.16.0.10) [(none)]> show processlist;
+--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 138199 | root | 172.16.60.50:39238 | NULL | Query | 0 | starting | show processlist |
| 138267 | root | 172.16.60.50:39776 | NULL | Sleep | 2 | | NULL |
| 138268 | root | 172.16.60.50:39778 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`dept_no`,`from_date`,`to_date` FROM `kevin`.`dept_emp` |
| 138269 | root | 172.16.60.50: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.60.50:39782 | NULL | Query | 2 | Sending to client | SELECT SQL_NO_CA
CHE `o_orderkey`,`o_custkey`,`o_orderstatus`,`o_totalprice`,`o_orderDATE`,`o_orderpr |
| 138271 | root | 172.16.60.50: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.60.50: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.60.50: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.60.50:39790 | NULL | Sleep | 2 | | NULL |
| 138275 | root | 172.16.60.50:39792 | NULL | Sleep | 1 | | NULL |
+--------+------+------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
可以看到138268和138269在备份kevin库,138270,138271,138272,138273在备份dbt3，这⾥没打印全。
- 备份过程如下:
终端会话1:
(root@localhost) [(none)]> al_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)
终端会话2:
[root@VM_0_5_centos ~]# mysqlpump --single-transaction kevin --set-gtid-purged=OFF -p123456> /tmp/backup.sql
Dump completed in 592 milliseconds
(root@localhost) [(none)]> select thread_id,left(argument, 64) al_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 |
+-----------+------------------------------------------------------------------+
根据上⾯信息，可以看出：
- 线程7 进⾏ FLUSH TABLES WITH READ LOCK 。对表加⼀个读锁
- 线程7、8、9分别开启⼀个事务（RR隔离级别）去备份数据，由于之前锁表了，所以这三个线程备份出的数据是具有⼀致性的
- 线程7 解锁 UNLOCK TABLE
- 整个过程都没有获取⼆进制位置点
- compress-output
mysqlpump⽀持压缩输出,⽀持LZ4和ZLIB（ZLIB压缩⽐相对较⾼，但是速度较慢）
[root@localhost tmp]# mysqlpump --single-transaction --compress-output=lz4 kevin --set-gtid-purged=OFF -p123456 > /tmp/backup_kevin.sql
Dump completed in 511 milliseconds
- 备份恢复
未压缩的备份
mysql < source /tmp/backup.sql;
压缩过的备份
先解压
[root@localhost ~]# lz4_decompress /tmp/backup_kevin.sql /tmp/kevin.sql
再导⼊
mysql < source /tmp/kevin.sql;
可以看出来，这个导⼊是单线程。mysqlpump备份的数据恢复时会先插⼊数据, 再建索引, ⽽mysqldump备份的数据恢复是在建⽴表的时候就把索引加上了, 所以前者备份的数据恢复时速度要快⼀点！
总体来说mysqlpump还是很好⽤的，尤其是多数据库表的备份。不过如果有⼀张表格外⼤，那么备份
的⼤部分时间还是要消耗在这张表上，因为mysqlpump的备份是基于表并⾏的，对于每张表的导出只能是单个线程的。另外注意mysqlpump备份时并发线程的数量还是要看⾃⾝服务器的IO负载能⼒，并不是说⼀味的增加并发线程数量就可以加快速
度。mysqldump和mysqlpump的使⽤⽅法绝⼤部分⼀致，mysqlpump新的参数⽂章上已经标明，到底⽤那种⼯具备份数据库这个要在具体的环境下才能做出选择，有些时候可能⽤物理备份更好（xtrabackup），总之根据需要进⾏测试，最后再决定使⽤哪种备份⼯具进⾏备份。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论