【MySQL】单表支持并行导出的工具--mydumper

【MySQL】单表支持并行导出的工具–mydumper
下载和安装：

https://github.com/maxbube/mydumper

RHEL6.5源码安装mydumper 0.9.5：

yum install -y cmake*
yum install -y glib2-devel zlib-devel pcre-devel openssl-devel
yum install -y mysql mysql-devel mysql-libs
unzip mydumper-0.9.5.zip
cd mydumper-0.9.5
cmake .
make
make install
注意：

  1、若安装不成功，多半是因为依赖包的原因，其中mysql-devel、mysql-libs包是必须安装的，或者也可以设置：

vi /etc/profile
export PS1="[\u@\h \W]$ "
export MYSQL_HOME=/usr/local/mysql57/mysql5719
export PATH=$PATH:$MYSQL_HOME/bin
export CLASSPATH=$CLASSPATH:$MYSQL_HOME/lib
source /etc/profile
2 、请使用网络yum源更新相关的依赖包，对于RHEL6.5，应该使用网络YUM更新相关的依赖包

—配置rhel6.5网络yum源
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
sed -i ‘s/$releasever/6/g’ /etc/yum.repos.d/CentOS-Base.repo
yum clean all
yum list
yum install ca-certificates

安装过程中的异常

错误1：

CMake Error: The following variables are used in this project, but they are set to NOTFOUND.

Please set them or make sure they are set and tested correctly in the CMake files:

PCRE_INCLUDE_DIR (ADVANCED)

used as include directory in directory /root/mydumper-0.6.2

PCRE_PCRE_LIBRARY (ADVANCED)

linked by target "mydumper" in directory /root/mydumper-0.6.2

linked by target "myloader" in directory /root/mydumper-0.6.2

###需要安装pcre-devel包，yum install pcre-devel

错误2：

CMake Error: The following variables are used in this project, but they are set to NOTFOUND.

Please set them or make sure they are set and tested correctly in the CMake files:

MYSQL_INCLUDE_DIR (ADVANCED)

used as include directory in directory /home/robin/mydumper-0.6.2

used as include directory in directory /home/robin/mydumper-0.6.2

used as include directory in directory /home/robin/mydumper-0.6.2

###出现上述错误，是由于没有配置MYSQL_INCLUDE_DIR目录。

###如果是编译安装到非缺省路径，可以将mysql安装路径添加到/etc/profile或者家目录~/.bash_profile后source生效

错误3：

[root@GZ-APP-BAK01 ~]# mydumper --help|more

mydumper: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

[root@GZ-APP-BAK01 ~]# mydumper

mydumper: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

###出现上述错误，应考虑建立软链

which libmysqlclient.so.18

/app/soft/mysql/lib/libmysqlclient.so.18

ln -s /app/soft/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18

错误4：

mydumper -uusr1 -ppwd -B blos -o /tmp/bak

option parsing failed: Error parsing option -r, try --help

###参数和字符串不能连着写，笔者刚开始是也是丈二和尚摸不着头脑。

mydumper --version

mydumper 0.6.2, built against MySQL 5.6.22

相比mysqldump，其优势如下：

1 速度快（好多资料都说快10倍，个人持保留意见)；

2 支持多线程导出和导入myloader；

3 采用守护进程方式运行，可定时扫描和快照binlog(-snapshot-interval)；

4 快速文件压缩；

一般做mysql逻辑备份用mysqldump比较多，但是有一款叫mydumper的工具速度上会mysqldump快很多，具体优点为：

支持多线程备份

支持文件压缩

支持多线程恢复

保证数据的一致性

比mysqldump备份速度和恢复速度都要快。

特性如下：

Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)

Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)

Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc

Manageability - supports PCRE for specifying database and tables inclusions and exclusions

并行导出(默认4个)、易于管理(表数据和元数据分开导出管理)、一致性、可指定或者排除某些数据库或者表的操作

也有稍许不足之处，如mydumper不处理MySQL的视图，触发器和存储过程，因此使用mydumper去导出schema并不可靠。

实际生产环境中，建议mydumper仅用于导出数据(使用 --no-schemas )，而通过mysqldump来导出schema。

mydumper除了为每个表会分别生成table.sql和table-schema.sql，还会生成一个.metadata文件，记录dump的开始和结束时间，以及binlog位置信息。

安装：

[root@iZ252affh58Z ~]# tar -zxvf mydumper-0.9.1.tar.gz
mydumper-0.9.1/CMakeLists.txt
mydumper-0.9.1/README
mydumper-0.9.1/binlog.c
mydumper-0.9.1/binlog.h
mydumper-0.9.1/cmake/
mydumper-0.9.1/common.h
mydumper-0.9.1/config.h. in
mydumper-0.9.1/docs/
mydumper-0.9.1/g_unix_signal.c
mydumper-0.9.1/g_unix_signal.h
mydumper-0.9.1/mydumper.c
mydumper-0.9.1/mydumper.h
mydumper-0.9.1/myloader.c
mydumper-0.9.1/myloader.h
mydumper-0.9.1/server_detect.c
mydumper-0.9.1/server_detect.h
mydumper-0.9.1/cmake/modules/
mydumper-0.9.1/cmake/modules/CppcheckTargets.cmake
mydumper-0.9.1/cmake/modules/FindGLIB2.cmake
mydumper-0.9.1/cmake/modules/FindMySQL.cmake
mydumper-0.9.1/cmake/modules/FindPCRE.cmake
mydumper-0.9.1/cmake/modules/FindSphinx.cmake
mydumper-0.9.1/cmake/modules/Findcppcheck.cmake
mydumper-0.9.1/cmake/modules/Findcppcheck.cpp
mydumper-0.9.1/docs/CMakeLists.txt
mydumper-0.9.1/docs/_build/
mydumper-0.9.1/docs/_static/
mydumper-0.9.1/docs/authors.rst
mydumper-0.9.1/docs/compiling.rst
mydumper-0.9.1/docs/examples.rst
mydumper-0.9.1/docs/files.rst
mydumper-0.9.1/docs/ index .rst
mydumper-0.9.1/docs/mydumper_usage.rst
mydumper-0.9.1/docs/myloader_usage.rst
mydumper-0.9.1/docs/_build/conf.py. in
mydumper-0.9.1/docs/_build/sources.cmake. in
[root@iZ252affh58Z ~]# cd mydumper-0.9.1
[root@iZ252affh58Z mydumper-0.9.1]# cmake .
– The C compiler identification is GNU 4.4.7
– The CXX compiler identification is GNU 4.4.7
– Check for working C compiler: /usr/bin/cc
– Check for working C compiler: /usr/bin/cc – works
– Detecting C compiler ABI info
– Detecting C compiler ABI info - done
– Check for working CXX compiler: /usr/bin/c++
– Check for working CXX compiler: /usr/bin/c++ – works
– Detecting CXX compiler ABI info
– Detecting CXX compiler ABI info - done
– Using mysql-config: /mnt/software/mysql/bin/mysql_config
– Found MySQL: /mnt/software/mysql/include, /mnt/software/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
– Found ZLIB: /usr/lib64/libz.so (found version “1.2.3”)
– Found PkgConfig: /usr/bin/pkg-config (found version “0.23”)
– checking for one of the modules ‘glib-2.0’
– checking for one of the modules ‘gthread-2.0’
– checking for module ‘libpcre’
– found libpcre, version 7.8
– Found PCRE: /usr/include
CMake Warning at docs/CMakeLists.txt:9 (message):
Unable to find Sphinx documentation generator

