一，安装

1) 安装依赖包

yum -y install gcc gcc-c++ cmake * glib2-devel zlib-devel pcre-devel openssl-devel

2) 下载

wget

3) 解压

tar -zxvf mydumper-0.9.1.tar.gz

4) 安装

#cmake .

#make

#make install

#mydumper -V

#ldd /usr/local/bin/mydumper 查看是否缺失共享库

#pwd

# cp libmysqlclient.so.18 /usr/lib64/

# mydumper -V

如果没有lib mysql client.so.18 则用find命令寻找

5) 参数

Application Options:

-B, –database Database to dump

指定备份的数据库，不指定则备份所有数据库，包括mysql，但是不包括information_schema、performance_schema，只能指定单库

-T, –tables-list Comma delimited table list to dump (does not exclude regex option)

指定备份的表，多个表以逗号分隔

-o, –outputdir Directory to output files to

指定备份输出位置，不指定输出到当前位置生成：export-date目录

-s, –statement-size Attempted size of INSERT statement in bytes, default 1000000

指定生成insert语句的字节数，默认1000000

-r, –rows Try to split tables into chunks of this many rows. This option turns off –chunk-filesize

用行块来分割表，该参数关闭–chunk-filesize

-F, –chunk-filesize Split tables into chunks of this output file size. This value is in MB

行块分割表的文件大小，单位是MB

-c, –compress Compress output files

压缩输出文件

-e, –build-empty-files Build dump files even if no data available from table

表数据为空还是产生一个空文件，默认无数据只有表结构文件

-x, –regex Regular expression for ‘db.table’ matching

使用正则表达式匹配

-i, –ignore-engines Comma delimited list of storage engines to ignore

忽略存储引擎，用逗号分隔

-m, –no-schemas Do not dump table schemas with the data

备份数据，不备份表结构

-d, –no-data Do not dump table data

不备份数据，备份表结构

-G, –triggers Dump triggers 触发器

-E, –events Dump events event事件

-R, –routines Dump stored procedures and functions 存储过程和函数

-k, –no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups 不锁表，但备份数据不一致

–less-locking Minimize locking time on InnoDB tables.

减少对InnoDB表的锁施加时间

-l, –long-query-guard Set long query timer in seconds, default 60

设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）

-K, –kill-long-queries Kill long running queries (instead of aborting)

杀掉长查询 (不退出)

-D, –daemon Enable daemon mode

启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份

-I, –snapshot-interval Interval between each dump snapshot (in minutes), requires –daemon, default 60

dump快照间隔时间，默认60s，需要在daemon模式下

-L, –logfile Log file name to use, by default stdout is used

使用的日志文件名(mydumper所产生的日志), 默认使用标准输出

–tz-utc SET TIME_ZONE=’+00:00′ at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use –skip-tz-utc to disable.

备份的时候允许备份Timestamp，这样会导致不同时区的备份还原会出问题，默认关闭，参数：–skip-tz-utc to disable.

–skip-tz-utc

–use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege

使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限

–success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn’t exist

–lock-all-tables Use LOCK TABLE for all, instead of FTWRL

锁全表， 代替FLUSH TABLE WITH READ LOCK

-U, –updated-since Use Update_time to dump only tables updated in the last U days

–trx-consistency-only Transactional consistency only

-h, –host The host to connect to

-u, –user Username with privileges to run the dump

-p, –password User password

-P, –port TCP/IP port to connect to

-S, –socket UNIX domain socket file to use for connection

-t, –threads Number of threads to use, default 4

-C, –compress-protocol Use compression on the MySQL connection

在mysql连接上使用压缩协议

-V, –version Show the program version and exit

-v, –verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

myloader –help

Usage:

myloader [OPTION…] multi-threaded MySQL loader

Help Options:

-?, –help Show help options

Application Options:

-d, –directory Directory of the dump to import

备份文件所在目录

-q, –queries-per-transaction Number of queries per transaction, default 1000

每个事务的query数量, 默认1000

-o, –overwrite-tables Drop tables if they already exist

如果表存在则先删除，使用该参数，需要备份时候要备份表结构，不然还原会找不到表

-B, –database An alternative database to restore into

指定数据库恢复的位置，及将A数据库还原到B数据库内

-s, –source-db Database to restore

需要还原的数据库

-e, –enable-binlog Enable binary logging of the restore data

启用二进制日志恢复数据

-h, –host The host to connect to

-u, –user Username with privileges to run the dump

-p, –password User password

-P, –port TCP/IP port to connect to

-S, –socket UNIX domain socket file to use for connection

-t, –threads Number of threads to use, default 4

-C, –compress-protocol Use compression on the MySQL connection

使用压缩协议连接

-V, –version Show the program version and exit

-v, –verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

二，使用

备份:

还原: