首頁 > 專題 > 正文

熱門：MySQL全面瓦解30：備份與恢復(fù)

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

合輯地址：MySQL全面瓦解


【資料圖】

1 為什么需要數(shù)據(jù)庫備份災(zāi)難恢復(fù)：當(dāng)發(fā)生數(shù)據(jù)災(zāi)難的時(shí)候，需要對損壞的數(shù)據(jù)進(jìn)行恢復(fù)和還原需求的變更或者回滾：當(dāng)需求發(fā)生變更，或者需要回滾到之前的版本時(shí)，數(shù)據(jù)庫備份也顯得很重要。審計(jì)：需要知道某一個(gè)階段的數(shù)據(jù)或者Schema的實(shí)際情況測試：將實(shí)際的生產(chǎn)環(huán)境的數(shù)據(jù)導(dǎo)入到本地備份為測試數(shù)據(jù)，來驗(yàn)證新功能，可以省去很多麻煩。2 備份需要考慮的幾個(gè)關(guān)鍵點(diǎn)恢復(fù)點(diǎn)目標(biāo)（PRO）：可以容忍丟失多少數(shù)據(jù)恢復(fù)時(shí)間目標(biāo)（RTO）：需要等待多久將數(shù)據(jù)恢復(fù)恢復(fù)的時(shí)候是需要持續(xù)提供服務(wù) 還是 停機(jī)恢復(fù)。需要恢復(fù)的內(nèi)容：整個(gè)服務(wù)器，多庫多表，單庫單表，或是特定的事務(wù)或語句。3 備份方案3.1 離線備份和在線備份

離線備份：就是傳統(tǒng)意義上的cold backup（冷備份）：需要關(guān)閉MySQL服務(wù)，讀寫請求均不允許狀態(tài)下進(jìn)行，這種模式下數(shù)據(jù)損壞和不一致性風(fēng)險(xiǎn)最小。半離線備份：也就是我們說的warm backup（溫備份）： MySQL服務(wù)不關(guān)閉，但只開放了Read操作，關(guān)閉了Write操作。在線備份：也就是hot backup（熱備份）：在數(shù)據(jù)備份的同時(shí)，MySQL業(yè)務(wù)持續(xù)進(jìn)行中，僅限于InnoDB引擎。

3.2 邏輯備份和物理備份3.2.1 邏輯備份：導(dǎo)出數(shù)據(jù)庫表的定義和數(shù)據(jù)

邏輯備份有如下優(yōu)點(diǎn)：

恢復(fù)非常簡單可以通過網(wǎng)絡(luò)來備份和恢復(fù)備份的結(jié)果為ASCII文件，可以編輯與存儲引擎無關(guān)非常靈活，可以使用mysqldump的工具提供很多可選項(xiàng)。

邏輯備份的缺點(diǎn)：

必須由數(shù)據(jù)庫服務(wù)器來完成備份和恢復(fù)過程備份結(jié)果占據(jù)更多的空間：邏輯備份在某些場景下比數(shù)據(jù)庫文件本身還要大精度問題，無法保證還原出來的數(shù)據(jù)強(qiáng)一致還原時(shí)間長：還原之后，加載注釋語句，轉(zhuǎn)換存儲格式，重建索引都需要消耗一定時(shí)間3.2.2 物理備份：直接復(fù)制原數(shù)據(jù)文件

物理備份的優(yōu)點(diǎn)：

備份和恢復(fù)操作都比較簡單，且能夠跨平臺，操作系統(tǒng)和MySQL版本。恢復(fù)速度快，都是基于文件的，復(fù)制到對應(yīng)的目的地即可，InnoDB需要停止數(shù)據(jù)庫服務(wù)，有額外的動(dòng)作。步驟更少：不需要執(zhí)行重新生成數(shù)據(jù)和重建索引的動(dòng)作，效率提升。

物理備份的缺點(diǎn)：

InnoDB備份的原始文件往往比邏輯備份的大很多，空間要求大。3.3 根據(jù)要備份的數(shù)據(jù)集合的范圍完全備份：full backup，備份整個(gè)數(shù)據(jù)庫信息。增量備份: incremental backup 上次完全備份或增量備份以來改變了的數(shù)據(jù)，需與完全備份配合使用。一般來說增量頻率高，備份頻率也高。差異備份：differential backup 上次完全備份以來改變了的數(shù)據(jù)。建議的恢復(fù)策略：完全+增量+二進(jìn)制日志完全+差異+二進(jìn)制日志4 備份的內(nèi)容主要有哪些？數(shù)據(jù)：基礎(chǔ)數(shù)據(jù)。日志：包含 二進(jìn)制日志 和 InnoDB事務(wù)日志 等。配置信息：包括服務(wù)器配置 和 復(fù)制相關(guān)的配置（主從復(fù)制中的中繼日志和日志索引文件等）。代碼：存儲過程、函數(shù)、觸發(fā)器、視圖等選定的OS文件：入U(xiǎn)NIX服務(wù)器上的 cron任務(wù)、用戶和組的配置、管理的腳本、sudo規(guī)則等。5 數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)方案介紹5.1 輸出outfile文件

