首頁 > 專題 > 正文

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

2023-05-30 01:38:33來源：博客園  

合輯地址：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.sqlmysqldump -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 總結

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

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

關鍵詞：

責任編輯：hnmd003

相關閱讀

推薦閱讀