ITPub博客

首页 > 数据库 > MySQL > mysqlpump - 一个数据库备份程序

mysqlpump - 一个数据库备份程序

原创 MySQL 作者:steven_hua 时间:2018-08-30 15:49:51 0 删除 编辑

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 < dump . sql

 

Mysqlpump 选项概要

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

Section 4.2.6, “Using Option Files” .

 

 

Table 4.15 mysqlpump 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。

若要改变备份集大小:

compress=true     |gzip

不过时间,会用到 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 -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允许的情况下,能用多线程就别用单线程备份。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/807718/viewspace-2213210/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-16

  • 博文量
    117
  • 访问量
    121852