---

– MYSQL_CONFIG = /mnt/software/mysql/bin/mysql_config
– CMAKE_INSTALL_PREFIX = /usr/local
– BUILD_DOCS = ON
– WITH_BINLOG = OFF
– RUN_CPPCHECK = OFF
– Change a values with: cmake -D=

---

–
– Configuring done
– Generating done
– Build files have been written to: /root/mydumper-0.9.1
[root@iZ252affh58Z mydumper-0.9.1]# make
Scanning dependencies of target mydumper
[ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ 75%] Built target mydumper
Scanning dependencies of target myloader
[100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Linking C executable myloader
[100%] Built target myloader
[root@iZ252affh58Z mydumper-0.9.1]# make install
[ 75%] Built target mydumper
[100%] Built target myloader
Install the project…
– Install configuration: “”
– Installing: /usr/local/bin/mydumper
– Removed runtime path from “/usr/local/bin/mydumper”
– Installing: /usr/local/bin/myloader
– Removed runtime path from “/usr/local/bin/myloader”
确认是否安装成功：缺少libmysqlclient.so.20

[root@iZ252affh58Z mydumper-0.9.1]# whereis mydumper
mydumper: /usr/ local /bin/mydumper
[root@iZ252affh58Z mydumper-0.9.1]#
[root@iZ252affh58Z mydumper-0.9.1]# ldd /usr/ local /bin/mydumper
linux-vdso.so.1 => (0x00007fffe131a000)
libmysqlclient.so.20 => not found
libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003541e00000)
libm.so.6 => /lib64/libm.so.6 (0x0000003542600000)
librt.so.1 => /lib64/librt.so.1 (0x0000003542a00000)
libdl.so.2 => /lib64/libdl.so.2 (0x0000003542200000)
libglib-2.0.so.0 => /lib64/libglib-2.0.so.0 (0x00007f1a283ad000)
libgthread-2.0.so.0 => /lib64/libgthread-2.0.so.0 (0x00007f1a281a9000)
libpcre.so.0 => /lib64/libpcre.so.0 (0x00007f1a27f7b000)
libz.so.1 => /lib64/libz.so.1 (0x0000003542e00000)
libc.so.6 => /lib64/libc.so.6 (0x0000003541a00000)
/lib64/ld-linux-x86-64.so.2 (0x0000003541600000)
解决办法：

[root@iZ252affh58Z mydumper-0.9.1]# locate libmysqlclient.so.20
/mnt/software/mysql/lib/libmysqlclient.so.20
/mnt/software/mysql/lib/libmysqlclient.so.20.2.1
[root@iZ252affh58Z mydumper-0.9.1]# cp /mnt/software/mysql/lib/libmysqlclient.so.20 /usr/lib64
[root@iZ252affh58Z mydumper-0.9.1]#
[root@iZ252affh58Z mydumper-0.9.1]# mydumper
** (mydumper:25013): CRITICAL **: Error connecting to database : Access denied for user ‘root’ @ ‘localhost’ (using password : NO )
查看版本号，已经安装成功了

[root@iZ252affh58Z mydumper-0.9.1]# mydumper -V
mydumper 0.9.1, built against MySQL 5.7.12
查看帮助：

[root@iZ252affh58Z mydumper-0.9.1]# mydumper --help
Usage:
mydumper [ OPTION …] multi-threaded MySQL dumping
Help Options:
-?, --help Show help options
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
-r, --rows Try to split tables into chunks of this many rows. This option turns off --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
-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.
-l, --long-query-guard Set long query timer in seconds, default 60
-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
-L, --logfile Log file name to use, by default stdout is used
–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
–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
-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
备份全库：

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/
还原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分钟

[root@iZ252affh58Z mnt]# time mydumper -u root -p ‘xxxxxDB2015!@#’ -o /mnt/backup/
real12m19.526s
user3m29.925s
sys0m36.017s
再用mysqldump全库备份，耗时为18分钟

[root@iZ252affh58Z mnt]# 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快很多。

参数

-long-query-guard：长查询上限(默认60s)，如果当前数据库存在运行时间大于此参数的查询则退出mydumper；

-kill-long-queries：杀死查出的长查询；

-daemon：启用守护进程模式；

-snapshot-interval：快照时间间隔，默认60s；

-t, --threads 使用的线程数，默认4

-C, --compress-protocol 在mysql连接上使用压缩

-e, --enable-binlog 启用二进制恢复数据

-r, --rows 将表分成多个文件批次导出，

1 确定表的行数，依据pk – uk – cardinality最高的索引(show index from table)选择索引，通过explain select index from table的rows字段确定行数；

2 已经total_rows和rows将表分成若干分块，每个分块可由不同worker并行执行，适用于大表；

–备份 导出到文件中

-m, --no-schemas Do not dump table schemas with the data --仅导出数据 无元数据

-d, --no-data Do not dump table data --仅导出元数据 无数据

  --trx-consistency-only      Transactional consistency only       --事物一致 获取开始导出时的数据快照 innodb适用

-t, --threads Number of threads to use, default 4 --线程数，默认4个

–全量备份

[root@hostmysql-m mysqlbkp]# mydumper -u root -p RootXXXX --outputdir db_all_mydumper/

–查看导出的文件，元数据和表数据分开的文件

[root@hostmysql-m db_all_mydumper]# ll -h test_conver_table_2

-rw-r–r-- 1 root root 239 Dec 4 16:09 flydb.test_conver_table_2-schema.sql

-rw-r–r-- 1 root root 199M Dec 4 16:09 flydb.test_conver_table_2.sql

