一、 简介
之前介绍了mysqldump和mysqlpump两种逻辑备份工具,但还是各有缺点。这里介绍一个更强大的逻辑备份工具mydumper & myloader,前者用于备份或者用于恢复。
mysqldump MySQL导入导出 —— mysqldump 简介及常见用法_Hehuyi_In的博客-CSDN博客
mysqlpump MySQL并行导入导出工具——mysqlpump_Hehuyi_In的博客-CSDN博客_mysqlpump并行导出
1. mydumper优点
- 采用C语言开发,使用glibc库
- 多线程备份,速度据说比mysqldump快10倍
- 多线程恢复
- 支持事务和非事务表的一致性备份(适用于0.2.2以上版本)
- 备份后会生成多个备份文件(因为是多线程备份)
- 备份时对MyISAM表施加FTWRL(FLUSH TABLES WITH READ LOCK),会阻塞DML语句
- 支持快速文件压缩
- 支持导出binlog(新版本已不支持)
- 支持将备份文件切块
- 支持以守护进程模式工作,定时快照和连续二进制日志
2. mydumper缺点
- 不可指定字符集,代码中写死字符集为binary,需要保证源与目标端字符集相同,否则可能出现乱码
- mydumper 工具没有排除选项,可以使用正则选项 --regex 做反向匹配,例如 --regex'^(?!(sys))' 表示不备份sys库
- 快照功能有bug,无法正常使用,会导致内存泄漏
二、 mydumper安装
mydumper安装依赖软件包:glibc, zlib, pcre, pcre-devel, gcc, gcc-c++, cmake, make, mysql客户端库文件
- 安装依赖软件包
yum -y install glib2-devel zlib-devel pcre-devel mysql-devel
- 安装mysql客户端,将客户端库文件路径添加至/etc/ld.so.conf, 如/usr/local/mysql/lib
- 解压软件包进入目录,cmake .
- make && make install
- 安装后 mydumper和myloader位于/usr/local/bin目录下。
三、 参数详解
1. mydumper
-B, --database 要备份的数据库,不指定则备份所有库(information_schema和performance_schema系统库除外)
-T, --tables-list 需要备份的表,名字用逗号隔开,可以用正则表达式
-o, --outputdir 备份文件输出的目录,不指定默认为当前目录
-s, --statement-size 生成的insert语句的字节数,默认1000000字节,超过这个大小会生成新的insert语句
-r, --rows 将表按行分块时,指定多少行数据作为一个块,指定这个选项会关闭 --chunk-filesize
-F, --chunk-filesize 将表按指定大小分块,单位是MB但使用该选项时不用带单位,-F 2即可
-c, --compress 压缩备份
-e, --build-empty-files 即使备份表数据是空,还是产生一个空文件(默认无数据只有表结构文件)
-x, --regex 使用正则表达式匹配备份/不备份的对象
-i, --ignore-engines 指定备份时要忽略的存储引擎,用逗号分割
-m, --no-schemas 不备份表结构
-d, --no-data 不备份表数据
-G, --triggers 备份触发器
-E, --events 备份事件(job)
-R, --routines 备份存储过程和函数
-W, --no-views 不备份视图
-k, --no-locks 不使用临时共享只读锁,这会造成备份数据不一致
--less-locking 最小化对InnoDB表的加锁时间
-l, --long-query-guard 如果备份时遇到某查询执行时间超过该选项指定值还没完成,则退出备份。单位是秒,默认60秒
--kill-long-queries 如果备份时遇到某查询执行时间超过该选项指定值还没完成,kill掉该慢查询,但不退出备份。
-b, --binlogs 导出binlog
-D, --daemon 启用守护进程模式,守护进程模式以某个间隔不间断对数据库进行备份
-I, --snapshot-interval dump快照间隔时间,默认60s,需要在daemon模式下
-L, --logfile 使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
--tz-utc 跨时区是使用的选项,不解释了
--skip-tz-utc 同上
--use-savepoints 使用savepoints来减少采集metadata所造成的锁时间,需要 SUPER 权限
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
-h, --host 连接的主机名
-u, --user 备份所使用的用户
-p, --password 密码
-P, --port 端口
-S, --socket 使用socket通信时的socket文件
-t, --threads 开启的备份线程数,默认是4
-C, --compress-protocol 压缩与mysql通信的数据
-V, --version 显示版本号
-v, --verbose 输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2
2. myloader
-d, --directory 备份文件的文件夹
-q, --queries-per-transaction 每次事物执行的查询数量,默认是1000
-o, --overwrite-tables 如果要恢复的表存在,则先drop掉该表,使用该参数,需要备份时候要备份表结构
-B, --database 需要还原到哪个数据库(目标数据库)
-e, --enable-binlog 指定加载数据时开启数据库实例的binlog记录功能
-h, --host 主机
-u, --user 还原的用户
-p, --password 密码
-P, --port 端口
-s, --source-db 需要还原哪个数据库(源数据库)
-S, --socket socket文件
-t, --threads 还原所使用的线程数,默认是4
-C, --compress-protocol 压缩协议
-V, --version 显示版本
-v, --verbose 输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2
四、 mydumper备份原理及主要流程
- 主线程执行 FLUSH TABLES WITH READ LOCK,施加全局只读锁、阻塞写操作,以保证数据的一致性
- 读取备份点的binlog文件名和日志写入的位置并记录在metadata文件中,以供即使点恢复使用
- N个(线程数可以指定,默认是4)dump线程开启读一致的事务 START TRANSACTION WITH CONSISTENT SNAPSHOT;
- dump non-InnoDB tables,首先导出非事务引擎表
- 主线程 UNLOCK TABLES 非事务引擎表备份完后,释放全局只读锁
- dump InnoDB tables,基于事务导出InnoDB表
- 事务结束
mydumper的less locking模式:
mydumper使用--less-locking可以减少锁等待时间,此时mydumper备份机制大致为:
- 主线程执行 FLUSH TABLES WITH READ LOCK
- Dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT;
- LL Dump线程 LOCK TABLES non-InnoDB (线程内部锁)
- 主线程UNLOCK TABLES
- LL Dump线程 dump non-InnoDB tables
- LL DUmp线程 UNLOCK non-InnoDB
- Dump线程 dump InnoDB tables
下面还有个更详细的,不过感觉过于复杂了
五、 使用案例
1. 备份
备份所有库(及二进制日志):
mydumper -u root -p 'xxxxxDB2015!@#' -o /mnt/backup/
备份指定数据库mysqlhqdb:
mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -o /mnt/backup/
备份多张表(tableA,tableB):
mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA,tableB -o /mnt/backup/
只备份tableA表的数据,不备份表结构
mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA -m -o /mnt/backup/
备份tableA表的数据并进行压缩
mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA -c -o /mnt/backup/
备份所生成的文件
- 所有的备份文件在一个目录中,目录可以自己指定
- 对于一致性备份,会生成一个metadata文件。若是在主库备份,其中会记录备份数据库在备份时间点的binlog文件名和写入位置若是在从库备份,还会记录备份时同步至主库的binlog文件名及写入位置
- 对每个库,会生成一个 dbname-schema-create.sql 文件,存放建库文件
- 对每个表,会生成两个备份文件:dbname.tbname-schema.sql 存放表结构文件,dbname.tbname.sql 表数据文件。如果对表文件分片,将生成多个备份数据文件,可以指定行数或指定大小分片
2. 还原
还原mysqlhqdb库
myloader -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -d /mnt/backup/
还原tableA表
myloader -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -o tableA -d /mnt/backup/
六、 mysqldump和mydumper的速度对比
首先用mydumper进行全库备份,耗时为12分钟
time mydumper -u root -p 'xxxxxDB2015!@#' -o /mnt/backup/
# 输出
real12m19.526s
user3m29.925s
sys0m36.017s
再用mysqldump全库备份,耗时为18分钟
time mysqldump -uroot -p'xxxxxDB2015!@#' --single-transaction --all-databases > /mnt/backup/all.sql
# 输出
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
real18m0.563s
user5m53.119s
sys0m48.295s
mydumper明显比mysqldump快很多。
参考
mydumper工作原理解析_51CTO博客_Mydumper