死磕數據庫系列（十）：MySQL 數據庫的備份與恢復

點關注公眾號，回復“1024”獲取2TB學習資源！

在這之前，我們學習了：死磕數據庫系列（八）：MySQL 主從同步詳解、死磕數據庫系列（九）：MySQL 讀寫分離詳解等內容。其實，在數據庫運行開始之時，有一個非常重要點就是：數據庫里的數據備份與恢復，數據對于任何一家企業來說，都是重中之重，那么今天，民工哥就給大家詳解介紹一下MySQL數據庫的數據備份與恢復。

很多人，一看這標題，肯定張口就會答，這不是廢話么。不備份故障了怎么辦？跑路嗎？數據被沙雕開發（不許噴）誤刪了怎么辦？背鍋嗎？

當然，大家都知道備份的重要性與必要性。1、保證數據安全與完整

企業的數據安全應該來說是企業的命脈，一旦丟失或造成損壞，輕則損失客戶與金錢，重則倒閉（已經有前例在）。

備份的目的：為了保證數據在被人為失誤、操作不當、蓄意等情況下刪除或損壞后，能及時、有效的進行恢復并不會很大程度上影響到業務運行。2、為業務提供不間斷服務

實際生產環境對數據庫的要求，首先就是具備7×24×365不間斷服務的能力，這也是一定要備份數據庫的其中原因之一。

一般情況下, 我們需要備份的數據分為以下幾種數據二進制日志, InnoDB事務日志代碼(存儲過程、存儲函數、觸發器、事件調度器)服務器配置文件

按照備份時數據庫的運行狀態，可以分為三種。冷備：停庫、停服務來備份

即當數據庫進行備份時, 數據庫不能進行讀寫操作, 即數據庫要下線溫備：不停庫、不停服務來備份，會(鎖表)阻止用戶的寫入

即當數據庫進行備份時, 數據庫的讀操作可以執行, 但是不能執行寫操作熱備（建議）：不停庫、不停服務來備份，也不會(鎖表)阻止用戶的寫入

即當數據庫進行備份時, 數據庫的讀寫操作均不是受影響MySQL 中進行不同類型的備份還要考慮存儲引擎是否支持

數據庫的備份方式

常用的備份方式包括以下：邏輯備份物理備份邏輯備份

邏輯備份其實就是利用MySQL數據庫自帶的mysqldump命令，或者使用第三方的工具，然后把數據庫里的數據以SQL語句的方式導出成文件的形式。在需要恢復數據時，通過使用相關的命令（如：source ）將備份文件里的SQL語句提取出來重新在數據庫中執行一遍，從而達到恢復數據的目的。

實例如下：

一般備份時都會進行壓縮處理，以節省磁盤空間，如下

恢復操作

上面的操作，都是針對整個數據庫的備份與恢復。但是，在實際生產環境中，這種的備份與恢復方式是有缺點時。因此，一般我們會針對某個庫、某個表，也就是我們所說的單庫、單表進行備份，在需要恢復數據時，我們可以選擇性的進行恢復，可以提高恢復效率，減少宕機時間。單庫備份

下面我們看看這個備份文件到底是什么內容

由上的文件內容，可以看出，這個備份實際的過程就是將創建數據庫、建表、插入數據的sql語句備份出來，也可以說是將sql語句導出。

參數的作用一目了然，就是當我們的數據庫丟失時，可以直接用此備份文件進行恢復，無需再重新建庫、建表，然后再進行數據恢復的操作。單表備份

分庫備份是為了恢復數據庫時方便操作，但是同樣面臨問題，如果是某個庫中的某一個表有損壞，但又不有全庫進行恢復，所以實際生產中常用的是分庫、分表進行備份，這樣數據也備份了，恢復時也好操作

因此分表備份同分庫備份一樣，只需要進行多次單表備份的操作，但是有的小伙伴肯定會提出問題了，如果一個庫里幾千張表，幾萬張表，這種備份要備到猴年馬月吧？？？？，數據量比較大的備份可以使用專業的備份工具，數據量不大或者表不是很多的情況，可以將備份操作寫成腳本 納入定時任務，定時執行（MySQL 數據庫定時備份的幾種方式（非常全面）），只需要檢查備份是否成功即可。邏輯備份的優點與使用場景優點：簡單，易操作，自帶工具方便、可靠。使用場景：數據庫數據量不大的情況可以使用，數據量比較大（超過20G左右）時備份速度比較慢，一定程度上還會影響數據庫本身的性能。物理備份

物理備份就是利用命令（如cp、tar、scp等）直接將數據庫的存儲數據文件復制一份或多份，分別存放在其它目錄，以達到備份的效果。

這種備份方式，由于在備份時數據庫還會存在數據寫入的情況，一定程度上會造成數據丟失的可能性。在進行數據恢復時，需要注意新安裝的數據的目錄路徑、版本、配置等與原數據要保持高度一致，否則同樣也會有問題。

