Mysql 5.7之后多了一个备份工具，mysqlpump

mysqlpump客户端 执行的是逻辑备份

Mysqlpump 新特性

并行执行 数据库和其中的对象，加快转储过程

更好的控制哪些数据库和数据库对象 来转储导出

导出用户账号 作为账号管理语句（create user, grant ），而不是插入到mysql系统数据库中

备份出来直接生成压缩备份文件

备份进度指标（估计值)

转储文件加载（还原），先建表后插入数据，最后建立索引，减少索引的维护开销，加快还原速度.

备份可以排除或指定数据库

mysqlpump需要的 权限， 不同的选项，需要不同的权限，可以在选项说明中查看。

功能

至少需要的权限

导出表

select 对应表权限

导出视图

show view 对应视图权限

导出存储过程

trigger 对应存储过程权限

--single-transaction 选项没有使用时

LOCK TABLES

导出用户定义

Selectmysql 系统库的权限

加载dump文件，必须有执行 dump文件所包含的语句 的权限，如 create 等。

NOTE

在windows上使用powershell 导出时，重定向到一个新建文件，会使用 utf-16 编码，这会导致错误，因为MySQL 连接字符集不支持utf-16 .

shell> mysqlpump [options] > dump.sql 错误

可以使用 --result-file 选项，来输出到 ASCII格式的文件上。

shell> mysqlpump [options] --result-file=dump.sql正确

Mysqldump 调用语法

shell> mysqlpump --all-databases

shell> mysqlpump db_name

shell> mysqlpump db_name tbl_name1 tbl_name2 ...

导出指定的多个库

shell> mysqlpump --databases db_name1 db_name2 ...

默认情况，mysqlpump 不导出用户账户定义，即使你导出含有授权表的 mysql 系统库。要以逻辑定义（create user 和 grant）形式导出授权表，使用 --users 选项 并且禁止所有数据库转储。

shell> mysqlpump --exclude-databases=% --users

这里的%是个通配符，他匹配所有的库， --exclude-database=% 即排除所有的库

Mysqlpump 支持几个选项，包含或排除数据库、表、存储过程、用户定义。看 mysqlpump object selection，

要加载转储文件，执行它包含的语句，如下：

shell> mysqlpump [options] > dump.sql

shell> mysql < dump.sql

Mysqlpump 选项概要

mysqlpump支持命令行指定选项，也可以在参数文件的[mysqlpump]and[client] 的组中指定。看

Section4.2.6, “Using Option Files”.

Table4.15mysqlpump Options

参数绝大多数和mysqldump一致,对于mysqlpump参数会用背景色 标记出来。

Format

Description

Introduced

--add-drop-database

AddDROP DATABASE statement before each CREATE DATABASE statement

在建库之前，先执行删除库操作

DROPDATABASE IF EXISTS `...`;

--add-drop-table

AddDROP TABLE statement before each CREATE TABLE statement

在建表之前先执行删表操作。

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

--add-drop-user

AddDROP USER statement before each CREATE USER statement

在CREATEUSER语句之前增加DROP USER，注意：这个参数需要和--users一起使用，否者不生效。

DROPUSER 'backup'@'192.168.123.%';

--add-locks

Surroundeach table dump with LOCK TABLES and UNLOCK TABLES statements

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

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

--all-databases

Dump all databases

备份所有库，-A。

--bind-address

Usespecified network interface to connect to MySQL Server

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

--character-sets-dir

Directorywhere character sets are installed

--complete-insert

Usecomplete INSERT statements that include column names

dump出包含所有列的完整insert语句。

--compress

Compressall information sent between client and server

在客户端和服务器传输的所有的数据包压缩，最后的备份集大小没有任何改变，-C。

若要改变备份集大小：

不过时间，会用到5倍于compress=false

会几倍于 --compress-output呢？

--compress-output

Outputcompression algorithm

默认不压缩输出，目前可以使用的压缩算法有LZ4和ZLIB。

shell>mysqlpump --compress-output=LZ4 > dump.lz4
shell> lz4_decompress dump.lz4dump.txt

shell>mysqlpump --compress-output=ZLIB > dump.zlib
shell> zlib_decompress dump.zlibdump.txt

--databases

Interpretall name arguments as database names

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

--debug

Writedebugging log

--debug-check

Printdebugging information when program exits

--debug-info

Printdebugging information, memory, and CPU statistics when program exits

--default-auth

Authenticationplugin to use

--default-character-set

Specifydefault character set

指定备份的字符集。

--default-parallelism

Defaultnumber of threads for parallel processing

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

--defaults-extra-file

Readnamed option file in addition to usual option files

--defaults-file

Readonly named option file

--defaults-group-suffix

Optiongroup suffix value

--defer-table-indexes

Forreloading, defer index creation until after loading table rows

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

--events

Dumpevents from dumped databases

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

--exclude-databases

Databasesto exclude from dump

备份排除该参数指定的数据库，多个用逗号分隔。类似的还有--exclude-events、--exclude-routines、--exclude-tables、--exclude-triggers、--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，并且需要过滤掉所有的数据库，如：

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

--exclude-events

Eventsto exclude from dump

--exclude-routines

Routinesto exclude from dump

--exclude-tables

Tablesto exclude from dump

