| 策略 | RPO | RTO | 適用場景 |
|---|---|---|---|
| 每日全備 + Binlog 增量 | 秒級 | 分鐘級 | 核心業務、金融級 |
| 每日全備 + 異地同步 | 24h | 小時級 | 一般業務、成本敏感 |
| 邏輯備份 | 依頻率 | 較長 | 跨版本遷移、審計 |
[mysqld]
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
server-id = 1
@echo off
setlocal enabledelayedexpansion
:: ===== 配置區 =====
set MYSQL_BIN="C:\Program Files\MySQL\MySQL Server 8.0\bin"
set BACKUP_ROOT=E:\mysql_backup
set DB_USER=backup_user
set DB_PASS=StrongPass123!
set DB_NAME=production_db
set KEEP_DAYS=7
:: ===================
set DATE_STR=%date:~0,4%%date:~5,2%%date:~8,2%
set TIME_STR=%time:~0,2%%time:~3,2%%time:~6,2%
set TIME_STR=%TIME_STR: =0%
set FULL_DIR=%BACKUP_ROOT%\full
set INCR_DIR=%BACKUP_ROOT%\incremental
set LOG_FILE=%BACKUP_ROOT%\backup_%DATE_STR%.log
mkdir %FULL_DIR% 2>nul
mkdir %INCR_DIR% 2>nul
echo [%DATE% %TIME%] 開始增量備份 >> %LOG_FILE%
:: 1. 刷新 Binlog,產生新文件
%MYSQL_BIN%\mysqladmin -u%DB_USER% -p%DB_PASS% flush-logs >> %LOG_FILE% 2>&1
if errorlevel 1 (
echo [%DATE% %TIME%] ERROR: flush-logs 失敗 >> %LOG_FILE%
goto END
)
:: 2. 複製新產生的 Binlog 到增量目錄
:: 獲取最新 binlog 文件名
for /f "tokens=*" %%i in ('dir /b /od %MYSQL_BIN%\..\data\mysql-bin.* 2^>nul') do set LATEST_BINLOG=%%i
if not defined LATEST_BINLOG (
echo [%DATE% %TIME%] ERROR: 未找到 binlog 文件 >> %LOG_FILE%
goto END
)
copy "%MYSQL_BIN%\..\data\%LATEST_BINLOG%" "%INCR_DIR%\%DATE_STR%_%TIME_STR%_%LATEST_BINLOG%" >> %LOG_FILE% 2>&1
:: 3. 壓縮增量備份
"%MYSQL_BIN%\..\..\bin\7z.exe" a -tzip "%INCR_DIR%\%DATE_STR%_%TIME_STR%_%LATEST_BINLOG%.zip" "%INCR_DIR%\%DATE_STR%_%TIME_STR%_%LATEST_BINLOG%" >> %LOG_FILE% 2>&1
del "%INCR_DIR%\%DATE_STR%_%TIME_STR%_%LATEST_BINLOG%"
:: 4. 清理過期備份
forfiles /p %FULL_DIR% /m *.zip /d -%KEEP_DAYS% /c "cmd /c del @path" >> %LOG_FILE% 2>&1
forfiles /p %INCR_DIR% /m *.zip /d -%KEEP_DAYS% /c "cmd /c del @path" >> %LOG_FILE% 2>&1
echo [%DATE% %TIME%] 增量備份完成 >> %LOG_FILE%
:END
exit /b
@echo off
set MYSQL_BIN="C:\Program Files\MySQL\MySQL Server 8.0\bin"
set BACKUP_ROOT=E:\mysql_backup
set DB_USER=backup_user
set DB_PASS=StrongPass123!
set DB_NAME=production_db
set DATE_STR=%date:~0,4%%date:~5,2%%date:~8,2%
%MYSQL_BIN%\mysqldump -u%DB_USER% -p%DB_PASS% --single-transaction --routines --triggers --events --default-character-set=utf8mb4 %DB_NAME% | gzip > %BACKUP_ROOT%\full\%DB_NAME%_%DATE_STR%.sql.gz
:: 驗證備份完整性
%MYSQL_BIN%\mysqlbinlog %BACKUP_ROOT%\full\%DB_NAME%_%DATE_STR%.sql.gz > nul 2>&1
if errorlevel 1 echo WARNING: 備份文件可能損壞
@echo off
set MYSQL_DATA="C:\ProgramData\MySQL\MySQL Server 8.0\Data"
set BACKUP_DIR=D:\mysql_cold_backup
set DATE_STR=%date:~0,4%%date:~5,2%%date:~8,2%
net stop MySQL80
xcopy %MYSQL_DATA% %BACKUP_DIR%\%DATE_STR%\ /E /H /C /Y /I
net start MySQL80
:: 保留 7 天
forfiles /p %BACKUP_DIR% /d -7 /c "cmd /c rmdir /s /q @path"
cmd.exe → 參數 /c "E:\scripts\backup_incremental.bat"@echo off
:: restore_test.bat
set MYSQL_BIN="C:\Program Files\MySQL\MySQL Server 8.0\bin"
set BACKUP_ROOT=E:\mysql_backup
set TARGET_DB=restore_test_db
:: 1. 建立測試庫
%MYSQL_BIN%\mysql -u root -p -e "CREATE DATABASE %TARGET_DB%;"
:: 2. 還原最近全量
for /f "delims=" %%i in ('dir /b /od %BACKUP_ROOT%\full\*.gz') do set LATEST_FULL=%%i
gzip -dc %BACKUP_ROOT%\full\%LATEST_FULL% | %MYSQL_BIN%\mysql -u root -p %TARGET_DB%
:: 3. 依序應用增量 Binlog
for /f "delims=" %%i in ('dir /b /od %BACKUP_ROOT%\incremental\*.zip') do (
7z x -o%TEMP% %%i
%MYSQL_BIN%\mysqlbinlog %TEMP%\*.0* | %MYSQL_BIN%\mysql -u root -p %TARGET_DB%
del %TEMP%\*.0*
)
echo 還原測試完成,請驗證數據一致性
mysqlbinlog --verify-binlog-checksumWindows 下 MySQL 增量備份核心是 **mysqldump 全量 + Binlog 增量**。批處理腳本需處理:日期時間格式化、錯誤碼檢查、日誌記錄、壓縮、過期清理、驗證。關鍵成功因子:備份賬號權限最小化、Binlog 完整性、定期恢復演練、異地複製。建議引入 7-Zip 壓縮、forfiles 清理、任務排程器高權限運行。