mysqlpump并行备份

一、适用版本

5.7.11之后的版本

二、mysqlpump优缺点

Mysqlpump主要特点
-  并行备份数据库和数据库中的对象的，加快备份过程。
-  更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
-  备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
-  备份出来直接生成压缩后的备份文件。
-  备份进度指示（估计值）。
-  重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。
-  备份可以排除或则指定数据库。
Mysqlpump缺点
-  只能并行到表级别,如果表特别大,开多线程和单线程是一样的,并行度不如mydumper；
-  无法获取当前备份对应的binlog位置；
-  MySQL5.7.11之前的版本不要使用,并行导出和single-transaction是互斥的；

三、备份语句

[root@data02 ~]# /data/mysql8/install/bin/mysqlpump --default-parallelism=6 --socket=/data/mysql8/tmp/mysql.sock -uroot -p'123456'  --single-transaction --triggers --routines --events --max_allowed_packet=16M --all-databases > test0526.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 1/1 tables, 0/0 rows
Dump progress: 26/26 tables, 2973/4144 rows
Dump progress: 27/29 tables, 25973/4980287 rows
Dump progress: 27/29 tables, 276723/4980287 rows
Dump progress: 27/29 tables, 404223/4980287 rows
Dump progress: 27/29 tables, 715973/4980287 rows
Dump progress: 27/29 tables, 1044973/4980287 rows
Dump progress: 27/29 tables, 1270473/4980287 rows
Dump progress: 27/29 tables, 1531973/4980287 rows
Dump progress: 27/29 tables, 1770223/4980287 rows
Dump progress: 27/29 tables, 2036723/4980287 rows
Dump progress: 27/29 tables, 2331473/4980287 rows
Dump progress: 27/29 tables, 2576223/4980287 rows
Dump progress: 27/29 tables, 2785973/4980287 rows
Dump progress: 27/29 tables, 3054723/4980287 rows
Dump progress: 27/29 tables, 3317973/4980287 rows
Dump progress: 27/29 tables, 3537473/4980287 rows
Dump progress: 27/29 tables, 3706473/4980287 rows
Dump progress: 27/29 tables, 3744473/4980287 rows
Dump progress: 27/29 tables, 3879973/4980287 rows
Dump progress: 27/29 tables, 4032473/4980287 rows
Dump progress: 27/29 tables, 4140473/4980287 rows
Dump progress: 27/29 tables, 4311973/4980287 rows
Dump progress: 27/29 tables, 4448723/4980287 rows
Dump progress: 27/29 tables, 4575723/4980287 rows
Dump progress: 28/29 tables, 4656473/4980287 rows
Dump progress: 28/29 tables, 4714723/4980287 rows
Dump progress: 28/29 tables, 4777223/4980287 rows
Dump progress: 28/29 tables, 4829723/4980287 rows
Dump progress: 28/29 tables, 4974723/4980287 rows
Dump completed in 44357
[root@data02 ~]# 

四、主要参数

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

五、详细参数

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;
...
UNLOCK TABLES;
5) --all-databases：备份所有库，即 -A。
6) --bind-address：指定通过哪个网络接口来连接Mysql服务器（一台服务器可能有多个IP），防止同一个网卡出去影响业务。
7) --complete-insert：dump出包含所有列的完整insert语句。
8) --compress： 压缩客户端和服务器传输的所有的数据，即 -C。
9) --compress-output：默认不压缩输出，目前可以使用的压缩算法有LZ4和ZLIB
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
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
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禁用此参数，不会在备份文件里面写出set names...
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：备份数据库用户，备份的形式是CREATE USER...，GRANT...，只备份数据库账号可以通过如下命令
40) --watch-progress：定期显示进度的完成，包括总数表、行和其他对象。该参数默认开启，用--skip-watch-progress来关闭。