MySQL Binlog 增量備份自動化腳本實戰

MySQL Binlog 增量備份自動化腳本實戰

Binlog（二進制日誌）記錄所有數據變更，是增量備份與主從同步的核心。本文提供基於 mysql-bin.index 的增量備份腳本，支援定時壓縮、FTP 上傳、定時任務調度。

一、啟用 Binlog

編輯 /etc/my.cnf（Windows 為 my.ini）：

[mysqld]
log-bin=mysql-bin
server-id=1
expire_logs_days=30   # 自動清理 30 天前 Binlog
# 從庫需額外開啟
log_slave_updates=1

重啟 MySQL：systemctl restart mysqld

二、增量備份腳本 /usr/work/back.sh

#!/bin/bash
# MySQL Binlog 增量備份 - 建議在從庫執行，避免影響主庫性能

BakDir=/usr/work/mysql/bak.log
DateDir=$(date +%Y%m%d)
TargetDir=$BakDir/$DateDir

mkdir -p $TargetDir
cd $BakDir

# 1. 刷新日誌，產生新的 binlog 文件
mysqladmin -uroot -p'your_password' flush-logs

# 2. 讀取 mysql-bin.index，獲取除最新外的所有舊 binlog
BinlogDir=/var/lib/mysql
head -n -1 $BinlogDir/mysql-bin.index | sed 's|./||' > /tmp/binlog_list.txt

# 3. 複製舊 binlog 到備份目錄
while read line; do
    cp $BinlogDir/$line $TargetDir/
    echo "Copied $line"
done < /tmp/binlog_list.txt

# 4. 壓縮打包
cd $BakDir
tar -zcvf $DateDir.tar.gz $DateDir/
rm -rf $TargetDir

# 5. FTP 上傳（按需配置）
ftp -ivn << EOF
open 192.168.1.125
user admin password
cd binlog_backup
put $DateDir.tar.gz
bye
EOF

echo "$(date) Binlog incremental backup completed."

三、加入定時任務

# 編輯 /etc/crontab
# 每天 02:30 執行增量備份
30 2 * * * root /usr/work/back.sh >> /var/log/mysql_binlog_backup.log 2>&1

# 重啟 crond 生效
systemctl restart crond

四、關鍵點說明

• flush-logs：切換新 Binlog，確保舊 Binlog 內容固化，可安全複製
• head -n -1：排除最新正在寫入的 Binlog，避免複製不完整文件
• 從庫執行：主庫寫入壓力大，建議在從庫（開啟 log_slave_updates=1）備份
• expire_logs_days：MySQL 端自動清理，防止磁盤佔滿
• 驗證恢復：定期用 mysqlbinlog 導出 SQL 並測試導入

五、恢復示例

# 1. 解壓備份包
tar -zxvf 20240115.tar.gz

# 2. 合併 Binlog 導出 SQL（按時間範圍過濾）
mysqlbinlog --start-datetime="2024-01-15 00:00:00" --stop-datetime="2024-01-15 23:59:59" \
  mysql-bin.000001 mysql-bin.000002 > incremental.sql

# 3. 關閉 Binlog 導入（避免產生新 Binlog）
mysql -uroot -p -e "SET SQL_LOG_BIN=0; SOURCE incremental.sql;"

將增量備份納入常態化運維體系，配合全量備份，構建 RPO < 24h、RTO 可控的災備能力。