前言
這周又是上線周。辦公桌的頭發(fā)越來(lái)越多了，保溫杯都是枸杞，電腦壁紙也換成了應(yīng)急逃生通道（不要問(wèn)我為什么是應(yīng)急通道，因?yàn)榇蛩汶S時(shí)跑路）。
因?yàn)槭切孪到y(tǒng)要與舊系統(tǒng)之間進(jìn)行數(shù)據(jù)同步，清洗，分發(fā)。所以，這周任務(wù)是不斷地核實(shí)數(shù)據(jù)，調(diào)試程序，與數(shù)據(jù)庫(kù)打交道的占比很高。
一旦要到數(shù)據(jù)庫(kù)這個(gè)話題，永遠(yuǎn)也避不開(kāi)數(shù)據(jù)安全的問(wèn)題。所以今天我就來(lái)講講怎么使用 MySQL 的備份與恢復(fù)。
拋出本文問(wèn)題
首先，在講 MySQL 備份之前，我想明確咱們接下來(lái)需要探究的問(wèn)題
- 備份這么麻煩，但是為什么值得我們?nèi)プ觯?/li>
- 多得一批的備份術(shù)語(yǔ)
- 我們究竟需要備份什么？
- 備份需要考慮什么因素？
- 備份的方案有哪些？
- 實(shí)踐
知識(shí)背景
為什么我們需要備份？
時(shí)間是往前流動(dòng)的，人生是不可逆轉(zhuǎn)的，但是數(shù)據(jù)庫(kù)能。我想說(shuō)幾個(gè)場(chǎng)景你是否還很熟悉？
- 線上項(xiàng)目因?yàn)?Bug 或客戶騷操作的問(wèn)題，導(dǎo)致業(yè)務(wù)數(shù)據(jù)缺失，流程無(wú)法繼續(xù)走下去，沒(méi)有回頭了只硬著頭皮線上改數(shù)據(jù)，結(jié)果表一多起來(lái)，改了那條都不知道了
- 上線前從舊系統(tǒng)遷移數(shù)據(jù)，為上線做準(zhǔn)備，結(jié)果一執(zhí)行清洗 SQL，哎呀，IS NOT NULL 忘了改回了 IS NULL，含淚全庫(kù)刪除，重新導(dǎo)庫(kù)清洗；
- 新同事在服務(wù)器執(zhí)行了技術(shù)大佬傳授真經(jīng)命令行 rm -rf /*，結(jié)果我趕緊給他發(fā)了一張高清的緊急逃生通道...
所以說(shuō)，為什么我們要備份？因?yàn)槲覀円龅綗o(wú)所畏懼，有路可退。在風(fēng)險(xiǎn)面前，我們盡能力去規(guī)避風(fēng)險(xiǎn)。這些風(fēng)險(xiǎn)，小到不小心在別的服務(wù)器執(zhí)行了 Alter Table，大到服務(wù)器硬件出現(xiàn)故障，全機(jī)崩潰，軟件硬件故障/自然災(zāi)害/人為操作等等。
所以我們需要備份是為了應(yīng)對(duì)來(lái)自各方面的威脅
多得一批的備份術(shù)語(yǔ)
說(shuō)起備份，可能你的頭腦里浮現(xiàn)了 熱備份/冷備份/增量備份/差異備份/邏輯備份...放棄的聲音席卷而來(lái)！
其實(shí)先不要害怕這些術(shù)語(yǔ)，它們都是有專(zhuān)門(mén)的由來(lái)的。
首先是熱備份，溫備份和冷備份。熱備份指的是不需要停止任何服務(wù)即可備份，就好像你備份不用關(guān)掉數(shù)據(jù)庫(kù)來(lái)備份，隨時(shí)隨地可進(jìn)行;冷備份指的是停止數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)備份。
然后全量備份和部分備份。
- 全量備份(類(lèi)似名字還有全局備份，完全備份)指的是將整個(gè)數(shù)據(jù)庫(kù)備份下來(lái)。顯然當(dāng)項(xiàng)目數(shù)據(jù)達(dá)到一定規(guī)模，那么整庫(kù)備份變得不現(xiàn)實(shí)，因?yàn)閭浞輹r(shí)間變得更長(zhǎng)，同時(shí)需要更多地磁盤(pán)資源，機(jī)器資源...
- 部分備份指的是將部分?jǐn)?shù)據(jù)集備份下來(lái)，例如備份某庫(kù)某表某個(gè)時(shí)間段的數(shù)據(jù)，或者是僅僅備份某庫(kù)某表的所有數(shù)據(jù)。部分備份一般不包含完整的數(shù)據(jù)集，而我們明顯可以僅僅備份所更改的數(shù)據(jù)，這樣可以減少服務(wù)器的開(kāi)銷(xiāo)/備份時(shí)間/備份空間。根據(jù)部分備份的概念，我們可以拆分成兩種備份方式：增量備份和差異備份，下面使用表格說(shuō)明：
名稱說(shuō)明增量備份對(duì)自上次全備份后所有改變的部分而做的備份差異備份自從任意類(lèi)型的上次備份后所有修改做的備份
舉例說(shuō)明，假設(shè)在周日做了一個(gè)全量備份。在周一，對(duì)自周日以來(lái)所有的改變做一個(gè)差異備份。在周二，你有兩個(gè)選擇：備份周日以來(lái)所有的改變(差異備份)，或只備份自從周一備份后所有的改變(增量備份)
我們究竟需要備份信息？
可能說(shuō)到這個(gè)問(wèn)題上，大多數(shù)人第一反應(yīng)就是備份表結(jié)構(gòu)+表數(shù)據(jù)。恭喜你，你猜對(duì)了一半，但是這個(gè)方案是備份中最低的要求，因?yàn)樵跀?shù)據(jù)庫(kù)中還存在很多被忽略的數(shù)據(jù)在默默支撐著數(shù)據(jù)庫(kù)的正常運(yùn)行。下面介紹一下數(shù)據(jù)庫(kù)哪些值得關(guān)注的數(shù)據(jù)：
類(lèi)型內(nèi)容非顯著信息二進(jìn)制日志和 InnoDB 事務(wù)日志代碼觸發(fā)器和存儲(chǔ)過(guò)程復(fù)制配置二進(jìn)制日志/中繼日志/日志索引文件/.info 文件服務(wù)器配置服務(wù)器的配置文件選定的操作系統(tǒng)文件對(duì)生產(chǎn)服務(wù)器至關(guān)重要的外部配置。在 unix 服務(wù)器上，可能包括了 cron 任務(wù)/用戶和組的配置/管理腳本/sudo 規(guī)則等
根據(jù)業(yè)務(wù)權(quán)衡，備份的數(shù)據(jù)越多，類(lèi)型越齊全，就越有利于你恢復(fù)到想要的效果
備份我們需要考慮什么因素
其實(shí)備份考慮的因素不多，關(guān)鍵的有以下幾個(gè)
- 鎖時(shí)間
- 備份時(shí)間
- 備份負(fù)載
- 恢復(fù)時(shí)間
關(guān)于鎖時(shí)間，我們需要考慮是否一定要鎖表？鎖表時(shí)間可接受的范圍是多少？如果是熱備份，在什么時(shí)候進(jìn)行鎖表才不會(huì)影響業(yè)務(wù)？
備份的方案有哪些？
方案名稱適用場(chǎng)景mysqldump + binlog全量備份 + 增量備份混合方案xtrabackupInnoDB 支持熱備，支持全量備份/增量備份，MyISAM 支持溫備，只支持全量備份lvm + binlog熱備，物理備份
實(shí)踐
前期準(zhǔn)備
- 創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)
- 執(zhí)行以下 SQL，準(zhǔn)備好我們的基礎(chǔ)數(shù)據(jù)
-- ----------------------------
-- 創(chuàng)建一個(gè)表
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- 插入基礎(chǔ)數(shù)據(jù)
-- ----------------------------
INSERT INTO `user` VALUES ('1', '123');
INSERT INTO `user` VALUES ('2', '456');
?? 使用 mysqldump+binlog 備份
mysqldump 其實(shí)是一個(gè) mysql 的一個(gè)命令行。binlog 是一個(gè)二進(jìn)制格式的文件,用于記錄用戶對(duì)數(shù)據(jù)庫(kù)更新的 SQL 語(yǔ)句信息,例如更改數(shù)據(jù)庫(kù)表和更改內(nèi)容的 SQL 語(yǔ)句都會(huì)記錄到 binlog 里，對(duì)查詢等操作并不會(huì)記錄。
場(chǎng)景模擬
- 在基礎(chǔ)數(shù)據(jù)下，先做一個(gè)全量備份
- 模擬新增數(shù)據(jù)操作，增加新數(shù)據(jù)
- 然后使用 binlog 做一個(gè)增量備份
- 模擬數(shù)據(jù)庫(kù)誤操作，將數(shù)據(jù)表刪除
- 關(guān)閉二進(jìn)制日志，然后恢復(fù)全量備份，備份完后開(kāi)啟二進(jìn)制日志
- 通過(guò)增量備份恢復(fù)數(shù)據(jù)
- 檢查恢復(fù)情況
根據(jù)場(chǎng)景模擬開(kāi)始之前，我們需要確認(rèn) mysqldump 是否開(kāi)啟。在 SQL 命令行模式下檢查是否開(kāi)啟：
// Off 關(guān)閉；On 開(kāi)啟 show variables like 'log_bin';
如果沒(méi)開(kāi)啟，我們打開(kāi)并編輯 /etc/my.cnf
log-bin=/root/mysql/bin-log/bin-log-file expire-logs-days = 14 max-binlog-size = 500M server-id = 1
保存后重啟，再次檢查是否開(kāi)啟
第一步
檢查目前的 binlog 備份狀態(tài)，便于
mysql -e 'SHOW MASTER STATUS'
結(jié)果
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000000 | 45 | | | +------------------+----------+--------------+------------------+
Position 代表著已經(jīng)被備份數(shù)據(jù)的位置，我們需要記住便于接下來(lái)從這個(gè)位置恢復(fù)。
使用 mysqldump 進(jìn)行全量備份
mysqldump --all-databases --lock-all-tables > user_backerup.sql
第二步
模擬前端新增操作，代表著目前的數(shù)據(jù)已經(jīng)發(fā)生了變化
INSERT INTO `user` VALUES ('3', '456');
第三步
我們?cè)俅尾榭茨壳暗脑隽總浞菸募嵌嗌?/p>
show master status
假設(shè)結(jié)果是
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000000 | 80 | | | +------------------+----------+--------------+------------------+
使用 binlog 進(jìn)行增量備份，在 sql 命令執(zhí)行 flush logs 后，會(huì)在你之前設(shè)的 logbin 文件夾下多一份文件 mysql-bin.000001，那么這份就是增量備份。
第四步
我們可以數(shù)據(jù)庫(kù)誤操作，例如說(shuō)不小心刪了表，或者刪除了一些表數(shù)據(jù)。我這里通過(guò)刪表作為誤操作
drop table user;
再檢查是否真的刪除了
show tables;
第五步
因?yàn)楝F(xiàn)在我們已經(jīng)誤操作了，我們需要進(jìn)行全量備份，然后再增量備份。
關(guān)閉二進(jìn)制日志
SET sql_log_bin=OFF;
然后執(zhí)行全量備份文件
mysql -uroot -p user < user_backerup.sql
執(zhí)行完后再次開(kāi)啟二進(jìn)制日志
SET sql_log_bin=ON;
第六步
這時(shí)候，我們應(yīng)該想到了，還差增量備份的數(shù)據(jù)。就能返回到了誤操作的前面。
所以我們使用 mysqlbinlog 命令執(zhí)行增量備份文件
mysqlbinlog --start-position=45 --stop-position=80 mysql-bin.000001 | mysql user
第七步
接下來(lái)就是檢查的情況了
show tables;
?? 使用 xtrabackup 備份
xtrabackup 是一款開(kāi)源的免費(fèi)數(shù)據(jù)庫(kù)熱備份軟件，實(shí)現(xiàn)非阻塞備份 InnoDB 引擎數(shù)據(jù)庫(kù)，但是對(duì)于 MyISAM 還是需要加表鎖備份。
下面是 xtrabackup 的優(yōu)點(diǎn)
- 備份速度快，還原速度快，物理備份可靠
- 無(wú)須鎖表，實(shí)現(xiàn)熱備份；支持壓縮備份
- 低負(fù)載備份，降低服務(wù)器負(fù)載
- 備份文件可跨平臺(tái)
- 還原速度快
- 支持加密備份
環(huán)境安裝
默認(rèn)你已經(jīng)根據(jù)自身情況安裝了相對(duì)的版本的 xtrabackup
我們依舊通過(guò)上面的場(chǎng)景模擬，用 xtrabackup 進(jìn)行全量備份腳本、增量備份恢復(fù)
模擬全量備份腳本
- 執(zhí)行以下 SQL，準(zhǔn)備好我們的基礎(chǔ)數(shù)據(jù)
- 使用 xtrabackup 進(jìn)行全量備份
- 模擬人為數(shù)據(jù)庫(kù)誤操作
- 通過(guò) xtrabackup 進(jìn)行恢復(fù)
使用命令行進(jìn)行全量備份
xtrabackup --backup --target-dir=/root/xtrabackup/bakcups --user=root --password=root
參數(shù)解釋?zhuān)?/p>
--backup：將備份文件讓道 target-dir，也就是說(shuō)明它和 target-dir 是搭配使用的
--target-dir：備份文件放置文件，當(dāng)前我使用的文件夾是 /root/xtrabackup/bakcups
如果看到有類(lèi)似輸出，即說(shuō)明已經(jīng)成功備份了
190904 14:30:48 [00] Writing xtrabackup_info 190904 14:30:48 [00] ...done xtrabackup: Transaction log of lsn (4417990) to (4417999) was copied. 190904 14:30:49 completed OK!
然后我們執(zhí)行 SQL，模擬誤操作，增刪改都可以。我這里就直接刪除一個(gè)表吧~
drop tables tablesname;
接著通過(guò)命令進(jìn)行全量恢復(fù)
xtrabackup --prepare --target-dir=/root/xtrabackup/bakcups
這時(shí)候可以打開(kāi)數(shù)據(jù)進(jìn)行檢驗(yàn)。
模擬增量備份恢復(fù)
增量備份目前僅可用于 InnoDB 或 XtraDB，對(duì)于 MyISAM，增量和全量備份同樣還是會(huì)掃描全表的
通常在做增量備份，先做一個(gè)全量備份的(如果需要賬號(hào)密碼登錄自行加上)。
xtrabackup --backup --target-dir=/root/xtrabackup/base
在 /data/backups/base 下會(huì)生成很多文件。我對(duì)于增量備份，我們著重看一個(gè)叫 xtrabackup_checkpoints。以下是它的結(jié)構(gòu)：
backup_type = full-backuped // 備份類(lèi)型 from_lsn = 0 // 初始位置 to_lsn = 15188961605 // 備份位置 last_lsn = 15188961605 // 最后備份位置
也就是說(shuō)，增量備份會(huì)基于全量備份的信息進(jìn)行備份的。
xtrabackup --backup --target-dir=/root/xtrabackup/inc1 --incremental-basedir=/root/xtrabackup/base
剛剛生成的 /root/xtrabackup/inc1 里邊包含大多信息，而且這里邊也有一個(gè) xtrabackup_checkpoints 文件。我給出一個(gè)大概結(jié)構(gòu)的文件
backup_type = incremental from_lsn = 4124244 to_lsn = 6938371 last_lsn = 7110572 compact = 0 recover_binlog_info = 1
現(xiàn)在我們通過(guò) xtrabackup --prepare 進(jìn)行數(shù)據(jù)恢復(fù)。
innobackupex --defaults-file=/etc/my.cnf --user=root --password='password' /backup/20180423/
接下來(lái)就是檢查的情況了
關(guān)于備份與恢復(fù)的一些知識(shí)點(diǎn)
- 有些部分備份不會(huì)真正減少服務(wù)器的開(kāi)銷(xiāo)。
- 不要備份沒(méi)有改變的表。MyISAM 會(huì)記錄每個(gè)表最后修改時(shí)間，通過(guò)查看磁盤(pán)文件或運(yùn)行 show tables status 來(lái)看時(shí)間；如果是 InnoDB。，可以利用觸發(fā)器記錄修改時(shí)間到一個(gè)小的“最后修改時(shí)間”表中，幫助追蹤最新的修改操作。需要確保只對(duì)變更不頻繁的表進(jìn)行跟蹤，這樣才能降低開(kāi)銷(xiāo)。通過(guò)定制腳本可以輕松獲得哪些表變更了。
- 增量備份的缺點(diǎn)是，增加了恢復(fù)的復(fù)雜度，額外的風(fēng)險(xiǎn)，更長(zhǎng)的恢復(fù)時(shí)間。如果可以做全備，盡量做全備。
- 建議備份至少一周一次。
- 但是一般情況下，這個(gè)備份是不能用于恢復(fù)的，因?yàn)閭浞莸臄?shù)據(jù)中可能會(huì)包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件處于不一致的狀態(tài)，我們現(xiàn)在就是要通過(guò)回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。
- 備份文件的命名需要規(guī)范起來(lái)。例如全量備份的話可以使用特定標(biāo)識(shí)作為前綴；增量備份可以以時(shí)間段作為命名