--exclude-triggers

Triggersto exclude from dump

--exclude-users

Usersto exclude from dump

--extended-insert

Usemultiple-row INSERT syntax

--get-server-public-key

RequestRSA public key from server

5.7.23

--help

Displayhelp message and exit

--hex-blob

Dumpbinary columns using hexadecimal notation

备份binary字段的时候使用十六进制计数法，受影响的字段类型有BINARY、VARBINARY、BLOB、BIT。

--host

Hostto connect to (IP address or hostname)

备份指定的数据库地址，-h。

--include-databases

Databasesto include in dump

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

--include-events

Eventsto include in dump

--include-routines

Routinesto include in dump

--include-tables

Tablesto include in dump

--include-triggers

Triggersto include in dump

--include-users

Usersto include in dump

--insert-ignore

WriteINSERT IGNORE rather than INSERT statements

备份用insertignore语句代替insert语句。

--log-error-file

Appendwarnings and errors to named file

备份出现的warnings和erros信息输出到一个指定的文件。

--login-path

Read login path options from .mylogin.cnf

--max-allowed-packet

Maximumpacket length to send to or receive from server

备份时用于client/server直接通信的最大buffer包的大小。

--net-buffer-length

Buffersize for TCP/IP and socket communication

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

--no-create-db

Donot write CREATE DATABASE statements

备份不写CREATEDATABASE语句。要是备份多个库，需要使用参数-B，而使用-B的时候会出现create database语句，该参数可以屏蔽createdatabase 语句。

--no-create-info

Donot write CREATE TABLE statements that re-create each dumped table

备份不写建表语句，即不备份表结构，只备份数据，-t。

--no-defaults

Readno option files

--parallel-schemas

Specifyschema-processing parallelism

指定并行备份的库，多个库用逗号分隔，如果指定了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不允许的话，可以少开几个线程和数据库进行并行备份

--password

Passwordto use when connecting to server

--plugin-dir

Directorywhere plugins are installed

--port

TCP/IPport number for connection

--print-defaults

Printdefault options

--protocol

Connectionprotocol to use

{TCP|SOCKET|PIPE|MEMORY}：指定连接服务器的协议。

--replace

WriteREPLACE statements rather than INSERT statements

备份出来replace into语句。

--result-file

Directoutput to a given file

--routines

Dumpstored routines (procedures and functions) from dumped databases

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

--secure-auth

Donot send passwords to server in old (pre-4.1) format

--server-public-key-path

Pathname to file containing RSA public key

5.7.23

--set-charset

AddSET NAMES default_character_set to output

备份文件里写SET NAMESdefault_character_set到输出，此参默认开启。--skip-set-charset禁用此参数，不会在备份文件里面写出set names...

--set-gtid-purged

Whetherto add SET @@GLOBAL.GTID_PURGED to output

5.7.18

--single-transaction

Dumptables within 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的互斥问题。

--skip-definer

OmitDEFINER and SQL SECURITY clauses from view and stored program CREATEstatements

忽略那些创建视图和存储过程用到的DEFINER和SQLSECURITY语句，恢复的时候，会使用默认值，否则会在还原的时候看到没有DEFINER定义时的账号而报错。

--skip-dump-rows

Donot dump table rows

只备份表结构，不备份数据，-d。注意：mysqldump支持--no-data，mysqlpump不支持--no-data

--socket

Forconnections to localhost, the Unix socket file to use

--ssl

Enableencrypted connection

--ssl参数将要被去除，用--ssl-mode取代。关于ssl相关的备份，请看官方文档。

--ssl-ca

Filethat contains list of trusted SSL Certificate Authorities

--ssl-capath

Directorythat contains trusted SSL Certificate Authority certificate files

--ssl-cert

Filethat contains X.509 certificate

--ssl-cipher

Listof permitted ciphers for connection encryption

--ssl-crl

Filethat contains certificate revocation lists

--ssl-crlpath

Directorythat contains certificate revocation list files

--ssl-key

Filethat contains X.509 key

--ssl-mode

Securitystate of connection to server

5.7.11

--ssl-verify-server-cert

Verifyhost name against server certificate Common Name identity

--tls-version

Protocolspermitted for encrypted connections

5.7.10

--triggers

Dumptriggers for each dumped table

备份出来包含触发器，默认开启，使用--skip-triggers来关闭。

--tz-utc

AddSET TIME_ZONE='+00:00' to dump file

--user

MySQL user name to use when connecting to server.

-u

--users

Dumpuser accounts

备份数据库用户，备份的形式是CREATEUSER...，GRANT...，只备份数据库账号可以通过如下命令：

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

--version

Displayversion information and exit

5.7.9

--watch-progress

Displayprogress indicator

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

不支持的参数

--flush-logs --flush-privileges 看来5.7 不需要导出时做这些动作了

--master-data --dump-slave 没有这个怎么搭建从库呢

使用说明：

mysqlpump的架构如下图所示：

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

#mysqlpump压缩备份vs数据库 三个并发线程备份，消耗时间：222s

mysqlpump -uzjy -p -h292.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 -h292.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/

从上面看出，mysqlpump的备份效率是最快的，mydumper次之，mysqldump最差。所以在IO允许的情况下，能用多线程就别用单线程备份。