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
root@dd:~/mydumper-0.5.2# cmake .
-- The CXX compiler identification is GNU
-- 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: /usr/bin/mysql_config
-- Found MySQL: /usr/include/mysql, /usr/lib/x86_64-linux-gnu/libmysqlclient_r.so;/usr/lib/x86_64-linux-gnu/libpthread.so;/usr/lib/x86_64-linux-gnu/libz.so;/usr/lib/x86_64-linux-gnu/libm.so;/usr/lib/x86_64-linux-gnu/librt.so;/usr/lib/x86_64-linux-gnu/libdl.so

CMake Warning at docs/CMakeLists.txt:9 (message):
  Unable to find Sphinx documentation generator


-- ------------------------------------------------
-- MYSQL_CONFIG = /usr/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /usr/local
-- BUILD_DOCS = ON
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
-- 
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mydumper-0.5.2
root@dd:~/mydumper-0.5.2# make
Scanning dependencies of target mydumper
[ 20%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 40%] Building C object CMakeFiles/mydumper.dir/binlog.c.o
[ 60%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 80%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ 80%] 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
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 options

Application 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 options

Application 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@zhoujy:/home/zhoujy/bak# ls -lh
-rw-r--r-- 1 root root  322 2013-11-14 17:59 chushihua.dba_hospital_all_name-schema.sql
-rw-r--r-- 1 root root  16M 2013-11-14 17:59 chushihua.dba_hospital_all_name.sql
-rw-r--r-- 1 root root  221 2013-11-14 17:59 chushihua.dba_hospital-schema.sql
-rw-r--r-- 1 root root  658 2013-11-14 17:59 chushihua.dba_hospital.sql
-rw-r--r-- 1 root root  198 2013-11-14 17:59 chushihua.dba_jobTitle-schema.sql
-rw-r--r-- 1 root root  300 2013-11-14 17:59 chushihua.dba_jobTitle.sql
-rw-r--r-- 1 root root  261 2013-11-14 17:59 chushihua.dba_locatedCity-schema.sql
-rw-r--r-- 1 root root 202K 2013-11-14 17:59 chushihua.dba_locatedCity.sql

分析: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、cde
mydumper -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进行对比测试。

#!/usr/bin/env python
#coding=utf-8
import MySQLdb
import os
import sys
import time

backup = os.system('')

def mysqldump_data():
    t1 = time.time()
    backup = os.system('mysqldump --no-defaults -uroot -p123456 -h192.168.200.25 --default-character-set=utf8 test > /home/zhoujy/test.bak')
    t2 = time.time()
    t = round(t2-t1)
    print "mysqldump Cost Time %s" %t

def mydumper_data():
    t1 = time.time()
    backup = os.system('mydumper -u root -p 123456 -h 192.168.200.25 -P 3306 -B test -o /home/zhoujy/bak/')
    t2 = time.time()
    t = round(t2-t1)
    print "mydumper Cost Time %s" %t

if __name__ =='__main__':
    mysqldump_data()
    mydumper_data()    
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      | 
SELECT /*!40001 SQL_NO_CACHE */ * FROM `virtual_station`.`core_event` WHERE `id` IS NULL OR (`id` >= 1 AND `id` < 446597) | | Sending to client |
SELECT /*!40001 SQL_NO_CACHE */ * FROM `virtual_station`.`core_error_log` WHERE `id` IS NULL OR (`id` >= 1 AND `id` < 351238) | | Sending to client |
SELECT /*!40001 SQL_NO_CACHE */ * FROM `virtual_station`.`core_event` WHERE (`id` >= 893193 AND `id` < 1339789) | | Sending to client |
SELECT /*!40001 SQL_NO_CACHE */ * FROM `virtual_station`.`core_event` WHERE (`id` >= 1339789 AND `id` < 1786385) | | Sending data |
SELECT /*!40001 SQL_NO_CACHE */ * FROM `virtual_station`.`core_error_log` WHERE (`id` >= 702475 AND `id` < 1053712) |

这个可以更好的备份数据库,不管数据库里是否有大表。

总结:

从上面的测试分析中看出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

posted @ 2013-11-15 13:36  jyzhou  阅读(12395)  评论(4编辑  收藏  举报