MySQL數據庫備份常用工具之MySQL Data Dumper簡析

本文主要給大家介紹MySQL數據庫備份常用工具之MySQL Data Dumper簡析，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續在億速云行業資訊里面關注我的更新文章的。

說到MySQL數據庫的備份, MySQL Data Dumper(項目)也是常用的工具, 其有兩個可執行程序: mydumper, 負責導出數據; myloader,  負責導入數據. mydumper相對于mysqldump, 多了些特性, 在下面分析選項的過程中能體會到.

由于是第三方工具, 先來看下安裝, 及可能遇到的問題.

a. mydumper需要依賴一些開發庫, 使用yum安裝即可.

root@db01: ~# yum install glib* zlib* pcre* -y

b. 添加連接MySQL需要的動態鏈接庫.

root@db01: ~# cat /etc/ld.so.conf.d/mysql.conf

/opt/mysql/lib

root@db01: ~#ldconfig

root@db01: ~#ldconfig --print-cache | grep 'mysql'

         libmysqlclient.so.18 (libc6,x86-64)=> /opt/mysql/lib/libmysqlclient.so.18

root@db01: ~# ls -l /opt/mysql/lib/libmysqlclient.so.18

lrwxrwxrwx 1 rootroot 26 Aug 25 14:21 /opt/mysql/lib/libmysqlclient.so.18 ->libmysqlclient_r.so.18.1.0

c. 編譯安裝.

root@db01: ~# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mydumper

root@db01: ~# make install

添加可執行命令的路徑到環境變量PATH中.

mysql@db01: ~$grep 'PATH' .bash_profile

PATH=/usr/local/mydumper/bin:/opt/mysql/bin/:$PATH:$HOME/bin

export PATH

d. 在命令行敲入mydumper回車, 看下面的返回信息, 安裝是正常的.

mysql@db01: ~$mydumper

**(mydumper:723): CRITICAL **: Error connecting to database: Access denied foruser 'root'@'localhost' (using password:NO)

mysql@db01: ~$myloader

**(myloader:5288): CRITICAL **: a directory needs to be specified, see --help

若出現如下報錯, 可能是步驟b有問題.

mysql@db01: ~$mydumper

mydumper: errorwhile loading shared libraries: libmysqlclient.so.18: cannot open shared objectfile: No such file or directory

下面是演示用到的數據庫數據表的信息:

(root@localhost)[(none)]> SELECT table_schema, table_name, engine FROM information_schema.tables WHERE (engine = 'InnoDB' OR engine = 'MyISAM') AND table_schema NOT IN('mysql', 'performance_schema' ,'information_schema');

+--------------+------------+--------+

| table_schema |table_name | engine |

+--------------+------------+--------+

| product      | pr1        | MyISAM |

| product      | pr2        | MyISAM |

| product      | pr3        | InnoDB |

| stage        | st1        | InnoDB |

| stage        | st2        | InnoDB |

| test         | tb1        | InnoDB |

| test         | tb2        |InnoDB |

+--------------+------------+--------+

7 rows in set(0.01 sec)

mydumper的選項也不少, 按照分析mysqldump一樣, 將其分成若干組, 看看重點選項的含義.

Connection Options組

該組選項指明了如何連接數據庫.

-h, --host      The host to connect to

-u, --user      Username with privileges to run the dump

-p,--password  User password

-P, --port       TCP/IPport to connect to

-S, --socket     domainsocket file to use for connection

Debug Options 組

改組指明了日志放在哪里, 以及日志的級別.

-L,--logfile   Log file name to use, by defaultstdout is used

-v,--verbose  Verbosity of output, 0 =silent, 1 = errors, 2 = warnings, 3 = info, default 2

Filtering Options組

改組指明了備份哪些數據庫對象, 以及對備份文件做什么附加處理(壓縮, 分割等).

-B,--database        Database to dump

-T,--tables-list        Comma delimitedtable list to dump (does not exclude regex option)

-o,--outputdir        Directory to outputfiles to

