一,安装
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
二,使用
备份:
还原: