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 < 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支持基于库和表的并行导出,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允许的情况下,能用多线程就别用单线程备份。


网页标题:mysqlpump-一个数据库备份程序
本文来源:http://pwwzsj.com/article/pojgjo.html