mydumper安装

mydumper安装
mydumper安装所依赖的软件包，glibc, zlib, pcre, pcre-devel, gcc, gcc-c++, cmake, make, mysql客户端库文件
Mydumper 工具安装

[root@mysql-150 ~]# yum -y install glib2-devel mysql-devel zlib-devel pcre-devel zlib gcc-c++ gcc cmake
[root@mysql-150 ~]# wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
[root@mysql-150 ~]# tar zxf mydumper-0.9.1.tar.gz
[root@mysql-150 ~]# cd mydumper-0.9.1/
[root@mysql-150 mydumper-0.9.1]# pwd
/root/mydumper-0.9.1
[root@mysql-150 mydumper-0.9.1]# cmake .
[root@mysql-150 mydumper-0.9.1]# make
[root@mysql-150 mydumper-0.9.1]# make install
#安装完成后生成两个二进制文件mydumper和myloader位于/usr/local/bin目录下
[root@mysql-150 bin]# ls /usr/local/bin/

mydumper参数详解
Application Options:
-B, --database Database to dump #指定数据库
-T, --tables-list Comma delimited table list to dump (does not exclude regex option) #指定表
-o, --outputdir Directory to output files to #指定备份路径
-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 #指定备份文件切割大小
-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 #使用正则表达式匹配‘db.table‘
-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 #备份定时任务
-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.
–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
-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 #使用socket通信时的socket文件
-t, --threads Number of threads to use, default 4 #开启的备份线程数，默认是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 #输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

PS：mydumper没有—where参数，部分场景不适用

myloader参数详解
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 #指定需要还原的数据库
-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 #使用socket通信时的socket文件
-t, --threads Number of threads to use, default 4 #开启的恢复线程数，默认是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 #输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

样例

备份多线程单表，必须启用-r指定行数拆分单表为多个文件，会生成多个文件

mydumper -u xxx -p xxx -h xxx -P 3307 -B lgjdb -T yyd_users,liangtab -t 4 -r 3000000 -o /root/lgj -c -C -l 200 &

还原数据
myloader -u xxx -p xxx -h xxx -P 3307 -B lgjdb -d /root/lgj