MySQL全量，增量備份與恢復

一.MySQL數據庫備份概述

1.數據備份的重要性

2.數據庫備份類型

3.常見的備份方法

二：數據庫完全備份操作

1.物理冷備份與恢復

2.mysqldump 備份與恢復

3.MySQL增量備份與恢復

3.1MySQL增量恢復

3.2MySQL備份案例

三：定制企業備份策略思路

四：擴展：Mysql的GTID 和 XtraBackup

1.Mysql 的 GTID

2.XtraBackup

一.MySQL數據庫備份概述

備份的主要目的是災難恢復，備份還可以測試應用、回滾數據修改、查詢歷史數據、審計等。之前已經學習過如何安裝 MySQL，本小節將從生產運維的角度了解備份恢復的分類與方法。

1.數據備份的重要性

在企業中數據的價值至關重要，數據保障了企業業務的正常運行。因此，數據的安全性及數據的可靠性是運維的重中之重,任何數據的丟失都可能對企業產生嚴重的后果。通常情況下造成數據丟失的原因有如下幾種:

程序錯誤，
人為操作錯誤。
運算錯誤。
磁盤故障。
災難(如火災、地震)和盜竊

2.數據庫備份類型

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

數據庫備份可以分為物理備份和邏輯備份。

物理備份是對數據庫操作系統物理文件(如數據文件、日志文件等)的備份。

這種類型的備份適用于在出現問題時需要快速恢復的大型重要數據庫。

物理備份又可以分為冷備份(脫機備份)、熱備份(聯機備份)和溫備份

冷備份:在數據庫關閉狀態下進行備份操作。
熱備份:在數據庫處于運行狀態時進行備份操作，該備份方法依賴數據庫的日志文件。
溫備份:數據庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作。

邏輯備份是對數據庫邏輯組件(如表等數據庫對象)的備份，表示為邏輯數據庫結構(CREATE DATABASE，CREATE TABLE 語句)和內容(INSERT 語句或分隔文本文件)的信息。這種類型的備份適用于可以編輯數據值或表結構較小的數據量，或者在不同的機器體系結構上重新創建數據

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

從數據庫的備份策略角度，數據庫的備份可分為完全備份、差異備份和增量備份。

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

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

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

3.常見的備份方法

MySQL 數據庫的備份可以采用很多種方式，如直接打包數據庫文件(物理冷備份)、專用備份工具(mysqldump)、二進制日志增量備份、第三方工具備份等。

（1）物理冷備份

物理冷備份時需要在數據庫處于關閉狀態下，能夠較好地保證數據庫的完整性。物理冷備份一般用于非核心業務，這類業務一般都允許中斷，物理冷備份的特點就是速度快，恢復時也是最為簡單的。通常通過直接打包數據庫文件夾(本章中的數據庫文件夾位于/usr/local/mysql/data)來實現備份。

（2）專用備份工具 mysqldump 或 mysqlhotcopy

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

mysqlhotcopy是由TimBunce 最初編寫和貢獻的Perl 腳本。mysqlhotcopy 僅用于備份 MyISAM 和 ARCHIVE 表。它只能運行在 UNIX 或 Linux上。因為使用范圍小，因此本文中不做詳細介紹，如果同學們有興趣可以在課下研究。

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

MySQL 支持增量備份，進行增量備份時必須啟用二進制日志。二進制日志文件為用戶提供復制，對執行備份點后進行的數據庫更改所需的信息進行恢復。如果進行增量備份(包含自上次完全備份或增量備份以來發生的數據修改)，需要刷新二進制日志。

（4）通過第三方工具備份

Percona XtraBackup 是一個免費的 MySQL 熱備份軟件，支持在線熱備份Innodb 和 XtraDB,也可以支持 MySQL 表備份,不過 MyISAM 表的備份要在表鎖的情況下進行。本節對于 Percona XtraBackupr 的敘述是基于 2.4 版本的。Percona XtrBackup 有三個主要的工具:xtrabackup、innobackupex、xbstream.

xtrabackup:是一個編譯了的二進制文件，只能備份Innodb/Xtradb 數據文件。
innodbackupex :是一個封裝了 xtrabackup 的Perl 腳本，除了可以備份Innodb/Xtradb 之外，還可以備份 MySIAM。
xbstream:是一個新組件，能夠允許將文件格式轉成xbstream 格式或從xbstream 格式轉到文件格式。

xtrabackup 工具可以單獨使用，但推薦使用 innobackupex 來進行備份這是因為 innobackupex本身就已經包含了 xtrabackup 的所有功能。

