MySQL多线程导入导出 —— mydumper & myloader 简介及常见用法

一、 简介

之前介绍了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

详解介绍mydumper原理-mysql教程-PHP中文网

mydumper备份原理和使用方法 - linuxnote - 博客园

mydumper使用详解_ITPUB博客

MySQL Backup mydumper - H_Johnny - 博客园