MySQL數據庫中怎么實現備份與恢復

MySQL數據庫中怎么實現備份與恢復，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

一、數據庫備份的一些概念

1.造成數據丟失的原因

程序錯誤、人為操作錯誤、運算錯誤、磁盤故障、災難（火災、地震）和盜竊等

2.數據備份的類型

1）從物理與邏輯的角度分類

物理備份可分為：

• 冷備份（脫機備份）：在數據庫關閉狀態下進行備份操作

• 熱備份（聯機備份）：在數據庫處于運行狀態時進行備份操作，該備份方法依賴數據庫的日志文件

• 溫備份：數據庫鎖定表格（不可寫入但可讀）的狀態下進行備份操作

說白了物理備份是對數據庫操作系統的物理文件（如數據文件、日志文件等）的備份，這種類型的備份適用于在出現問題時需要快速恢復的大型重要數據庫。

邏輯備份是對數據庫邏輯組件（如表等數據庫對象）的備份，這種類型的備份使用于可以編輯數據值或表結構較小的數據量，或者在不同的機器體系結構上重新創建數據。

2）從數據庫的備份策略角度分類

從備份策略可分為：

• 完全備份：每次對數據進行完整的備份，即對整個數據庫的備份、數據庫結構和文件結構的備份，保存的是備份完成時刻的數據庫，是差異備份與增量備份的基礎。完全備份的備份與恢復操作都非常簡單方便，但數據存在大量的重復，并且會占用大量的磁盤空間，備份時間也很長。

• 差異備份：備份那些從上一次完全備份之后被修改過的所有文件，備份的時間節點是從上次完整備份起，備份數據量會越來越大。恢復數據時，只需恢復上次的完整備份與最近一次的差異備份。

• 增量備份：只有那些在上次完全備份或者增量備份后被修改的文件才會被備份。以上次完整備份或上次增量備份的時間為時間點，僅備份這之間的數據變化，因而備份的數據量小，占用空間小，備份速度快。但恢復時，需要從上一次的完整備份開始到***一次增量備份之間的所有增量一次恢復，如中間某次的備份數據損壞，將導致數據的丟失。

如圖所示：

3.常見的備份方法

1）物理冷備份

需要在數據庫關閉狀態下進行，能夠較好的保證數據庫的完整性。一般用于非核心業務，這類業務一般都允許終端，物理冷備份的特點就是速度快，恢復操作簡單。

通常通過直接打包數據庫文件夾（如：/usr/local/mysql/data）來實現

2）專用備份工具mysqldump或mysqlhotcopy

mysqldump是客戶端常用邏輯備份程序，能夠產生一組被執行以再現原始數據庫對象定義和表數據的SQL語句。它可以轉儲一個到多個MySQL數據庫，對其進行備份或傳輸到遠程SQL服務器。Mysqldump更為通用，因為它可以備份各種表。

Mysqlhotcopy是由Tim Bunce最初編寫和貢獻的Perl腳本。Mysqlhotcopy僅用于備份MyISAM和ARCHIVE表。它只能運行在UNIX或linux上，因為使用范圍很小，在這里就不細說了。

3）通過啟用二進制日志進行增量備份

MySQL支持增量備份，進行增量備份時必須啟用二進制日志。二進制日志文件為用戶提供復制，對執行備份點后進行的數據庫更改所需的信息進行恢復。如果進行增量備份，需要刷新二進制日志。

4）通過第三方工具備份

Percona XtraBackup是一個免費的MySQL熱備份軟件，支持在線熱備份Innodb和XtraDB，也可以支持MySQL表備份，不過MyISAM表的備份要在表鎖的情況進行。由于內容太多，這里就不詳細講解了，有興趣的話可以查閱該軟件的詳細介紹了解一下。

二、MySQL的完全備份與恢復

1.物理冷備份與恢復

物理冷備份一般用tar命令直接打包數據庫文件夾，而在進行備份之前需要使用先關閉mysql服務。

1）備份數據庫

創建一個/backup目錄作為備份數據存儲路徑，使用tar創建備份文件。整個數據庫文件夾備份屬于完全備份。如下圖：

2）恢復數據庫

模擬故障，將/usr/local/mysql/data移動到/bak目錄下。如下圖：

從備份文件恢復數據。如下圖：

2.mysqldump備份與恢復

