mysqlpump - 一个数据库备份程序

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

…

默认情况，

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

.

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

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-creat开发云主机域名e-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允许的情况下，能用多线程就别用单线程备份。

相关推荐: mysql多实例部署

1.mysql启动开发云主机域名 2.命令行登陆相关推荐: MySQL的在RC和RR模式下的锁InnoDB的锁机制：数据库使用所是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持锁的存储引擎，锁的类型有：共享锁（S）、排它锁（X）、意向共享…

mysqlpump – 一个数据库备份程序

相关

相关推荐