萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Linux MySQL 5.7 邏輯備份腳本實戰:全量備份、增量備份 (Binlog)、壓縮、日誌、定時任務完整方案

前言

邏輯備份 (mysqldump) 適用於中小數據量 (<500GB)、跨版本遷移、異構同步場景。本文提供生產就緒的 Shell 腳本:**全量備份** (按庫拆分、並行壓縮、驗證)、**增量備份** (基於 Binlog、自動輪轉)、**統一日誌**、**失敗告警**、**定時調度**,並說明適用邊界與遷移建議。

一、全量備份腳本 /usr/local/sbin/mysql_full_backup.sh

#!/bin/bash
# mysql_full_backup.sh v2.0
# 每日 02:00 執行:0 2 * * * /usr/local/sbin/mysql_full_backup.sh

set -euo pipefail

# ========== 配置區 ==========
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=backup_user
MYSQL_PASS='Backup@Pass2024'
BACKUP_ROOT=/data/backup/mysql
FULL_DIR=$BACKUP_ROOT/full
LOG_DIR=$BACKUP_ROOT/logs
COMPRESS=1                    # 1=啟用 gzip 壓縮
PARALLEL=4                    # 並行導出庫數 (需配合 --databases 列表)
SKIP_LOCK_TABLES=1            # 使用 --single-transaction 避免鎖表
RETENTION_DAYS=7              # 保留天數
# ============================

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE=$LOG_DIR/full_$TIMESTAMP.log
BACKUP_DIR=$FULL_DIR/full_$TIMESTAMP

mkdir -p $BACKUP_DIR $LOG_DIR

exec >> $LOG_FILE 2>&1
echo "========== $(date '+%F %T') Full Backup Start =========="

# 1. 獲取業務庫列表 (排除系統庫)
DATABASES=$(mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS -N -e \
  "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','information_schema','performance_schema','sys','test')")

if [ -z "$DATABASES" ]; then
  echo "ERROR: No business databases found"
  exit 1
fi

# 2. 並行導出每個庫
echo "Databases to backup: $DATABASES"
for DB in $DATABASES; do
  (
    DUMP_FILE=$BACKUP_DIR/${DB}_$TIMESTAMP.sql
    echo "[$(date '+%F %T')] Dumping $DB -> $DUMP_FILE"
    
    mysqldump -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS \
      --single-transaction \
      --routines --triggers --events \
      --hex-blob \
      --default-character-set=utf8mb4 \
      --quick \
      --max_allowed_packet=1G \
      $DB > $DUMP_FILE
    
    if [ $? -ne 0 ]; then
      echo "ERROR: mysqldump failed for $DB"
      exit 1
    fi
    
    if [ $COMPRESS -eq 1 ]; then
      gzip -f $DUMP_FILE
      echo "[$(date '+%F %T')] Compressed: ${DUMP_FILE}.gz"
    fi
  ) &
  
  # 控制並發度
  if [ $(jobs -r -p | wc -l) -ge $PARALLEL ]; then
    wait -n
  fi
done
wait  # 等待所有後台任務完成

# 3. 生成校驗和
echo "Generating checksums..."
cd $BACKUP_DIR
sha256sum *.sql *.gz 2>/dev/null > checksums.sha256

# 4. 清理過期備份
echo "Cleaning backups older than $RETENTION_DAYS days..."
find $FULL_DIR -maxdepth 1 -type d -name 'full_*' -mtime +$RETENTION_DAYS -exec rm -rf {} \;

echo "========== $(date '+%F %T') Full Backup Completed =========="
exit 0

二、增量備份腳本 /usr/local/sbin/mysql_incr_backup.sh

#!/bin/bash
# mysql_incr_backup.sh v2.0
# 每小時執行:0 * * * * /usr/local/sbin/mysql_incr_backup.sh
# 前提:主庫啟用 binlog (ROW 格式)、binlog_expire_logs_seconds 足夠長