使用 select into outfile方式實(shí)現(xiàn)數(shù)據(jù)的備份和還原具體的操作步驟如下：

# 選擇對應(yīng)的數(shù)據(jù)庫mysql> use attend;  Database changed# 查詢需要備份的數(shù)據(jù)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)# 選擇備份的數(shù)據(jù)（可以精確條件），應(yīng)該有兩條數(shù)據(jù)，注意備份的地址具備write權(quán)限mysql> select * from userinfo where id < 10000 into outfile "/Users/Brand/Downloads/tmp/userinfo.txt" ;# 檢查文件是否存在brand@MacBook-Pro ~ %  cd /Users/Brand/Downloads/tmp/# 因?yàn)樗俏谋灸Ｊ剑晕覀兪褂?load data infile 恢復(fù)，并且在恢復(fù)之前先刪除掉要恢復(fù)的數(shù)據(jù)，做個(gè)測試mysql> delete from userinfo where id < 10000;mysql> load data infile "/Users/Brand/Downloads/tmp/userinfo.txt" into table userinfo;
5.2 使用工具進(jìn)行備份與還原

可以使用類似 mysqldump工具 或者 mysqlhotcopy工具對數(shù)據(jù)進(jìn)行備份和還原，也可以使用免費(fèi)的熱備份軟件 Percona XtraBackup。這邊以 mysqldump 為例子演示溫備的實(shí)現(xiàn)：

5.2.1 備份基本語法
mysqldump -h主機(jī) -P端口 -u用戶名  -p密碼 param1, param2, param3... > bak_filename.sql

這邊對各個(gè)字段坐下說明：

h：登錄用戶所在的主機(jī)名稱P：主機(jī)端口u：登錄用戶用戶名p：用戶密碼param：導(dǎo)出參數(shù)（庫、表、加鎖等參數(shù)）">"：將備份數(shù)據(jù)表的定義和數(shù)據(jù)寫入備份文件的定義bak_filename.sql：備份的文件名5.2.2 導(dǎo)出全部數(shù)據(jù)庫

–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 導(dǎo)出部分?jǐn)?shù)據(jù)表

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

mysqldump -uroot -p123456 --databases db1  --tables tb1 --where="id>1000"  > /user/brand/db_bak/db1_tb1.sql
5.2.4 創(chuàng)建之前先刪庫或表

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

在create database 前先 drop database；在create table之前先 drop table默認(rèn)關(guān)閉，所以一般在導(dǎo)入時(shí)需要保證數(shù)據(jù)庫已存在。。
mysqldump -uroot -p123456  -A --add-drop-database --skip-add-drop-table >  /user/brand/db_bak/all.sql
5.2.5 鎖表

–add-locks：備份數(shù)據(jù)庫表時(shí)鎖定數(shù)據(jù)庫表，默認(rèn)就是打開的狀態(tài)，可以使用–skip-add-locks取消

# 不佳參數(shù)選項(xiàng)的時(shí)候，默認(rèn)是添加LOCK的mysqldump -uroot -p123456  -A  >  /user/brand/db_bak/all.sql# 取消LOCK的狀態(tài)mysqldump -uroot -p123456  -A --skip-add-locks   > /user/brand/db_bak/all_skip_lock.sql
5.2.6 進(jìn)行壓縮

–compact：壓縮模式，去掉注釋、頭尾等結(jié)構(gòu)信息，讓輸出更少

mysqldump -uroot -p123456  -A --compact >  /user/brand/db_bak/all_compact.sql
5.2.7 數(shù)據(jù)恢復(fù)

使用mysql命令進(jìn)行恢復(fù)，語法如下

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

注意箭頭方向

# 刪除數(shù)據(jù)庫，模擬數(shù)據(jù)庫損壞mysql> drop database db1;# 導(dǎo)入完全備份的文件mysql < /user/brand/db_bak/all_compact.sql
6 總結(jié)

備份和恢復(fù)主要使用在以下幾個(gè)方面：

災(zāi)難恢復(fù)需求的變更或者版本回滾數(shù)據(jù)和變更審計(jì)多版本測試

關(guān)鍵詞：

責(zé)任編輯：hnmd003

相關(guān)閱讀

推薦閱讀