MYSQL 5.7 mysqldump 备份工具说明

清疚 2022-05-22 00:40 278阅读 0赞

# [MySQL 5.7 mysqlpump 备份工具说明][MySQL 5.7 mysqlpump] #

MySQL5.7之后多了一个备份工具：mysqlpump。它是mysqldump的一个衍生，mysqldump就不多说明了，现在看看mysqlpump到底有了哪些提升，可以查看[官方文档][Link 1]，这里针对如何使用做下说明。

mysqlpump和mysqldump一样，属于逻辑备份，备份以SQL形式的文本保存。逻辑备份相对物理备份的好处是不关心undo log的大小，直接备份数据即可。它最主要的特点是：

*  并行备份数据库和数据库中的对象的，加快备份过程。
 *  更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
 *  备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
 *  备份出来直接生成压缩后的备份文件。
 *  备份进度指示（估计值）。
 *  重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。
 *  备份可以排除或则指定数据库。

参数：绝大部分参数和mysqldump一致，顺便复习一下。对于mysqlpump参数会用背景色    标记出来。

1：[\--add-drop-database][--add-drop-database]：在建立库之前先执行删库操作。

DROP DATABASE IF EXISTS `...`;

2：[\--add-drop-table][--add-drop-table]：在建表之前先执行删表操作。

DROP TABLE IF EXISTS `...`.`...`;

3：[\--add-drop-user][--add-drop-user]：在CREATE USER语句之前增加DROP USER，注意：这个参数需要和[\--users][--users]一起使用，否者不生效。

DROP USER 'backup'@'192.168.123.%';

4：[\--add-locks][--add-locks]：备份表时，使用LOCK TABLES和UNLOCK TABLES。注意：这个参数不支持并行备份，需要关闭并行备份功能：[\--default-parallelism][--default-parallelism]=0

LOCK TABLES `...`.`...` WRITE;
    ...
    UNLOCK TABLES;

5：[\--all-databases][--all-databases]：备份所有库，-A。

6：[\--bind-address][--bind-address]：指定通过哪个网络接口来连接Mysql服务器（一台服务器可能有多个IP），防止同一个网卡出去影响业务。

7：[\--complete-insert][--complete-insert]：dump出包含所有列的完整insert语句。

8：[\--compress][--compress]： 压缩客户端和服务器传输的所有的数据，-C。

9：[\--compress-output][--compress-output]：默认不压缩输出，目前可以使用的压缩算法有LZ4和ZLIB。

shell> mysqlpump --compress-output=LZ4 > dump.lz4
    shell> lz4_decompress dump.lz4 dump.txt
    
    shell> mysqlpump --compress-output=ZLIB > dump.zlib
    shell> zlib_decompress dump.zlib dump.txt

10：[\--databases][--databases]：手动指定要备份的库，支持多个数据库，用空格分隔，-B。

11：[\--default-character-set][--default-character-set]：指定备份的字符集。

12：[\--default-parallelism][--default-parallelism]：指定并行线程数，默认是2，如果设置成0，表示不使用并行备份。注意：每个线程的备份步骤是：先create table但不建立二级索引（主键会在create table时候建立），再写入数据，最后建立二级索引。

13：[\--defer-table-indexes][--defer-table-indexes]：延迟创建索引，直到所有数据都加载完之后，再创建索引，默认开启。若关闭则会和mysqldump一样：先创建一个表和所有索引，再导入数据，因为在加载还原数据的时候要维护二级索引的开销，导致效率比较低。关闭使用参数：[\--skip--defer-table-indexes][--defer-table-indexes]。

14：[\--events][--events]：备份数据库的事件，默认开启，关闭使用--skip-events参数。

15：[\--exclude-databases][--exclude-databases]：备份排除该参数指定的数据库，多个用逗号分隔。类似的还有[\--exclude-events][--exclude-events]、[\--exclude-routines][--exclude-routines]、[\--exclude-tables][--exclude-tables]、[\--exclude-triggers][--exclude-triggers]、[\--exclude-users][--exclude-users]。

mysqlpump --exclude-databases=mysql,sys    #备份过滤mysql和sys数据库
    
    mysqlpump --exclude-tables=rr,tt   #备份过滤所有数据库中rr、tt表
    
    mysqlpump -B test --exclude-tables=tmp_ifulltext,tt #备份过滤test库中的rr、tt表
    ...

注意：要是只备份数据库的账号，需要添加参数[\--users][--users]，并且需要过滤掉所有的数据库，如：

mysqlpump --users --exclude-databases=sys,mysql,db1,db2 --exclude-users=dba,backup  #备份除dba和backup的所有账号。

16：[\--include-databases][--include-databases]：指定备份数据库，多个用逗号分隔，类似的还有[\--include-events][--include-events]、[\--include-routines][--include-routines]、[\--include-tables][--include-tables]、[\--include-triggers][--include-triggers]、[\--include-users][--include-users]，大致方法使用同15。

