MySQL 備份與還原標準作業流程：鎖表、mysqldump、Flush Logs、時間點復原

可靠的 MySQL 備份不僅是 mysqldump，更包含 **一致性鎖定**、**二進位日誌切換**、**異地傳輸**、**還原演練** 等閉環流程。本文依官方最佳實務定義標準 SOP，適用 MyISAM/InnoDB 混合環境。

標準備份流程 (單資料庫/多資料庫)

階段 1：一致性鎖定與備份

-- 1. 讀鎖所有相關表 (確保邏輯一致性)
mysql -u root -p -e "FLUSH TABLES tbl1, tbl2, ... WITH READ LOCK;"
-- 或鎖定整個資料庫 (影響寫入，僅適合維護窗口)
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

-- 2. 同步執行 mysqldump (另一終端/腳本)
mysqldump --single-transaction --routines --triggers --events \
  --master-data=2 --flush-logs --hex-blob \
  -u root -p db_name > /backup/db_name_$(date +%F_%H%M).sql

-- 參數詳解：
-- --single-transaction : InnoDB 熱備份核心，啟動可重複讀事務，無需鎖表
-- --master-data=2       : 輸出 CHANGE MASTER TO 註解 (含 binlog 位置)，供從庫搭建/時間點復原
-- --flush-logs          : 切換新 binlog，備份後變更記錄在新檔，便於增量復原
-- --hex-blob            : 二進位欄位以 16 進位輸出，避免編碼問題
-- --routines/triggers/events : 含存儲程序/觸發器/事件

階段 2：釋放鎖與備份驗證

-- 3. 釋放讀鎖 (若步驟 1 用 FLUSH TABLES WITH READ LOCK)
mysql -u root -p -e "UNLOCK TABLES;"

-- 4. 基本驗證
head -30 backup.sql  -- 確認 CREATE TABLE、INSERT 結構
wc -l backup.sql     -- 行數合理
ls -lh backup.sql    -- 檔案大小非 0

階段 3：異地傳輸與保留策略

# 壓縮加密傳輸
gzip -c backup.sql | openssl enc -aes-256-cbc -salt -pbkdf2 -pass pass:'Key' > backup.sql.gz.enc
scp backup.sql.gz.enc backup-server:/offsite/mysql/

# 保留策略範例 (每日全備 + 每小時增量 binlog)
# 保留 30 天全備、7 天增量 binlog
find /backup -name '*.sql.gz.enc' -mtime +30 -delete
find /var/lib/mysql -name 'binlog.*' -mtime +7 -delete

標準還原流程 (時間點復原 PITR)

場景：資料庫損壞，需恢復至故障前 1 小時

-- 1. 寫鎖目標資料庫 (防止還原期間寫入)
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

-- 2. 還原最近一次全備
mysql -u root -p db_name < /backup/db_name_20240315_0200.sql

-- 3. 套用增量 binlog 至目標時間點
# 假設全備對應 binlog.000123 pos 456，目標恢復至 2024-03-15 10:30:00
mysqlbinlog --start-position=456 --stop-datetime='2024-03-15 10:30:00' \
  /var/lib/mysql/binlog.000123 /var/lib/mysql/binlog.000124 ... | mysql -u root -p db_name

-- 4. 切換新 binlog
mysql -u root -p -e "FLUSH LOGS;"

-- 5. 釋放鎖
mysql -u root -p -e "UNLOCK TABLES;"

-- 6. 驗證資料完整性
mysql -u root -p -e "CHECKSUM TABLE critical_tbl;"
mysql -u root -p -e "SELECT COUNT(*) FROM critical_tbl WHERE created_at > '2024-03-15 10:00:00';"

增量備份策略：二進位日誌 (Binlog) 管理

自動化腳本骨架 (每日 02:00 執行)

#!/bin/bash
set -euo pipefail
DB_USER="root"
DB_PASS="*****"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"
DATE=$(date +%F_%H%M)
DBS=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | grep -v -E "^(Database|information_schema|performance_schema|sys|mysql)$")

for DB in $DBS; do
    FILE="$BACKUP_DIR/${DB}_${DATE}.sql"
    mysqldump --single-transaction --routines --triggers --events \
        --master-data=2 --flush-logs --hex-blob \
        -u"$DB_USER" -p"$DB_PASS" "$DB" | gzip > "${FILE}.gz"
    # 驗證
    [[ -s "${FILE}.gz" ]] || { echo "Backup empty: $DB"; exit 1; }
done

# 清理舊備份
find "$BACKUP_DIR" -name '*.gz' -mtime +$RETENTION_DAYS -delete

# 同步至異地 (rsync over SSH)
rsync -avz --delete "$BACKUP_DIR/" backup-user@offsite:/offsite/mysql/

關鍵檢查清單 (每季演練)

• [ ] 全備 + Binlog PITR 還原至測試環境，**資料筆數、檢查和、關鍵業務查詢結果** 全數一致。
• [ ] RTO (恢復時間目標) < 4 小時、RPO (恢復點目標) < 1 小時 實測達標。
• [ ] 異地備份可讀、可解密、可還原。
• [ ] mysqldump --single-transaction 對 InnoDB 表**無鎖**、對 MyISAM 表**仍鎖表** (評估影響)。
• [ ] 監控 Seconds_Behind_Master、備份成功/失敗告警、磁碟空間告警全數正常。