mysql多线程导出_mydumper 多线程导入导出mysql

今天在线上使用mysqldump将数据表从一个库导入到另外一个库，结果速度特别慢，印象中有个多线程的数据导入导出工具Mydumper，于是简单的调查和测试一下。

下午导数据的过程中，这个表是没有更新的，因此不需要确保多个数据之间的一致性，就简单的写个shell脚本启动多个mysqldumper来导数据，这样有几个问题：

需要处理表数据大小不均匀的问题，有的会很快结束，有的会比较慢。

如果需要保证多个导出之间的一致性时，则无法保证。

Mydumper是一个使用c语言编写的多线程导出导入工具，并且能够保证多个表之间的一致性。Mydumper已经好几篇blog在讨论：Mydumper性能测试，Mydumper使用和源码分析。通过stronghearted的测试，我们看到不是线程越多越好，6个线程的时候速度最快(这个肯定跟机器的配置等诸多因素有关，只能作为一个经验值而不是绝对值，机器好的时候，线程越多越好)。

一、原理

mydumper工作流程图

主要步骤概括

主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，以阻止DML语句写入，保证数据的一致性

读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用 N个(线程数可以指定，默认是4)

dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致的事物

dump non-InnoDB tables, 首先导出非事物引擎的表

主线程 UNLOCK TABLES

非事物引擎备份完后，释放全局只读锁

dump InnoDB tables, 基于事物导出InnoDB表 事物结束

备份所生成的文件

所有的备份文件在一个目录中，目录可以自己指定 目录中包含一个metadata文件

记录了备份数据库在备份时间点的二进制日志文件名，日志的写入位置，

如果是在从库进行备份，还会记录备份时同步至主库的二进制日志文件及写入位置

每个表有两个备份文件：

database.table-schema.sql 表结构文件

database.table.sql 表数据文件

如果对表文件分片，将生成多个备份数据文件，可以指定行数或指定大小分片

Mydumper如何保证数据的一致性？下面是官方给出的解答，摘抄如下，主要是使用flush tables with read lock和start transaction with consistent snapshot，在flush tables with read lock时开启所有的线程，并且通过show master status和show slave status获得当前的position(便于使用Mydumper重建slave以及确保多个表之间的数据一致性)。

Global write lock is acquired (“FLUSH TABLES WITH READ LOCK”)

Various metadata is read (“SHOW SLAVE STATUS”,”SHOW MASTER STATUS”)

Other threads connect and establish snapshots (“START TRANSACTION WITH CONSISTENT SNAPSHOT”)，On pre-4.1.8 it creates dummy InnoDB table, and reads from it.

Once all worker threads announce the snapshot establishment, master executes “UNLOCK TABLES” and starts queueing jobs.

二、安装

下载源码，https://launchpad.net/mydumper

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

cmake .

make && make install

三、使用

Mydumper不能读取/etc/my.cnf中配置文件，需要手工制定用户名、密码等等

mydumper -P 3306 -u admin -p '***' -h db23 -B meituan -T test1,test2,test3,test4,test5 -o ./

myloader -v 3 --threads=6 -P 3306 -u admin -p '***' -h 127.0.0.1 -S /opt/tmp/mysql3306.sock -B test  -d ./

四、测试

对mysqldump和Mydumper做了一个简单测试，测试结果如下(测试结果受环境影响，结果仅供参考)：

5张表，每张表600M。

导出：

远程进行，导出两遍，取最小值。

mysqldump 37s

Mydumper 21s

导入：

mysqldump 14m4s

mydumper 9m4s

五、结论

结论：Mydumper在导出导入过程因为可以多线程进行，因此速度上肯定是优于mysqldump，可以用来替换mysqldump；Mydumper不能读取/etc/my.cnf中的配置文件，这个挺麻烦的，必须进行指定。

六、参数

mydumper参数

-B, --database              要备份的数据库，不指定则备份所有库

-T, --tables-list           需要备份的表，逗号隔开

-o, --outputdir             备份文件输出的目录

-s, --statement-size        生成的insert语句的字节数，默认1000000

-r, --rows                  将表按行分块时，指定的块行数，指定这个选项会关闭 --chunk-filesize

-F, --chunk-filesize        将表按大小分块时，指定的块大小，单位是 MB

-c, --compress              压缩输出文件

-e, --build-empty-files     如果表数据是空，还是产生一个空文件(默认无数据则只有表结构文件)

-x, --regex                 是同正则表达式匹配 'db.table'

-i, --ignore-engines        忽略的存储引擎，逗号分割

-m, --no-schemas            不备份表结构

-k, --no-locks              不使用临时共享只读锁，使用这个选项会造成数据不一致

--less-locking              减少对InnoDB表的锁施加时间

-l, --long-query-guard      设定阻塞备份的长查询超时时间，单位是秒，默认是60秒(超时后默认mydumper将会退出)

--kill-long-queries         杀掉长查询 (不退出)

-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, --pass                  密码

-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

myloader使用参数

-d, --directory                   备份文件的文件夹

-q, --queries-per-transaction     每次事物执行的查询数量，默认是1000

-o, --overwrite-tables            如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构

-B, --database                    需要还原的数据库

-e, --enable-binlog               启用还原数据的二进制日志

-h, --host                        主机

-u, --user                        还原的用户

-p, --pass                        密码

-P, --port                        端口

-S, --socket

-t, --threads                     还原所使用的线程数，默认是4

-C, --compress-protocol           压缩协议

-V, --version                     显示版本

-v, --verbose                     输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

less locking模式

mydumper使用--less-locking可以减少锁等待时间，此时mydumper的执行机制大致为

主线程 FLUSH TABLES WITH READ LOCK (全局锁)

Dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT;

Dump线程 LOCK TABLES non-InnoDB (线程内部锁)

主线程UNLOCK TABLES

Dump线程 dump non-InnoDB tables

DUmp线程 UNLOCK non-InnoDB

Dump线程 dump InnoDB tables