Windows Server 定时备份 MySQL 信息升级版：单表备份 + 压缩功能 + 运维统计

《Windows Server 定时备份 MySQL 数据升级版：单表备份 + 压缩功能 + 运维统计》

1. 升级功能点

1.1 单表备份功能

功能亮点

  1. 细粒度备份：可指定备份特定数据库的特定表。
  2. 配置驱动：通过SINGLE_TABLES变量动态解析备份目标。
  3. 独立文件存储：每个表生成单独备份文件（数据库名_表名_日期.sql）。
if /i "%IS_SINGLE_TABLES%"=="1" (
    for /f "tokens=1* delims=;" %%a in ("%SINGLE_TABLES%") do (
        set "current_pair=%%a"
        ...
        for %%t in (!tables!) do (
            "%MYSQL_BIN%" ... !danbiao_db! %%t > "!danbiao_db!_%%t_%Ymd%.sql"
        )
    )
)

1.2 压缩功能

优化点

  1. 多工具支持：优先级检测 WinRAR → Bandizip → 7-Zip。
  2. 参数化压缩：根据工具类型动态生成压缩命令。
  3. 空间优化：压缩成功后自动清理原始 SQL 文件。
if /i "%IS_ZIP%"=="1" (
    :: 自动检测可用压缩工具（WinRAR > Bandizip > 7-Zip）
    if exist "%WINRAR_PATH%" ( ... )
    else if exist "%BANDZIP_PATH%" ( ... )
    else ( ... )
    :: 压缩后删除原SQL文件
    !COMPRESS_CMD! "!zip_file!" "!sql_file!"
    if !ERRORLEVEL! equ 0 del /q "!sql_file!"
)

1.3 健壮性增强

  1. 预检机制
:: 检查 MySQL 客户端是否存在
if not exist "%MYSQL_BIN_TEST%" ( ... exit /b 2 )
:: 测试数据库连接
"%MYSQL_BIN_TEST%" -h%MYSQL_HOST% ... -e "SELECT @@version;"
if %ERRORLEVEL% NEQ 0 ( ... exit /b 1 )
  1. 目录轮转优化
:: 使用 robocopy 替代 move（更可靠的空目录处理）
robocopy "!source_dir!" "!target_dir!" ... /mov /e /njh /njs
  1. 错误处理
set ERROR_FLAG=0  :: 全局错误标记
if !ERRORLEVEL! GTR 1 set ERROR_FLAG=1

1.4 备份统计

:: 计算备份耗时（精确到毫秒）
call :calculate_backup_time  → 输出 %TOTAL_ELAPSED_TIME%
:: 统计备份目录大小（智能单位转换）
call :get_folder_size "%BACKUP_ROOT%D7" → 输出 %FOLDER_SIZE%

2. 完整的备份脚本