–按库备份 flydb

[root@hostmysql-m mysqlbkp]# mydumper -u root -p RootXXXX --database flydb --outputdir flydb_mydumper/

–按表备份 flydb.t_test_blob

[root@hostmysql-m mysqlbkp]# mydumper -u root -p RootXXXX --database flydb --tables-list test_conver_table_1 --rows=10000 --threads 8 --outputdir flydb_test_conver_table_1_mydumper/

–时间点 恢复测试

–在建立备份时，只记录开始备份时的状态，所以在开始备份后 所产生的操作不在备份中，需要利用二进制日志执行时间点的恢复操作

–恢复测试流程如下：

           备份单库flydb，

           备份后建立新表test_conver_table_bak， 

           删除库flydb， 

           从备份中恢复flydb，

           利用二进制日志恢复 备份后新建立表的操作

–按库备份 flydb

[root@hostmysql-m mysqlbkp]# mydumper -u root -p RootXXXX --database flydb --outputdir flydb_all_mydumper/

–备份后，建立test_conver_table_bak

mysql> create table test_conver_table_bak as select * from test_conver_table where 1=2;

Query OK, 0 rows affected (0.45 sec)

Records: 0 Duplicates: 0 Warnings: 0

–删除flydb 库

mysql> drop database flydb;

Query OK, 49 rows affected (3.93 sec)

–从备份文件中 恢复flydb库

[root@hostmysql-m mysqlbkp]# myloader --directory=flydb_all_mydumper/ -u root -p Root123$

–恢复完成后，查看备份后的新建表test_conver_table_bak并不存在

mysql> select * from test_conver_table_bak;

ERROR 1146 (42S02): Table ‘flydb.test_conver_table_bak’ doesn’t exist

–查看备份元数据文件的position，应用binlog 从备份后重做一遍所有操作

[root@hostmysql-m flydb_all_mydumper]# more metadata

Started dump at: 2018-12-05 10:02:27

SHOW MASTER STATUS:

    Log: mysql-bin.000102

    Pos: 473

    GTID:

Finished dump at: 2018-12-05 10:03:10

–查看mysql-bin.000102二进制日志，把473之后 到775 删除flydb之前的 都重做一遍

[root@hostmysql-m mysql]# mysqlbinlog -vv mysql-bin.000102

at 473

#181205 10:04:48 server id 1 end_log_pos 538 CRC32 0x4d14afc8 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no

SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 538

#181205 10:04:48 server id 1 end_log_pos 710 CRC32 0xcf3438f4 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1543975488/!/;

CREATE TABLE test_conver_table_bak (

a int(11) DEFAULT NULL,

b varchar(10) DEFAULT NULL

)

/!/;

at 710

#181205 10:08:27 server id 1 end_log_pos 775 CRC32 0x5db739dd Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no

SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 775

#181205 10:08:27 server id 1 end_log_pos 875 CRC32 0x10260fcb Query thread_id=4 exec_time=3 error_code=0

SET TIMESTAMP=1543975707/!/;

drop database flydb

/!/;

SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;

DELIMITER ;

End of log file

/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;

/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;

–具体的执行时间点恢复操作，应用二进制日志恢复

