【MySQL】單表支援並行匯出的工具--mydumper

lhrbest發表於2015-03-19

【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快很多,具體優點為:

  1. 支援多執行緒備份

  2. 支援檔案壓縮

  3. 支援多執行緒恢復

  4. 保證資料的一致性

  5. 比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 .
-- 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

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

備份全庫:

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!@#'  --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

#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備份之【mydumper 學習】

      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

下載:

wget 

安裝:解壓後,在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://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、部落格園、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寶典今日頭條號地址:

........................................................................................................................

● 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

● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解

● 版權所有,歡迎分享本文,轉載請保留出處

........................................................................................................................

小麥苗的微店

小麥苗出版的資料庫類叢書http://blog.itpub.net/26736162/viewspace-2142121/

小麥苗OCP、OCM、高可用網路班http://blog.itpub.net/26736162/viewspace-2148098/

小麥苗騰訊課堂主頁https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號( xiaomaimiaolhr)及QQ群(DBA寶典)、新增小麥苗微 信, 學習最實用的資料庫技術。

........................................................................................................................

歡迎與我聯絡

 

 



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-1466393/,如需轉載,請註明出處,否則將追究法律責任。

相關文章