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 總結
備份和恢複主要使用在以下幾個方面：
- 災難恢複
- 需求的變更或者版本復原
- 資料和變更審計
- 多版本測試
為幫助開發者們提升面試技能、有機會入職BATJ等大廠公司，特别制作了這個專輯——這一次整體放出。
大緻内容包括了： Java 集合、JVM、多線程、并發程式設計、設計模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大廠面試題等、等技術棧！
歡迎大家關注公衆号【Java爛豬皮】，回複【666】，擷取以上最新Java後端架構VIP學習資料以及視訊學習教程，然後一起學習，一文在手，面試我有。
每一個專欄都是大家非常關心，和非常有價值的話題，如果我的文章對你有所幫助，還請幫忙點贊、好評、轉發一下，你的支援會激勵我輸出更高品質的文章，非常感謝！