所以，這種物理備份方式，常常需要在停機狀態下進行，一般對實際生產中的數據庫不太可取。因此，此方式比較適用于數據庫物理遷移，這種場景下這種方式比較高效率。物理備份的優點及使用場景優點：速度快，效率高。場景：可用于停機維護及數據庫物理遷移場景中。

實際生產環境中，具體使用哪種方式，就需要看需求與應用場景所定。

在介紹完備份方式之后，再來介紹一下，增量與全量備份這兩個概念。什么是全量備份？

全量備份：就是將數據庫中的所有數據，或者是某一個特定的庫里的所有數據，一次全部備份下來。備份數據庫中所有數據

備份某個庫的數據

什么是增量備份？

增量備份：指的是上一次全量備份之后到下一次全量備份這前這段時間內數據庫所更新或者是增加的數據，將其備份下來。注：全量備份是一個文件，而增量備份則是MySQL的binlog日志文件。所以常說的增量備份就是備份binlog日志文件。兩者的區別在哪？

全量備份：需要的備份時間長一點，恢復時間會短一點，因為文件數少，維護方便。但是，全量備份的文件大，占用一定的磁盤空間，全理備份時會一定程序上影響數據庫的性能（這也就是為什么在0:00點備份的原因），也因文件大的原因，不便于服務器本地保存過多文件，重要業務的全量備份文件可能需要手工下載或遷移到服務器之外的存儲空間中。

增量備份：備份簡單，恢復時復雜一點，因為文件數量多，需將所有binlog文件解析成SQL語句，如下：

然后，再通過恢復的方式進行恢復

或者如下操作

生產Mysql數據庫數據恢復實戰過程 這篇文章是很早之前寫的，大家可以參考一下。Xtrabackup介紹

Xtrabackup是由percona開源的免費數據庫熱備份軟件，它能對InnoDB數據庫和XtraDB存儲引擎的數據庫非阻塞地備份（對于MyISAM的備份同樣需要加表鎖）；mysqldump備份方式是采用的邏輯備份，其最大的缺陷是備份和恢復速度較慢，如果數據庫大于50G，mysqldump備份就不太適合。

Xtrabackup安裝完成后有4個可執行文件，其中2個比較重要的備份工具是innobackupex、xtrabackup

Xtrabackup優點備份速度快，物理備份可靠備份過程不會打斷正在執行的事務（無需鎖表）能夠基于壓縮等功能節約磁盤空間和流量自動備份校驗還原速度快可以流傳將備份傳輸到另外一臺機器上在不增加服務器負載的情況備份數據Xtrabackup備份原理

備份開始時首先會開啟一個后臺檢測進程，實時檢測mysq redo的變化，一旦發現有新的日志寫入，立刻將日志記入后臺日志文件xtrabackup_log中，之后復制innodb的數據文件一系統表空間文件ibdatax，復制結束后，將執行flush tables with readlock,然后復制.frm MYI MYD等文件，最后執行unlock tables,最終停止xtrabackup_log輸出如下提示信息

Xtrabackup增量備份介紹xtrabackup增量備份的原理1)、首先完成一個完全備份，并記錄下此時檢查點LSN；2)、然后增量備份時，比較表空間中每個頁的LSN是否大于上次備份的LSN，若是則備份該頁并記錄當前檢查點的LSN。增量備份優點1)、數據庫太大沒有足夠的空間全量備份，增量備份能有效節省空間，并且效率高；2)、支持熱備份，備份過程不鎖表（針對InnoDB而言），不阻塞數據庫的讀寫；3)、每日備份只產生少量數據，也可采用遠程備份，節省本地空間；4)、備份恢復基于文件操作，降低直接對數據庫操作風險；5)、備份效率更高，恢復效率更高。Xtrabackup安裝

下載安裝xtrabackup

創建測試數據

Xtrabackup全量備份與恢復

從備份過程截圖可以看出會創建一個時間的目錄

接下來準備恢復誤刪除數據

Xtrabackup增量備份與恢復

#需要注意的是，增量備份僅能應用于InooDB或XtraDB表，對于MyISAM表，增量與全備相同

創建用于增量備份的數據，用來模擬刪除掉了全備后的數據，能否通過增量備份文件來恢復

刪除一條數據來測試增量恢復

增量恢復操作過程如下

恢復全部數據

查看恢復的數據完整性以上就是針對 MySQL 數據庫的數據備份與恢復的詳細介紹，希望對大家有所幫助，點個在看與轉發支持一下吧。

推薦閱讀 點擊標題可跳轉

瘋狂漲價！甲骨文推出全新定價模式，想錢想瘋了

卸載 Notepad++！事實已證明，它更牛逼…

你還在用 Xshell？試試這款跨平臺 SSH 終端工具吧！

最牛逼的監控系統，不接受反駁！！

98家央企及下屬409家上市企業全名單（ 2023版）

干掉 VPN ！下一代內核級虛擬網絡組網神器橫空出世

開工第一天！就把 Zabbix 換成了 Prometheus

轉發 關注

點亮下方“在看”圖標

更多人看到

南京兆柏數據恢復中心 南京兆柏數據恢復中心