您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

核心業務 MySQL 冷備架構設計：雙機熱備 + 異地歸檔落地實踐

編輯：MySQL 綜合教學

核心業務 MySQL 冷備架構設計：雙機熱備 + 異地歸檔落地實踐

針對核心業務「資料不可丟失、RPO 接近 0、RTO 分鐘級」的需求，單純本機備份風險極高（磁碟損壞、機房火災、誤操作）。本文落地一套「一主一從 + 從庫冷備 + 異地歸檔」的分層備份架構，附完整腳本與驗證流程。

架構拓撲

┌─────────────┐     非同步複寫      ┌─────────────┐
│  Master     │ ─────────────────▶ │  Slave      │
│  (寫入節點)  │                    │  (備份節點)  │
└─────────────┘                    └──────┬──────┘
                                          │
                              每日 00:00  │ mysqldump (單線程/並行)
                                          ▼
                                 ┌─────────────────┐
                                 │  Backup Server  │  (本機保留 7 天)
                                 │  /data/backup   │
                                 └────────┬────────┘
                                          │
                              每日 01:00  │ rsync over SSH
                                          ▼
                                 ┌─────────────────┐
                                 │  Offsite Storage│  (公司 NAS/雲存儲/異地機房)
                                 │  保留 90 天/年   │
                                 └─────────────────┘

關鍵設計決策

決策點	選擇	理由
備份來源	Slave 庫	避免 Master 鎖表影響業務；`--single-transaction` 獲取一致性視圖無阻塞
備份工具	mysqldump	邏輯備份，跨版本/引擎相容，便於單表恢復；大表 (>500GB) 改用 XtraBackup 物理備份
一致性保證	`--single-transaction --master-data=2`	記錄 Binlog 位置，支援增量恢復/主從重建
壓縮傳輸	gzip + rsync -z	SQL 文字檔壓縮比 1:5~1:10，大幅節省頻寬與儲存
異地傳輸	rsync over SSH	增量傳輸、斷點續傳、加密、驗證完整性
保留策略	本地 7 天、異地 90 天/年	滿足合規與誤操作回滾窗口

生產級備份腳本

#!/bin/bash
# /usr/local/bin/mysql_cold_backup.sh
# 每日 00:00 crontab 執行

set -euo pipefail

# ===== 配置區 =====
MYSQL_USER="backup_user"
MYSQL_PASS="******"
MYSQL_HOST="10.0.1.50"          # Slave IP
MYSQL_PORT="3306"
MYSQL_SOCKET="/tmp/mysql_3306.sock"
BACKUP_DIR="/data/cold_backup"
DB_NAMES="core_db1 core_db2"    # 需備份的資料庫，空格分隔
RETENTION_DAYS=7
LOG_FILE="${BACKUP_DIR}/backup_$(date +%F).log"
DATE_TAG=$(date +%Y%m%d_%H%M%S)
# ==================

exec > >(tee -a "${LOG_FILE}") 2>&1

log() { echo "[$(date '+%F %T')] $*"; }

log "=== 開始冷備 ==="

# 1. 檢查 Slave 複寫狀態
log "檢查 Slave 複寫狀態..."
mysql -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -e "SHOW SLAVE STATUS\G" | \
  grep -E 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master' || { log "ERROR: 無法獲取 Slave 狀態"; exit 1; }

# 2. 執行備份
for DB in ${DB_NAMES}; do
  BACKUP_FILE="${BACKUP_DIR}/${DB}_${DATE_TAG}.sql.gz"
  log "備份資料庫: ${DB} -> ${BACKUP_FILE}"
  
  mysqldump \
    --host="${MYSQL_HOST}" --port="${MYSQL_PORT}" \
    --user="${MYSQL_USER}" --password="${MYSQL_PASS}" \
    --socket="${MYSQL_SOCKET}" \
    --single-transaction \
    --master-data=2 \
    --routines --triggers --events \
    --quick --max_allowed_packet=1G \
    --databases "${DB}" | gzip > "${BACKUP_FILE}"
  
  # 驗證備份檔非空且可解壓
  if [[ ! -s "${BACKUP_FILE}" ]]; then
    log "ERROR: 備份檔為空 ${BACKUP_FILE}"
    exit 1
  fi
  gzip -t "${BACKUP_FILE}" || { log "ERROR: 備份檔損壞 ${BACKUP_FILE}"; exit 1; }
  
  log "備份完成: $(du -h "${BACKUP_FILE}" | cut -f1)"