17：[\--insert-ignore][--insert-ignore]：备份用insert ignore语句代替insert语句。

18：[\--log-error-file][--log-error-file]：备份出现的warnings和erros信息输出到一个指定的文件。

19：[\--max-allowed-packet][--max-allowed-packet]：备份时用于client/server直接通信的最大buffer包的大小。

20：[\--net-buffer-length][--net-buffer-length]：备份时用于client/server通信的初始buffer大小，当创建多行插入语句的时候，mysqlpump 创建行到N个字节长。

21：[\--no-create-db][--no-create-db]：备份不写CREATE DATABASE语句。要是备份多个库，需要使用参数-B，而使用-B的时候会出现create database语句，该参数可以屏蔽create database 语句。

22：[\--no-create-info][--no-create-info]：备份不写建表语句，即不备份表结构，只备份数据，-t。

23：[\--hex-blob][--hex-blob]： 备份binary字段的时候使用十六进制计数法，受影响的字段类型有BINARY、VARBINARY、BLOB、BIT。

24：[\--host][--host] ：备份指定的数据库地址，-h。

25：[`--parallel-schemas=[N:]db_list`][--parallel-schemas_N_db_list]：指定并行备份的库，多个库用逗号分隔，如果指定了N，将使用N个线程的地队列，如果N不指定，将由 --default-parallelism才确认N的值，可以设置多个`--parallel-schemas。`

mysqlpump --parallel-schemas=4:vs,aa --parallel-schemas=3:pt   #4个线程备份vs和aa，3个线程备份pt。通过show processlist 可以看到有7个线程。
    
    mysqlpump --parallel-schemas=vs,abc --parallel-schemas=pt  #默认2个线程，即2个线程备份vs和abc，2个线程备份pt
    
    ####当然要是硬盘IO不允许的话，可以少开几个线程和数据库进行并行备份

26：[\--password][--password]：备份需要的密码。

27：[\--port][--port] ：备份数据库的端口。

28：`--protocol={TCP|SOCKET|PIPE|MEMORY}：指定连接服务器的协议。`

29：[\--replace][--replace]：备份出来replace into语句。

30：[\--routines][--routines]：备份出来包含存储过程和函数，默认开启，需要对 `mysql.proc表有查看权限。生成的文件中会包含CREATE PROCEDURE 和 CREATE FUNCTION语句以用于恢复，关闭则需要用--skip-routines参数。`

31：[\--triggers][--triggers]：备份出来包含触发器，默认开启，使用`--skip-triggers来关闭。`

31：[\--set-charset][--set-charset]：备份文件里写SET NAMES default\_character\_set 到输出，此参默认开启。 -- skip-set-charset禁用此参数，不会在备份文件里面写出set names...

32：[\--single-transaction][--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][--skip-definer]：忽略那些创建视图和存储过程用到的 DEFINER 和 SQL SECURITY 语句，恢复的时候，会使用默认值，否则会在还原的时候看到没有DEFINER定义时的账号而报错。

34：[\--skip-dump-rows][--skip-dump-rows]：只备份表结构，不备份数据，-d。注意：mysqldump支持--no-data，mysqlpump不支持--no-data

35：[\--socket][--socket]：对于连接到localhost，Unix使用套接字文件，在Windows上是命名管道的名称使用，-S。

36：[\--ssl][--ssl]：--ssl参数将要被去除，用[\--ssl-mode][--ssl]取代。关于ssl相关的备份，请看[官方文档][Link 2]。

37：[\--tz-utc][--tz-utc]：备份时会在备份文件的最前几行添加SET TIME\_ZONE='+00:00'。注意：如果还原的服务器不在同一个时区并且还原表中的列有timestamp字段，会导致还原出来的结果不一致。默认开启该参数，用 `--skip-tz-utc来关闭参数。`

38：[\--user][--user]：备份时候的用户名，-u。

39：[\--users][--users]：备份数据库用户，备份的形式是CREATE USER...，GRANT...，只备份数据库账号可以通过如下命令：

mysqlpump --exclude-databases=% --users    #过滤掉所有数据库

40：[\--watch-progress][--watch-progress]：定期显示进度的完成，包括总数表、行和其他对象。该参数默认开启，用`--skip-watch-progress来关闭。`

使用说明：

mysqlpump的架构如下图所示：

![163084-20160720125824779-1563550856.png][]

mysqlpump支持基于库和表的并行导出，mysqlpump的并行导出功能的架构为：队列+线程，允许有多个队列（`--parallel-schemas？`），每个队列下有多个线程（N？），而一个队列可以绑定1个或者多个数据库（逗号分隔）。mysqlpump的备份是基于表并行的，对于每张表的导出只能是单个线程的，这里会有个限制是如果某个数据库有一张表非常大，可能大部分的时间都是消耗在这个表的备份上面，并行备份的效果可能就不明显。这里可以利用[mydumper][]其是以chunk的方式批量导出，即mydumper支持一张表多个线程以chunk的方式批量导出。但是相对于mysqldump还是有了很大的提升。这里大致测试下mysqlpump和mysqldump的备份效率。