xtrabackup 是基于 Innodb 的災難恢復功能進行設計的，備份工具復制Innodb 的數據文件。但是，由于不鎖表，這樣復制出來的數據將不一致。Innodb維護了一個重做日志，包含 Innodb 數據的所有改動情況。在 xtrabackup 備份[nnodb 數據的同時，xtrabackup 還有另外一個線程用來監控重做日志，一但日志發生變化，就把發生變化的日志數據復制走。這樣就可以利用重做日志做災難恢復了。

以上是備份過程，如果需要恢復數據，則在準備階段，xtrabackup 就需要使用之前復制的重做日志對備份出來的 Innodb 數據文件進行災難恢復,此階段完成之后，數據庫就可以進行重建還原了。

二：數據庫完全備份操作

上面提到根據數據庫備份策略分類，備份可分為完全備份、差異備份和增量備份。

1.物理冷備份與恢復

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

（1）備份數據庫

創建一個/backup 目錄作為備份數據存儲路徑，使用tar 創建備份文件。

整個數據庫文件夾備份屬于完全備份

備份auth數據庫下的user表

（2）恢復數據庫

先刪除 auth 下的 use r表

執行下面操作將數據庫文件/usr/local/mysql/data/轉移至 bak 目錄下

恢復data文件

2.mysqldump 備份與恢復

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

（1）備份數據庫

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

格式1:備份指定庫中的部分表

mysqldump [選項] 庫名 [表名 1] [表名 2]… > /備份路徑/備份文件名

格式2:備份一個或多個完整的庫(包括其中所有的表)

mysqldump [選項] --databases 庫名1 [庫名2] … >?/備份路徑/備份文件名

格式3:備份MySQL服務器中所有的庫。

mysqldump [選項] --all-databases > /備份路徑/備份文件名

其中，常用的選項包括“-u”、.“-p”，分別用于指定數據庫用戶名、密碼。例如，以下操作分別使用格式 1、格式 2，將 mysql 庫中的 user 表導出為mysq1-user.sql，將整個 mysql 庫導出為 mysql.sql 文件，所有操作均以 root用戶身份驗證。

若需要備份整個 MySQL 服務器中的所有庫，應使用格式 3。當導出的數據量較大的時候，可以添加“--opt”選項以優化執行速度。例如，執行以下操作將創建備份文件 all-data.sq1，其中包括 MySQL 服務器中的所有庫。

(2)查看備份文件

通過 mysqldump 工具導出的 SQL 腳本是文本文件，其中“/*…*/”部分或以“一”開頭的行表示注釋信息。使用 grep、less、cat 等文本工具可以查看腳本內容。例如,執行以下操作可以過濾出users.sql 腳本中的數據庫操作語句。

（3）恢復數據庫

使用 mysqldump 命令導出的 SQL 備份腳本，在需要恢復時可以通過 mysql命令對其進行導入操作，命令格式如下所示。

mysql [選項 ][庫名] [表名] < /備份路徑/備份文件名

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

例如，執行以下操作可以從備份文件 mysql-user.sql 中將表導入mysql 庫。其中“-e”選項是用于指定連接 MySQL 后執行的命令，命令執行完后自動退出。

例如，執行以下操作可以從備份文件 auth.sql 恢復 auth?庫。

刪除auth數據庫，模擬故障

執行恢復操作

確認恢復結果

3.MySQL增量備份與恢復

使用 mysqldump 進行完全備份，備份的數據中有重復數據，備份時間與恢復時間過長。而增量備份就是自上一次備份之后增加或改變的內容。

（1）MySQL增量備份概述

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

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

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

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

Mysq18.0 默認已經開啟 binlog，無需顯示配置 binlog(默認 binlog 文件為:binlog.000001)，如需自定義binlog 配置，請添加如下配置項

重啟數據庫后查看二進制文件

3.1MySQL增量恢復

在維護數據庫時,因為各種各樣的原因可能會導致數據丟失,如:人為的 SQI語句破壞數據庫、在進行下一次全備份之前發生系統故障導致數據庫數據丟失、在數據庫主從架構中主庫的數據發生故障等。當出現以上場景時可以使用增量恢復來恢復數據。

常用的增量恢復的方法有三種:一般恢復、基于位置的恢復、基于時間點的恢復。

一般恢復:將所有備份的二進制日志內容全部恢復，命令格式如下所示。

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

基于位置的恢復:數據庫管理員在操作數據庫時可能在同一時間點既有錯誤的操作也有正確的操作，通過基于位置進行恢復可以更加精準，命令格式如下所示

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

mysqlbinlog --stop-position=' 操作 id ' 二進制文件 |?mysql -u 用戶名 -p 密碼

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

mysqlbinlog --start-position=' 操作 id ' 二進制文件 |?mysql -u 用戶名 -p 密碼

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

格式 1:從日志開頭截止到某個時間點的恢復。

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

格式 2:從某個時間點到日志結尾的恢復。

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

格式 3:從某個時間點到某個時間點的恢復。

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

3.2MySQL備份案例

（1）添加數據庫，表，錄入信息

（2）先進行一次完全備份

刷新二進制日志

（3）繼續錄入新的數據進行增量備份

繼續錄入兩個用戶的數據，并執行“mysqladmin-uroot-p flush-logs’命令刷新二進制日志,進行增量備份。如此,二進制日志文件 mysql-bin.000003 中僅保留插入兩個用戶數據的操作。

復制二進制文件mysql-bin.000003

（4）模擬誤操作刪除user表

（5）恢復操作

執行恢復操作時，需要先恢復完全備份，再恢復增量備份

恢復完全備份

恢復增量備份

基于位置恢復

由于前面已經做過備份操作，接下來直接進行模擬故障與數據恢復的操作。

模擬誤操作刪除user表

恢復完全備份

想要實現基于位置或時間點恢復數據,必須先通過查看二進制日志文件確定恢復的位置或時間點。使用“mysqlbinlog -no-defaults 二進制日志文件”可以查看二進制日志文件的具體內容。

基于時間點恢復

三：定制企業備份策略思路

在企業中備份策略并不是千篇一律的，而是根據每個企業的實際生產環境與業務需求制定合適的備份策略。無論是選擇完全備份，還是選擇增量備份，都需考慮它們的優缺點，是否適合當前的生產環境。同時，為了保證恢復的完整性，建議開啟二進制日志功能，二進制日志文件給恢復工作也帶來了很大的靈活性，可以基于時間點或位置進行恢復。考慮到數據庫性能，可以將二進制日志文件保存到其他安全的硬盤中

在進行熱備份時，備份操作和應用服務在同時運行，這樣就十分消耗系統資源了，導致數據庫服務性能下降，這就需要選擇一個合適的時間(如在應用負擔很小的時候)再來進行備份操作。

需要注意的是，不是備份完就萬事大吉，最好確認備份是否可用，所以備份之后的恢復測試是非常有必要的。同時備份時間也要靈活調整，如:

數據更新頻繁，則應該頻繁地備份。
數據的重要性，在有適當更新時進行備份
在數據庫壓力小的時間段進行備份，如一周一次完全備份，每天進行增量備份。
中小公司，完全備份一般一天一次即可。
大公司可每周進行一次完全備份，每天進行一次增量備份。
盡量為企業實現主從復制架構，以增加數據的可用性。

四：擴展：Mysql的GTID 和 XtraBackup

1.Mysql 的 GTID

GTID 即全局事務 ID(global transaction identifier)，其保證為每一個在主上提交的事務在復制集群中可以生成一個唯一的 ID。GTID 最初由 google實現，官方 MySQL在 5.6才加入該功能

GTID 實際上是由 UUID+TID(即 transactionId)組成的。其中 UUID(即server uuid)產生于auto.conf 文件(cat /data/mysql/data/auto.cnf)，是個 MySQL 實例的唯一標識。TID 代表了該實例上已經提交的事務數量，并且隨著事務提交單調遞增，所以GTID能夠保證每個MySQL,實例事務的執行(不會重復執行同一個事務，并且會補全沒有執行的事務)。GTID在一組復制中，全局唯一

通過下面的實驗，了解基于 gtid 的增量備份和恢復(Gtid 的備份也是基于binlog 的)

(1)配置 my.cnf 開啟 gtid

（2）創建基本測試庫，表，數據

初始化master

再test數據庫下創建1測試表user1，初始、插入3條數據

（3）全量備份

（4）插入新數據

（5）模擬數據誤刪

（6）導出增量數據

（7）恢復全量

（8）恢復增量

2.XtraBackup

MySQL 冷備、mysqldump、MySQL 熱拷貝都無法實現對數據庫進行增量備份。在實際生產環境中增量備份是非常實用的，如果數據大于50G或100G，存儲空間足夠的情況下，可以每天進行完整備份，如果每天產生的數據量較大，需要定制數據備份策略。例如每周實用完整備份，周一到周六實用增量備份。而Percona-Xtrabackup 就是為了實現增量備份而出現的一款主流備份工具，xtrabakackup有2個工具，分別是xtrabakup、innobakupe。

Percona-xtrabackup 是 Percona 公司開發的一個用于 MySQL, 數據庫物理熱備的備份工具，支持 MySQL、Percona server 和 MariaDB，開源免費，是目前較為受歡迎的主流備份工具。xtrabackup 只能備份 innoDB 和 xtraDB 兩種數據引擎的表，而不能備份 MyISAM 數據表。

（1)安裝xtrabackup