MySQL 全量、增量備份與恢復

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

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

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

2 數據庫備份類型
2.1 從物理與邏輯的角度分類
????????數據庫備份可以分為物理備份和邏輯備份。

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

這種類型的備份適用于在出現問題時需要快速恢復的大型重要數據庫。
????????物理備份又可以分為冷備份(脫機備份)、熱備份(聯機備份)和溫備份

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

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

2.2.從數據庫的備份策略角度分類
????????從數據庫的備份策略角度，數據庫的備份可分為完全備份、差異備份和增量備份。
????????完全備份:每次對數據進行完整的備份，即對整個數據庫、數據庫結構和文件結構的備份,保存的是備份完成時刻的數據庫,是差異備份與增量備份的基礎。完全備份的備份與恢復操作都非常簡單方便，但是數據存在大量的重復，并且會占用大量的磁盤空間，備份的時間也很長。
????????差異備份:備份那些自從上次完全備份之后被修改過的所有文件，備份的時間節點是從上次完整備份起，備份數據量會越來越大。恢復數據時，只需恢復上次的完全備份與最近的一次差異備份。

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

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

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

3.2 專用備份工具 mysqldump 或 mysqlhotcopy
????????mysqldump 程序和 mysqlhotcopy 都可以做備份。mysqldump 是客戶端常用邏輯備份程序，能夠產生一組被執行以后再現原始數據庫對象定義和表數據的SQL 語句。它可以轉儲一個到多個 MySQL 數據庫，對其進行備份或傳輸到遠程SQL 服務器。mysqldump 更為通用，因為它可以備份各種表。mysqlhotcopy 僅適用于某些存儲引擎。
????????mysqlhotcopy是由TimBunce 最初編寫和貢獻的Perl 腳本。mysqlhotcopy 僅用于備份 MyISAM 和 ARCHIVE 表。它只能運行在 UNIX 或 Linux上。因為使用范圍小，因此本文中不做詳細介紹，如果同學們有興趣可以在課下研究。

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

3.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 備份Innodb 數據的同時，xtrabackup 還有另外一個線程用來監控重做日志，一但日志發生變化，就把發生變化的日志數據復制走。這樣就可以利用重做日志做災難恢復了。
????????以上是備份過程，如果需要恢復數據，則在準備階段，xtrabackup 就需要使用之前復制的重做日志對備份出來的 Innodb 數據文件進行災難恢復,此階段完成之后，數據庫就可以進行重建還原了。
????????Percona XtraBackup 對 MySIAM 的復制，是按這樣的一個順序進行的:首先鎖定表，然后復制，再解鎖表。第三方工具備份操作本章中不做詳細介紹，如果同學們有興趣可以在課下研究

二.數據庫完全備份操作
????????上面提到根據數據庫備份策略分類，備份可分為完全備份、差異備份和增量備份。在本章中我們只介紹完全備份與增量備份，感興趣的同學可以課下自學差異備份的方法。
1 物理冷備份與恢復
????????物理冷備份一般用 tar 命令直接打包數據庫文件夾，而在進行備份之前需要使用“systemctl stop mysqld”命令關閉 mysqld 服務。
1.1備份數據庫
????????創建一個/backup 目錄作為備份數據存儲路徑，使用 tar 創建備份文件。

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

1.2 恢復數據庫
????????執行下面操作將數據庫文件/usr/local/mysql/data/轉移至 bak 目錄下模擬故障。

2 mysqldump 備份與恢復
????????通過 mysqldump 命令可以將指定的庫、表或全部的庫導出為 SQL 腳本，便于該命令在不同版本的 MySQL 服務器上使用。例如，當需要升級 MySQL 服務器時，可以先使用 mysqldump 命令將原有庫信息導出,然后直接在升級后的 MySQI服務器中導入即可。
2.1備份數據庫
????????使用 mysqldump 命令導出數據時，默認會直接在終端顯示，若要保存到文件，還需要結合 Shel1 的“>”重定向輸出操作，命令格式如下所示。

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

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

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

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

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

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

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

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

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

????????若備份文件中已經包括完整的庫信息，則執行導入操作時無須指定庫名

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

//刪除 test 數據庫,模擬故障

//查看 test 數據庫是否存在

????????除了使用 mysq1 命令結合“<”恢復數據外，還可以使用 source 命令恢復數據，具體用法如下

3 MySQL 增量備份與恢復
????????使用 mysqldump 進行完全備份，備份的數據中有重復數據，備份時間與恢復時間過長。而增量備份就是自上一次備份之后增加或改變的內容。
3.1 MySQL 增量備份概述
(1)增量備份的特點
????????與完全備份不同，增量備份沒有重復數據，備份量不大，時間短;但其恢復麻煩，需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復。MySQL 沒有提供直接的增量備份辦法，可以通過MySQL 提供的二進制日志 (binary logs)間接實現增量備份。

