Percona XtraBackup簡(jiǎn)介
專注于為中小企業(yè)提供成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)樂昌免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都,凝聚了一批互聯(lián)網(wǎng)行業(yè)人才,有力地推動(dòng)了1000多家企業(yè)的穩(wěn)健成長(zhǎng),幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
其是一款開源,免費(fèi)的MySQL數(shù)據(jù)庫熱備工具,可對(duì)InnoDB存儲(chǔ)引擎進(jìn)行non-blocking的全備和增備,同時(shí)還支持壓縮,加密和流式等特性.
安裝Generic版本
1. root@db01 /data/software # ls percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
2. root@db01 /data/software # tar zxf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
3. root@db01 /usr/local # ln -s /data/software/percona-xtrabackup-2.4.8-Linux-x86_64 percona-xtrabackup-2.4.8
4.設(shè)置環(huán)境變量PATH
root@db01 ~ # grep 'PATH' .bash_profile
PATH=/usr/local/percona-xtrabackup-2.4.8/bin:/usr/local/sysbench-1.0.9/bin:/opt/mysql/bin:$PATH:$HOME/bin
export PATH
root@db01 ~ # xtrabackup --version
xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux(x86_64) (revision id: 97330f7)
同時(shí)也能查看Percona XtraBackup相應(yīng)命令的man page了,如命令行敲入man xtrabackup.
root@db01 ~ # man -w
/usr/local/percona-xtrabackup-2.4.8/man:/opt/mysql/man:/usr/local/share/man:/usr/share/man/overrides:/usr/share/man/en:/usr/share/man
體會(huì)下命令xtrabackup的使用.
1.創(chuàng)建全備
root@db01 /data1/xtrabackup_test # xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup
創(chuàng)建增備
root@db01 /data1/xtrabackup_test # xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_inc --backup --incremental-basedir=./pxb_full
2. Prepare階段
root@db01 /data1/xtrabackup_test # xtrabackup --target-dir=./pxb_full --prepare --apply-log-only --use-memory=4G
root@db01 /data1/xtrabackup_test # xtrabackup --target-dir=./pxb_full --prepare --use-memory=4G --incremental-dir=./pxb_inc
3.恢復(fù)備份
root@db01 /data1/xtrabackup_test # xtrabackup --defaults-file=/data1/3316/conf/my.cnf --target-dir=./pxb_full --copy-back
上面xtrabackup的工作過程簡(jiǎn)述如下.
xtrabackup啟動(dòng)后,會(huì)先創(chuàng)建redo線程,再創(chuàng)建ibd線程. redo線程負(fù)責(zé)拷貝事物日志,若是增備情況,其會(huì)根據(jù)上次備份中文件xtrabackup_checkpoints里to_lsn的值進(jìn)行拷貝; ibd線程負(fù)責(zé)拷貝數(shù)據(jù)文件.
ibd線程拷貝事物數(shù)據(jù)文件過程中, redo線程會(huì)一直監(jiān)控,并拷貝新增的事物日志.待ibd線程拷貝完事物數(shù)據(jù)文件, xtrabackup執(zhí)行FLUSH TABLES WITH READ LOCK,獲取全局讀鎖, ibd線程開始拷貝非事物數(shù)據(jù)文件,這期間redo線程仍在工作.待ibd線程拷貝完非事物數(shù)據(jù)文件,該線程結(jié)束, xtrabackup執(zhí)行SHOW MASTER STATUS,獲取二進(jìn)制日志坐標(biāo),執(zhí)行FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS,刷新InnoDB存儲(chǔ)引擎日志,這時(shí)redo線程結(jié)束,執(zhí)行UNLOCK TABLES,釋放鎖.最后寫元數(shù)據(jù).
Prepare階段, xtrabackup進(jìn)行crash recovery操作(類似InnoDB存儲(chǔ)引擎的崩潰恢復(fù)),將提交的事物roll forward,未提交的事物roll back,將數(shù)據(jù)庫恢復(fù)到FLUSH TABLES WITH READ LOCK時(shí)刻的一致狀態(tài).
接下來從三個(gè)場(chǎng)景出發(fā)進(jìn)一步熟練xtrabackup的使用.
1.如何在從數(shù)據(jù)庫上進(jìn)行備份,用于部署一新的從數(shù)據(jù)庫.
這里主要是參數(shù)--slave-info的使用,有了該參數(shù),其會(huì)記錄下主數(shù)據(jù)庫二進(jìn)制日志的坐標(biāo),類似于命令mysqldump的參數(shù)--dump-slave,命令行如下.
root@db02 /data1/xtrabackup_test # xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=pxb_full --backup --slave-info
注意,若從數(shù)據(jù)庫開啟了多線程復(fù)制, xtrabackup會(huì)退出,并報(bào)錯(cuò)如下.
The --slave-info option requires GTID enabled for a multi-threadedslave.
2. xtrabackup備份屬于物理備份,相比mysqldump的邏輯備份,占用磁盤空間較大,實(shí)際使用中,一般會(huì)把備份文件壓縮, xtrabackup有兩種壓縮方式,一是借助tar和gzip進(jìn)行壓縮,一是其自身提供了壓縮功能.
2.1 tar和gzip方式
root@db01 /data1/xtrabackup_test # mkdir pxb_full
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=tar 2> ./pxb_full/pxb.log | gzip -> ./pxb_full/pxb_full.tar.gz
xtrabackup還提供了并行備份的參數(shù)--parallel,是否可以加速上面的過程呢,測(cè)試如下:
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=tar --parallel=4 2> ./pxb_full/pxb.log | gzip - > ./pxb_full/pxb_full.tar.gz
xtrabackup在STDERR中提示如下,參數(shù)--parallel不支持tar流,其會(huì)被忽略.
xtrabackup: warning: the --parallel option does not have anyeffect when streaming in the 'tar' format. You can use the 'xbstream' formatinstead.
2.2自帶的壓縮功能, quicklz和xbstream方式
root@db01 /data1/xtrabackup_test # mkdir pxb_full
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=xbstream --compress=quicklz 2> ./pxb_full/pxb.log > ./pxb_full/pxb_full.qp.xbstream
利用參數(shù)--compress-threads和--parallel加速上面的過程.
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=xbstream --compress=quicklz --compress-threads=4 --parallel=4 2> ./pxb_full/pxb.log > ./pxb_full/pxb_full.qp.xbstream
3.如何盡量快的部署一個(gè)從數(shù)據(jù)庫呢...該過程可分成3個(gè)步驟:備份數(shù)據(jù),傳送備份,將數(shù)據(jù)準(zhǔn)備到Prepare階段前,在后的過程都是一樣的.大致有4種方式,分別測(cè)試如下.
3.1 tar流, gzip壓縮,通過scp傳送到遠(yuǎn)端.
3.1.1備份數(shù)據(jù)
root@db01 /data1/xtrabackup_test # mkdir pxb_full
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=tar 2> ./pxb_full/pxb.log | gzip -> ./pxb_full/pxb_full.tar.gz
real 7m14.839s
user 7m7.201s
sys 0m6.227s
3.1.2.傳送備份
root@db01 /data1/xtrabackup_test/pxb_full # time scp pxb_full.tar.gz 192.168.1.4:/data1/xtrabackup_test/pxb_full
pxb_full.tar.gz 100% 2214MB 110.7MB/s 00:20
real 0m19.992s
user 0m7.095s
sys 0m4.136s
3.1.3解壓備份
root@db02 /data1/xtrabackup_test/pxb_full # time tar zxf pxb_full.tar.gz
real 1m7.359s
user 0m59.890s
sys 0m7.446s
總耗時(shí): 7m14.839s + 0m19.992s + 1m7.359s = 522.190s
3.2 quicklz壓縮, xbstream流,通過scp傳送到遠(yuǎn)端.
3.2.1備份數(shù)據(jù)
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=xbstream --compress=quicklz --compress-threads=4 --parallel=4 2> ./pxb_full/pxb.log > ./pxb_full/pxb_full.qp.xbstream
real 0m19.780s
user 0m35.536s
sys 0m7.299s
3.2.2傳送備份
root@db01 /data1/xtrabackup_test/pxb_full # time scp pxb_full.qp.xbstream 192.168.1.4:/data1/xtrabackup_test/pxb_full
pxb_full.qp.xbstream 100% 3032MB112.3MB/s 00:27
real 0m27.373s
user 0m10.102s
sys 0m6.272s
3.2.3提取,解壓備份
解壓quicklz算法壓縮的文件,需安裝工具qpress,將其和命令xtrabackup放在相同目錄即可,下載鏈接: http://www.quicklz.com.
3.2.3.1提取
root@db02 /data1/xtrabackup_test/pxb_full # mkdir for_repl
root@db02 /data1/xtrabackup_test/pxb_full # time xbstream --extract --directory=./for_repl < pxb_full.qp.xbstream
real 0m4.760s
user 0m0.410s
sys 0m4.279s
3.2.3.2解壓
root@db02 /data1/xtrabackup_test/pxb_full # time xtrabackup --target-dir=./for_repl --decompress --remove-original --parallel=4
real 0m8.714s
user 0m20.519s
sys 0m9.200s
總耗時(shí): 0m19.780s + 0m27.373s + 0m4.760s + 0m8.714s = 60.627s
3.3 tar流,通過ssh傳送到遠(yuǎn)端.
3.3.1備份,傳送數(shù)據(jù)
root@db01 /data1/xtrabackup_test # mkdir pxb_full
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=tar 2> ./pxb_full/pxb.log | ssh 192.168.1.4 "tar xfi - --directory=/data1/xtrabackup_test/pxb_full"
real 1m0.779s
user 0m31.110s
sys 0m10.708s
總耗時(shí): 1m0.779s = 60.779s
3.4 quicklz壓縮, xbstream流,通過ssh傳送到遠(yuǎn)端.
3.4.1備份數(shù)據(jù)
root@db01 /data1/xtrabackup_test # mkdir pxb_full
root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=xbstream --compress=quicklz --compress-threads=4 2> ./pxb_full/pxb.log | ssh 192.168.1.4 "/usr/local/percona-xtrabackup-2.4.8/bin/xbstream - --extract --directory=/data1/xtrabackup_test/pxb_full --parallel=4"
real 0m48.778s
user 0m44.296s
sys 0m6.859s
3.4.2解壓
root@db02 /data1/xtrabackup_test # time xtrabackup --target-dir=./pxb_full --decompress --remove-original --parallel=4
real 0m8.116s
user 0m20.610s
sys 0m9.236s
總耗時(shí): 0m48.778s + 0m8.116s = 56.894s
可見第四種方法最快,第三種次之,但兩者都是流式傳送,要求網(wǎng)絡(luò)環(huán)境較好,否則就是第二種方法了.
另,前面還說到xtrabackup提供了備份加密功能,其有key和key-file兩種方式,此處選擇key-file演示,同時(shí)一并梳理整個(gè)備份恢復(fù)過程.
1.制作key-file文件
root@db01 /data1/xtrabackup_test # openssl rand -base64 24
qEBPZrck0JQHYSnG8ScdW0UjAeKkOCFt
root@db01 /data1/xtrabackup_test # echo -n 'qEBPZrck0JQHYSnG8ScdW0UjAeKkOCFt' > /usr/local/percona-xtrabackup-2.4.8/bin/keyfile
2.備份數(shù)據(jù)
root@db01 /data1/xtrabackup_test # mkdir pxb_full
root@db01 /data1/xtrabackup_test # xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=xbstream --compress=quicklz --compress-threads=4 --encrypt=AES256 --encrypt-key-file=/data/software/percona-xtrabackup-2.4.8-Linux-x86_64/bin/keyfile --encrypt-threads=4 --parallel=4 2> ./pxb_full/pxb.log > ./pxb_full/pxb_full.qp.encrypt.xbstream
3.提取
root@db01 /data1/xtrabackup_test/pxb_full # mkdir for_recovery
root@db01 /data1/xtrabackup_test/pxb_full # xbstream --extract --directory=./for_recovery < pxb_full.qp.encrypt.xbstream
4.解密
root@db01 /data1/xtrabackup_test/pxb_full # xtrabackup --target-dir=./for_recovery --encrypt-key-file=/data/software/percona-xtrabackup-2.4.8-Linux-x86_64/bin/keyfile --decrypt=AES256 --remove-original --parallel=4
5.解壓
root@db01 /data1/xtrabackup_test/pxb_full # xtrabackup --target-dir=./for_recovery --decompress --remove-original --parallel=4
6. Prepare階段
root@db01 /data1/xtrabackup_test/pxb_full # xtrabackup --target-dir=./for_recovery --prepare --use-memory=4G
7.恢復(fù)
root@db01 /data1/xtrabackup_test/pxb_full # xtrabackup --defaults-file=/data1/3316/conf/my.cnf --target-dir=./for_recovery --copy-back --parallel=4
至此整個(gè)MySQL Database Backup Methods系列就結(jié)束了,算是自己的一個(gè)總結(jié),也希望看到文章的人能有所收獲.
再有,馬上就國(guó)慶,中秋節(jié)了,提前祝福節(jié)日快樂!
若感興趣可關(guān)注訂閱號(hào)”數(shù)據(jù)庫最佳實(shí)踐”(DBBestPractice).
分享文章:MySQLDatabaseBackupMethodsSeason3–PerconaXtraBackup
鏈接地址:http://bm7419.com/article40/phoeeo.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站收錄、電子商務(wù)、網(wǎng)站設(shè)計(jì)、網(wǎng)站導(dǎo)航、網(wǎng)頁設(shè)計(jì)公司、虛擬主機(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í)需注明來源: 創(chuàng)新互聯(lián)