mysql datapump_mysqlpump - 一个数据库备份程序

Mysql 5.7

之后多了一个备份工具，

mysqlpump

mysqlpump客户端 执行的是

逻辑备份

Mysqlpump

新特性

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

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

导出用户账号

作为账号管理语句(

create user

,

grant

)，而不是插入到mysql系统数据库中

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

备份进度指标(估计值

)

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

.

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

mysqlpump

需要的

权限，

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

功能

至少需要的权限

导出表

导出视图

导出存储过程

trigger

对应存储过程权限

--single-transaction

选项没有使用时

导出用户定义

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

...

默认情况，

mysql

p

ump

不导出用户账户定义，即使你导出含有授权表的

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]

的组中指定。看

Table 4.15 mysqlpump Options

参数绝大多数和

mysqldump

一致

,

对于

mysqlpump

参数会用背景色

标记出来。

Format

Description

Introduced

Add   DROP DATABASE statement before each CREATE DATABASE statement

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

DROP   DATABASE IF EXISTS

`...`;

Add   DROP TABLE statement before each CREATE TABLE statement

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

DROP TABLE IF EXISTS

`...`.`...`;

Add   DROP USER statement before each CREATE USER statement

在CREATE   USER语句之前增加DROP USER，

注意：

这个参数需要和

--users

一起使用，否者不生效。

DROP   USER 'backup'

@

'192.168.123.%'

;

Surround   each table dump with LOCK TABLES and UNLOCK TABLES statements

备份表时，使用LOCK   TABLES和UNLOCK TABLES。

注意：

这个参数不支持并行备份，需要关闭并行备份功能：

--default-parallelism

=0

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

...

UNLOCK TABLES;

Dump all databases

备份所有库，-A。

Use   specified network interface to connect to MySQL Server

指定通过哪个网络接口来连接

Mysql

服务器(一台服务器可能有多个

IP

)，防止同一个网卡出去影响业务。

Directory   where character sets are installed

Use   complete INSERT statements that include column names

dump

出包含所有列的完整

insert

语句。

Compress   all information sent between client and server

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

若要改变备份集大小：

compress=true     |gzip

不过时间，会用到

5

倍于

compress=

false

会几倍于 --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

Interpret   all name arguments as database names

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

Write   debugging log

Print   debugging information when program exits

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

Authentication   plugin to use

Specify   default character set

指定备份的字符集。

Default   number of threads for parallel processing

指定并行线程数，默认是2，如果设置成0，表示不使用并行备份。

注意：

每个线程的备份步骤是：先create table但不建立二级索引(主键会在create   table时候建立)，再写入数据，最后建立二级索引。

Read   named option file in addition to usual option files

Read   only named option file

Option   group suffix value

For   reloading, defer index creation until after loading table rows

延迟创建索引，直到所有数据都加载完之后，再创建索引，默认开启。若关闭则会和

mysqldump

一样：先创建一个表和所有索引，再导入数据，因为在加载还原数据的时候要维护二级索引的开销，导致效率比较低。关闭使用参数：

--skip--defer-table-indexes

。

Dump   events from dumped databases

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

Databases   to exclude from dump

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

的所有账号。

Events   to exclude from dump

Routines   to exclude from dump

Tables   to exclude from dump

Triggers   to exclude from dump

Users   to exclude from dump

Use   multiple-row INSERT syntax

Request   RSA public key from server

5.7.23

Display   help message and exit

Dump   binary columns using hexadecimal notation

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

Host   to connect to (IP address or hostname)

备份指定的数据库地址，

-h

。

Events   to include in dump

Routines   to include in dump

Tables   to include in dump

Triggers   to include in dump

Users   to include in dump

Write   INSERT IGNORE rather than INSERT statements

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

Append   warnings and errors to named file

备份出现的

warnings

和

erros

信息输出到一个指定的文件。

Read login path options from

.mylogin.cnf

Maximum   packet length to send to or receive from server

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

Buffer   size for TCP/IP and socket communication

备份时用于

client/server

通信的初始

buffer

大小，当创建多行插入语句的时候，

mysqlpump

创建行到

N

个字节长。

Do   not write CREATE DATABASE statements

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

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

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

-t

。

Read   no option files

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   to use when connecting to server

Directory   where plugins are installed

TCP/IP   port number for connection

Print   default options

Connection   protocol to use

Write   REPLACE statements rather than INSERT statements

备份出来

replace into

语句。

Direct   output to a given file

Dump   stored routines (procedures and functions) from dumped databases

备份出来包含存储过程和函数，默认开启，

需要对

mysql.proc表有查看权限

。生成的文件中会包含CREATE   PROCEDURE 和 CREATE FUNCTION语句以用于恢复，关闭则需要用--skip-routines参数。

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

Path   name to file containing RSA public key

5.7.23

Add   SET NAMES default_character_set to output

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

Whether   to add SET @@GLOBAL.GTID_PURGED to output

5.7.18

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

的互斥问题。

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

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

Do   not dump table rows

只备份表结构，不备份数据，-d。

注意：

mysqldump支持--no-data，mysqlpump不支持--no-data

For   connections to localhost, the Unix socket file to use

Enable   encrypted connection

--ssl

参数将要被去除，用

--ssl-mode

取代。关于

ssl

相关的备份，请看

官方文档

。

File   that contains list of trusted SSL Certificate Authorities

Directory   that contains trusted SSL Certificate Authority certificate files

File   that contains X.509 certificate

List   of permitted ciphers for connection encryption

File   that contains certificate revocation lists

Directory   that contains certificate revocation list files

File   that contains X.509 key

Security   state of connection to server

5.7.11

Verify   host name against server certificate Common Name identity

Protocols   permitted for encrypted connections

5.7.10

Dump   triggers for each dumped table

备份出来包含触发器，默认开启，使用

--skip-triggers

来关闭。

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

MySQL user name to use when connecting to server

.

-u

Dump   user accounts

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

mysqlpump --exclude-databases=% --users    #

过滤掉所有数据库

Display   version information and exit

5.7.9

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 -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/

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