MySQL 資料函式庫備份還原策略詳解

– 11 分鐘閱讀

MySQL 資料函式庫備份是確保資料安全的重要環節，本文將探討多種備份方法，包含邏輯 SQL 轉儲、檔案系統快照與 Percona XtraBackup 等工具。邏輯 SQL 轉儲雖然易於理解，但在大規模資料函式庫備份時效率較低。檔案系統快照，特別是 LVM 快照，提供更有效率的線上備份方式，透過建立資料卷的邏輯副本，可在不停機的情況下進行備份。Percona XtraBackup 作為一個熱門的備份工具，支援多種備份方式，包括壓縮和加密，並利用 InnoDB 的當機還原機制確保資料一致性。文章也將詳細說明如何使用這些方法進行備份和還原，以及如何規劃 LVM 快照空間和加密備份的注意事項。

邏輯 SQL 備份

邏輯 SQL 轉儲是大多數人熟悉的，因為它們是 mysqldump 預設建立的。例如，使用預設選項轉儲一個小表將產生以下（縮寫）輸出：

$ mysqldump test t1
-- [版本和主機註解]
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
-- [更多版本特定的註解以儲存還原選項]
--
-- 表 `t1` 的表結構
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
`a` int NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- 為表 `t1` 轉儲資料
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- [更多選項還原]

內容解密：

此輸出包含了表的結構和資料，所有內容都以有效的 SQL 命令寫出。檔案的開頭包含註解，設定各種 MySQL 選項，以使還原更有效率或相容性更好。接下來，您可以看到表的結構，然後是表的資料。最後，指令碼重置了在轉儲開始時更改的選項。

此轉儲檔案的輸出可用於還原操作。這很方便，但 mysqldump 的預設選項不適合進行大規模備份。

mysqldump 不是唯一可以進行 SQL 邏輯備份的工具。例如，您也可以使用 mydumper 或 phpMyAdmin 建立它們。我們真正想指出的是，不僅僅是某個特定工具的問題，而是首先進行單一 SQL 邏輯備份的缺點。以下是主要問題區域：

結構和資料儲存在一起

雖然這對於從單個檔案還原很方便，但如果您只需要還原一個表或只想還原資料，則會很困難。您可以透過兩次轉儲來緩解這一問題——一次用於資料，一次用於結構——但您仍然會有下一個問題。

邏輯備份的缺點

邏輯備份雖然方便，但也有其侷限性。在需要快速還原或處理大規模資料時，它們可能不是最佳選擇。因此，在選擇備份策略時，需要根據具體需求和環境進行仔細評估。

資料備份的挑戰與解決方案

在進行資料備份時，許多人會遇到一些常見的問題和誤解。瞭解這些問題並找到適當的解決方案對於確保資料的安全和完整性至關重要。

邏輯備份的挑戰

邏輯備份雖然是一種常見的備份方法，但它存在一些挑戰。首先，大量的 SQL 陳述式會對伺服器造成很大的負擔，因為伺服器需要解析和執行這些陳述式。這種方法不僅效率低下，而且速度慢，特別是在處理大量資料時。

程式碼範例：SQL 陳述式執行

-- 假設有一個龐大的資料表需要備份
SELECT * FROM large_table;

內容解密：

  1. 這條 SQL 陳述式嘗試從 large_table 表中選取所有資料。
  2. 當資料量龐大時，這種操作會佔用大量資源並導致效能下降。
  3. 應該考慮使用更高效的備份工具或方法來避免這種情況。

此外，邏輯備份還面臨著單一巨大檔案的問題。大多數文字編輯器難以編輯大型檔案或具有非常長行的檔案。雖然可以使用命令列串流編輯器（如 sed 或 grep）來提取所需的資料，但保持檔案小巧仍然是更好的選擇。

檔案系統快照：一個更好的備份方法

檔案系統快照是一種優秀的線上備份方式。具備快照功能的檔案系統可以在某一時刻建立其內容的一致性映像，然後可以用這個映像進行備份。這種方法比邏輯備份更高效，因為它不需要透過客戶端/伺服器協定取得資料。

使用 LVM 快照進行備份

LVM（邏輯卷管理）使用複製即寫（copy-on-write）技術來建立快照，即在某一時刻建立整個卷的邏輯副本。這種技術類別似於資料函式庫中的 MVCC（多版本並發控制），但它只保留一份舊版本的資料。

