MySQL 本地備份與雙機互備 Shell 腳本：三種模式、排除庫、首次遠端建庫自動化

腳本定位

單一腳本支援三種運作模式，涵蓋從「首次建立遠端庫」到「日常本地/遠端備份」的完整生命週期：

• first：首次執行，在遠端主機建立同名資料庫
• local：本地備份，壓縮存放於指定目錄，保留上一版
• 無參數（預設）：遠端互備，透過管道 mysqldump | mysql 直接同步至遠端

配置區塊（請依環境修改）

# 遠端主機
REMOTE_HOST="192.168.1.100"
REMOTE_PASSWORD="RemoteRootPass!"

# 本地設定
DB_PATH="/var/lib/mysql"
LOCAL_PASSWORD="LocalRootPass!"
DB_BACKUP_PATH="/root/db_backup"
NO_BACKUP_DB="test information_schema performance_schema sys"

核心邏輯解析

#!/bin/bash
# 掃描本地資料目錄下的每個資料庫
for db in $(ls $DB_PATH); do
  [[ -d $DB_PATH/$db ]] || continue
  # 排除檢查
  skip=0
  for n in $NO_BACKUP_DB; do [[ $db == $n ]] && skip=1; done
  [[ $skip -eq 1 ]] && { echo "[$db] 跳過備份"; continue; }

  case "$1" in
    first)
      echo -n "[$db] 首次在遠端建庫... "
      mysqladmin -h $REMOTE_HOST -p$REMOTE_PASSWORD create $db && echo "成功" || { echo "失敗"; exit 1; }
      ;;
    local)
      [[ -d $DB_BACKUP_PATH ]] || mkdir -p $DB_BACKUP_PATH
      [[ -f $DB_BACKUP_PATH/$db.gz ]] && mv $DB_BACKUP_PATH/$db.gz $DB_BACKUP_PATH/$db.1.gz
      mysqldump -p$LOCAL_PASSWORD $db | gzip > $DB_BACKUP_PATH/$db.gz
      echo "[$db] 本地備份完成"
      ;;
    *)
      echo -n "[$db] 遠端同步... "
      mysqldump --add-drop-table -p$LOCAL_PASSWORD $db | mysql -h $REMOTE_HOST -p$REMOTE_PASSWORD $db
      echo "完成"
      ;;
  esac
done

部署與使用

1. 儲存為 /usr/local/sbin/mysql_dual_backup.sh，chmod +x
2. 首次部署：mysql_dual_backup.sh first（在遠端建立所有庫）
3. 日常本地備份：mysql_dual_backup.sh local（crontab 每天凌晨）
4. 日常遠端互備：mysql_dual_backup.sh（crontab 每小時/按需）

安全強化建議

• 密碼勿寫死腳本，改用 --defaults-extra-file=/root/.mysql_remote.cnf、--defaults-extra-file=/root/.mysql_local.cnf
• 遠端布號僅授予 CREATE、INSERT、UPDATE、DELETE、DROP 權限於特定庫
• 通訊啟用 SSL：--ssl-mode=REQUIRED
• 腳本回傳碼非 0 時觸發監控告警

進階擴充：增量備份與 PITR

雙機互備僅解決「資料同步」，無法替代「時間點恢復」。生產環境請疊加：

1. 啟用 Binlog（log_bin、binlog_format=ROW、expire_logs_days=7）
2. 每日全量 mysqldump --master-data=2 --single-transaction + 定時 mysqlbinlog 歸檔 Binlog
3. 異地儲存全量 + Binlog，支援 PITR（Point-In-Time Recovery）

結語

一個支援 first|local|remote 三模式的腳本，能覆蓋 MySQL 雙機互備的大部分需求。但請記住：**同步 ≠ 備份**。唯有「全量 + Binlog + 異地 + 定期演練」才能構成完整的資料保護體系。