done

# 3. 清理本地舊備份
log "清理 ${RETENTION_DAYS} 天前本地備份..."
find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete

# 4. 記錄 Binlog 位置（供增量恢復/重建從庫）
LATEST_DUMP=$(ls -t "${BACKUP_DIR}"/*.sql.gz | head -1)
zcat "${LATEST_DUMP}" | head -30 | grep -E 'CHANGE MASTER TO|MASTER_LOG_FILE|MASTER_LOG_POS' > "${BACKUP_DIR}/binlog_pos_${DATE_TAG}.txt"
log "Binlog 位置已記錄: $(cat "${BACKUP_DIR}/binlog_pos_${DATE_TAG}.txt")"

log "=== 冷備結束 ==="

異地同步腳本

#!/bin/bash
# /usr/local/bin/rsync_offsite.sh
# 每日 01:00 執行

set -euo pipefail

SRC_DIR="/data/cold_backup"
DEST_HOST="backup@offsite-nas.company.com"
DEST_DIR="/volume1/mysql_archive"
SSH_KEY="/home/backup/.ssh/id_rsa"
LOG_FILE="${SRC_DIR}/rsync_$(date +%F).log"

exec > >(tee -a "${LOG_FILE}") 2>&1

log() { echo "[$(date '+%F %T')] $*"; }

log "=== 異地同步開始 ==="

rsync -avz --progress --partial \
  --delete-after \
  --bwlimit=50000 \          # 限速 50MB/s 避免佔滿頻寬
  -e "ssh -i ${SSH_KEY} -o StrictHostKeyChecking=no -o ConnectTimeout=30" \
  "${SRC_DIR}/" "${DEST_HOST}:${DEST_DIR}/"

RSYNC_EXIT=$?
if [[ ${RSYNC_EXIT} -eq 0 || ${RSYNC_EXIT} -eq 24 ]]; then  # 24 = 部分檔案消失(正常)
  log "異地同步成功"
else
  log "ERROR: rsync 失敗 exit code=${RSYNC_EXIT}"
  # 這裡可接入告警：發送郵件/鉤子/企業微信
  exit 1
fi

log "=== 異地同步結束 ==="

恢復演練標準流程

全量恢復： 新建實例 → 導入最新全量 SQL → 重放 Binlog 至目標時間點
單表恢復： 解壓 SQL → grep -A 10000 'CREATE TABLE `target_table`' 提取建表+資料 → 導入目標庫
異地拉取測試： 季度從異地存儲拉取一份備份在測試環境全量恢復，驗證資料完整性與 RTO

冷備 vs 熱備 vs 快照對比

維度	冷備	熱備	存儲快照
業務影響	無（從庫）	無	無
一致性	強（單事務視圖）	最終一致	崩潰一致
恢復速度	慢（SQL 重放）	中	極快（秒級）
跨版本/引擎	支援	不支援	不支援
單表恢復	容易	困難	困難
儲存成本	低（壓縮後）	高	中

避坑清單

備份帳號權限最小化：GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup_user'@'%'
關閉 Binlog 寫入備份操作：SET SESSION SQL_LOG_BIN=0;（mysqldump --master-data 已隱含）
大表並行備份：使用 mydumper 或 mysqldump --tab + 多線程壓縮
磁碟空間預留：備份目錄預留 2 倍最大資料庫大小，防止爆盤
定期校驗 CHECKSUM TABLE 或 pt-table-checksum 驗證主從一致性