MySQL 備份還原方案深度對比

資料備份是資料庫運維的生命線。本文對比兩種主流方案：PHP 自動化腳本（邏輯備份）與mysqldump 官方工具，分析適用場景、優缺點與最佳實務。

方法一：PHP 腳本實現自動化備份/還原（適合 Web 後台整合）

核心邏輯

1. 遍歷資料庫所有表，SELECT * INTO OUTFILE 導出 CSV/TSV 臨時檔。
2. 將各表臨時檔合併為單一 .sql 備份檔（含建表語句 + 資料）。
3. 還原時解析備份檔，TRUNCATE TABLE 清空表 → LOAD DATA INFILE 高速導入。

關鍵程式碼片段（備份）

$tables = $db->query("SHOW TABLES");
foreach ($tables as $tbl) {
    $sql = "SELECT * INTO OUTFILE '{$tmpDir}/{$tbl}.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' FROM `{$tbl}`";
    $db->query($sql);
}
// 合併檔案、加上建表語句...

關鍵程式碼片段（還原）

// 解析備份檔，提取各表資料區塊
preg_match_all("/`(.*)`\{\{(.*)\}\}/isU", $content, $matches);
foreach ($matches[1] as $i => $table) {
    $data = $matches[2][$i];
    file_put_contents("{$tmpDir}/{$table}.txt", $data);
    $db->query("TRUNCATE TABLE `$table`");
    $db->query("LOAD DATA LOW_PRIORITY INFILE '{$tmpDir}/{$table}.txt' INTO TABLE `$table` FIELDS TERMINATED BY ',' ENCLOSED BY '\"'");
    unlink("{$tmpDir}/{$table}.txt");
}

優缺點分析

優點	缺點
可整合進 Web 後台，提供圖形化備份/還原介面	需 PHP 有檔案系統寫入權限、MySQL 有 FILE 權限
邏輯備份，跨版本/跨平台遷移相容性好	SELECT INTO OUTFILE 受 secure_file_priv 限制，需配置輸出目錄
可自定義備份格式、壓縮、加密、上傳雲端	大資料量（>5GB）執行時間長，易觸發 PHP max_execution_time 限制
LOAD DATA INFILE 導入速度極快（比 INSERT 快 10-20 倍）	無法直接備份存儲過程、觸發器、視圖、事件（需額外查詢 information_schema）

方法二：mysqldump 官方工具（生產環境標準配置）

常用參數組合

# 單庫熱備（InnoDB 必加 --single-transaction）
mysqldump -u root -p --single-transaction --routines --triggers --events --master-data=2 --flush-logs db_name > backup_$(date +%F).sql

# 全庫備份
mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events --master-data=2 --flush-logs > full_backup_$(date +%F).sql

關鍵參數解析

• --single-transaction：InnoDB 熱備核心，啟動一致性快照，不鎖表。
• --master-data=2：輸出 CHANGE MASTER TO 註解，記錄備份時刻 binlog 位置，便於增量恢復。
• --flush-logs：備份前滾動 binlog，配合 --master-data 實現精確 PITR。
• --routines --triggers --events：備份存儲過程、觸發器、事件調度器。
• --hex-blob：二進位欄位用十六進位輸出，避免編碼問題。
• --compress：傳輸層壓縮，跨網路備份時節省頻寬。

還原方式

# 方式 1：進入 MySQL 再 source（可見進度）
mysql -u root -p db_name < backup.sql

# 方式 2：管道直導（較快，無進度顯示）
mysql -u root -p db_name < backup.sql

# 方式 3：併行還原（需將 dump 拆分為多檔，配合 mydumper/myloader 或自行拆表）

方案選型決策樹

1. 資料量 < 5GB、需 Web 介面操作、跨平台遷移 → PHP 腳本方案（或 phpMyAdmin 匯出/匯入）。
2. 資料量 5GB~500GB、生產環境例行備份、需 PITR → mysqldump --single-transaction + 定時任務 + binlog 保留。
3. 資料量 > 500GB、追求極速備份/還原、可接受物理備份限制 → Percona XtraBackup（物理熱備、支援增量、流式傳輸、併行壓縮）。

通用最佳實務（無論哪種方案）

• 3-2-1 原則：3 份副本、2 種媒介、1 份異地。
• 定期演練還原：每季至少一次完整還原測試，驗證備份可用性與 RTO/RPO。
• 備份檔加密：openssl enc -aes-256-cbc -salt -in backup.sql -out backup.sql.enc。
• 監控備份狀態：檢查檔案大小、耗時、錯誤日誌，異常即時告警。