前言

這周又是上線周。辦公桌的頭發越來越多了，保溫杯都是枸杞，電腦壁紙也換成了應急逃生通道（不要問我為什么是應急通道，因為打算隨時跑路）。

因為是新系統要與舊系統之間進行數據同步，清洗，分發。所以，這周任務是不斷地核實數據，調試程序，與數據庫打交道的占比很高。

一旦要到數據庫這個話題，永遠也避不開數據安全的問題。所以今天我就來講講怎么使用 MySQL 的備份與恢復。

拋出本文問題

首先，在講 MySQL 備份之前，我想明確咱們接下來需要探究的問題

1. 備份這么麻煩，但是為什么值得我們去做？
2. 多得一批的備份術語
3. 我們究竟需要備份什么？
4. 備份需要考慮什么因素？
5. 備份的方案有哪些？
6. 實踐

知識背景

為什么我們需要備份？

時間是往前流動的，人生是不可逆轉的，但是數據庫能。我想說幾個場景你是否還很熟悉？

1. 線上項目因為 Bug 或客戶騷操作的問題，導致業務數據缺失，流程無法繼續走下去，沒有回頭了只硬著頭皮線上改數據，結果表一多起來，改了那條都不知道了
2. 上線前從舊系統遷移數據，為上線做準備，結果一執行清洗 SQL，哎呀，IS NOT NULL 忘了改回了 IS NULL，含淚全庫刪除，重新導庫清洗；
3. 新同事在服務器執行了技術大佬傳授真經命令行 rm -rf /*，結果我趕緊給他發了一張高清的緊急逃生通道...

所以說，為什么我們要備份？因為我們要做到無所畏懼，有路可退。在風險面前，我們盡能力去規避風險。這些風險，小到不小心在別的服務器執行了 Alter Table，大到服務器硬件出現故障，全機崩潰，軟件硬件故障/自然災害/人為操作等等。

所以我們需要備份是為了應對來自各方面的威脅

多得一批的備份術語

說起備份，可能你的頭腦里浮現了 熱備份/冷備份/增量備份/差異備份/邏輯備份...放棄的聲音席卷而來！

其實先不要害怕這些術語，它們都是有專門的由來的。

首先是熱備份，溫備份和冷備份。熱備份指的是不需要停止任何服務即可備份，就好像你備份不用關掉數據庫來備份，隨時隨地可進行;冷備份指的是停止數據庫進行數據備份。

然后全量備份和部分備份。

1. 全量備份(類似名字還有全局備份，完全備份)指的是將整個數據庫備份下來。顯然當項目數據達到一定規模，那么整庫備份變得不現實，因為備份時間變得更長，同時需要更多地磁盤資源，機器資源...
2. 部分備份指的是將部分數據集備份下來，例如備份某庫某表某個時間段的數據，或者是僅僅備份某庫某表的所有數據。部分備份一般不包含完整的數據集，而我們明顯可以僅僅備份所更改的數據，這樣可以減少服務器的開銷/備份時間/備份空間。根據部分備份的概念，我們可以拆分成兩種備份方式：增量備份和差異備份，下面使用表格說明：

名稱說明增量備份對自上次全備份后所有改變的部分而做的備份差異備份自從任意類型的上次備份后所有修改做的備份

舉例說明，假設在周日做了一個全量備份。在周一，對自周日以來所有的改變做一個差異備份。在周二，你有兩個選擇：備份周日以來所有的改變(差異備份)，或只備份自從周一備份后所有的改變(增量備份)

我們究竟需要備份信息？

可能說到這個問題上，大多數人第一反應就是備份表結構+表數據。恭喜你，你猜對了一半，但是這個方案是備份中最低的要求，因為在數據庫中還存在很多被忽略的數據在默默支撐著數據庫的正常運行。下面介紹一下數據庫哪些值得關注的數據：

類型內容非顯著信息二進制日志和 InnoDB 事務日志代碼觸發器和存儲過程復制配置二進制日志/中繼日志/日志索引文件/.info 文件服務器配置服務器的配置文件選定的操作系統文件對生產服務器至關重要的外部配置。在 unix 服務器上，可能包括了 cron 任務/用戶和組的配置/管理腳本/sudo 規則等

根據業務權衡，備份的數據越多，類型越齊全，就越有利于你恢復到想要的效果

備份我們需要考慮什么因素

其實備份考慮的因素不多，關鍵的有以下幾個

1. 鎖時間
2. 備份時間
3. 備份負載
4. 恢復時間

關于鎖時間，我們需要考慮是否一定要鎖表？鎖表時間可接受的范圍是多少？如果是熱備份，在什么時候進行鎖表才不會影響業務？

備份的方案有哪些？

方案名稱適用場景mysqldump + binlog全量備份 + 增量備份混合方案xtrabackupInnoDB 支持熱備，支持全量備份/增量備份，MyISAM 支持溫備，只支持全量備份lvm + binlog熱備，物理備份

實踐

前期準備

1. 創建一個數據庫
2. 執行以下 SQL，準備好我們的基礎數據

-- ----------------------------
-- 創建一個表
-- ----------------------------
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;
-- ----------------------------
-- 插入基礎數據
-- ----------------------------
INSERT INTO `user` VALUES ('1', '123');
INSERT INTO `user` VALUES ('2', '456');

?? 使用 mysqldump+binlog 備份

mysqldump 其實是一個 mysql 的一個命令行。binlog 是一個二進制格式的文件,用于記錄用戶對數據庫更新的 SQL 語句信息,例如更改數據庫表和更改內容的 SQL 語句都會記錄到 binlog 里，對查詢等操作并不會記錄。

場景模擬

1. 在基礎數據下，先做一個全量備份
2. 模擬新增數據操作，增加新數據
3. 然后使用 binlog 做一個增量備份
4. 模擬數據庫誤操作，將數據表刪除
5. 關閉二進制日志，然后恢復全量備份，備份完后開啟二進制日志
6. 通過增量備份恢復數據
7. 檢查恢復情況

根據場景模擬開始之前，我們需要確認 mysqldump 是否開啟。在 SQL 命令行模式下檢查是否開啟：

// Off 關閉；On 開啟
show variables like 'log_bin';

如果沒開啟，我們打開并編輯 /etc/my.cnf

log-bin=/root/mysql/bin-log/bin-log-file
expire-logs-days = 14
max-binlog-size = 500M
server-id = 1

保存后重啟，再次檢查是否開啟

第一步

檢查目前的 binlog 備份狀態，便于

mysql -e 'SHOW MASTER STATUS'

結果

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000000 | 45 | | |
+------------------+----------+--------------+------------------+

Position 代表著已經被備份數據的位置，我們需要記住便于接下來從這個位置恢復。

使用 mysqldump 進行全量備份

mysqldump --all-databases --lock-all-tables > user_backerup.sql

第二步

模擬前端新增操作，代表著目前的數據已經發生了變化

INSERT INTO `user` VALUES ('3', '456');

第三步

我們再次查看目前的增量備份文件是多少

show master status

假設結果是

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000000 | 80 | | |
+------------------+----------+--------------+------------------+

使用 binlog 進行增量備份，在 sql 命令執行 flush logs 后，會在你之前設的 logbin 文件夾下多一份文件 mysql-bin.000001，那么這份就是增量備份。

第四步

我們可以數據庫誤操作，例如說不小心刪了表，或者刪除了一些表數據。我這里通過刪表作為誤操作

drop table user;

再檢查是否真的刪除了

show tables;

第五步

因為現在我們已經誤操作了，我們需要進行全量備份，然后再增量備份。

關閉二進制日志

SET sql_log_bin=OFF;

然后執行全量備份文件

mysql -uroot -p user < user_backerup.sql

執行完后再次開啟二進制日志

SET sql_log_bin=ON;

第六步

這時候，我們應該想到了，還差增量備份的數據。就能返回到了誤操作的前面。

所以我們使用 mysqlbinlog 命令執行增量備份文件

mysqlbinlog --start-position=45 --stop-position=80 mysql-bin.000001 | mysql user

第七步

接下來就是檢查的情況了

show tables;

?? 使用 xtrabackup 備份

xtrabackup 是一款開源的免費數據庫熱備份軟件，實現非阻塞備份 InnoDB 引擎數據庫，但是對于 MyISAM 還是需要加表鎖備份。

下面是 xtrabackup 的優點

1. 備份速度快，還原速度快，物理備份可靠
2. 無須鎖表，實現熱備份；支持壓縮備份
3. 低負載備份，降低服務器負載
4. 備份文件可跨平臺
5. 還原速度快
6. 支持加密備份

環境安裝

默認你已經根據自身情況安裝了相對的版本的 xtrabackup

我們依舊通過上面的場景模擬，用 xtrabackup 進行全量備份腳本、增量備份恢復

模擬全量備份腳本

1. 執行以下 SQL，準備好我們的基礎數據
2. 使用 xtrabackup 進行全量備份
3. 模擬人為數據庫誤操作
4. 通過 xtrabackup 進行恢復

使用命令行進行全量備份

xtrabackup --backup --target-dir=/root/xtrabackup/bakcups --user=root --password=root

參數解釋：

--backup：將備份文件讓道 target-dir，也就是說明它和 target-dir 是搭配使用的

--target-dir：備份文件放置文件，當前我使用的文件夾是 /root/xtrabackup/bakcups

如果看到有類似輸出，即說明已經成功備份了

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!

然后我們執行 SQL，模擬誤操作，增刪改都可以。我這里就直接刪除一個表吧~

drop tables tablesname;

接著通過命令進行全量恢復

xtrabackup --prepare --target-dir=/root/xtrabackup/bakcups

這時候可以打開數據進行檢驗。

模擬增量備份恢復

增量備份目前僅可用于 InnoDB 或 XtraDB，對于 MyISAM，增量和全量備份同樣還是會掃描全表的

通常在做增量備份，先做一個全量備份的(如果需要賬號密碼登錄自行加上)。

xtrabackup --backup --target-dir=/root/xtrabackup/base

在 /data/backups/base 下會生成很多文件。我對于增量備份，我們著重看一個叫 xtrabackup_checkpoints。以下是它的結構：

backup_type = full-backuped // 備份類型
from_lsn = 0 // 初始位置
to_lsn = 15188961605 // 備份位置
last_lsn = 15188961605 // 最后備份位置

也就是說，增量備份會基于全量備份的信息進行備份的。

xtrabackup --backup --target-dir=/root/xtrabackup/inc1  --incremental-basedir=/root/xtrabackup/base

剛剛生成的 /root/xtrabackup/inc1 里邊包含大多信息，而且這里邊也有一個 xtrabackup_checkpoints 文件。我給出一個大概結構的文件

backup_type = incremental
from_lsn = 4124244 
to_lsn = 6938371
last_lsn = 7110572
compact = 0
recover_binlog_info = 1

現在我們通過 xtrabackup --prepare 進行數據恢復。

innobackupex --defaults-file=/etc/my.cnf --user=root --password='password' /backup/20180423/

接下來就是檢查的情況了

關于備份與恢復的一些知識點

1. 有些部分備份不會真正減少服務器的開銷。
2. 不要備份沒有改變的表。MyISAM 會記錄每個表最后修改時間，通過查看磁盤文件或運行 show tables status 來看時間；如果是 InnoDB。，可以利用觸發器記錄修改時間到一個小的“最后修改時間”表中，幫助追蹤最新的修改操作。需要確保只對變更不頻繁的表進行跟蹤，這樣才能降低開銷。通過定制腳本可以輕松獲得哪些表變更了。
3. 增量備份的缺點是，增加了恢復的復雜度，額外的風險，更長的恢復時間。如果可以做全備，盡量做全備。
4. 建議備份至少一周一次。
5. 但是一般情況下，這個備份是不能用于恢復的，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件處于不一致的狀態，我們現在就是要通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。
6. 備份文件的命名需要規范起來。例如全量備份的話可以使用特定標識作為前綴；增量備份可以以時間段作為命名