(2) MySQL 二進制日志對備份的意義
????????二進制日志保存了所有更新數據庫的操作。二進制日志在啟動 MySQL 服務器后開始記錄，并在文件達到二進制日志所設置的最大值或者接收到flushlogs 命令后重新創建新的日志文件，生成二進制文件序列，并及時把這些日志保存到安全的存儲位置，即可完成一個時間段的增量備份。使maxbinlog_size配置項可以設置二進制日志文件的最大值，如果二進制文件的大小超過了max binlog size，它就會自動創建新的二進制文件。
????????要進行 MySQL 的增量備份，首先要開啟二進制日志功能。開啟 MySQL的進制日志功能的實現方法有很多種，最常用的是在 MySQL配置文件的 mysqld項下加入“log-bin=/文件路徑/文件名”前綴，如1og-bin=/usr/local/mysql/mysq1-bin,然后重啟 MySQL 服務就可以在指定路徑下查看二進制日志文件了。默認情況下，二進制日志文件的擴展名是一個六位的數字，如 mysql-bin.000001。
????????Mysq18.0 默認已經開啟 binlog，無需顯示配置 binlog(默認 binlog 文件為:binlog.000001)，如需自定義binlog 配置，請添加如下配置項

vim /etc/my.cnf

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

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

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

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

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

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

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

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

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

3.3 MySQL 企業備份案例
????????需求描述:北京移電通信公司的用戶信息數據庫為client，用戶資費數據表為 user info，該公司每周需要進行完全備份，每天需要進行增量備份。新增加的用戶信息如表 4-1 所示。

身份證	姓名	性別	用戶ID號	資費
000006	張三	男	016	10
000007	李四	女	017	91
000008	王五	女	018	23
000009	趙六	男	019	37
000010	孫七	男	020	36

1.一般恢復
(1)添加數據庫、表，錄入信息
????????在進行備份之前，先根據給出的需求創建用戶信息數據庫 client、用戶資費數據表 user info，并且根據需求描述中的表格插入前三條用戶的數據。

(2)先進行一次完全備份
????????為方便驗證二進制日志的增量恢復功能，在插入三條用戶數據后先對client 數據庫的 user info 表進行一次完全備份。然后在 Linux 系統命令行下執行“mysqladmin -uroot -p flush-logs”命令或在“mysql>”命令提示符下執行“flush logs;”生成新的二進制日志。

(3)繼續錄入新的數據并進行增量備份
????????繼續錄入兩個用戶的數據，并執行“mysqladmin-uroot-p flush-logs命令刷新二進制日志,進行增量備份。如此,二進制日志文件 mysql-bin.000002

中僅保留插入兩個用戶數據的操作。

(4)模擬誤操作刪除user info 表

(5)恢復操作
再執行恢復操作時，需要先恢復完全備份，然后恢復增量備份。

//恢復完全備份

//恢復增量備份

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

[root@localhost ~]#?mysql -u root -p -e 'DROP *TABLE client.user_info;'

//模擬誤操作刪除 user info 表

[root@localhost ~]#mysql -u root -p-e 'SELECT * FROM client.user_info;

//查看 user_info 表是否存在

[root@localhost ~]#mysql -u root -p client</mysql bak/client userinfo-2025-05-27.sql

//恢復完全備份

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

[root@localhost ~]#mysqlbinlog --no-defaults /mysql bak/mysql-bin.00002

????????通過查看日志文件的具體內容可以發現，在每進行一個操作之前都會有一個獨特的編號，如“# at 663”。此編號隨著操作數增多而變大，我們稱之為操作ID。在操作 ID 下面緊跟著的是時間標記。要實現基于位置或時間點恢復數據，需要分別依賴二進制日志文件中的操作 ID或者時間標記。例如，通過二進制日志文件得知，在操作 ID 為“663”的時候，user info 表中插入了“孫七”的用戶數據。因此執行以下命令可以實現僅恢復到操作ID為“663”之前的數據即不恢復“孫七”的信息。這時所恢復的數據是從二進制日志文件的開始位置直到指定位置。

[root@localhost ~]#mysqlbinlog --no-defaults --stop-position='623’/mysql bak/mysql-bin.00002 mysql -uroot -p

[root@localhost ~]#mysql -uroot -p -e 'SELECT * FROM client.user_info;'