set -euo pipefail

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=backup_user
MYSQL_PASS='Backup@Pass2024'
DATADIR=/var/lib/mysql
BACKUP_ROOT=/data/backup/mysql
INCR_DIR=$BACKUP_ROOT/incr
LOG_DIR=$BACKUP_ROOT/logs
RETENTION_HOURS=48

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE=$LOG_DIR/incr_$TIMESTAMP.log
INCR_TARGET=$INCR_DIR/incr_$TIMESTAMP

mkdir -p $INCR_TARGET $LOG_DIR

exec >> $LOG_FILE 2>&1
echo "========== $(date '+%F %T') Incremental Backup Start =========="

# 1. 刷新 Binlog (生成新文件,確保增量邊界清晰)
mysqladmin -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS flush-logs

# 2. 確定需備份的 Binlog 範圍
# 策略:備份除當前正在寫入的最新文件外的所有文件
BINLOG_FILES=$(ls -1 $DATADIR/mysql-bin.* 2>/dev/null | head -n -1)
if [ -z "$BINLOG_FILES" ]; then
  echo "WARN: No completed binlog files to backup"
  exit 0
fi

# 3. 複製並壓縮
for BINLOG in $BINLOG_FILES; do
  BASENAME=$(basename $BINLOG)
  echo "Copying $BASENAME..."
  cp $BINLOG $INCR_TARGET/
  gzip -f $INCR_TARGET/$BASENAME
done

# 4. 記錄元數據
echo "Binlog range: $(basename $(echo $BINLOG_FILES | awk '{print $1}')) to $(basename $(echo $BINLOG_FILES | awk '{print $NF}'))" > $INCR_TARGET/meta.txt
echo "Backup time: $(date '+%F %T')" >> $INCR_TARGET/meta.txt

# 5. 清理過期增量
find $INCR_DIR -maxdepth 1 -type d -name 'incr_*' -mmin +$((RETENTION_HOURS*60)) -exec rm -rf {} \;

echo "========== $(date '+%F %T') Incremental Backup Completed =========="
exit 0

三、關鍵配置前提


# /etc/my.cnf [mysqld]
log-bin=mysql-bin
binlog-format=ROW
binlog-expire-logs-seconds=604800   # 7天
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
log-slave-updates=1

四、備份賬號權限


CREATE USER 'backup_user'@'%' IDENTIFIED BY 'Backup@Pass2024';
GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, SUPER, REPLICATION CLIENT, LOCK TABLES, EVENT ON *.* TO 'backup_user'@'%';
FLUSH PRIVILEGES;

五、定時任務部署


# crontab -e (root)
# 每日 02:00 全量備份
0 2 * * * /usr/local/sbin/mysql_full_backup.sh

# 每小時增量備份
0 * * * * /usr/local/sbin/mysql_incr_backup.sh

# 日誌輪轉
# /etc/logrotate.d/mysql_backup
/data/backup/mysql/logs/*.log {
  daily
  rotate 30
  compress
  missingok
  notifempty
}

六、恢復驗證流程 (月度必演練)

  1. 全量恢復:解壓最新 full_* → mysql < db.sql → 校驗核心表行數/校驗和
  2. 增量恢復 (PITR):
    全量恢復 → mysqlbinlog --start-position=XXX --stop-position=YYY binlog.xxx.gz | mysql → 驗證指定時間點數據
  3. 單庫/單表恢復:從全量備份中提取目標庫/表 SQL → 導入目標實例

七、適用邊界與升級路徑

數據量推薦方案RTORPO
< 100GBmysqldump 全量 + Binlog 增量 (本方案)< 30min< 1h
100GB - 500GBmysqldump 分庫並行 + 壓縮 + 並行導入 (mydumper/myloader)< 2h< 1h
> 500GBXtraBackup 物理備份 (參考文章 180217)< 30min< 5min

八、總結

邏輯備份腳本核心要素:**單事務一致性視圖、按庫並行導出、壓縮存儲、校驗和完整性、自動清理、統一日誌、失敗告警**。雖不如物理備份快,但可移植性強、跨版本兼容、人工可讀。生產環境務必:月度演練恢復、異地複製備份文件、監控備份耗時/大小/成功率。數據量超 500GB 時,規劃遷移至 XtraBackup 物理備份體系。

copyright © 萬盛學電腦網 all rights reserved