windows服务器全量备份及差异备份

windows服务器全量备份及差异备份

@echo off
chcp 65001
setlocal

:: 配置信息
set "MYSQL_USER=root"
set "MYSQL_PASSWORD=123456"
set "MYSQL_HOST=localhost"
set "BACKUP_DIR=D:\backup\Mysql"
set "DATABASES=doorData test"  
:: MySQL binlog目录
set "BINLOG_DIR=D:\Mysql\mysql-5.7.38\Data"  
set "LAST_BACKUP_FILE=%BACKUP_DIR%\last_backup.info"

:: 创建日期格式的文件夹
for /f "tokens=2 delims==" %%a in ('wmic OS get LocalDateTime /value') do set "dt=%%a"
set "YYYY=%dt:~0,4%"
set "MM=%dt:~4,2%"
set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%"
set "MIN=%dt:~10,2%"
set "SEC=%dt:~12,2%"
set "BACKUP_DATE=%YYYY%-%MM%-%DD%_%HH%-%MIN%-%SEC%"
set "TARGET_DIR=%BACKUP_DIR%\%BACKUP_DATE%"

:: 获取星期几（0=周日）
for /f "tokens=2 delims==" %%d in ('wmic path win32_localtime get dayofweek /format:list') do set DOW=%%d

:: 创建备份目录
if not exist "%TARGET_DIR%" mkdir "%TARGET_DIR%"

if "%DOW%"=="0" (
	echo 每周日执行全量备份...
	:: 执行全量备份
	for %%d in (%DATABASES%) do (
		echo 正在备份数据库: %%d
		mysqldump -h%MYSQL_HOST% -u%MYSQL_USER% -p%MYSQL_PASSWORD% %%d > "%TARGET_DIR%\%%d.sql"
		if errorlevel 1 (
			echo 备份数据库 %%d 失败!
		) else (
			echo 备份数据库 %%d 成功!
		)
	)
	echo 全量备份完成!
	:: mysqlbinlog --flush-logs --stop-never --raw --result-file="%TARGET_DIR%\binlog_" "%BINLOG_DIR%\mysql-bin.000001"
	echo %BACKUP_DATE% > "%LAST_BACKUP_FILE%"
	goto :EOF
	
)

for /f "tokens=*" %%a in (%LAST_BACKUP_FILE%) do set "LAST_POS=%%a"

:: 获取当前binlog文件
for /f "tokens=*" %%a in ('mysql -h%MYSQL_HOST% -u%MYSQL_USER% -p%MYSQL_PASSWORD% -e "SHOW MASTER STATUS\G" ^| findstr "File"') do set "CURRENT_BINLOG=%%a"
set "CURRENT_BINLOG=%CURRENT_BINLOG:File: =%"

:: 生成差异备份
echo 正在生成差异备份...
mysqlbinlog --start-datetime="%LAST_POS%" "%BINLOG_DIR%\%CURRENT_BINLOG%" > "%TARGET_DIR%\diff_backup.sql"

:: 更新上次备份时间
echo %BACKUP_DATE% > "%LAST_BACKUP_FILE%"

echo 差异备份完成!