邏輯備份 (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
}
mysql < db.sql → 校驗核心表行數/校驗和mysqlbinlog --start-position=XXX --stop-position=YYY binlog.xxx.gz | mysql → 驗證指定時間點數據| 數據量 | 推薦方案 | RTO | RPO |
|---|---|---|---|
| < 100GB | mysqldump 全量 + Binlog 增量 (本方案) | < 30min | < 1h |
| 100GB - 500GB | mysqldump 分庫並行 + 壓縮 + 並行導入 (mydumper/myloader) | < 2h | < 1h |
| > 500GB | XtraBackup 物理備份 (參考文章 180217) | < 30min | < 5min |
邏輯備份腳本核心要素:**單事務一致性視圖、按庫並行導出、壓縮存儲、校驗和完整性、自動清理、統一日誌、失敗告警**。雖不如物理備份快,但可移植性強、跨版本兼容、人工可讀。生產環境務必:月度演練恢復、異地複製備份文件、監控備份耗時/大小/成功率。數據量超 500GB 時,規劃遷移至 XtraBackup 物理備份體系。