LVM 快照的工作原理
  1. 當原始卷中的資料被修改時，LVM 會在寫入任何變更之前將受影響的區塊複製到為快照保留的區域。
  2. 當請求這些區塊在快照中的資料時，LVM 會從複製的區塊中讀取資料，而不是從原始卷中讀取。

這種方法可以理論上對龐大的卷進行快照，同時只消耗非常少的實體空間。然而，需要為預期在原始卷中更新的區塊保留足夠的空間。如果沒有保留足夠的複製即寫空間，快照將會耗盡空間，並且裝置將變得不可用。

組態 LVM 快照的先決條件

要成功建立 LVM 快照，需要確保系統滿足以下條件：

  • 所有 InnoDB 檔案必須位於單一邏輯卷（分割區）上。
  • MySQL 資料目錄必須位於相同的邏輯捲上，除非使用其他方法備份表格定義。
  • 在卷組中必須有足夠的可用空間來建立快照。

檢視卷組資訊

$ vgs
VG #PV #LV #SN Attr VSize VFree
vg 1 4 0 wz--n- 534.18G 249.18G

內容解密：

  1. vgs 命令用於顯示系統上的卷組資訊。
  2. 輸出結果顯示了卷組 vg 的大小、可用空間等資訊。
  3. 確保有足夠的可用空間 (VFree) 來建立快照。

使用 LVM 快照進行資料備份

在 Linux 系統中，邏輯卷管理（LVM）提供了一種建立檔案系統快照的強大方法，允許在不中斷資料函式庫執行的情況下進行備份。以下是如何使用 LVM 快照來備份 MySQL 資料函式庫的詳細步驟。

檢查 LVM 組態

首先，檢查系統上的邏輯卷和物理卷組態。使用 vgs 命令檢視卷組的概況：

$ vgs
VG   #PV #LV #SN Attr   VSize   VFree 
vg     1   4   0 wz--n- 299.99G 250.00G

此輸出顯示有一個卷組（vg），包含四個邏輯卷，分佈在一塊物理捲上，並且大約有 250 GB 的可用空間。

檢視邏輯卷詳情

使用 lvs 命令檢視系統上的邏輯卷：

$ lvs
LV   VG   Attr       LSize   Origin Snap%  Move Log Cpy%Sync 
home vg   -wi-ao
---
-  40.00G                                      
mysql vg   -wi-ao
---
- 225.00G                                      
tmp  vg   -wi-ao
---
-  10.00G                                      
var  vg   -wi-ao
---
-  10.00G

此輸出表明 mysql 邏輯卷的大小為 225 GB，其裝置名稱為 /dev/vg/mysql。

建立 LVM 快照

建立快照時，需要決定快照的位置和分配的空間大小。對於 MySQL 資料函式庫的備份，可以建立一個名為 backup_mysql 的快照，並分配 16 GB 的空間：

$ lvcreate --size 16G --snapshot --name backup_mysql /dev/vg/mysql
Logical volume "backup_mysql" created

內容解密：

  1. --size 16G：指定快照的大小為 16 GB。
  2. --snapshot：指示 lvcreate 命令建立一個快照。
  3. --name backup_mysql：將快照命名為 backup_mysql。
  4. /dev/vg/mysql：指定要建立快照的原始邏輯卷。

檢視快照狀態

使用 lvs 命令檢視新建立的快照狀態：

$ lvs
LV          VG   Attr       LSize   Origin Snap%  Move Log Cpy%Sync 
backup_mysql vg   swi-a-s---  16.00G mysql    0.01         
home         vg   -wi-ao
---
-  40.00G                                      
mysql        vg   owi-ao
---
- 225.00G                                      
tmp          vg   -wi-ao
---
-  10.00G                                      
var          vg   -wi-ao
---
-  10.00G

內容解密：

  1. Attr 列顯示快照的屬性，如 `swi-a-s

表示這是一個快照卷。 2.Origin列顯示原始邏輯卷的名稱，即mysql。 3. Snap%` 列顯示快照中已使用的空間百分比。

掛載和使用快照

掛載快照並檢視其內容：

