mysqldump和mydumer时间的比较

目标： 测试mydumper和mysqldump之间的性能；

 

环境： mysql 5.6.36  

 

mydumper安装

yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake

wget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz

tar zxvf mydumper-0.9.1.tar.gz
cd mydumper-0.9.1/cmake .
make
make install

 mydumper介绍

MySQL自身的mysqldump工具支持单线程工作，依次一个个导出多个表，没有一个并行的机，这就使得它无法迅速的备份数据。

 

mydumper作为一个实用工具，能够良好支持多线程工作，可以并行的多线程的从表中读入数据并同时写到不同的文件里，这使得它在处理速度方面快于传统的mysqldump。其特征之一是在处理过程中需要对列表加以锁定，因此如果我们需要在工作时段执行备份工作，那么会引起DML阻塞。但一般现在的MySQL都有主从，备份也大部分在从上进行，所以锁的问题可以不用考虑。这样，mydumper能更好的完成备份任务。

①多线程备份

②因为是多线程逻辑备份，备份后会生成多个备份文件

③备份时对MyISAM表施加FTWRL(FLUSH TABLES WITH READ LOCK),会阻塞DML语句

④保证备份数据的一致性

⑤支持文件压缩

⑥支持导出binlog

⑦支持多线程恢复

⑧支持以守护进程模式工作，定时快照和连续二进制日志

⑨支持将备份文件切块

 

测试脚本：

#!/bin/bash


#test mysqldump and mydump time
#purge query cache
/export/servers/mysql/bin/mysql --login-path=localhost -e "flush tables;"
t1_mydumper=`date +"%s"`
echo "before run time:  $t1_mydumper"
#backup
/usr/local/bin/mydumper -u username -h address -P 3358 -p password-o /export/data/dbbak/bak_mydumper -t 20
t2_mydumper=`date +"%s"`
echo "after run time: $t2_mydumper"
interval=`expr $t2_mydumper - $t1_mydumper`
echo "interval seconds  is: $interval"
echo "the backup size is `du -sh /export/data/dbbak/bak_mydumper`"

sleep 10

#flush cache
/export/servers/mysql/bin/mysql --login-path=localhost -e "flush tables;"
#test mysqldump and mydump time
t1_mydumper=`date +"%s"`
echo "before run time:  $t1_mydumper"
#backup
mysqldump -u username -h address -P 3358  -p password --single-transaction -A >  /export/data/dbbak/bak_mysqldumpe/dumper.sql
t2_mydumper=`date +"%s"`
echo "after run time: $t2_mydumper"
interval=`expr $t2_mydumper - $t1_mydumper`
echo "interval seconds  is: $interval"
echo "the backup size is `du -sh /export/data/dbbak/bak_mysqldumpe`"

执行结果如下：

[root@A02-R05-I41-72-516WMTW dbbak]# cat test.log 
before run time:  1537448401
after run time: 1537448432
interval seconds  is: 31
the backup size is 4.5G	/export/data/dbbak/bak_mydumper
before run time:  1537448442
after run time: 1537448489
interval seconds  is: 47
the backup size is 4.5G	/export/data/dbbak/bak_mysqldumpe

由结果可知，同样备份4.5G的数据，mydumper开启20个线程并发花费31s，mysqldump 47s

把脚本中-t 20改为50，测试50线程并发

[root@A02-R05-I41-72-516WMTW dbbak]# sh testbak.sh 
before run time:  1537456671

** (mydumper:15256): CRITICAL **: Couldn't acquire global lock, snapshots will not be consistent: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
after run time: 1537456699
interval seconds  is: 28
the backup size is 4.5G	/export/data/dbbak/bak_mydumper
before run time:  1537456709
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
after run time: 1537456757
interval seconds  is: 48
the backup size is 4.5G	/export/data/dbbak/bak_mysqldumpe
[root@A02-R05-I41-72-516WMTW dbbak]# 

开启50个线程并发，花费28s, mysqldump是48s，相差将近一倍；

当然，开销也是会增大的

 

CPU核数	12
内存	64G
硬盘	1.1T非SSD
备份方式	并发数	mysql下文件大小	dump文件大小	CPU(%)	IO(%)	耗时(秒)
mydumper	1	149G	72G	18%	20%	1274
mydumper	2	149G	72G	28%	58%	639
mydumper	3	149G	72G	34%	78%	509
mydumper	4	149G	72G	36%	88%	496
mydumper	5	149G	72G	39%	92%	441
mysqldump	mysqldump	149G	73G	12%	26%	1340