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

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

Mysqlpump 新特性

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

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

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

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

备份进度指标（估计值)

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

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

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

功能

至少需要的权限

导出表

select 对应表权限

导出视图

show view 对应视图权限

导出存储过程

trigger 对应存储过程权限

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

LOCK TABLES

导出用户定义

Select mysql 系统库的权限

加载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

Add DROP DATABASE statement before each CREATE DATABASE statement

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

DROP DATABASE IF EXISTS `...`;

--add-drop-table

Add DROP TABLE statement before each CREATE TABLE statement

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

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

--add-drop-user

Add DROP USER statement before each CREATE USER statement

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

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

--add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements

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

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

--all-databases

Dump all databases

备份所有库，-A。

--bind-address

Use specified network interface to connect to MySQL Server

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

--character-sets-dir

Directory where character sets are installed

--complete-insert

Use complete INSERT statements that include column names

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

--compress

Compress all information sent between client and server

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

若要改变备份集大小：

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

会几倍于 --compress-output呢？

--compress-output

Output compression algorithm

默认不压缩输出，目前可以使用的压缩算法有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

--databases

Interpret all name arguments as database names

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

--debug

Write debugging log

--debug-check

Print debugging information when program exits

--debug-info

Print debugging information, memory, and CPU statistics when program exits

--default-auth

Authentication plugin to use

--default-character-set

Specify default character set

指定备份的字符集。

--default-parallelism

Default number of threads for parallel processing

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

--defaults-extra-file

Read named option file in addition to usual option files

--defaults-file

Read only named option file

--defaults-group-suffix

Option group suffix value

--defer-table-indexes

For reloading, defer index creation until after loading table rows

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

--events

Dump events from dumped databases

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

--exclude-databases

Databases to 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

Events to exclude from dump

--exclude-routines

Routines to exclude from dump

--exclude-tables

Tables to exclude from dump

--exclude-triggers

Triggers to exclude from dump

--exclude-users

Users to exclude from dump

--extended-insert

Use multiple-row INSERT syntax

--get-server-public-key

Request RSA public key from server

5.7.23

--help

Display help message and exit

--hex-blob

Dump binary columns using hexadecimal notation

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

--host

Host to connect to (IP address or hostname)

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

--include-databases

Databases to include in dump

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

--include-events

Events to include in dump

--include-routines

Routines to include in dump

--include-tables

Tables to include in dump

--include-triggers

Triggers to include in dump

--include-users

Users to include in dump

--insert-ignore

Write INSERT IGNORE rather than INSERT statements

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

--log-error-file

Append warnings and errors to named file

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

--login-path

Read login path options from .mylogin.cnf

--max-allowed-packet

Maximum packet length to send to or receive from server

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

--net-buffer-length

Buffer size for TCP/IP and socket communication

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

--no-create-db

Do not write CREATE DATABASE statements

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

--no-create-info

Do not write CREATE TABLE statements that re-create each dumped table

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

--no-defaults

Read no option files

--parallel-schemas

Specify schema-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

Password to use when connecting to server

--plugin-dir

Directory where plugins are installed

--port

TCP/IP port number for connection

--print-defaults

Print default options

--protocol

Connection protocol to use

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

--replace

Write REPLACE statements rather than INSERT statements

备份出来replace into语句。

--result-file

Direct output to a given file

--routines

Dump stored routines (procedures and functions) from dumped databases

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

--secure-auth

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

--server-public-key-path

Path name to file containing RSA public key

5.7.23

--set-charset

Add SET NAMES default_character_set to output

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

--set-gtid-purged

Whether to add SET @@GLOBAL.GTID_PURGED to output

5.7.18

--single-transaction

Dump tables 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

Omit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements

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

--skip-dump-rows

Do not dump table rows

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

--socket

For connections to localhost, the Unix socket file to use

--ssl

Enable encrypted connection

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

--ssl-ca

File that contains list of trusted SSL Certificate Authorities

--ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

--ssl-cert

File that contains X.509 certificate

--ssl-cipher

List of permitted ciphers for connection encryption

--ssl-crl

File that contains certificate revocation lists

--ssl-crlpath

Directory that contains certificate revocation list files

--ssl-key

File that contains X.509 key

--ssl-mode

Security state of connection to server

5.7.11

--ssl-verify-server-cert

Verify host name against server certificate Common Name identity

--tls-version

Protocols permitted for encrypted connections

5.7.10

--triggers

Dump triggers for each dumped table

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

--tz-utc

Add SET TIME_ZONE='+00:00' to dump file

--user

MySQL user name to use when connecting to server.

-u

--users

Dump user accounts

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

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

--version

Display version information and exit

5.7.9

--watch-progress

Display progress 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允许的情况下，能用多线程就别用单线程备份。