合輯地址：MySQL全面瓦解

1 為什麼需要資料庫備份

災難恢復：當發生資料災難的時候，需要對損壞的資料進行恢復和還原
需求的變更或者回滾：當需求發生變更，或者需要回滾到之前的版本時，資料庫備份也顯得很重要。
審計：需要知道某一個階段的資料或者Schema的實際情況
測試：將實際的生產環境的資料匯入到本地備份為測試資料，來驗證新功能，可以省去很多麻煩。

2 備份需要考慮的幾個關鍵點

恢復點目標（PRO）：可以容忍丟失多少資料
恢復時間目標（RTO）：需要等待多久將資料恢復
恢復的時候是需要持續提供服務還是停機恢復。
需要恢復的內容：整個伺服器，多庫多表，單庫單表，或是特定的事務或語句。

3 備份方案

3.1 離線備份和線上備份

離線備份：就是傳統意義上的cold backup（冷備份）：需要關閉MySQL服務，讀寫請求均不允許狀態下進行，這種模式下資料損壞和不一致性風險最小。
半離線備份：也就是我們說的warm backup（溫備份）： MySQL服務不關閉，但只開放了Read操作，關閉了Write操作。
線上備份：也就是hot backup（熱備份）：在資料備份的同時，MySQL業務持續進行中，僅限於InnoDB引擎。

3.2 邏輯備份和物理備份

3.2.1 邏輯備份：匯出資料庫表的定義和資料

邏輯備份有如下優點：

恢復非常簡單
可以通過網路來備份和恢復
備份的結果為ASCII檔案，可以編輯
與儲存引擎無關
非常靈活，可以使用mysqldump的工具提供很多可選項。

邏輯備份的缺點：

必須由資料庫伺服器來完成備份和恢復過程
備份結果佔據更多的空間：邏輯備份在某些場景下比資料庫檔案本身還要大
精度問題，無法保證還原出來的資料強一致
還原時間長：還原之後，載入註釋語句，轉換儲存格式，重建索引都需要消耗一定時間

3.2.2 物理備份：直接複製原資料檔案

物理備份的優點：

備份和恢復操作都比較簡單，且能夠跨平臺，作業系統和MySQL版本。
恢復速度快，都是基於檔案的，複製到對應的目的地即可，InnoDB需要停止資料庫服務，有額外的動作。
步驟更少：不需要執行重新生成資料和重建索引的動作，效率提升。

物理備份的缺點：

InnoDB備份的原始檔案往往比邏輯備份的大很多，空間要求大。

3.3 根據要備份的資料集合的範圍

完全備份：full backup，備份整個資料庫資訊。
增量備份: incremental backup 上次完全備份或增量備份以來改變了的資料，需與完全備份配合使用。一般來說增量頻率高，備份頻率也高。
差異備份：differential backup 上次完全備份以來改變了的資料。
建議的恢復策略：
- 完全+增量+二進位制紀錄檔
- 完全+差異+二進位制紀錄檔

4 備份的內容主要有哪些？

資料：基礎資料。
紀錄檔：包含二進位制紀錄檔和 InnoDB事務紀錄檔等。
設定資訊：包括伺服器設定和複製相關的設定（主從複製中的中繼紀錄檔和紀錄檔索引檔案等）。
程式碼：儲存過程、函數、觸發器、檢視等
選定的OS檔案：入UNIX伺服器上的 cron任務、使用者和組的設定、管理的指令碼、sudo規則等。

5 資料備份和資料恢復方案介紹

5.1 輸出outfile檔案

使用 select into outfile 方式實現資料的備份和還原
具體的操作步驟如下：

# 選擇對應的資料庫
mysql> use attend;  
Database changed

# 查詢需要備份的資料
mysql> select * from userinfo where id < 10000;
+----+----------+------------------+---------+
| id | usercode | username         | usersex |
+----+----------+------------------+---------+
|  1 | 374532   | 翁智華_attend    |       1 |
|  2 | 123456   | 小度             |       0 |
+----+----------+------------------+---------+
2 rows in set (0.01 sec)

# 選擇備份的資料（可以精確條件），應該有兩條資料，注意備份的地址具備write許可權
mysql> select * from userinfo where id < 10000 into outfile '/Users/Brand/Downloads/tmp/userinfo.txt' ;

# 檢查檔案是否存在
brand@MacBook-Pro ~ %  cd /Users/Brand/Downloads/tmp/

# 因為它是文字模式，所以我們使用 load data infile 恢復，並且在恢復之前先刪除掉要恢復的資料，做個測試
mysql> delete from userinfo where id < 10000;
mysql> load data infile '/Users/Brand/Downloads/tmp/userinfo.txt' into table userinfo;

5.2 使用工具進行備份與還原

可以使用類似 mysqldump工具或者 mysqlhotcopy工具對資料進行備份和還原，也可以使用免費的熱備份軟體 Percona XtraBackup。
這邊以 mysqldump 為例子演示溫備的實現：

5.2.1 備份基本語法

mysqldump -h主機 -P埠 -u使用者名稱  -p密碼 param1, param2, param3... > bak_filename.sql

這邊對各個欄位坐下說明：

h：登入使用者所在的主機名稱
P：主機埠
u：登入使用者使用者名稱
p：使用者密碼
param：匯出引數（庫、表、加鎖等引數）
">"：將備份資料表的定義和資料寫入備份檔案的定義
bak_filename.sql：備份的檔名

5.2.2 匯出全部資料庫

–all-databases 或者 -A

mysqldump -uroot -p123456  --all-databases  >  /user/brand/db_bak/all.sql
mysqldump -uroot -p123456  -A  > /user/brand/db_bak/all.sql

5.2.3 匯出部分資料表

-databases [dbname,[dbname...]] --tables [tbname,[tbname...]] ,如果多個表where條件相同，也可以組合在一起使用：

mysqldump -uroot -p123456 --databases db1  --tables tb1 --where="id>1000"  > /user/brand/db_bak/db1_tb1.sql

5.2.4 建立之前先刪庫或表

–add-drop-database 、 –add-drop-table

在create database 前先 drop database；在create table之前先 drop table
預設關閉，所以一般在匯入時需要保證資料庫已存在。。

mysqldump -uroot -p123456  -A --add-drop-database --skip-add-drop-table >  /user/brand/db_bak/all.sql

5.2.5 鎖表

–add-locks：備份資料庫表時鎖定資料庫表，預設就是開啟的狀態，可以使用–skip-add-locks取消

# 不佳引數選項的時候，預設是新增LOCK的
mysqldump -uroot -p123456  -A  >  /user/brand/db_bak/all.sql

# 取消LOCK的狀態
mysqldump -uroot -p123456  -A --skip-add-locks   > /user/brand/db_bak/all_skip_lock.sql

5.2.6 進行壓縮

–compact：壓縮模式，去掉註釋、頭尾等結構資訊，讓輸出更少

mysqldump -uroot -p123456  -A --compact >  /user/brand/db_bak/all_compact.sql

5.2.7 資料恢復

使用mysql命令進行恢復，語法如下

mysql -u user -p pwd [dbname] < bak_filename.sql

注意箭頭方向

# 刪除資料庫，模擬資料庫損壞
mysql> drop database db1;

# 匯入完全備份的檔案
mysql < /user/brand/db_bak/all_compact.sql

6 總結

備份和恢復主要使用在以下幾個方面：

災難恢復
需求的變更或者版本回滾
資料和變更審計
多版本測試

MySQL全面瓦解30：備份與恢復