通過mysqldump命令可以將指定的庫、表或全部的庫導出為SQL腳本，便于該命令在不同版本的MySQL服務器上使用。例如，當需要升級MySQL服務器時，可以先使用mysqldump命令將原有庫信息導出，然后直接在升級后的MySQL服務器中導入即可。

1）備份數據庫

使用mysqldump命令導出數據時，默認會直接在終端顯示，若要保存到文件，還需要結合shell的">"重定向輸出操作。命令格式如下圖：

常用的選項：-u：指定用戶名；-p：指定密碼

示例：將mysql庫中的user表導出為mysql-user.sql，將整個auth庫導出為auth.sql，將所有庫導出為all-data.sql文件。如下圖：

2）查看備份文件

通過mysqldump工具導出的SQL腳本是文本文件，其中"/*...*/"部分或以"--"開頭的行為注釋信息，使用grep、less、cat等文本工具可以查看腳本內容。如下圖（查看auth.sql文件中的內容）：

3）恢復數據庫

使用mysqldump命令導出的SQL備份腳本，在恢復時通過mysql命令對其進行導入操作。命令格式為："mysql [選項] [庫名] [表名] < /備份路徑/備份文件名"。

當備份文件中只包含表的備份，而不包含創建的庫的語句時，執行導入操作時必須指定庫名，且目標庫必須存在。

示例：從備份文件mysql-user.sql中將表導入test庫。如下圖：

若備份文件中已經包括完整的庫信息，則導入操作時不需要指定庫名。如下圖：

三、MySQL的增量備份與恢復

1.增量備份的概述

1）增量恢復的特點

與完全備份不同，增量備份沒有重復數據，備份量不大，時間短；但其恢復麻煩，需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復，MySQL沒有提供直接的增量備份辦法，可以通過MySQL提供的二進制日志（binary logs）間接實現增量備份。

2）MySQL二進制日志對備份的意義

二進制日志保存了所有更新或者可能更新數據庫的操作。二進制日志在啟動MySQL服務器后開始記錄，并在文件達到二進制日志所設置的***值或者接收到flush logs命令后重新創建新的日志文件，生成二進制文件序列，并及時把這些日志保存到安全的存儲位置，即可完成一個時間段的增量備份。使用max_binlog_size配置項可以設置二進制日志文件的***值，如果二進制文件的大小超過了max_binlog_size，它就會自動創建新的二進制文件。

要進行MySQL的增量備份，首先要開啟二進制日志功能，開啟MySQL的二進制日志功能的實現方法有很多種，最常用的是在MySQL配置文件的mysql項下加入"log-bin=/文件路徑/文件名"前綴，如log-bin=/usr/local/mysql/mysql-bin，然后重啟MySQL服務，就可以在指定路徑下查看二進制日志文件了（如下圖）。默認情況下，二進制日志文件的擴展名是一個六位的數字，如mysql-bin.000001。

2.增量恢復

在維護數據庫時，因為各種各樣的原因可能會導致數據丟失，如：認為的SQL語句破壞、在進行下一次全備份之前發生系統故障、數據庫主從架構中主庫的數據發生故障等。所以常用的增量恢復的方法有三種：一般恢復、基于位置的恢復、基于時間點的恢復

1）一般恢復：將所有備份的二進制日志內容全部恢復

命令格式：

"mysqlbinlog [--no-defaults] 增量備份文件 | mysql -u 用戶名 -p"

2）基于位置的恢復：數據庫管理員在操作數據庫時可能在同一時間點既有錯誤的操作也有正確的操作，通過基于位置進行恢復可以更加精準

命令格式：

格式1：恢復數據到指定位置

"mysqlbinlog --stop-position='操作id' 二進制日志 | mysql -u 用戶名 -p 密碼"

格式2：從指定的位置開始恢復

"mysqlbinlog --start-position='操作id' 二進制日志 | mysql -u 用戶名 -p 密碼"

3）基于時間點恢復：跳過某個發生錯誤的時間點實現數據恢復，而基于時間點的恢復可以分為三種情況

A.從日志開頭截止到某個時間點的恢復

命令格式：

"mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u 用戶名 -p 密碼"

B.從某個時間點到日志結尾的恢復

命令格式：

"mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u 用戶名 -p 密碼"

C.從某個時間點到某個時間點的恢復

命令格式：

"mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' --stop-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u 用戶名 -p 密碼"

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。