下面一起來了解下MySQL 與 xtrabackup如何實(shí)現(xiàn)備份與恢復(fù)，相信大家看完肯定會(huì)受益匪淺，文字在精不在多，希望Mysql 與 xtrabackup如何實(shí)現(xiàn)備份與恢復(fù)這篇短內(nèi)容是你想要的。
10年積累的網(wǎng)站制作、做網(wǎng)站經(jīng)驗(yàn)，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識你，你也不認(rèn)識我。但先做網(wǎng)站后付款的網(wǎng)站建設(shè)流程，更有道外免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
Mysql 備份恢復(fù)與 xtrabackup備份
1.1 備份的原因
備份是數(shù)據(jù)安全的最后一道防線，對于任何數(shù)據(jù)丟失的場景，備份雖然不一定能恢復(fù)百分之百的數(shù)據(jù)(取決于備份周期)，但至少能將損失降到最低。衡量備份恢復(fù)有兩個(gè)重要的指標(biāo)：恢復(fù)點(diǎn)目標(biāo)(RPO)和恢復(fù)時(shí)間目標(biāo)(RTO)，前者重點(diǎn)關(guān)注能恢復(fù)到什么程度，而后者則重點(diǎn)關(guān)注恢復(fù)需要多長時(shí)間。
1.1.1 備份的目錄
做災(zāi)難恢復(fù)：對損壞的數(shù)據(jù)進(jìn)行恢復(fù)和還原
需求改變：因需求改變而需要把數(shù)據(jù)還原到改變以前
測試：測試新功能是否可用
1.1.2 備份中需要考慮的問題
可以容忍丟失多長時(shí)間的數(shù)據(jù)；
恢復(fù)數(shù)據(jù)要在多長時(shí)間內(nèi)完；
恢復(fù)的時(shí)候是否需要持續(xù)提供服務(wù)；
恢復(fù)的對象，是整個(gè)庫，多個(gè)表，還是單個(gè)庫，單個(gè)表。
1.1.3 備份的類型
熱備份：
這些動(dòng)態(tài)備份在讀取或修改數(shù)據(jù)的過程中進(jìn)行，很少中斷或者不中斷傳輸或處理數(shù)據(jù)的功能。使用熱備份時(shí)，系統(tǒng)仍可供讀取和修改數(shù)據(jù)的操作訪問。
冷備份：
這些備份在用戶不能訪問數(shù)據(jù)時(shí)進(jìn)行，因此無法讀取或修改數(shù)據(jù)。這些脫機(jī)備份會(huì)阻止執(zhí)行任何使用數(shù)據(jù)的活動(dòng)。這些類型的備份不會(huì)干擾正常運(yùn)行的系統(tǒng)的性能。但是，對于某些應(yīng)用程序，會(huì)無法接受必須在一段較長的時(shí)間里鎖定或完全阻止用戶訪問數(shù)據(jù)。
溫備份：
這些備份在讀取數(shù)據(jù)時(shí)進(jìn)行，但在多數(shù)情況下，在進(jìn)行備份時(shí)不能修改數(shù)據(jù)本身。這種中途備份類型的優(yōu)點(diǎn)是不必完全鎖定最終用戶。但是，其不足之處在于無法在進(jìn)行備份時(shí)修改數(shù)據(jù)集，這可能使這種類型的備份不適用于某些應(yīng)用程序。在備份過程中無法修改數(shù)據(jù)可能產(chǎn)生性能問題。
1.2 備份的方式
1.2.1 冷備份
最簡單的備份方式就是，關(guān)閉MySQL云服務(wù)器，然后將data目錄下面的所有文件進(jìn)行拷貝保存，需要恢復(fù)時(shí)，則將目錄拷貝到需要恢復(fù)的機(jī)器即可。這種方式確實(shí)方便，但是在生產(chǎn)環(huán)境中基本沒什么作用。因?yàn)樗械臋C(jī)器都是要提供服務(wù)的，即使是Slave有時(shí)候也需要提供只讀服務(wù)，所以關(guān)閉MySQL停服備份是不現(xiàn)實(shí)的。與冷備份相對應(yīng)的一個(gè)概念是熱備份，所謂熱備份是在不影響MySQL對外服務(wù)的情況下，進(jìn)行備份。
冷備份及停止業(yè)務(wù)進(jìn)行備份。
1.2.2 快照備份
首先要介紹的熱備份是快照備份，快照備份是指通過文件系統(tǒng)支持的快照功能對數(shù)據(jù)庫進(jìn)行備份。備份的原理是將所有的數(shù)據(jù)庫文件放在同一分區(qū)中，然后對該分區(qū)執(zhí)行快照工作，對于Linux而言，需要通過LVM(Logical Volumn Manager)來實(shí)現(xiàn)。LVM使用寫時(shí)復(fù)制(copy-on-write)技術(shù)來創(chuàng)建快照，例如，對整個(gè)卷的某個(gè)瞬間的邏輯副本，類似于數(shù)據(jù)庫中的innodb存儲(chǔ)引擎的MVCC，只不過LVM的快照在文件系統(tǒng)層面，而MVCC在數(shù)據(jù)庫層面，而且僅支持innodb存儲(chǔ)引擎。
LVM有一個(gè)快照預(yù)留區(qū)域，如果原始卷數(shù)據(jù)有變化時(shí)，LVM保證在任何變更寫入之前，會(huì)復(fù)制受影響塊到快照預(yù)留區(qū)域。簡單來說，快照區(qū)域內(nèi)保留了快照點(diǎn)開始時(shí)的一致的所有old數(shù)據(jù)。對于更新很少的數(shù)據(jù)庫，快照也會(huì)非常小。
對于MySQL而言，為了使用快照備份，需要將數(shù)據(jù)文件，日志文件都放在一個(gè)邏輯卷中，然后對該卷快照備份即可。由于快照備份，只能本地，因此，如果本地的磁盤損壞，則快照也就損壞了?？煺諅浞莞蛴趯φ`操作防范，可以將數(shù)據(jù)庫迅速恢復(fù)到快照產(chǎn)生的時(shí)間點(diǎn)，然后結(jié)合二進(jìn)制日志可以恢復(fù)到指定的時(shí)間點(diǎn)。基本原理如下圖：
1.2.3 邏輯備份（文本表示：SQL 語句）
冷備份和快照備份由于其弊端在生產(chǎn)環(huán)境中很少使用，使用更多是MySQL自帶的邏輯備份和物理備份工具，這節(jié)主要講邏輯備份，MySQL官方提供了Mysqldump邏輯備份工具，雖然已經(jīng)足夠好，但存在單線程備份慢的問題。在社區(qū)提供了更優(yōu)秀的邏輯備份工具mydumper，它的優(yōu)勢主要體現(xiàn)在多線程備份，備份速度更快。
1.2.4 其他常用的備份方式
物理備份（數(shù)據(jù)文件的二進(jìn)制副本）
全量備份概念
全量數(shù)據(jù)就是數(shù)據(jù)庫中所有的數(shù)據(jù)（或某一個(gè)庫的全部數(shù)據(jù)）；
全量備份就是把數(shù)據(jù)庫中所有的數(shù)據(jù)進(jìn)行備份。
mysqldump會(huì)取得一個(gè)時(shí)刻的一致性數(shù)據(jù).
增量備份（刷新二進(jìn)制日志）
增量數(shù)據(jù)就是指上一次全量備份數(shù)據(jù)之后到下一次全備之前數(shù)據(jù)庫所更新的數(shù)據(jù)
對于mysqldump,binlog就是增量數(shù)據(jù).
1.2.5 備份工具的介紹
1、mysqldump: mysql原生自帶很好用的邏輯備份工具
2、mysqlbinlog: 實(shí)現(xiàn)binlog備份的原生態(tài)命令
3、xtrabackup: precona公司開發(fā)的性能很高的物理備份工具
1.3 mysqldump備份介紹
備份的基本流程如下：
1.調(diào)用FTWRL(flush tables with read lock)，全局禁止讀寫 2.開啟快照讀，獲取此時(shí)的快照(僅對innodb表起作用) 3.備份非innodb表數(shù)據(jù)(*.frm,*.myi,*.myd等) 4.非innodb表備份完畢后，釋放FTWRL鎖 5.逐一備份innodb表數(shù)據(jù) 6.備份完成。
整個(gè)過程，可以參考我同事的一張圖，但他的這張圖只考慮innodb表的備份情況，實(shí)際上在unlock tables執(zhí)行完畢之前，非innodb表已經(jīng)備份完畢，后面的t1,t2和t3實(shí)質(zhì)都是innodb表，而且5.6的mysqldump利用保存點(diǎn)機(jī)制，每備份完一個(gè)表就將一個(gè)表上的MDL鎖釋放，避免對一張表鎖更長的時(shí)間。
1.3.1 mysqldump備份流程
1.3.2 常用的備份參數(shù)
參數(shù) 參數(shù)說明
-A 備份全庫
-B 備某一個(gè)數(shù)據(jù)庫下的所有表
-R, --routines 備份存儲(chǔ)過程和函數(shù)數(shù)據(jù)
--triggers 備份觸發(fā)器數(shù)據(jù)
--master-data={1|2} 告訴你備份后時(shí)刻的binlog位置如果等于1，則將其打印為CHANGE MASTER命令; 如果等于2，那么該命令將以注釋符號為前綴。
--single-transaction 對innodb引擎進(jìn)行熱備
-F, --flush-logs 刷新binlog日志
-x, --lock-all-tables 鎖定所有數(shù)據(jù)庫的所有表。這是通過在整個(gè)轉(zhuǎn)儲(chǔ)期間采用全局讀鎖來實(shí)現(xiàn)的。
-l, --lock-tables 鎖定所有表以供讀取
-d 僅表結(jié)構(gòu)
-t 僅數(shù)據(jù)
--compact 減少無用數(shù)據(jù)輸出(調(diào)試)
一個(gè)完整的備份語句： innodb引擎的備份命令如下：mysqldump -A -R --triggers --master-data=2 --single-transaction |gzip >/opt/all$(date +%F).sql.gz 適合多引擎混合（例如：myisam與innodb混合）的備份命令如下： mysqldump -A -R --triggers --master-data=2 |gzip >/opt/all$(date +%F).sql.gz
1.3.3 -A 參數(shù)
備份全庫，備份語句
mysqldump -uroot -p123 -A > /backup/full.sql
1.3.4 -B 參數(shù)
備某一個(gè)數(shù)據(jù)庫下的所有表
增加建庫（create）及“use庫”的語句，可以直接接多個(gè)庫名，同時(shí)備份多個(gè)庫* -B 庫1 庫2
mysqldump -uroot -p123 -B world > /backup/worldb.sql
備份語句：
create database if not 存在 use db1 drop table create table insert into
不加-B備份數(shù)據(jù)庫時(shí)，只是備份數(shù)據(jù)庫下的所有表，不會(huì)創(chuàng)建數(shù)據(jù)庫
只能備份單獨(dú)的數(shù)據(jù)庫（一般用于備份單表時(shí)使用） mysqldump -uroot -p123 world > /backup/world.sql 備份單表 mysqldump -uroot -p123 world city > /backup/world_city.sql 對于單表備份的粒度，再恢復(fù)數(shù)據(jù)庫數(shù)據(jù)時(shí)速度最快。 備份多個(gè)表 mysqldump 庫1 表1 表2 表3 >庫1.sql mysqldump 庫2 表1 表2 表3 >庫2.sql
分庫備份:for循環(huán)
mysqldump -uroot -p'mysql123' -B mysql ... mysqldump -uroot -p'mysql123' -B mysql_utf8 ... mysqldump -uroot -p'mysql123' -B mysql ... ......
分庫備份
for name in `mysql -e "show databases;"|sed 1d` do mysqldump -uroot -p'mysql123' -B $name done
1.3.5 --master-data={1|2}參數(shù)
告訴你備份后時(shí)刻的binlog位置
2為注釋 1為非注釋，要執(zhí)行的(主從復(fù)制)
[root@db02 logs]# sed -n '22p' /opt/t.sql CHANGE MASTER TO MASTER_LOG_FILE='clsn-bin.000005', MASTER_LOG_POS=344; [root@db02 logs]# mysqldump -B --master-data=2 clsn >/opt/t.sql
1.3.6 --single-transaction 參數(shù)
對innodb引擎進(jìn)行熱備
只支持innodb引擎 使用該參數(shù)會(huì)單獨(dú)開啟一個(gè)事務(wù)進(jìn)行備份，利用事務(wù)的快照技術(shù)實(shí)現(xiàn)。
基于事務(wù)引擎:不用鎖表就可以獲得一致性的備份.
體現(xiàn)了ACID四大特性中的隔離性，生產(chǎn)中99% 使用innodb事務(wù)引擎.
雖然支持熱備，并不意味著你可以再任意時(shí)間點(diǎn)進(jìn)行備份，特別是業(yè)務(wù)繁忙期，不要做備份策略，一般夜里進(jìn)行備份。
innodb引擎的備份命令如下：
mysqldump -A -B -R --triggers --master-data=2 --single-transaction |gzip >/opt/all.sql.gz
1.3.7 --flush-logs參數(shù)/-F
刷新binlog日志
每天晚上0點(diǎn)備份數(shù)據(jù)庫
mysqldump -A -B -F >/opt/$(date +%F).sql [root@db02 ~]# ll /application/mysql/logs/ -rw-rw---- 1 mysql mysql 168 Jun 21 12:06 clsn-bin.000001 -rw-rw---- 1 mysql mysql 168 Jun 21 12:06 clsn-bin.000002 -rw-rw---- 1 mysql mysql 210 Jun 21 12:07 clsn-bin.index 提示:每個(gè)庫都會(huì)刷新一次.
1.3.8 壓縮備份
壓縮備份命令:
mysqldump -B --master-data=2 clsn|gzip >/opt/t.sql.gz
解壓:
zcat t.sql.gz >t1.sql gzip -d t.sql.gz #刪壓縮包 gunzip alL_2017-12-22.sql.gz
一個(gè)完整的備份語句
innodb引擎的備份命令如下： mysqldump -A -R --triggers --master-data=2 --single-transaction |gzip >/opt/all.sql.gz 適合多引擎混合（例如：myisam與innodb混合）的備份命令如下： mysqldump -A -R --triggers --master-data=2 |gzip >/opt/alL_$(date +%F).sql.gz
1.3.9 使用Mysqldump備份進(jìn)行恢復(fù)實(shí)踐
備份innodb引擎數(shù)據(jù)庫clsn并壓縮：
mysqldump -B -R --triggers --master-data=2 clsn|gzip >/opt/all_$(date +%F).sql.gz
人為刪除clsn數(shù)據(jù)庫：
[root@db02 opt]# mysql -e “drop database clsn;” [root@db02 opt]# mysql -e “show databases;”
恢復(fù)數(shù)據(jù)庫：
使用gzip解壓 gzip -d xxx.gz shell> mysql set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source /backup/alL_2017-12-22.sql
驗(yàn)證數(shù)據(jù)：
[root@db02 opt]# mysql -e “use clsn;select * from test;”
1.4 【模擬】增量恢復(fù)企業(yè)案例
1.4.1 前提條件:
1.具備全量備份（mysqldump）。
2.除全量備份以外，還有全量備份之后產(chǎn)生的的所有binlog增量日志。
1.4.2 環(huán)境準(zhǔn)備
(1)準(zhǔn)備環(huán)境：
drop database clsn; CREATE DATABASE clsn; USE `clsn`; CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; INSERT INTO `test` VALUES (1,'clsn'),(2,'znix'),(3,'inca'),(4,'zuma'),(5,'kaka');
查看創(chuàng)建好的數(shù)據(jù)
mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | clsn | | 2 | znix | | 3 | inca | | 4 | zuma | | 5 | kaka | +----+------+ 5 rows in set (0.00 sec)
(2)模擬環(huán)境：
mkdir /data/backup -p date -s "2017/12/22"
全備份:
mysqldump -B --master-data=2 --single-transaction clsn|gzip>/data/backup/clsn_$(date +%F).sql.gz
模擬增量:
mysql -e "use clsn;insert into test values(6,'haha');" mysql -e "use clsn;insert into test values(7,'hehe');" mysql -e "select * from clsn.test;"
(3)模擬誤刪數(shù)據(jù)：
date -s "2017/12/22 11:40" mysql -e "drop database clsn;show databases;"
出現(xiàn)問題10分鐘后,發(fā)現(xiàn)問題,刪除了數(shù)據(jù)庫了.
1.4.3 恢復(fù)數(shù)據(jù)準(zhǔn)備
(1)采用iptables防火墻屏蔽所有應(yīng)用程序的寫入。
[root@clsn ~]# iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP #<==非172.16.1.51禁止訪問數(shù)據(jù)庫3306端口。 或采用mysql 配置參數(shù)，但是需要重啟數(shù)據(jù)庫 --skip-networking 復(fù)制二進(jìn)制日志文件 cp -a /application/mysql/logs/clsn-bin.* /data/backup/ 截取日志 zcat clsn_2017-12-22.sql.gz >clsn_2017-12-22.sql sed -n '22p' clsn_2017-12-22.sql mysqlbinlog -d clsn --start-position=339 clsn-bin.000008 -r bin.sql
需要恢復(fù)的日志:
1.clsn_2017-12-22.sql 2.bin.sql grep -i drop bin.sql sed -i '/^drop.*/d' bin.sql
1.4.4 進(jìn)行數(shù)據(jù)恢復(fù)
恢復(fù)數(shù)據(jù)
重慶分公司
重慶分公司
