您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

Linux 系統下 MySQL 自動備份腳本最佳實踐：全備、增量、定時、壓縮、異地同步

編輯：MySQL 資料庫相關知識

一、基礎版：每日全量備份（gzip 壓縮 + crontab）

1. 創建目錄結構

mkdir -p /backup/mysqlbackup

2. 編寫備份腳本 `/backup/mysqlbackup/mysql_autobackup.sh`

#!/bin/bash
# 每日全量備份腳本
FILENAME=$(date +%Y%m%d)
DB_NAME="your_database"
DB_USER="root"
DB_PASS="your_password"   # 建議用 .my.cnf 或密鑰文件避免明文
BAK_DIR="/backup/mysqlbackup"

/usr/bin/mysqldump --opt --single-transaction --master-data=2 \
  -u${DB_USER} -p${DB_PASS} ${DB_NAME} | gzip > ${BAK_DIR}/${DB_NAME}_${FILENAME}.sql.gz

# 保留 30 天
find ${BAK_DIR} -name "${DB_NAME}_*.sql.gz" -mtime +30 -delete

3. 賦權 + 定時任務

chmod +x /backup/mysqlbackup/mysql_autobackup.sh
crontab -e
# 每天 01:01 執行
01 01 * * * /bin/bash /backup/mysqlbackup/mysql_autobackup.sh

二、進階版：生產級全量 + 增量備份體系

核心前提

開啟 log_bin（Binlog）
Binlog 格式 ROW（增量解析準確）
expire_logs_days 設定保留天數（如 7）

1. 目錄規劃

/backup/mysql/
├── full/           # 全量備份
├── incremental/    # 增量備份
└── logs/           # 備份日誌

2. 全量備份腳本 `full_backup.sh`

#!/bin/bash
# 全量備份 + 刷新 Binlog
DATE=$(date +%Y%m%d)
BAK_DIR="/backup/mysql/full"
LOG_FILE="/backup/mysql/logs/full_${DATE}.log"

echo "=== $(date) Full Backup Start ===" >> ${LOG_FILE}

/usr/bin/mysqldump --flush-logs --single-transaction --master-data=2 \
  -u${DB_USER} -p${DB_PASS} ${DB_NAME} | gzip > ${BAK_DIR}/${DB_NAME}_${DATE}.sql.gz

echo "=== $(date) Full Backup Done ===" >> ${LOG_FILE}

# 清理 35 天前全量備份
find ${BAK_DIR} -name "${DB_NAME}_*.sql.gz" -mtime +35 -delete

3. 增量備份腳本 `incremental_backup.sh`

#!/bin/bash
# 增量備份：基於 Binlog
DATE=$(date +%Y%m%d)
BAK_DIR="/backup/mysql/incremental"
BINLOG_DIR="/var/lib/mysql"          # 根據實際調整
BINLOG_INDEX="${BINLOG_DIR}/mysql-bin.index"
LOG_FILE="/backup/mysql/logs/inc_${DATE}.log"

echo "=== $(date) Incremental Backup Start ===" >> ${LOG_FILE}

# 1. 刷新 Binlog 生成新文件
/usr/bin/mysqladmin -u${DB_USER} -p${DB_PASS} flush-logs

# 2. 解析昨天產生的 Binlog
START_TIME=$(date -d "1 day ago" +"%Y-%m-%d %H:%M:%S")

for BINLOG_FILE in $(cat ${BINLOG_INDEX} | awk -F'/' '{print $NF}'); do
  /usr/bin/mysqlbinlog --start-datetime="${START_TIME}" \
    -d ${DB_NAME} ${BINLOG_DIR}/${BINLOG_FILE} | gzip >> ${BAK_DIR}/inc_${DATE}.sql.gz
done

echo "=== $(date) Incremental Backup Done ===" >> ${LOG_FILE}

# 清理 32 天前增量備份
find ${BAK_DIR} -name "inc_*.sql.gz" -mtime +32 -delete

4. 定時策略

# 全量：每天 03:01
01 03 * * * /bin/bash /backup/mysql/full_backup.sh
# 增量：每小時執行（或每天 04:00）
00 04 * * * /bin/bash /backup/mysql/incremental_backup.sh

三、異地容災：rsync 同步至遠端存儲

# 在備份腳本末尾添加
RSYNC_PASS="/etc/rsync.passwd"
REMOTE_USER="backup"
REMOTE_HOST="10.0.0.100"
REMOTE_DIR="/remote/backup/mysql"

/usr/bin/rsync -avz --delete --password-file=${RSYNC_PASS} \
  /backup/mysql/ ${REMOTE_USER}@${REMOTE_HOST}::${REMOTE_DIR}

四、恢復演練：定期驗證備份可用性

全量恢復：gunzip < backup.sql.gz | mysql -u root -p db_name
增量恢復：全量恢復後，按順序回放 Binlog：mysqlbinlog inc_20230101.sql.gz | mysql -u root -p db_name
時間點恢復 (PITR)：mysqlbinlog --stop-datetime="2023-01-01 12:00:00" ...

建議：每月至少一次完整恢復演練，記錄 RTO/RPO 實測值。

五、避坑指南與優化建議

問題	解決
mysqldump 鎖表影響業務	InnoDB 必加 `--single-transaction`；MyISAM 無解，建議從庫備份
備份文件過大傳輸慢	分庫分表備份、並行壓縮 `pigz`、增量備份為主
Binlog 衝突導致增量解析失敗	確保 `binlog_format=ROW`，避免 DDL 衝突
密碼明文在腳本/進程列表	使用 `[client]` 寫入 `~/.my.cnf` (chmod 600)，腳本不帶 -p
磁盤空間耗盡	配置 `df -h` 監控告警，備份腳本加空間檢查
從庫備份延遲導致數據不一致	備份前 `STOP SLAVE`，備份後 `START SLAVE`，或用 LVM 快照/Percona XtraBackup