[root@hostmysql-m mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000102 --start-position=473 --stop-position=710 --disable-log-bin | mysql -uroot -pRootXXXX

–再次查看表恢复成功

mysql> select count(1) from test_conver_table_bak;

±---------+

| count(1) |

±---------+

| 0 |

±---------+

1 row in set (0.04 sec)

安装

1 安装cmake，apt-get install cmake；

2 安装兼容包，如apt-get install libglib2.0-dev libmysqlclient15-dev，具体参照官网；

3 下载安装包并编译

#wget http://launchpad.net/mydumper/0.2/0.2.3/+download/mydumper-0.2.3.tar.gz

#tar xzvf mydumper-0.2.3.tar.gz

#cd mydumper-0.2.3/

#cmake .

#make

#make install

原理

Mysqldump是个单线程工具，只能逐个导出表，而mydumper支持多线程并行导出；

尽管mydumper主线程已经登录进入了mysql，但worker子线程必须再登录一次，因为libmysql是线程不安全的?

工作流程

1 连接目标数据库；

2 通过show processlist来判断是否有长查询，如果有长查询则退出dump(通过-long-query-guard指定)，或者使用-kill-long-queries杀掉长查询；

3 锁定myisam表，flush tables with read lock； 针对innodb table开启事务，start transaction；

4 创建worker子线程；

5 确定候选表，根据类别分别插入innodb_table，non_innodb_table以及table_schemas链表(表结构)；

6 将候选表通过g_async_queue_push加入任务队列(队列最后元素是thread shutdown)，由worker子线程从队列中读取表信息并执行数据导出

for(;😉 {

....

job=(struct job *)g_async_queue_pop(conf->queue);

....

switch (job->type) {

    case JOB_DUMP:

        ....

        dump_table_data_file(thrconn, tj->database, tj->table, tj->where, tj->filename);

        ....

    case JOB_DUMP_NON_INNODB:

        ....

        dump_table_data_file(thrconn, tj->database, tj->table, tj->where, tj->filename);

    case JOB_SCHEMA:

        ....

        dump_schema_data(thrconn, sj->database, sj->table, sj->filename);

}

当worker读取到job_shutdown任务时，则会执行如下

case JOB_SHUTDOWN:

g_message("Thread %d shutting down", td->thread_id);

if (thrconn)

    mysql_close(thrconn);

g_free(job);

mysql_thread_end();

return NULL;

break;

7 执行unlock tables，处理完myisam表后立即解锁，以减少锁定时间；

Main_thread：记录myisam数量

for (non_innodb_table= g_list_first(non_innodb_table); non_innodb_table; non_innodb_table= g_list_next(non_innodb_table)) {

    dbt= (struct db_table*) non_innodb_table->data;

    dump_table(conn, dbt->database, dbt->table, &conf, FALSE);

    g_atomic_int_inc(&non_innodb_table_counter);

}

child_thread：当队列中myisam为0时，加入unlock_tables任务

if (g_atomic_int_dec_and_test(&non_innodb_table_counter) && g_atomic_int_get(&non_innodb_done)) {

    g_async_queue_push(conf->unlock_tables, GINT_TO_POINTER(1));

}

main_thread：主线程读取unlock_tables任务并执行

g_async_queue_pop(conf.unlock_tables);

g_message(“Non-InnoDB dump complete, unlocking tables”);

mysql_query(conn, “UNLOCK TABLES”);

8 等待worker退出；

Myloader将数据导入数据库，原理与mydumper类似。

-queries-per-transaction

-directory

参考资料

http://www.hoterran.info/mydumper_usage

http://blogread.cn/it/article/4071

http://centminmod.com/mydumper.html

MySQL备份之【mydumper 学习】
MySQL在备份方面包含了自身的mysqldump工具，但其只支持单线程工作，这就使得它无法迅速的备份数据。而 mydumper作为一个实用工具，能够良好支持多线程工作，这使得它在处理速度方面十倍于传统的mysqldump。其特征之一是在处理过程中需要对列表加以锁定，因此如果我们需要在工作时段执行备份工作，那么会引起DML阻塞。但一般现在的MySQL都有主从，备份也大部分在从上进行，所以锁的问题可以不用考虑。这样，mydumper能更好的完成备份任务。

###更新（2016-04-01）###

注意： mydumper的多线程备份是基于表的，所以当只有一张表或99张是小表，1张是超级大表，mydumper不如mysqldump，甚至更慢。 其实mydumper是支持对一张表多个线程备份的，参数-r。

Mydumper主要特性：是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具，开发人员主要来自MySQL,Facebook,SkySQL公司。

1：轻量级C语言写的
2：执行速度比mysqldump快10倍
3：事务性和非事务性表一致的快照(适用于0.2.2以上版本)
4：快速的文件压缩
5：支持导出binlog
6：多线程恢复(适用于0.2.1以上版本)
7：以守护进程的工作方式，定时快照和连续二进制日志(适用于0.5.0以上版本)
8：开源 (GNU GPLv3)

下载安装：

环境：Ubuntu 12.04

下载：

wget https://launchpad.net/mydumper/0.5/0.5.2/+download/mydumper-0.5.2.tar.gz
安装：解压后，在README中安装说明

apt-get install cmake make libglib2.0-dev libmysqlclient15-dev zlib1g-dev libpcre3-dev g++

cmake .

make
View Code
生成2个工具：mydumper（备份），myloader（导入），放入到bin目录下。

参数：

mydumper（0.5.2）：

root@dd:~/mydumper-0.5.2# ./mydumper --help
Usage:
mydumper [OPTION…] multi-threaded MySQL dumping
Help Options:
-?, --help Show help optionsApplication Options:
-B, --database 需要备份的库
-T, --tables-list 需要备份的表，用逗号分隔
-o, --outputdir 输出文件的目录
-s, --statement-size 生成插入语句的字节数, 默认 1000000，这个参数不能太小，不然会报 Row bigger than statement_size for tools.t_serverinfo
-r, --rows 分裂成很多行块表
-c, --compress 压缩输出文件
-e, --build-empty-files 即使表没有数据，还是产生一个空文件
-x, --regex 正则表达式: ‘db.table’
-i, --ignore-engines 忽略的存储引擎，用逗号分隔
-m, --no-schemas 不导出表结构
-k, --no-locks 不执行共享读锁 警告：这将导致不一致的备份
-l, --long-query-guard 设置长查询时间,默认60秒，超过该时间则会报错：There are queries in PROCESSLIST running longer than 60s, aborting dump
–kill-long-queries kill掉长时间执行的查询
-b, --binlogs 导出binlog
-D, --daemon 启用守护进程模式
-I, --snapshot-interval dump快照间隔时间，默认60s，需要在daemon模式下
-L, --logfile 日志文件
-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 使用的线程数，默认4
-C, --compress-protocol 在mysql连接上使用压缩协议
-V, --version Show the program version and exit
-v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
myloader（0.5.2）：

root@dd:~/mydumper-0.5.2# ./myloader --help
Usage:
myloader [OPTION…] multi-threaded MySQL loader
Help Options:
-?, --help Show help optionsApplication Options:
-d, --directory 备份文件所在的目录
-q, --queries-per-transaction 每次执行查询数量, 默认1000
-o, --overwrite-tables 如果表存在则先删除。这里注意下，使用该参数，需要备份时候要备份表结构，不然会出问题
-B, --database 指定需要还原的数据库
-e, --enable-binlog 启用二进制恢复数据
-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 使用的线程数量，默认4
-C, --compress-protocol 连接上使用压缩协议
-V, --version Show the program version and exit
-v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
###更新（2016-04-01）###

上面介绍的是0.5.2版本，目前版本已经更新到了0.9.1，新增了一些参数，现在对新参数了解一下：

mydumper（0.9.1）：

root@op3:/home/zhoujy# mydumper --help

Usage:

mydumper [OPTION…] multi-threaded MySQL dumping

Help Options:

-?, --help Show help options

Application Options:

-B, --database 需要备份的数据库，一个数据库一条命令备份，要不就是备份所有数据库，包括mysql。

-T, --tables-list 需要备份的表，用逗号分隔。

-o, --outputdir 备份文件目录

-s, --statement-size 生成插入语句的字节数，默认1000000，这个参数不能太小，不然会报 Row bigger than statement_size for tools.t_serverinfo

-r, --rows 试图用行块来分割表，该参数关闭–chunk-filesize

-F, --chunk-filesize 行块分割表的文件大小，单位是MB

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

-e, --build-empty-files 即使表没有数据，也产生一个空文件

-x, --regex 正则表达式匹配，如’db.table’

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

-m, --no-schemas 不导出表结构

-d, --no-data 不导出表数据

-G, --triggers 导出触发器

-E, --events 导出事件

-R, --routines 导出存储过程

-k, --no-locks 不执行共享读锁 警告：这将导致不一致的备份

–less-locking 减到最小的锁在innodb表上.

-l, --long-query-guard 设置长查询时间,默认60秒，超过该时间则会报错：There are queries in PROCESSLIST running longer than 60s, aborting dump

-K, --kill-long-queries kill掉长时间执行的查询，备份报错：Lock wait timeout exceeded; try restarting transaction

-D, --daemon 启用守护进程模式

-I, --snapshot-interval dump快照间隔时间，默认60s，需要在daemon模式下

-L, --logfile 使用日志文件，默认标准输出到终端

–tz-utc 备份的时候允许备份Timestamp，这样会导致不同时区的备份还原会出问题，默认关闭，参数：–skip-tz-utc to disable.

–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

–lock-all-tables 锁全表，代替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 备份执行的线程数,默认4个线程

-C, --compress-protocol 在mysql连接上使用压缩协议

-V, --version Show the program version and exit

-v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

加粗部分是新增加的参数。

myloader（0.9.1）：

root@op:~# myloader --help

Usage:

myloader [OPTION…] multi-threaded MySQL loader

Help Options:

-?, --help Show help options

Application Options:

-d, --directory 备份文件所在的目录

-q, --queries-per-transaction 每个事务的query数量, 默认1000

-o, --overwrite-tables 如果表存在则先删除，使用该参数，需要备份时候要备份表结构，不然还原会找不到表

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

-s, --source-db 还原的数据库

-e, --enable-binlog 启用二进制日志恢复数据

-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 使用的线程数量，默认4

-C, --compress-protocol 连接上使用压缩协议

-V, --version Show the program version and exit

-v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

加粗部分是新增加的参数。

测试： 测试基本用法

1:备份

./mydumper -u zjy -p ##### -h 192.168.220.245 -P 3306 -B chushihua -o /home/zhoujy/bak/
备份analyzedxy数据库到/home/zhoujy/bak/ 目录中， 查看是否多线程：

| 4937639 | zjy | 192.168.200.25:34781 | NULL| Query | 0 | NULL | show processlist |
| 4937677 | zjy | 192.168.200.25:34791 | NULL| Query | 10 | Writing to net | SELECT /*!40001 SQL_NO_CACHE /|
| 4937678 | zjy | 192.168.200.25:34792 | NULL| Query | 5 | Writing to net | SELECT /!40001 SQL_NO_CACHE /|
| 4937679 | zjy | 192.168.200.25:34793 | NULL| Query | 10 | Writing to net | SELECT /!40001 SQL_NO_CACHE /|
| 4937680 | zjy | 192.168.200.25:34794 | NULL| Query | 10 | Writing to net | SELECT /!40001 SQL_NO_CACHE */|

上面显示确实是 4个线程（默认）在备份，查看备份文件：

root:homezhoujybak# ls
分析： mydumper把数据和表结构分开备份，并且把二进制日志备份出来单独放到一个文件中。

metadata:元数据 记录备份开始和结束时间，以及binlog日志文件位置。
table data:每个表一个文件

table schemas:表结构文件
binary logs: 启用–binlogs选项后，二进制文件存放在binlog_snapshot目录下
daemon mode:在这个模式下，有五个目录0，1，binlogs，binlog_snapshot，last_dump。
备份目录是0和1，间隔备份，如果mydumper因某种原因失败而仍然有一个好的快照，当快照完成后，last_dump指向该备份。
2：还原： 还原到另一台服务器，先建立要还原的数据库(chushihua)

./myloader -u root -p 123456 -h 192.168.200.25 -P 3307 -B chushihua -d /home/zhoujy/bak/
和备份一样查看 是否多线程：

| 19 | root | | NULL | Query | 0 | init | show processlist|| 30 | root | | chushihua | Query | 5 | update| INSERT INTO || 31 | root | | chushihua | Query | 5 | update| INSERT INTO || 32 | root | | chushihua | Query | 5 | update| INSERT INTO || 33 | root | | chushihua | Query | 5 | update| INSERT INTO |
上面显示确实是 4个线程（默认）在还原。

进一步测试： 测试一些常用的参数

1）：备份指定表（-T），并且不要导出表结构（-m）

复制代码
./mydumper -u root-p 123456 -h 192.168.220.252 -P 3306 -m -B test -T b,a,c,d,e,g,f,h,i -o /home/zhoujy/bak/zhoujy@zhoujy:~/bak$ ls -lh-rw-rw-r-- 1 zhoujy zhoujy 3.4K 2013-11-14 20:57 test.a.sql-rw-rw-r-- 1 zhoujy zhoujy 1.6M 2013-11-14 20:57 test.b.sql-rw-rw-r-- 1 zhoujy zhoujy 7.8M 2013-11-14 20:57 test.c.sql-rw-rw-r-- 1 zhoujy zhoujy 1.7M 2013-11-14 20:57 test.d.sql-rw-rw-r-- 1 zhoujy zhoujy 303K 2013-11-14 20:57 test.e.sql-rw-rw-r-- 1 zhoujy zhoujy 517K 2013-11-14 20:57 test.f.sql-rw-rw-r-- 1 zhoujy zhoujy 646K 2013-11-14 20:57 test.g.sql-rw-rw-r-- 1 zhoujy zhoujy 394K 2013-11-14 20:57 test.h.sql-rw-rw-r-- 1 zhoujy zhoujy 34K 2013-11-14 20:57 test.i.sql-rw-rw-r-- 1 zhoujy zhoujy 75 2013-11-14 20:57 metadata
复制代码
2）压缩备份文件（-c），备份binlog（-b），正则表达式备份表（-x）

复制代码
./mydumper -u root -p 123456 -h 192.168.200.25 -P 3306 -m -c -b --regex=tmp.* -B test -o /home/zhoujy/bak/drwx------ 2 zhoujy zhoujy 4.0K 2013-11-14 21:16 binlog_snapshot-rw-rw-r-- 1 zhoujy zhoujy 133 2013-11-14 21:16 metadata-rw-rw-r-- 1 zhoujy zhoujy 94K 2013-11-14 21:16 test.tmp_0808.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 75K 2013-11-14 21:16 test.tmp_0809.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 25K 2013-11-14 21:16 test.tmp_0813.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 208K 2013-11-14 21:16 test.tmp_0826.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 915 2013-11-14 21:16 test.tmp_0827.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 901 2013-11-14 21:16 test.tmp_0912.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 2.1K 2013-11-14 21:16 test.tmp_0916.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 622K 2013-11-14 21:16 test.tmp_0918_a.sql.gz-rw-rw-r-- 1 zhoujy zhoujy 28M 2013-11-14 21:16 test.tmp_0918_ff.sql.gz
复制代码
如上所示，备份文件已经是压缩的了（用gzip -d 解压），并且备份出了tmp.*匹配出来的所有表，二进制日志也被备份到了binlog_snapshot文件中，并且也是被压缩的。

###更新（2016-04-01）###

新版本里已经 不能备份binlog了，没有-b参数。这里说明下 备份指定数据库的方法： --regex 正则匹配

#指定备份数据库：备份abc、bcd、cdemydumper -u backup -p 123456 -h 192.168.180.13 -P 3306 -t 3 -c -l 3600 -s 10000000 -e --regex ‘abc|bcd|cde’ -o bbb/#指定不备份的数据库：不备份abc、mysql、test，备份其他数据库 mydumper -u backup -p 123456 -h 192.168.180.13 -P 3306 -t 3 -c -l 3600 -s 10000000 -e --regex ‘^(?!(abc|mysql|test))’ -o bbb/
3）还原，表存在先删除（-o）：这里需要 注意，使用该参数，备份目录里面需要有表结构的备份文件。

./myloader -u root -p 123456 -h 192.168.200.25 -P 3306 -o -B test -d /home/zhoujy/bak/
更多的参数效果，请自己测试。

最后测试：用mysqldump和mydumper进行对比测试。

View Code
测试了2个数据库：

1：

mysqldump Cost Time ：162s
mydumper Cost Time ：61s

2：

mysqldump Cost Time ：483s
mydumper Cost Time ：337s

从上面的时间来看，mydumper 确实提升了备份数据，还原也同理。

2016-07-20 更新

mydumper支持一张表多个线程以chunk的方式批量导出，参数-r：试图用行块来分割表，该参数关闭–chunk-filesize参数。如：

mydumper -u zjy -p xxx -h 192.168.123.70 -P 3306 -t 5 -c -r 300000 -l 3600 -s 10000000 -B vs -o /home/zhoujy/vs/
表示每个线程用300000行块来分割表，通过show processlist 看到：5个线程备份

复制代码
Sending data
virtual_station.Sending client `virtual_station`. Sending client
virtual_station.Sending client `virtual_station`. Sending data virtual_station.``
复制代码
这个可以更好的备份数据库，不管数据库里是否有大表。

总结：

从上面的测试分析中看出mydumper可以提升备份还原的效率，虽然是多线程操作，但是提升多少受限于磁盘的IO能力，在使用前做好磁盘IO的评估，大家可以尝试使用该工具。

更多信息见：

http://blog.csdn.net/leshami/article/details/46815553

http://www.hoterran.info/mydumper_usage

http://www.ttlsa.com/html/1077.html

http://blog.sina.com.cn/s/blog_534360f5010157rx.html

测试环境说明：

OS Centos 6.4 X86_64 2U 8 Core 24 Threads
MYSQL 5.5.34
备份盘：普通SAS盘 10k 2块 Raid 1
MYSQL 数据文件盘： Intel® SSD DC S3500 Series Raid 1

下载mydumper 源文件并解压
wget https://launchpadlibrarian.net/123439581/mydumper-0.5.2.tar.gz

安装依赖包
yum install cmake glib2-devel pcre-devel zlib-devel [mysql-devel 这里已经安装过]

[root@host105 mydumper-0.5.2]# cmake . ###这里cmake 后面有一个点（.）表示当前目录

[root@host105 mydumper-0.5.2]# cmake .
省略部分输出 … …
– Configuring done
– Generating done
– Build files have been written to: /home/lidan/mydumper-0.5.2

看到这cmake基本上就可以说成功了
然后
[root@host105 mydumper-0.5.2]# make&&make install
省略部分输出 … …
[ 80%] Built target mydumper
[100%] Built target myloader
Install the project…
– Install configuration: “”
– Installing: /usr/local/bin/mydumper

– Installing: /usr/local/bin/myloader

之后使用/usr/local/bin/mydumper --help 检查是否可用

测试数据库大小220M

注意：注意账号密码和密码与选项间的空格是必须的

单 线程无压缩
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -t 1 -o /data/mysql_bak/

real 0m9.221s
user 0m2.122s

sys 0m0.299s

备份出的文件大小：190M
drwx------ 2 root root 4096 Mar 8 13:25 binlog_snapshot --拷贝的binlog文件（该binlog表示的是备份镜像之前的所有binlog）
-rw-r–r-- 1 root root 210 Mar 8 13:25 metadata --binlog的信息以及复制到master的位置信息
rw-r–r-- 1 root root 417 Mar 8 21:00 yiqifa_bs_push.yiqifa_commission_qq-schema.sql --dump表结构文件
-rw-r–r-- 1 root root 206 Mar 8 21:00 yiqifa_bs_push.yiqifa_commission_qq.sql --dump数据文件
… …

其中metadata记录的信息如下：
SHOW MASTER STATUS:
Log: mysql-bin.000003
Pos: 2414
上述表示备份到备份数据库的binlog文件和position点

SHOW SLAVE STATUS:
Host: 192.168.5.112
Log: mysql-bin.000294
Pos: 604030830

上述表示备份时间点master的信息

注意： 这里Pos表示的是备库sql_thread 执行到主库binlog的位置即show slave status 里的 Exec_Master_Log_Pos

Finished dump at: 2014-03-08 13:25:42

备份期间登陆mysql查看进程：
有一个查询线程（dump数据），另外还有这两个模拟slave 导出binlog的线程
93616 | root | localhost | NULL | Binlog Dump | 5 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 93617 | root | localhost | NULL | Binlog Dump | 5 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |

2线程无压缩备份
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -t 2 -o /data/mysql_bak/
real 0m8.947s
user 0m2.262s

sys 0m0.284s

4线程无压缩
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -t 4 -o /data/mysql_bak/
real 0m8.564s
user 0m2.076s
sys 0m0.297s

6线程无压缩

[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -t 6 -o /data/mysql_bak/
real 0m9.042s
user 0m2.308s
sys 0m0.322s

8线程无压缩

[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -t 8 -o /data/mysql_bak/

real 0m8.432s
user 0m1.964s
sys 0m0.260s
12线程无压缩
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -t 12 -o /data/mysql_bak/
real 0m9.583s
user 0m2.545s
sys 0m0.359s

总结：

这里所有的数据都是测试过3-5次然后取平均值（样本太少，可能还是不准）多线程备份随着线程的增加备份速度会相应加快，但是当线程增大到一定程度后备份速度反而下降，这个时候主要是磁盘瓶颈，这里测试在8线程下备份速度最优。

需要注意的是：在测试6线程的时候几乎和开启1个线程的备份消耗时间差不多，后来通过show processlist发现当开启6线程的时候实际上只有一个会话在dump数据，但是-v 3 看到的又是6线程dump数据不知道这算不算一个bug。

多线程备份带压缩 后的备份文件大小 ：36M 压缩大概为1：6到1:7左右 猜测就是使用gzip 进行的压缩不过备份效率下降了近50%

8线程带压缩

[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -c -t 8 -o /data/mysql_bak/

real 0m16.587s
user 0m11.127s
sys 0m0.179s
real 0m16.757s
user 0m11.320s
sys 0m0.149s

查看备份后的文件，可见压缩其实使用的就是类似gzip的方式，包括binlog在内的文件都进行压缩

-rw-r–r-- 1 root root 4671 Mar 8 19:54 mysql-bin.000001.gz
-rw-r–r-- 1 root root 182394 Mar 8 19:54 mysql-bin.000002.gz
-rw-r–r-- 1 root root 673 Mar 8 19:54 mysql-bin.000003.gz
drwx------ 2 root root 4096 Mar 8 19:54 binlog_snapshot
-rw-r–r-- 1 root root 210 Mar 8 19:54 metadata
-rw-r–r-- 1 root root 281 Mar 8 19:54 yiqifa_bs_push.xxxxxxschema.sql.gz --使用gzip压缩，可手动gunzip解压
-rw-r–r-- 1 root root 615 Mar 8 19:54 yiqifa_bs_push.xxxxwebsite.sql.gz

查看mydumper执行时候的详细信息 -v 3

[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p xxx --binlogs -e -c -t 2 -v 3 -o /data/mysql_bak/
** Message: Connected to a MySQL server
** Message: Started dump at: 2014-03-08 21:14:20
** Message: Written master status
** Message: Written slave status
** Message: Thread 1 connected using MySQL connection ID 93732
** Message: Thread 2 connected using MySQL connection ID 93733
** Message: Thread 1 dumping data for yiqifa_bs_push.apply_xxx_website
** Message: Non-InnoDB dump complete, unlocking tables
** Message: Thread 2 dumping data for yiqifa_bs_push.cpa_xxx_log
** Messa
** Message: Thread 2 shutting down
** Message: Finished dump at: 2014-03-08 21:14:32

备份过程如果有慢查询是否Kill 慢查询：（默认不kill，而是等待60s后如果发现慢查询还在那么备份就自己退出）
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p emarmysqldba --binlogs -e -c -t 2 -v 3 --long-query-guard 2 --kill-long-queries -o /data/mysql_bak/
** Message: Connected to a MySQL server

** (mydumper:25127): WARNING **: Killed a query that was running for 3s --kill 了一个慢查询
** Message: Started dump at: 2014-03-08 21:21:54
** Message: Thread 2 shutting down
** Message: Thread 1 shutting down
** Message: Finished dump at: 2014-03-08 21:22:06

被杀掉的查询：
(user:root time: 21:21)[db: test]select count(1) ,sleep(30) from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

如果只指定了了慢查询时间（默认60s)但是没有指定–kill-long-queries 那么mydumper直接崩溃退出
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs_push -u root -p emarmysqldba --binlogs -e -c -t 2 -v 3 --long-query-guard 2 -o /data/mysql_bak/

** (mydumper:26620): CRITICAL **: There are queries in PROCESSLIST running longer than 2s, aborting dump,
use --long-query-guard to change the guard value, kill queries (–kill-long-queries) or use
different server for dump

测试备份是否真的需要flush table with read lock：
[root@host105 mysql_bak]# time mydumper -B mysql -u root -p xxx --binlogs -m -c -v 3 -o /data/mysql_bak/

部分输出信息：
** Message: Thread 2 dumping data for mysql.time_zone_transition_type
** Message: Thread 2 dumping data for mysql.user
** Message: Thread 2 connected using MySQL connection ID 97512 (in binlog mode)
** Message: Thread 2 dumping binary log file mysql-bin.000001
** Message: Non-InnoDB dump complete, unlocking tables --这里有一个释放锁的操作
** Message: Finished dump at: 2014-03-08 21:57:54

另一个会话同时执行的
(user:root time: 21:57)[db: test]create table a(id int) ;drop table a;
Query OK, 0 rows affected ( 5.38 sec) – 阻塞了5秒多，说明确实加了一把全局的读锁

另外，如果备份的数据库都是innodb引擎的表，因为没有myisam表需要备份，那么flush table with read lock 将会很快释放，所以如果没有myisam表的备份，那么你几乎感觉不到读锁的存在。
再者，因为对myisam表备份需要表锁，所有mydumper会优先处理myisam表，记录myisam表个数，每处理一个myisam都原子操作数量减一，在myisam表都处理完毕后立即解锁，尽量减少锁定的时间，而不是在导出innodb表数据的时候还在lock myisam表。

测试 -e (默认情况不加-e 如果表没有记录将不会导出，加上-e 没有数据也会在导出的时候创建空文件）
root@host105 mysql_bak]# time mydumper -T user -u root -p xxx --binlogs -c
-e -v 3 -o /data/mysql_bak/

-rw-r–r-- 1 root root 167 Mar 9 01:26 test.user-schema.sql.gz
-rw-r–r-- 1 root root 78 Mar 9 01:26 test.user.sql.gz – 没有数据依然导出一个空数据的文件

root@host105 mysql_bak]# time mydumper -T user -u root -p xxx --binlogs -c -v 3 -o /data/mysql_bak/

-rw-r–r-- 1 root root 509 Mar 9 01:30 mysql.user.sql.gz
-rw-r–r-- 1 root root 167 Mar 9 01:30 test.user-schema.sql.gz – 不加-e 的话test.user只有表结构文件

测试 -T （不需要加dbname 前缀，表之间用,分隔 ；逗号和表之间不能有空格）和 --regex (需要dbname前缀)
time mydumper -T user,test -u root -p xxx --binlogs -c -v 3 -o /data/mysql_bak/

-rw-r–r-- 1 root root 624 Mar 9 01:38 mysql.user-schema.sql.gz
-rw-r–r-- 1 root root 121 Mar 9 01:38 test.test.sql.gz
-rw-r–r-- 1 root root 167 Mar 9 01:38 test.user-schema.sql.gz
-rw-r–r-- 1 root root 109 Mar 9 01:38 test.user.sql.gz

time mydumper --regex=‘mysql.user|test.test’ -u root -p xxx --binlogs -c -v 3 -o /data/mysql_bak/

-rw-r–r-- 1 root root 624 Mar 9 01:42 mysql.user-schema.sql.gz
-rw-r–r-- 1 root root 509 Mar 9 01:42 mysql.user.sql.gz
-rw-r–r-- 1 root root 235 Mar 9 01:42 test.test2-schema.sql.gz
-rw-r–r-- 1 root root 229 Mar 9 01:42 test.test-schema.sql.gz
-rw-r–r-- 1 root root 121 Mar 9 01:42 test.test.sql.gz

排除yiqifa_bs_push、yiqifa_bs和yiqifa_an及mysql库，相当于只备份test库
time mydumper --regex=’^(?!(yiqifa_bs_push|yiqifa_bs|yiqifa_an|mysql))’ -u root -p xxx --binlogs -c -v 3 -o /data/mysql_bak/

-rw-r–r-- 1 root root 235 Mar 9 01:47 test.test2-schema.sql.gz
-rw-r–r-- 1 root root 229 Mar 9 01:47 test.test-schema.sql.gz

测试 --regex 和 -T 的优先级
time mydumper --regex=‘mysql.user’ -T test,user -u root -p xxx --binlogs -c -v 3 -o /data/mysql_bak/
-rw-r–r-- 1 root root 624 Mar 9 02:01 mysql.user-schema.sql.gz
-rw-r–r-- 1 root root 509 Mar 9 02:01 mysql.user.sql.gz

可见，–regex的处理在–tables-list(-T)后， 先满–tables-list再满足–regex(两者必须是and的关系否则什么都不导出），如下只会dump表user

测试 --snapshot-interval (-I) （必须和 --daemon 一起用）
mydumper --regex=‘test.*’ -u root -p xxx -I 1 -D-c -v 3 -L /tmp/dump.log -o /data/mysql_bak/
drwx------ 2 root root 4096 Mar 9 02:35 0
drwx------ 2 root root 4096 Mar 9 02:33 1
drwx------ 2 root root 4096 Mar 9 02:38 binlogs
lrwxrwxrwx 1 root root 1 Mar 9 02:33 last_dump -> 1
-rw-r–r-- 1 root root 210 Mar 9 02:17 metadata
-rw-r–r-- 1 root root 235 Mar 9 02:17 test.test2-schema.sql.gz
-rw-r–r-- 1 root root 229 Mar 9 02:17 test.test-schema.sql.gz
-rw-r–r-- 1 root root 121 Mar 9 02:17 test.test.sql.gz
-rw-r–r-- 1 root root 167 Mar 9 02:17 test.user-schema.sql.gz
-rw-r–r-- 1 root root 109 Mar 9 02:17 test.user.sql.gz

设置这两个参数那么mydumper会定时 在后台（这里每隔 1分钟）进行一次备份，并且备份文件目录会在目录0,1（last_dump交叉指向0或1目录）之间进行切换。

-rows的使用

设置-rows可以把一个表分成多个文件。分块的原则并不是根据-rows设定的行数来决定生成文件里包含的函数，而是通过rows和表的总行数计算出要生成的文件个数，尽量保证每个文件的大小一致。表的总行数是如何获得的？首先mydumper会选择一个索引，顺序是pk、uk或者show index from table里Cardinality最高的一个索引，再通过explain select index from table的rows字段获得总行数total_nums（可能不准确），于是第一个文件就是从select

• from table where index >=1 and index < total_nums/ (int(total_nums/ rows) – 1) + 1。每个分块可以分到不同的线程，所以即便同一个表dump都可以很快加速。

PS:

如果想知道dump镜像所在的binlog日志位置可以:

cd binlog_snapshot/ root@host105 binlog_snapshot]# ll

total 1076
-rw-r–r-- 1 root root 27736 Mar 9 03:48 mysql-bin.000001
-rw-r–r-- 1 root root 1062832 Mar 9 03:48 mysql-bin.000002
-rw-r–r-- 1 root root 8034 Mar 9 03:48 mysql-bin.000003
解析最后一个日志就可以知道
mysqlbinlog mysql-bin.000003
#140309 3:36:27 server id 127105 end_log_pos 8034 Xid = 252012609
COMMIT/!/;

而binlog当前的position点为
#140309 3:39:27 server id 127105 end_log_pos 8129 Query thread_id=99206 exec_time=0 error_code=0
SET TIMESTAMP=1394307567/!/;

create table b11111(id int)

故备份文件里的binlog镜像并不是最新的binlog日志。

mydumper 和mysqldump 性能测试对比 （以及metadata的问题）

需要备份数据库大小：129G

最后备份文件（无压缩）大小：100G

使用mydumper 分别 在4、8、12线程下进行备份

[root@host105 mysql_bak]# time mydumper -B yiqifa_bs -u root -pxxx -t
4 -o /data/mysql_bak/
real 11m37.456s
user 17m48.952s
sys 2m52.717s
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs -u root -p xxx -t
8 -o /data/mysql_bak/
real 11m19.416s
user 21m23.441s

sys 3m38.803s

第二次 在凌晨测试

real 7m4.005s
user 17m43.677s
sys 4m40.592s

real 8m25.918s
user 18m53.535s
sys 5m9.220s
[root@host105 mysql_bak]# time mydumper -B yiqifa_bs -u root -p emarmysqldba -t
12 -o /data/mysql_bak/

real 12m14.479s
user 27m20.982s
sys 5m16.919s

使用mysql原生mysqldump进行备份 （备份文件大小也是100G）
time mysqldump -uroot -p xxx yiqifa_bs --opt --single-transaction >yiqifa_bs.dmp

real 50m49.690s
user 33m49.342s

sys 4m27.011s

对比4、8线程备份mydumper的备份速度要比原生mysqldump快近 5-7倍。需要特别注意mydumper备份不会对表加metadata lock，故使用mydumper进行备份期间尽量不要对表进行ddl操作，否则可能在基于时间点恢复的时候出现 数据不一致的问题。

什么情况下会导致数据不一致？

比如，在备份期间先对表t 执行了insert 操作，然后执行alter table t drop name 而此时备份记录的binlog pos点是在insert 之前的点

当基于时间点恢复的时候因为备份文件里T的字段name 已经被drop 那么在解析binlog 执行insert的时候就会报列匹配不上的错误

这也是为何mysql5.5加入metadata lock的主要原因----防止事物被ddl语句破坏。（读者可自行测试，我已验证)

最后，在测试过程中mydumper出现丢失数据的问题，其中有一个表test 里面含有5条记录dump出来后数据文件为空，这个问题有空再找找原因。(后来没有复现)

另外需要注意的是mydumper 无法备份view和trigger （无论是整库还是单独指定库），不过整库备份模式下可以备份procedure和function（因为存储在mysql.proc里），但是单库备份依然无法备份procedure和function，希望想使用mydumper进行备份的小伙伴们引起重视。

About Me

…

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号（ xiaomaimiaolhr）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162

● 本文博客园地址： http://www.cnblogs.com/lhrbest

● 本文CSDN地址： https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

…

● QQ群号： 230161599 、618766405

● 微 信群：可加我微 信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友 （ 646634621 ），注明添加缘由

● 于 2019-12-01 06:00 ~ 2019-12-31 24:00 在西安完成

● 最新修改时间：2019-12-01 06:00 ~ 2019-12-31 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

…

● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/

● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/

…

使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ xiaomaimiaolhr）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。

…

 欢迎与我联系