$ mkdir /tmp/backup
$ mount /dev/mapper/vg-backup_mysql /tmp/backup
$ ls -l /tmp/backup
total 188880
-rw-r
---
--. 1 mysql mysql    56 Jul 30 22:16 auto.cnf
-rw-r
---
--. 1 mysql mysql   475 Jul 30 22:31 binlog.000001
...

內容解密：

  1. /dev/mapper/vg-backup_mysql 是快照的裝置名稱。
  2. /tmp/backup 是掛載點，用於存取快照中的資料。

移除快照

完成備份後，解除安裝並移除快照：

$ umount /tmp/backup
$ rmdir /tmp/backup
$ lvremove --force /dev/vg/backup_mysql
Logical volume "backup_mysql" successfully removed

內容解密：

  1. umount 命令用於解除安裝快照。
  2. lvremove 命令用於刪除快照卷，--force 選項強制刪除。

使用 LVM 快照進行無鎖 InnoDB 備份

對於使用 MySQL 8+ 和 InnoDB 表的情況，可以在 MySQL 執行時直接建立快照、掛載快照並複製檔案到備份位置，無需鎖定任何檔案。還原備份時，InnoDB 將執行當機還原，並且 GTID（全域性事務 ID）設定將確保事務的一致性。

LVM 快照備份規劃

規劃 LVM 快照備份時，最重要的是分配足夠的空間給快照。考慮以下因素：

  • InnoDB 的寫入模式：InnoDB 將資料寫入兩次，因此至少一半的寫入 I/O 操作會寫入到 doublewrite 緩衝區、日誌檔案和其他相對較小的磁碟區域，這些區域會重複使用相同的磁碟塊。
  • 使用 vmstat 或 iostat 蒐集伺服器每秒寫入的區塊數量。
  • 評估將備份複製到另一個位置所需的時間，即需要保持 LVM 快照開啟的時間。

例如，如果伺服器每秒寫入 10 MB 的資料，預計一半的寫入會導致對快照的寫入，並且需要一小時（3600 秒）來複製備份，則需要大約 18 GB 的空間來存放快照。為保險起見，可以再增加一些額外的空間。

其他用途和替代方案

LVM 快照不僅可以用於備份，還可以用於在進行可能危險的操作之前建立檢查點。一些系統允許將快照提升為原始檔案系統，從而可以輕鬆回復到建立快照時的狀態。

此外，還有其他方法可以取得資料的瞬時副本，例如 RAID 分割。如果有一個三磁碟軟體 RAID 映象，可以從映象中移除一個磁碟並單獨掛載，這樣就沒有了 copy-on-write 的開銷。然而，將磁碟重新新增到 RAID 集後，需要重新同步，這仍然會帶來一些成本。

Percona XtraBackup：高效的MySQL備份解決方案

Percona XtraBackup是備份MySQL資料函式庫最受歡迎的解決方案之一，其高度可組態性使其成為許多使用者的首選。該工具支援多種備份方式，包括壓縮和加密檔案的備份。

XtraBackup的工作原理

InnoDB是一種具備當機安全性的儲存引擎。當MySQL發生當機時，它會利用根據重做日誌的當機還原模式，將資料正確地還原到線上狀態。Percona XtraBackup正是根據這種設計原理。當使用XtraBackup進行備份時，它會記錄日誌序列號（LSN），並利用該LSN對備份檔案執行當機還原。同時，它也會在特定的時間點進行鎖定，以確保有關複製的資料與實際資料保持一致。

執行XtraBackup的範例流程

$ xtrabackup --backup --target-dir=/backups/

內容解密：

  • --backup引數指定XtraBackup進入備份模式。
  • --target-dir=/backups/引數指定備份檔案的儲存目錄。
  • XtraBackup首先會判斷正在執行的MySQL版本，以決定其功能及備份方式。
  • LOCK TABLES FOR BACKUP命令用於鎖定資料表，以確保資料的一致性。
  • 之後，XtraBackup開始將資料檔案從原始位置複製到指定的備份目錄。
  • 在完成檔案複製後，XtraBackup會收集複製資訊，並記錄LSN以便套用在備份過程中發生的寫入操作。

使用XtraBackup的基本範例

在使用XtraBackup之前，請注意以下幾點：

  • 確保MySQL安裝具有足夠的許可權，並使用--user和--password選項指定帳戶。
  • XtraBackup的輸出非常詳細，為了突出重點，我們對輸出進行了修剪。
  • 在執行任何命令之前，請務必查閱Percona XtraBackup的官方手冊，因為語法和選項可能會有所變更。

