【MySQL】單表支援並行匯出的工具--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
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位置資訊。
安裝:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92 |
[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 .
CMake Warning
at
docs/CMakeLists.txt:9 (message):
Unable
to
find Sphinx documentation generator
[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...
|
確認是否安裝成功:缺少libmysqlclient.so.20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
[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) |
解決辦法:
1
2
3
4
5
6
7 |
[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
) |
檢視版本號,已經安裝成功了
1
2 |
[root@iZ252affh58Z mydumper-0.9.1]# mydumper -V
mydumper 0.9.1, built against MySQL 5.7.12 |
檢視幫助:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44 |
[root@iZ252affh58Z mydumper-0.9.1]# mydumper
Usage:
mydumper [
OPTION
...] multi-threaded MySQL dumping
Help Options:
-?,
Application Options:
-B,
-T,
-o,
-s,
-r,
-F,
-c,
-e,
-x,
-i,
-m,
-d,
-G,
-E,
-R,
-k,
-l,
-K,
-D,
-I,
-L,
-U,
-h,
-u,
-p,
-P,
-S,
-t,
-C,
-V,
-v,
|
備份全庫:
1 |
mydumper -u root -p
'xxxxxDB2015!@#'
-o /mnt/backup/ |
備份mysqlhqdb資料庫:
1 |
mydumper -u root -p
'xxxxxDB2015!@#'
-B mysqlhqdb -o /mnt/backup/ |
備份多張表(tableA,tableB):
1 |
mydumper -u root -p
'xxxxxDB2015!@#'
-B mysqlhqdb -T tableA,tableB -o /mnt/backup/ |
備份tableA表的資料,不備份表結構
1 |
mydumper -u root -p
'xxxxxDB2015!@#'
-B mysqlhqdb -T tableA -m -o /mnt/backup/ |
備份tableA表的資料,並進行壓縮
1 |
mydumper -u root -p
'xxxxxDB2015!@#'
-B mysqlhqdb -T tableA -c -o /mnt/backup/ |
還原mysqlhqdb庫:
1 |
myloader -u root -p
'xxxxxDB2015!@#'
-B mysqlhqdb -d /mnt/backup/ |
還原tableA表
1 |
myloader -u root -p
'xxxxxDB2015!@#'
-B mysqlhqdb -o tableA -d /mnt/backup/ |
mysqldump和mydumper的速度對比
首先用mydumper進行全庫備份,耗時為12分鐘
1
2
3
4 |
[root@iZ252affh58Z mnt]#
time
mydumper -u root -p
'xxxxxDB2015!@#'
-o /mnt/backup/
real12m19.526s
user3m29.925s
sys0m36.017s |
再用mysqldump全庫備份,耗時為18分鐘
1
2
3
4
5 |
[root@iZ252affh58Z mnt]#
time
mysqldump -uroot -p
'xxxxxDB2015!@#'
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
#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://blogread.cn/it/article/4071
MySQL在備份方面包含了自身的mysqldump工具,但其只支援單執行緒工作,這就使得它無法迅速的備份資料。而
作為一個實用工具,能夠良好支援多執行緒工作,這使得它在處理速度方面十倍於傳統的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
下載:
安裝:解壓後,在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個執行緒(預設)在備份,檢視備份檔案:
分析:
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://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
安裝依賴包
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部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-1466393/,如需轉載,請註明出處,否則將追究法律責任。