????????上述操作命令中，“--stop-position”指定的是停止的位置。如果僅恢復“孫七”的信息，跳過“趙六”的信息恢復，可以使用“--start-position”選項指定開始恢復數據的位置。這時所恢復的數據是從指定位置開始直到二進制日志文件的最后。

[root@localhost ~]#mysql -uroot -p -e 'DROP TABLE client.user_info;'

[root@localhost ~]#mysql -uroot -p client</mysql bak/client userinfo-2025-05-27.sql

[root@localhost ~]#mysqlbinlog --no-defaults --start-position='663/mysql bak/mysql-bin.000002 mysql -uroot -p

[root@localhost ~]#mysql -uroot -pe'SELECT * FROM client.user_info;'

3.基于時間點恢復
? ? ? ? 基于時間點的數據恢復所使用的選項是“--stop-datetime”’，指定的時間同樣也是查詢二進制日志所得。執行以下操作可以實現僅恢復到 16:41∶24 之前的數據，即不恢復“孫七”的信息。

[root@localhost ~]#mysql -uroot -p -e 'DROP TABLE client.user_info;'

[root@localhost ~]#mysql -uroot -p client</mysql bak/client userinfo-2025-05-27.sql

[root@localhost ~]#mysql -uroot -pe'SELECT * FROM client.user_info;'

執行以下操作可以實現僅恢復“孫七”的信息，跳過“趙六”的信息恢復。

[root@localhost ~]#mysql -uroot -p -e 'DROP TABLE client.user_info;'

[root@localhost ~]#mysql -uroot -p client</mysql bak/client userinfo-2025-05-27.sql

[root@localhost ~]#mysql -uroot -pe'SELECT * FROM client.user_info;'

三.制定企業備份策略的思路
????????在企業中備份策略并不是千篇一律的，而是根據每個企業的實際生產環境與業務需求制定合適的備份策略。無論是選擇完全備份，還是選擇增量備份，都需考慮它們的優缺點，是否適合當前的生產環境。同時，為了保證恢復的完整性，建議開啟二進制日志功能，二進制日志文件給恢復工作也帶來了很大的靈活性，可以基于時間點或位置進行恢復。考慮到數據庫性能，可以將二進制日志文件保存到其他安全的硬盤中。
????????在進行熱備份時，備份操作和應用服務在同時運行，這樣就十分消耗系統資源了，導致數據庫服務性能下降，這就需要選擇一個合適的時間(如在應用負擔很小的時候)再來進行備份操作。
????????需要注意的是，不是備份完就萬事大吉，最好確認備份是否可用，所以備份之后的恢復測試是非常有必要的。同時備份時間也要靈活調整，如:

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

四.擴展: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)創建基本測試庫、表、數據

創建測試庫 test，測試表user，并導入3條數據

（3)全量備份

關于數據一致性的提醒的警告信息，可忽略

(4)插入新數據

(5)模擬數據誤刪除

(6)導出增量數據

[root@localhost ~]#mysqlbinlog--include-gtids= d780a5a6-055f-11f0-b6e6-000c29078b04:3-7/usr/local/mysql/data/mysql-bin.000001 >/mysqlbak. sql

(7)恢復全量

[root@localhost ~]#mysql -u root p e "RESET MASTER;'

[root@localhost ~]#mysql -uroot p<test.sql

[root@localhost ~]#mysql -uroot -p -e 'select *from test.user,'

（8）恢復增量

mysqluroot -p<mysqlbak.sql

mysql -uroot -p -e"select * from test.user;

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

(2)安裝 qpress

qpress 是解壓縮需要用到的工具

(3)完整備份與恢復

恢復(恢復數據的時候要先關閉 MySQL，清理數據存儲目錄)

(5)增量備份與恢復
備份

增量恢復

恢復數據的時候要先關閉 MySQL，清理數據存儲目錄
# 解壓備份數據

#解壓備份數據

# 準備數據

#把增量備份的數據合并到完整備份里面

#完整重放日志

#恢復數據

#修改權限，替換成自己的數據存儲目錄

# 然后啟動 MySQL 即可

總結
????????通過本課程的學習，我們不僅系統掌握了數據庫備份的核心方法論，包括物理備份與邏輯備份的本質差異、冷溫熱備的場景適配原則，更通過實戰深入理解了 MySQL, 完全備份與恢復的標準化流程，以及基于 XtraBackup 的增量備份鏈構建與恢復策略。課程特別強調，完全備份是數據安全的基石，而增量備份則通過日志鏈機制實現了存儲效率與恢復能力的平衡;在恢復環節，從備份目錄準備到日志重放的完整流程，尤其是 prepare 階段對--apply-log-only 參數的精準把控，確保了數據恢復的可靠性。擴展部分深入解析的 GTID 全局事務標識技術，