-s,--statement-size    Attempted size ofINSERT statement in bytes, default 1000000

-r, --rows            Try to split tables into chunks ofthis many rows. This option turns off --chunk-filesize

-F,--chunk-filesize      Split tables into chunks of this output filesize. This value is in MB

-c,--compress         Compress output files

-e,--build-empty-files   Build dump files even if no data availablefrom table

-x, --regex             Regular expression for 'db.table'matching

-m,--no-schemas       Do not dump tableschemas with the data

-d,--no-data           Do not dump tabledata

-G,--triggers           Dump triggers

-E, --events            Dump events

-R, --routines           Dump stored procedures and functions

Transactional Options 組

該組主要涉及到備份時如何加鎖, 下面使用該命令行進行測試mydumper --regex '^(?!(mysql))'--threads=1 [Option], 同時結合general log, 看mydumper是如何工作的.

1. 先看不加選項時, 是什么情況.

Master線程, 獲取GLOBAL READ LOCK, 開啟一致性讀事物, 得到二進制日志的坐標.

1587512Query   FLUSH TABLES WITH READ LOCK

1587512Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1587512Query   SHOW MASTER STATUS

Dump線程, 設置事物隔離級別為REPEATABLE READ, 開啟一致性讀事物進行非事物數據表的備份.

1587513Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1587513Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1587513Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr1`

1587513Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr2`

Master線程, 待Dump線程備份完非事物數據表后, 釋放鎖.

1587512Query   UNLOCK TABLES /* FTWRL */

Dump線程, 繼續其它事物數據表的備份.

2. -k,--no-locks  Do not execute the temporaryshared read lock.  WARNING: This willcause inconsistent backups

使用該選項時, mydumper會有如下類似提示:

**(mydumper:4095): WARNING **: Executing in no-locks mode, snapshot will notbeconsistent

其主要作用過程如下:

Master線程, 開啟一致性讀事物, 得到二進制日志的坐標.

1586766Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1586766Query   SHOW MASTER STATUS

Dump線程, 設置事物隔離級別為REPEATABLE READ, 開啟一致性讀事物進行數據表的備份.

1586767Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1586767Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

該過程由于未執行FLUSH TABLES WITH READ LOCK, 得到的二進制日志坐標可能不準確; (多個)線程開啟一致性讀事物時, 數據表可能會有變動, 這兩點會造成備份數據不一致.                                           

3.--less-locking  Minimize locking time onInnoDB tables.

Master線程, 獲取GLOBAL READ LOCK, 開啟一致性讀事物, 得到二進制日志的坐標.

1588054Query   FLUSH TABLES WITH READ LOCK

1588054Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1588054Query   SHOW MASTER STATUS

Dump2線程, 設置事物隔離級別為REPEATABLE READ, 開啟一致性讀事物.

1588056Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1588056Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

Dump1線程, 鎖定非事物數據表.

1588055 Query   LOCK TABLES `product`.`pr1` READ LOCAL,`product`.`pr2` READ LOCAL

Master線程, 釋放鎖.

1588054Query   UNLOCK TABLES /* FTWRL */

Dump1線程, 備份非事物數據表.

1588055Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr1`

1588055Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr2`

Dump1線程, 備份完成后, 釋放鎖.

1588055Query   UNLOCK TABLES /* Non Innodb */

Dump2線程, 繼續其它事物數據表的備份.

4.--use-savepoints  Use savepoints toreduce metadata locking issues, needs SUPER privilege

該選項含義是, 盡快釋放元數據鎖, 其它過程和1相同.

1601611 Query         SAVEPOINT mydumper

1601611 Query         ROLLBACK TO SAVEPOINT mydumper

5.--lock-all-tables  Use LOCK TABLE forall, instead of FTWRL

Master線程, 獲取有那些數據庫和數據庫表, 然后把需要備份的數據表加鎖, 開啟一致性讀事物, 再后得到二進制日志的坐標.