@echo off
setlocal enabledelayedexpansion
:: ================================================
:: 自动请求管理员权限
:: ================================================
:: 检查是否已管理员身份运行
net session >nul 2>&1
if %ERRORLEVEL% NEQ 0 (
    echo 正在请求管理员权限...
    powershell -Command "Start-Process '%~s0' -Verb RunAs"
    exit /b
)
:: ================================================
:: 数据库备份脚本（优化版）
:: 功能：自动备份MySQL数据库，保留7天历史备份
:: 特点：WinRAR压缩、并行执行、错误日志、权限控制
:: 配置日期：2025-09-05
:: ================================================
:: ------------------------------
:: 配置区域（根据实际环境修改）
:: 初始化变量（需要根据实际环境修改）
:: ------------------------------
set "MYSQL_BIN=G:\SoftwareWork\mysql\bin\mysqldump"
set "MYSQL_BIN_TEST=G:\SoftwareWork\mysql\bin\mysql.exe"
:: 需要确保备份的用户(root)有导出权限功能
set "DATABASE_USER=root"
:: 如果密码带有特殊字符，需要在特殊字符前面加上^来转义，确保密码可以被系统正确读取
set "DATABASE_PASSWORD=123456"
:: 默认本地IP，可改为远程服务器IP
set "MYSQL_HOST=127.0.0.1"
:: 是否开启全库备份功能 1 开启 0 不开启
set "IS_QUANKU_BEIFEN=1"
:: 数据库名称，中间通过英文 , 进行分隔
set "DATABASES=app,client,plat"
:: 配置区域新增单表备份配置
:: 是否开启单表备份 1 开启单表备份 0 不开启单表备份
set "IS_SINGLE_TABLES=1"
:: 格式：数据库名1:表名1,表名2;数据库名2:表名1,表名2
set "SINGLE_TABLES=app:studen"
:: 是否启用压缩，1 启用 0 不启用
:: 压缩软件安装目录，支持WinRAR、BandZIP、7ZIP
:: 首选的压缩软件是WinRAR
set "IS_ZIP=1"
set "WINRAR_PATH=G:\Program Files\WinRAR\WinRAR.exe"
set "BANDZIP_PATH="
set "7ZIP_PATH="
:: ------------------------------
:: 配置区域（不需要修改）
:: 初始化变量
:: ------------------------------
:: 当前文件夹
set "BACKUP_ROOT=%~dp0"
:: 年月
set "Ym=%date:~,4%%date:~5,2%"
:: 年月日
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
:: 日志目录
set "LOG_DIR=%BACKUP_ROOT%logs"
:: 备份日志文件，每个月一份日志文件
set "LOGFILE=%BACKUP_ROOT%logs/backup_%Ym%.log"
set ERROR_FLAG=0
:: 记录备份开始时间
set "BACKUP_START_TIME=%time%"
set "BACKUP_START_DATE=%date%"
set BACKUP_DIR="%BACKUP_ROOT%D1"
:: 创建日志目录
if not exist "%LOG_DIR%" (
    mkdir "%LOG_DIR%"
)
echo ====================================================================================================================== >> %LOGFILE%
:: 验证mysqldump可用性
:: 检查是否在PATH上配置MYSQL_HOME路径(例如 G:\SoftwareWork\mysql\bin)
echo [%date% %time%] ========== 验证MYSQL_BIN、MYSQL_BIN_TEST开始 ========== >> "%LOGFILE%"
where mysqldump >nul 2>&1
if %errorlevel% neq 0 (
    echo [%date% %time%] 错误: mysqldump未找到 >> "%LOGFILE%"
    exit /b 1
) else (
    echo [%date% %time%] 路径: %MYSQL_BIN%存在 >> "%LOGFILE%"
)
:: 检查mysql是否存在
if not exist "%MYSQL_BIN_TEST%" (
    echo [%date% %time%] 错误: MySQL客户端未找到 >> "%LOGFILE%"
    echo 路径: %MYSQL_BIN_TEST% >> "%LOGFILE%"
    exit /b 2
)else (
	echo [%date% %time%] 路径: %MYSQL_BIN_TEST%存在 >> %LOGFILE%
)
echo [%date% %time%] ========== 验证MYSQL_BIN、MYSQL_BIN_TEST结束 ========== >> "%LOGFILE%"
echo [%date% %time%] ================== 开始目录检查 ================== >> %LOGFILE%
:: ------------------------------
:: 目录检查与创建
:: ------------------------------
for /l %%i in (1,1,7) do (
    if not exist %BACKUP_ROOT%D%%i (
        mkdir %BACKUP_ROOT%D%%i
        echo [%date% %time%] 提示：创建目录: D%%i >> %LOGFILE%
    )
)
:: ------------------------------
:: 清理旧备份（保留7天）
:: ------------------------------
echo [%date% %time%] ======================== 清理旧备份开始 ======================== >> "%LOGFILE%"
:: 检查目录是否存在
if not exist %BACKUP_DIR% (
    echo [%date% %time%] 警告: 目录 %BACKUP_DIR% 不存在 >> "%LOGFILE%"
    goto :skip_delete
)
:: 检查目录是否为空
dir /a-d /b %BACKUP_DIR%\* >nul 2>&1
if %ERRORLEVEL% neq 0 (
    echo [%date% %time%] 警告: %BACKUP_DIR% 无文件可删除 >> "%LOGFILE%"
    goto :skip_delete
)
:: 删除所有文件
echo [%date% %time%] 正在清理 %BACKUP_DIR% 目录下的所有文件... >> "%LOGFILE%"
del /Q /F %BACKUP_DIR%\* >> "%LOGFILE%" 2>&1
if %ERRORLEVEL% neq 0 (
    echo [%date% %time%] 错误: 目录文件删除 %BACKUP_DIR% 失败 (错误码: %ERRORLEVEL%) >> "%LOGFILE%"
    set ERROR_FLAG=1
) else (
    echo [%date% %time%] 成功: 目录文件已清理 %BACKUP_DIR% >> "%LOGFILE%"
)
:skip_delete
echo [%date% %time%] ======================== 清理旧备份结束 ======================== >> "%LOGFILE%"
:: ------------------------------
:: 目录轮转（使用robocopy更可靠）
:: ------------------------------
echo [%date% %time%] ================== 开始目录轮转 ================== >> %LOGFILE%
for /l %%i in (2,1,7) do (
    set /a j=%%i-1
    set "source_dir=%BACKUP_ROOT%D%%i"
    set "target_dir=%BACKUP_ROOT%D!j!"
    :: 使用robocopy移动文件（自动处理空目录）
    robocopy "!source_dir!" "!target_dir!" *.* /mov /e /njh /njs /ndl /nc /ns /nfl >nul 2>&1
    if !ERRORLEVEL! GTR 1 (
        echo [%date% %time%] 错误: 移动D%%i到D!j!失败（错误码: !ERRORLEVEL!） >> %LOGFILE%
        set ERROR_FLAG=1
    ) else (
        echo [%date% %time%] 成功: D%%i文件已移动到D!j! >> %LOGFILE%
    )
)
echo [%date% %time%] ================== 结束目录检查 ================== >> %LOGFILE%
echo [%date% %time%] ======================== 备份开始 ======================== >> "%LOGFILE%"
echo [%date% %time%] ================== 测试数据库连接开始 ================== >> "%LOGFILE%"
:: 测试数据库连接
"%MYSQL_BIN_TEST%" -h%MYSQL_HOST% -u"%DATABASE_USER%" -p"%DATABASE_PASSWORD%" -e "SELECT @@version;" >nul 2>&1
if %ERRORLEVEL% equ 0 (
    echo [%date% %time%] MySQL服务正常 >> "%LOGFILE%"
) else (
    echo [%date% %time%] 失败 错误代码: %ERRORLEVEL% >> "%LOGFILE%"
    echo [%date% %time%] 请检查: >> "%LOGFILE%"
    echo 1. MySQL服务是否运行 >> "%LOGFILE%"
    echo 2. 用户名/密码是否正确 >> "%LOGFILE%"
    echo 3. 防火墙是否放行3306端口 >> "%LOGFILE%"
    exit /b 1
)
echo [%date% %time%] ================== 测试数据库连接结束 ================== >> "%LOGFILE%"
echo ========================================================================================== >> "%LOGFILE%"
:: ==================== 单表备份功能 ====================
echo [%date% %time%] 检查单表备份功能设置 IS_SINGLE_TABLES=%IS_SINGLE_TABLES% >> "%LOGFILE%"
:: 清理变量值中的空格
set "IS_SINGLE_TABLES=%IS_SINGLE_TABLES: =%"
if /i "%IS_SINGLE_TABLES%"=="1" (
	:: 单表备份(单独备份重要的表)
	echo [%date% %time%] ================== 单表备份开始 ================== >> "%LOGFILE%"
	:: 解析单表备份配置
	for /f "tokens=1* delims=;" %%a in ("%SINGLE_TABLES%") do (
		set "current_pair=%%a"
		set "remaining_pairs=%%b"
		echo [%date% %time%] 准备备份数据库: "!current_pair!" 下的单表  >> "%LOGFILE%"
		:process_pair
		for /f "tokens=1* delims=:" %%d in ("!current_pair!") do (
			set "danbiao_db=%%d"
			set "tables=%%e"
			:: 备份该数据库下的所有指定表
			for %%t in (!tables!) do (
				echo [%date% %time%] 开始备份单表: !danbiao_db!.%%t >> "%LOGFILE%"
				"%MYSQL_BIN%" -h%MYSQL_HOST% -u"%DATABASE_USER%" -p"%DATABASE_PASSWORD%" !danbiao_db! %%t ^
					> "%BACKUP_ROOT%D7\!danbiao_db!_%%t_%Ymd%.sql" 2>> "%LOGFILE%"
				if !ERRORLEVEL! equ 0 (
					echo [%date% %time%] 单表备份成功: !danbiao_db!.%%t 已备份到 D7\!danbiao_db!_%%t_%Ymd%.sql >> "%LOGFILE%"
				) else (
					echo [%date% %time%] 单表备份失败: !danbiao_db!.%%t 备份错误（代码!ERRORLEVEL!） >> "%LOGFILE%"
					set ERROR_FLAG=1
				)
			)
		)
		:: 处理下一对数据库:表
		if defined remaining_pairs (
			set "current_pair=!remaining_pairs!"
			set "remaining_pairs="
			goto process_pair
		)
	)
	echo [%date% %time%] ================== 单表备份结束 ================== >> "%LOGFILE%"
) else (
	echo [%date% %time%] 单表备份功能未启用(IS_SINGLE_TABLES=%IS_SINGLE_TABLES%) >> "%LOGFILE%"
)
echo ========================================================================================== >> "%LOGFILE%"
:: 全库备份
echo [%date% %time%] 检查全库备份功能设置 IS_QUANKU_BEIFEN=%IS_QUANKU_BEIFEN% >> "%LOGFILE%"
:: 清理变量值中的空格
set "IS_QUANKU_BEIFEN=%IS_QUANKU_BEIFEN: =%"
if /i "%IS_QUANKU_BEIFEN%"=="1" (
	echo [%date% %time%] ================== 全库备份开始 ================== >> "%LOGFILE%"
	for %%d in (%DATABASES%) do (
		set "current_db=%%d"
		echo [%date% %time%] 开始备份: !current_db! >> "%LOGFILE%"
		:: 核心备份命令（显式指定-h）
		"%MYSQL_BIN%" -h%MYSQL_HOST% -u"%DATABASE_USER%" -p"%DATABASE_PASSWORD%" ^
			--single-transaction --source-data=2 --routines --triggers --events %%d ^
			> "%BACKUP_ROOT%D7\!current_db!_%Ymd%.sql" 2>> "%LOGFILE%"
		if !ERRORLEVEL! equ 0 (
			echo [%date% %time%] 成功: !current_db! 已备份到 D7\!current_db!_%Ymd%.sql >> "%LOGFILE%"
		) else (
			echo [%date% %time%] 失败: !current_db! 备份错误（代码!ERRORLEVEL!） >> "%LOGFILE%"
			type "%LOGFILE%" | findstr /i "error" | tail -n 3 >> "%LOGFILE%"
		)
	)
	echo [%date% %time%] ================== 全库备份结束 ================== >> "%LOGFILE%"
) else (
	echo [%date% %time%] 全库备份功能未启用(IS_QUANKU_BEIFEN=%IS_QUANKU_BEIFEN%) >> "%LOGFILE%"
)
echo ========================================================================================== >> "%LOGFILE%"
:: ==================== 压缩功能 ====================
echo [%date% %time%] 检查压缩设置 IS_ZIP=%IS_ZIP% >> "%LOGFILE%"
:: 清理变量值中的空格
set "IS_ZIP=%IS_ZIP: =%"
if /i "%IS_ZIP%"=="1" (
    echo [%date% %time%] ===================== 开始压缩备份文件 ===================== >> "%LOGFILE%"
    :: 检测可用的压缩工具（按优先级排序）
    set "COMPRESS_TOOL="
    set "COMPRESS_CMD="
    :: 1. 检测WinRAR
    if exist "%WINRAR_PATH%" (
        set "COMPRESS_TOOL=WinRAR"
        set "COMPRESS_CMD="%WINRAR_PATH%" a -afzip -ep1 -ibck -inul -m5"
    ) else (
        :: 2. 检测Bandizip
        if exist "%BANDZIP_PATH%" (
            set "COMPRESS_TOOL=Bandizip"
            set "COMPRESS_CMD="%BANDZIP_PATH%" c -fmt:zip -y"
        ) else (
            :: 3. 检测7-Zip（如果以后需要支持）
            where 7z >nul 2>&1
            if !ERRORLEVEL! equ 0 (
                set "COMPRESS_TOOL=7-Zip"
                set "COMPRESS_CMD=7z a -tzip -mx5"
            )
        )
    )
    :: 如果没有检测到任何压缩工具
    if not defined COMPRESS_TOOL (
        echo [%date% %time%] 错误: 未找到支持的压缩工具 >> "%LOGFILE%"
        echo [%date% %time%] 请安装WinRAR、Bandizip、7-ZIP >> "%LOGFILE%"
        set ERROR_FLAG=1
		echo [%date% %time%] ===================== 结束压缩备份文件 ===================== >> "%LOGFILE%"
    ) else (
		echo [%date% %time%] 压缩工具: !COMPRESS_TOOL!  >> "%LOGFILE%"
		echo [%date% %time%] 压缩参数: !COMPRESS_CMD!  >> "%LOGFILE%"
		:: 执行压缩操作
		echo [%date% %time%] 正在使用!COMPRESS_TOOL!压缩D7目录下的SQL文件 >> "%LOGFILE%"
		:: 压缩每个SQL文件为单独的ZIP
		for %%f in ("%BACKUP_ROOT%D7\*.sql") do (
			set "sql_file=%%f"
			set "zip_file=%%~nf.zip"
			echo [%date% %time%] sql文件正在压缩: !sql_file! >> "%LOGFILE%"
			echo [%date% %time%] 压缩命令为: !COMPRESS_CMD! "%BACKUP_ROOT%D7\!zip_file!" "!sql_file!" >> "%LOGFILE%"
			!COMPRESS_CMD! "%BACKUP_ROOT%D7\!zip_file!" "!sql_file!"
			if !ERRORLEVEL! equ 0 (
				del /q "!sql_file!"
				echo [%date% %time%] sql文件压缩成功: !zip_file! >> "%LOGFILE%"
			) else (
				echo [%date% %time%] sql文件压缩失败: !sql_file! (错误码:!ERRORLEVEL!) >> "%LOGFILE%"
				set ERROR_FLAG=1
			)
		)
		echo [%date% %time%] ===================== 结束压缩备份文件 ===================== >> "%LOGFILE%"
	)
) else (
	echo [%date% %time%] 压缩功能未启用(IS_ZIP=%IS_ZIP%) >> "%LOGFILE%"
)
:: ==================== 备份统计功能 ====================
echo ========================================================================================== >> "%LOGFILE%"
echo [%date% %time%] ======================== 开始统计备份统计信息 ======================== >> "%LOGFILE%"
:: 1. 计算总备份时间
call :calculate_backup_time
:: 2. 统计D7文件夹大小
call :get_folder_size "%BACKUP_ROOT%D7"
:: 3. 输出统计结果
echo [%date% %time%] ============================================== >> "%LOGFILE%"
echo [%date% %time%] 备份开始时间: %BACKUP_START_DATE% %BACKUP_START_TIME% >> "%LOGFILE%"
echo [%date% %time%] 备份结束时间: %date% %time% >> "%LOGFILE%"
echo [%date% %time%] 总备份耗时: %TOTAL_ELAPSED_TIME% >> "%LOGFILE%"
echo [%date% %time%] D7文件夹大小: %FOLDER_SIZE% >> "%LOGFILE%"
echo [%date% %time%] ============================================== >> "%LOGFILE%"
echo [%date% %time%] ======================== 结束统计备份统计信息 ======================== >> "%LOGFILE%"
echo [%date% %time%] ======================== 备份完成 ======================== >> "%LOGFILE%"
:: ------------------------------
:: 计算备份总耗时的辅助函数
:: ------------------------------
:calculate_backup_time
    :: 解析开始时间
    for /f "tokens=1-4 delims=:.," %%a in ("%BACKUP_START_TIME%") do (
        set "start_h=%%a"
        set "start_m=%%b"
        set "start_s=%%c"
        set "start_ms=%%d"
    )
    :: 解析结束时间
    for /f "tokens=1-4 delims=:.," %%a in ("%time%") do (
        set "end_h=%%a"
        set "end_m=%%b"
        set "end_s=%%c"
        set "end_ms=%%d"
    )
    :: 转换为秒数进行计算
    set /a start_total=1000*(1%start_h%-100)*3600 + 1000*(1%start_m%-100)*60 + 1000*(1%start_s%-100) + (1%start_ms%-100)
    set /a end_total=1000*(1%end_h%-100)*3600 + 1000*(1%end_m%-100)*60 + 1000*(1%end_s%-100) + (1%end_ms%-100)
    :: 计算时间差（毫秒）
    set /a elapsed_ms=end_total - start_total
    :: 处理跨天情况
    if %elapsed_ms% lss 0 set /a elapsed_ms+=86400000
    :: 转换回小时:分钟:秒.毫秒格式
    set /a elapsed_s=elapsed_ms / 1000
    set /a elapsed_ms=elapsed_ms %% 1000
    set /a elapsed_m=elapsed_s / 60
    set /a elapsed_s=elapsed_s %% 60
    set /a elapsed_h=elapsed_m / 60
    set /a elapsed_m=elapsed_m %% 60
    :: 格式化输出（补前导零）
    if %elapsed_h% lss 10 set elapsed_h=0%elapsed_h%
    if %elapsed_m% lss 10 set elapsed_m=0%elapsed_m%
    if %elapsed_s% lss 10 set elapsed_s=0%elapsed_s%
    if %elapsed_ms% lss 100 set elapsed_ms=0%elapsed_ms%
    if %elapsed_ms% lss 10 set elapsed_ms=0%elapsed_ms%
    set "TOTAL_ELAPSED_TIME=%elapsed_h%:%elapsed_m%:%elapsed_s%.%elapsed_ms%"
