MySQL數(shù)據(jù)庫(kù)備份常用工具之MySQLDataDumper簡(jiǎn)析
本文主要給大家介紹MySQL數(shù)據(jù)庫(kù)備份常用工具之MySQL Data Dumper簡(jiǎn)析，希望可以給大家補(bǔ)充和更新些知識(shí)，如有其它問(wèn)題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關(guān)注我的更新文章的。
創(chuàng)新互聯(lián)是一家專注于網(wǎng)站制作、網(wǎng)站設(shè)計(jì)與策劃設(shè)計(jì),棲霞網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:棲霞等地區(qū)。棲霞做網(wǎng)站價(jià)格咨詢:18982081108
說(shuō)到MySQL數(shù)據(jù)庫(kù)的備份, MySQL Data Dumper(項(xiàng)目)也是常用的工具,其有兩個(gè)可執(zhí)行程序: mydumper,負(fù)責(zé)導(dǎo)出數(shù)據(jù); myloader, 負(fù)責(zé)導(dǎo)入數(shù)據(jù). mydumper相對(duì)于mysqldump,多了些特性,在下面分析選項(xiàng)的過(guò)程中能體會(huì)到.
由于是第三方工具,先來(lái)看下安裝,及可能遇到的問(wèn)題.
a. mydumper需要依賴一些開發(fā)庫(kù),使用yum安裝即可.
root@db01: ~# yum install glib* zlib* pcre* -y
b.添加連接MySQL需要的動(dòng)態(tài)鏈接庫(kù).
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
添加可執(zhí)行命令的路徑到環(huán)境變量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
若出現(xiàn)如下報(bào)錯(cuò),可能是步驟b有問(wèn)題.
mysql@db01: ~$mydumper
mydumper: errorwhile loading shared libraries: libmysqlclient.so.18: cannot open shared objectfile: No such file or directory
下面是演示用到的數(shù)據(jù)庫(kù)數(shù)據(jù)表的信息:
(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的選項(xiàng)也不少,按照分析mysqldump一樣,將其分成若干組,看看重點(diǎn)選項(xiàng)的含義.
Connection Options組
該組選項(xiàng)指明了如何連接數(shù)據(jù)庫(kù).
-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組
改組指明了日志放在哪里,以及日志的級(jí)別.
-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組
改組指明了備份哪些數(shù)據(jù)庫(kù)對(duì)象,以及對(duì)備份文件做什么附加處理(壓縮,分割等).
-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組
該組主要涉及到備份時(shí)如何加鎖,下面使用該命令行進(jìn)行測(cè)試mydumper --regex '^(?!(mysql))'--threads=1 [Option],同時(shí)結(jié)合general log,看mydumper是如何工作的.
1.先看不加選項(xiàng)時(shí),是什么情況.
Master線程,獲取GLOBAL READ LOCK,開啟一致性讀事物,得到二進(jìn)制日志的坐標(biāo).
1587512Query FLUSH TABLES WITH READ LOCK
1587512Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */
1587512Query SHOW MASTER STATUS
Dump線程,設(shè)置事物隔離級(jí)別為REPEATABLE READ,開啟一致性讀事物進(jìn)行非事物數(shù)據(jù)表的備份.
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線程備份完非事物數(shù)據(jù)表后,釋放鎖.
1587512Query UNLOCK TABLES /* FTWRL */
Dump線程,繼續(xù)其它事物數(shù)據(jù)表的備份.
2. -k,--no-locks Do not execute the temporaryshared read lock. WARNING: This willcause inconsistent backups
使用該選項(xiàng)時(shí), mydumper會(huì)有如下類似提示:
**(mydumper:4095): WARNING **: Executing in no-locks mode, snapshot will notbeconsistent
其主要作用過(guò)程如下:
Master線程,開啟一致性讀事物,得到二進(jìn)制日志的坐標(biāo).
1586766Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */
1586766Query SHOW MASTER STATUS
Dump線程,設(shè)置事物隔離級(jí)別為REPEATABLE READ,開啟一致性讀事物進(jìn)行數(shù)據(jù)表的備份.
1586767Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ
1586767Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */
該過(guò)程由于未執(zhí)行FLUSH TABLES WITH READ LOCK,得到的二進(jìn)制日志坐標(biāo)可能不準(zhǔn)確; (多個(gè))線程開啟一致性讀事物時(shí),數(shù)據(jù)表可能會(huì)有變動(dòng),這兩點(diǎn)會(huì)造成備份數(shù)據(jù)不一致.
3.--less-locking Minimize locking time onInnoDB tables.
Master線程,獲取GLOBAL READ LOCK,開啟一致性讀事物,得到二進(jìn)制日志的坐標(biāo).
1588054Query FLUSH TABLES WITH READ LOCK
1588054Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */
1588054Query SHOW MASTER STATUS
Dump2線程,設(shè)置事物隔離級(jí)別為REPEATABLE READ,開啟一致性讀事物.
1588056Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ
1588056Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */
Dump1線程,鎖定非事物數(shù)據(jù)表.
1588055 Query LOCK TABLES `product`.`pr1` READ LOCAL,`product`.`pr2` READ LOCAL
Master線程,釋放鎖.
1588054Query UNLOCK TABLES /* FTWRL */
Dump1線程,備份非事物數(shù)據(jù)表.
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線程,繼續(xù)其它事物數(shù)據(jù)表的備份.
4.--use-savepoints Use savepoints toreduce metadata locking issues, needs SUPER privilege
該選項(xiàng)含義是,盡快釋放元數(shù)據(jù)鎖,其它過(guò)程和1相同.
1601611 Query SAVEPOINT mydumper
1601611 Query ROLLBACK TO SAVEPOINT mydumper
5.--lock-all-tables Use LOCK TABLE forall, instead of FTWRL
Master線程,獲取有那些數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)表,然后把需要備份的數(shù)據(jù)表加鎖,開啟一致性讀事物,再后得到二進(jìn)制日志的坐標(biāo).
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線程,設(shè)置事物隔離級(jí)別為REPEATABLE READ,開啟一致性讀事物進(jìn)行非事物數(shù)據(jù)表的備份.
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線程備份完非事物數(shù)據(jù)表后,釋放鎖.
1586979Query UNLOCK TABLES /* FTWRL */
Dump線程,繼續(xù)其它事物數(shù)據(jù)表的備份.
此種加鎖方式,若數(shù)據(jù)庫(kù)數(shù)據(jù)表比較多時(shí),加鎖效率不高.
6.--trx-consistency-only Transactionalconsistency only
使用該選項(xiàng)時(shí), mydumper會(huì)有如下類似提示:
**(mydumper:2573): WARNING **: Using trx_consistency_only, binlog coordinateswill not be accurate if you are writing to non transactional tables
Master線程,獲取GLOBAL READ LOCK,開啟一致性讀事物,得到二進(jìn)制日志的坐標(biāo).
1588315Query FLUSH TABLES WITH READ LOCK
1588315Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */
1588315Query SHOW MASTER STATUS
Dump線程,設(shè)置事物隔離級(jí)別為REPEATABLE READ,開啟一致性讀事物.
1588316Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ
1588316Query START TRANSACTION /*!40108 WITHCONSISTENT SNAPSHOT */
Master線程,釋放鎖.
1588315Query UNLOCK TABLES /* trx-only */
Dump線程,備份數(shù)據(jù)表.
此方式,從加鎖到釋放鎖,時(shí)間最短,效率最高.
經(jīng)上面的分析,可得到加鎖過(guò)程影響大小順序如下:
--lock-all-tables>不加該組選項(xiàng) = --use-savepoints >--less-locking > --trx-consistency-only > --no-locks
Performance Options組
該組指定了線程數(shù)量,和如何處理長(zhǎng)查詢.
-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)
參數(shù)了解完了,看兩個(gè)實(shí)際工作中例子.
1.備份除數(shù)據(jù)庫(kù)mysql之外的其它數(shù)據(jù)庫(kù).
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.備份全部數(shù)據(jù)庫(kù).
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
經(jīng)過(guò)選項(xiàng)分析和實(shí)踐過(guò)程,總結(jié)下mydumper的特點(diǎn):
1.多線程備份,可指定線程數(shù)量,其也是速度優(yōu)于mysqldump的關(guān)鍵.
2.對(duì)于備份數(shù)據(jù)一致性方面考慮較多,主要體現(xiàn)在非事物數(shù)據(jù)表的備份上.
3.分析選項(xiàng)時(shí),沒有指定字符集的,查看general log后,發(fā)現(xiàn)是這樣處理的/*!40101 SET NAMES binary*/,即省去了轉(zhuǎn)換字符集的開銷.
4.提供了如何應(yīng)對(duì)長(zhǎng)查詢的選項(xiàng).
myloader并沒有太多需要說(shuō)明的,看下選項(xiàng)解釋,實(shí)踐下即可.
mydumper在備份時(shí),效率有了很大提升,但其終究還是將數(shù)據(jù)轉(zhuǎn)化為SQL語(yǔ)句,即常說(shuō)的邏輯備份.
看了以上關(guān)于MySQL數(shù)據(jù)庫(kù)備份常用工具之MySQL Data Dumper簡(jiǎn)析，希望能給大家在實(shí)際運(yùn)用中帶來(lái)一定的幫助。本文由于篇幅有限，難免會(huì)有不足和需要補(bǔ)充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問(wèn)題的。
本文題目：MySQL數(shù)據(jù)庫(kù)備份常用工具之MySQLDataDumper簡(jiǎn)析
URL鏈接：http://muchs.cn/article40/jejsho.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、做網(wǎng)站、電子商務(wù)、網(wǎng)站制作、網(wǎng)頁(yè)設(shè)計(jì)公司、品牌網(wǎng)站設(shè)計(jì)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 什么是動(dòng)態(tài)網(wǎng)站建設(shè) 2016-11-04
- 動(dòng)態(tài)網(wǎng)站和靜態(tài)網(wǎng)站 2016-11-09
- 企業(yè)應(yīng)選擇靜態(tài)網(wǎng)站，還是動(dòng)態(tài)網(wǎng)站設(shè)計(jì)？ 2016-08-11
- 企業(yè)網(wǎng)站建設(shè)是動(dòng)態(tài)性的好還是靜態(tài)性的好 2013-12-07
- 深圳動(dòng)態(tài)網(wǎng)站制作 2013-11-12
- 如何去做動(dòng)態(tài)網(wǎng)站優(yōu)化排名 2022-05-28
- 分析動(dòng)態(tài)網(wǎng)站和靜態(tài)網(wǎng)站的區(qū)別 2016-12-04
- 動(dòng)態(tài)網(wǎng)站建設(shè)的利與弊 2022-05-02
- 網(wǎng)站是做動(dòng)態(tài)好還是靜態(tài)好-網(wǎng)站決策 2016-11-05
- 成都網(wǎng)站建設(shè)之動(dòng)態(tài)網(wǎng)站好還是靜態(tài)網(wǎng)站好 2022-07-31
- 做網(wǎng)站的時(shí)候是制作動(dòng)態(tài)網(wǎng)站好還是制作靜態(tài)網(wǎng)站好呢？ 2016-11-14
- 成都網(wǎng)站建設(shè):助你了解靜態(tài)網(wǎng)站和動(dòng)態(tài)網(wǎng)站！ 2017-01-06