1586979Query   SELECT TABLE_SCHEMA, TABLE_NAMEFROM information_schema.TABLES WHERE TABLE_TYPE ='BASE TABLE' AND TABLE_SCHEMANOT IN ('information_schema', 'performance_schema', 'data_dictionary') AND NOT(TABLE_SCHEMA = 'mysql' AND (TABLE_NAME = 'slow_log' OR TABLE_NAME ='general_log'))

1586979Query   LOCK TABLE `product`.`pr1` READ,`product`.`pr2` READ, `product`.`pr3` READ, `stage`.`st1` READ, `stage`.`st2`READ, `test`.`tb1` READ, `test`.`tb2` READ

1586979Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1586979Query   SHOW MASTER STATUS

Dump線程, 設置事物隔離級別為REPEATABLE READ, 開啟一致性讀事物進行非事物數據表的備份.

1586980Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1586980Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1586980Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr1`

1586980Query   SELECT /*!40001 SQL_NO_CACHE */ *FROM `product`.`pr2`

Master線程, 待Dump線程備份完非事物數據表后, 釋放鎖.

1586979Query   UNLOCK TABLES /* FTWRL */

Dump線程, 繼續其它事物數據表的備份.

此種加鎖方式, 若數據庫數據表比較多時, 加鎖效率不高.

6.--trx-consistency-only  Transactionalconsistency only 

使用該選項時, mydumper會有如下類似提示:

**(mydumper:2573): WARNING **: Using trx_consistency_only, binlog coordinateswill not be accurate if you are writing to non transactional tables

Master線程, 獲取GLOBAL READ LOCK, 開啟一致性讀事物, 得到二進制日志的坐標.

1588315Query   FLUSH TABLES WITH READ LOCK

1588315Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

1588315Query   SHOW MASTER STATUS

Dump線程, 設置事物隔離級別為REPEATABLE READ, 開啟一致性讀事物.

1588316Query   SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1588316Query   START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */

Master線程, 釋放鎖.

1588315Query   UNLOCK TABLES /* trx-only */

Dump線程, 備份數據表.

此方式, 從加鎖到釋放鎖, 時間最短, 效率最高.

經上面的分析, 可得到加鎖過程影響大小順序如下:

--lock-all-tables> 不加該組選項 = --use-savepoints >--less-locking > --trx-consistency-only > --no-locks

Performance Options 組

該組指定了線程數量, 和如何處理長查詢.

-t,--threads          Number of threads touse, default 4

-l,--long-query-guard  Set long query timerin seconds, default 60

-K,--kill-long-queries   Kill long runningqueries (instead of aborting)

參數了解完了, 看兩個實際工作中例子.

1. 備份除數據庫mysql之外的其它數據庫.

mysql@db01:~/dbbackup$ mydumper --outputdir=20170826 --compress --build-empty-files--regex '^(?!(mysql))' --triggers --events --routines --logfile=error.txt--use-savepoints --trx-consistency-only --threads=4 --verbose=3

2. 備份全部數據庫.

mysql@db01:~/dbbackup$ mydumper --outputdir=20170826 --compress --build-empty-files--triggers --events --routines --long-query-guard=60 --kill-long-queries--logfile=error.txt --use-savepoints --trx-consistency-only --threads=4--verbose=3

經過選項分析和實踐過程, 總結下mydumper的特點:

1. 多線程備份, 可指定線程數量, 其也是速度優于mysqldump的關鍵.

2. 對于備份數據一致性方面考慮較多, 主要體現在非事物數據表的備份上.

3. 分析選項時, 沒有指定字符集的, 查看general log后, 發現是這樣處理的/*!40101 SET NAMES binary*/, 即省去了轉換字符集的開銷.

4. 提供了如何應對長查詢的選項.

myloader并沒有太多需要說明的, 看下選項解釋, 實踐下即可.

mydumper在備份時, 效率有了很大提升, 但其終究還是將數據轉化為SQL語句, 即常說的邏輯備份.

看了以上關于MySQL數據庫備份常用工具之MySQL Data Dumper簡析，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。