基本備份到目錄

$ xtrabackup --backup --target-dir=/backups/

內容解密：

  • 此命令將MySQL資料完整備份到/backups/目錄中。
  • 需要有足夠的空間來存放備份資料。

串流備份

$ xtrabackup --backup --stream=xbstream > /backups/backup.xbstream

內容解密：

  • 使用--stream=xbstream選項將備份輸出為單一檔案。
  • 將輸出重新導向到/backups/backup.xbstream檔案中。

壓縮備份

$ xtrabackup --backup --compress --stream=xbstream > /backups/backup-compressed.xbstream

內容解密：

  • 結合--compress選項使用壓縮功能，以減少備份檔案的大小。
  • 在測試中，一個94 MB的未壓縮xbstream檔案被壓縮到6.5 MB。

資料備份與還原策略詳解

在資料管理中，備份與還原是至關重要的環節。本文將探討如何使用加密技術進行備份，以及如何從不同型別的備份中還原資料。

使用加密技術進行備份

在進行資料備份時，為了保護資料的安全，使用加密技術是一種常見的做法。加密備份可以防止未授權的存取，從而提高資料的安全性。然而，使用加密也會增加 CPU 的負擔，並延長備份過程的時間。

執行加密備份的指令

要進行加密備份，可以使用以下指令：

$ xtrabackup --backup --encrypt=AES256 --encrypt-key-file=/safe/key/location/encrypt.key --stream=xbstream > /backups/backup-encrypted.xbstream

在這個指令中，--encrypt=AES256 指定了加密演算法，--encrypt-key-file 指定了加密金鑰的存放位置。

內容解密：

  • --backup 引數指示 xtrabackup 進行備份操作。
  • --encrypt=AES256 使用 AES256 演算法進行加密，提高資料安全性。
  • --encrypt-key-file 指定加密金鑰的檔案位置，確保金鑰的安全儲存。
  • --stream=xbstream 將備份資料串流輸出到指定的檔案。

還原資料的步驟

從備份中還原資料的過程取決於備份的方式。以下是一般的還原步驟：

  1. 停止 MySQL 伺服器：確保在還原過程中，資料函式庫不會被存取或修改。
  2. 記錄伺服器組態和檔案許可權：在還原前，記錄相關的組態和許可權，以便在還原後還原原狀。
  3. 將備份資料複製到 MySQL 資料目錄：根據備份型別，將資料複製到正確的位置。
  4. 進行組態變更：根據需要調整 MySQL 的組態。
  5. 變更檔案許可權：確保 MySQL 可以正確存取還原的資料檔案。
  6. 以有限存取模式重啟 MySQL 伺服器：確保伺服器正常啟動，並檢查資料完整性。
  7. 載入邏輯備份檔案：如果是邏輯備份，需要使用 MySQL 伺服器載入資料。
  8. 檢查和重放二進位日誌：根據需要，檢查和重放二進位日誌，以確保資料的一致性。
  9. 驗證還原結果：檢查還原的資料是否正確完整。
  10. 以完整存取模式重啟 MySQL 伺服器：在確認資料正確後，重啟伺服器，還原正常服務。

邏輯備份的還原

邏輯備份是將資料函式庫中的資料和結構以 SQL 陳述式的形式匯出。要還原邏輯備份，需要使用 MySQL 伺服器執行這些 SQL 陳述式。

還原邏輯備份的指令

假設有一個名為 sakila-backup.sql 的邏輯備份檔案，可以使用以下指令還原：

$ mysql < sakila-backup.sql

或者，在 MySQL 命令列客戶端中使用 SOURCE 命令：

SET SQL_LOG_BIN = 0;
SOURCE sakila-backup.sql;
SET SQL_LOG_BIN = 1;

內容解密：

  • mysql < sakila-backup.sql 將 SQL 檔案輸入到 MySQL 伺服器中執行，實作資料還原。
  • SOURCE 命令允許在 MySQL 客戶端中載入並執行 SQL 檔案，同時可以控制是否記錄二進位日誌。

從快照中還原始檔案

從快照中還原始檔案通常涉及將檔案複製到正確的位置。對於傳統的 InnoDB 設定，需要關閉 MySQL，將檔案複製到位，然後重啟伺服器。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。