![复制代码][copycode.gif]

#mysqlpump压缩备份vs数据库 三个并发线程备份，消耗时间：222s
    mysqlpump -uzjy -p -h192.168.123.70 --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B vs > /home/zhoujy/vs_db.sql.lz4
    
    #mysqldump备份压缩vs数据库 单个线程备份，消耗时间：900s，gzip的压缩率比LZ4的高
    mysqldump -uzjy -p -h192.168.123.70 --default-character-set=utf8 -P3306 --skip-opt --add-drop-table --create-options  --quick --extended-insert --single-transaction -B vs | gzip > /home/zhoujy/vs.sql.gz
    
    #mydumper备份vs数据库 三个并发线程备份，消耗时间：300s，gzip的压缩率比LZ4的高
    mydumper -u zjy -p  -h 192.168.123.70 -P 3306 -t 3 -c -l 3600 -s 10000000 -B vs -o /home/zhoujy/vs/
    
    #mydumper备份vs数据库，五个并发线程备份，并且开启对一张表多个线程以chunk的方式批量导出，-r。消耗时间：180s
    mydumper -u zjy -p  -h 192.168.123.70 -P 3306 -t 5 -c -r 300000 -l 3600 -s 10000000 -B vs -o /home/zhoujy/vs/

![复制代码][copycode.gif]

从上面看出，mysqlpump的备份效率是最快的，mydumper次之，mysqldump最差。所以在IO允许的情况下，能用多线程就别用单线程备份。并且mysqlpump还支持多数据库的并行备份，而mydumper要么备份一个库，要么就备份所有库。姜大神的[Oracle官方并行逻辑备份工具mysqlpump][Oracle_mysqlpump]这篇文章的测试结果也说明了mysqlpump比mysqldump的测试好。由于实际情况不同，测试给出的速度提升只是参考。到底开启多少个并行备份的线程，这个看磁盘IO的承受能力，若该服务器只进行备份任务，可以最大限制的来利用磁盘。

总结：

mysqldump和mysqlpump的使用方法绝大部分一致，mysqlpump新的参数文章上已经标明，到底用那种工具备份数据库这个要在具体的环境下才能做出选择，有些时候可能用物理备份更好（xtrabackup），总之根据需要进行测试，最后再决定使用哪种备份工具进行备份。

[MySQL 5.7 mysqlpump]: https://www.cnblogs.com/zhoujinyi/p/5684903.html
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
[--add-drop-database]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_add-drop-database
[--add-drop-table]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_add-drop-table
[--add-drop-user]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_add-drop-user
[--users]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_users
[--add-locks]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_add-locks
[--default-parallelism]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_default-parallelism
[--all-databases]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_all-databases
[--bind-address]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_bind-address
[--complete-insert]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_complete-insert
[--compress]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_compress
[--compress-output]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_compress-output
[--databases]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_databases
[--default-character-set]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_default-character-set
[--defer-table-indexes]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_defer-table-indexes
[--events]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_events
[--exclude-databases]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_exclude-databases
[--exclude-events]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_exclude-events
[--exclude-routines]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_exclude-routines
[--exclude-tables]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_exclude-tables
[--exclude-triggers]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_exclude-triggers
[--exclude-users]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_exclude-users
[--include-databases]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_include-databases
[--include-events]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_include-events
[--include-routines]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_include-routines
[--include-tables]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_include-tables
[--include-triggers]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_include-triggers
[--include-users]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_include-users
[--insert-ignore]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_insert-ignore
[--log-error-file]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_log-error-file
[--max-allowed-packet]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_max-allowed-packet
[--net-buffer-length]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_net-buffer-length
[--no-create-db]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_no-create-db
[--no-create-info]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_no-create-info
[--hex-blob]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_hex-blob
[--host]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_host
[--parallel-schemas_N_db_list]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_parallel-schemas
[--password]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_password
[--port]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_port
[--replace]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_replace
[--routines]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_routines
[--triggers]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_triggers
[--set-charset]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_set-charset
[--single-transaction]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_single-transaction
[--skip-definer]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer
[--skip-dump-rows]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-dump-rows
[--socket]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_socket
[--ssl]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_ssl
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/secure-connection-options.html#option_general_ssl-mode
[--tz-utc]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_tz-utc
[--user]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_user
[--watch-progress]: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_watch-progress
[163084-20160720125824779-1563550856.png]: /images/20220522/e913c497096341568dde48a14fe65f9b.png
[mydumper]: http://www.cnblogs.com/zhoujinyi/p/3423641.html
[copycode.gif]: /images/20220522/b91dc52b77ce4b4fbda5b87c4d678c8f.png
[Oracle_mysqlpump]: http://www.innomysql.com/article/25383.html