goto :eof
:: ------------------------------
:: 获取文件夹大小的辅助函数（增强版）
:: ------------------------------
:get_folder_size
    set "folder=%~1"
    set "FOLDER_SIZE=0"
    set "raw_size="
    echo [%date% %time%] 开始计算文件夹大小: "%folder%" >> "%LOGFILE%"
    :: 使用增强的PowerShell命令，处理特殊路径和错误
    for /f "usebackq delims=" %%a in (`
        powershell -Command "$ErrorActionPreference='Stop'; try { (Get-ChildItem -Path '%folder%' -Recurse -File -ErrorAction Stop | Measure-Object -Property Length -Sum).Sum } catch { Write-Host 'ERROR'; }"
    `) do (
        set "raw_size=%%a"
    )
    :: 检查是否获取到有效大小
    if defined raw_size (
        if "!raw_size!"=="ERROR" (
            echo [%date% %time%] 错误: 无法访问文件夹 "%folder%" >> "%LOGFILE%"
            set "FOLDER_SIZE=访问失败"
        ) else if "!raw_size!"=="" (
            echo [%date% %time%] 警告: 文件夹为空或未找到文件 >> "%LOGFILE%"
            set "FOLDER_SIZE=0 B"
        ) else (
            :: 转换为人类可读格式（增强版）
            set "bytes=!raw_size!"
            :: 使用PowerShell进行单位转换（更可靠）
            for /f "usebackq delims=" %%s in (`
                powershell -Command "[math]::Round(!bytes!/1GB,2) | Out-String"
            `) do set "gb_size=%%s"
            for /f "usebackq delims=" %%s in (`
                powershell -Command "[math]::Round(!bytes!/1MB,2) | Out-String"
            `) do set "mb_size=%%s"
            for /f "usebackq delims=" %%s in (`
                powershell -Command "[math]::Round(!bytes!/1KB,2) | Out-String"
            `) do set "kb_size=%%s"
            :: 选择合适的单位显示
            if !bytes! gtr 1073741824 (
                set "FOLDER_SIZE=!gb_size: =! GB"
            ) else if !bytes! gtr 1048576 (
                set "FOLDER_SIZE=!mb_size: =! MB"
            ) else if !bytes! gtr 1024 (
                set "FOLDER_SIZE=!kb_size: =! KB"
            ) else (
                set "FOLDER_SIZE=!bytes! B"
            )
            echo [%date% %time%] 原始大小: !bytes! 字节 >> "%LOGFILE%"
            echo [%date% %time%] 转换结果: !FOLDER_SIZE! >> "%LOGFILE%"
        )
    )
    :: 最终检查
    if not defined FOLDER_SIZE set "FOLDER_SIZE=计算失败"
    if "!FOLDER_SIZE!"=="0" set "FOLDER_SIZE=0 B"
    echo [%date% %time%] 文件夹大小计算完成: !FOLDER_SIZE! >> "%LOGFILE%"
goto :eof
endlocal

运行后效果：

在这里插入图片描述

posted @ 2025-10-23 13:30  ycfenxi  阅读(2)  评论(0)    收藏  举报