windows服务器下备份mysql数据库的脚本分享

@echo off
chcp 65001 >nul
cls
echo ============================================
echo    mysql 5.7 数据库备份工具
echo    database: jfdzyl
echo ============================================
echo.

rem ============ 配置区域 ============
rem 1. mysql 5.7 路径（默认安装路径）
set "mysql_path=c:\program files\mysql\mysql server 5.7\bin"
if not exist "%mysql_path%\mysqldump.exe" (
    echo [错误] 未找到 mysql 5.7
    echo 请修改 mysql_path 为正确的安装路径
    pause
    exit /b 1
)

rem 2. 数据库连接信息
set db_host=localhost
set db_port=3306
set db_user=root
set db_pass=mysql
set db_name=xxxx

rem 3. 备份设置
set backup_root="d:\mysql_backups\mysql5.7"
set keep_days=7           rem 保留最近7天备份
set max_backups=20        rem 最大保留备份文件数
set compress=true         rem 是否启用压缩

echo [信息] mysql 5.7 路径: %mysql_path%
echo [信息] 数据库: %db_name%@%db_host%:%db_port%

rem ============ 创建备份目录 ============
for /f "tokens=1-3 delims=/ " %%a in ('date /t') do (
    set year=%%c
    set month=%%a
    set day=%%b
)

rem 处理日期格式（如果日期是yyyy/mm/dd格式）
if "%year:~0,2%"=="20" (
    set date_string=%year%%month%%day%
) else (
    set date_string=%day%%month%%year%
    set year=%date_string:~4,4%
    set month=%date_string:~2,2%
    set day=%date_string:~0,2%
)

set timestamp=%time: =0%
set timestamp=%timestamp::=-%
set backup_dir=%backup_root%%year%-%month%-%day%
set log_file=%backup_dir%%db_name%_backup.log

if not exist %backup_root% (
    mkdir %backup_root%
    echo [信息] 创建备份根目录: %backup_root%
)

if not exist "%backup_dir%" (
    mkdir "%backup_dir%"
    echo [信息] 创建备份目录: %backup_dir%
)

echo ============================================
echo 开始备份时间: %date% %time%
echo 备份目录: %backup_dir%
echo ============================================
echo.

rem ============ 执行备份 ============
set backup_file=%db_name%_%date_string%_%timestamp%.sql
set backup_file_full=%backup_dir%%backup_file%

echo [步骤1] 检查mysql连接...
cd /d "%mysql_path%"
mysql.exe -h%db_host% -p%db_port% -u%db_user% -p%db_pass% -e "select 1" >nul 2>&1
if errorlevel 1 (
    echo [错误] mysql连接失败！
    echo 请检查:
    echo 1. mysql 5.7服务是否运行
    echo 2. 用户名/密码是否正确
    echo 3. 端口号是否正确(默认:3306)
    pause
    exit /b 1
)
echo [成功] mysql连接正常

echo.
echo [步骤2] 获取数据库信息...
mysql.exe -h%db_host% -p%db_port% -u%db_user% -p%db_pass% -n -b -e "select table_schema 'database', round(sum(data_length+index_length)/1024/1024,2) 'size_mb' from information_schema.tables where table_schema='%db_name%' group by table_schema" 2>nul

echo.
echo [步骤3] 执行备份...
echo 开始时间: %time%
echo 备份文件: %backup_file%

rem mysql 5.7 优化备份参数
mysqldump.exe ^
    -h%db_host% ^
    -p%db_port% ^
    -u%db_user% ^
    -p%db_pass% ^
    --databases %db_name% ^
    --default-character-set=utf8mb4 ^
    --single-transaction ^
    --routines ^
    --triggers ^
    --events ^
    --hex-blob ^
    --complete-insert ^
    --add-drop-database ^
    --add-drop-table ^
    --result-file="%backup_file_full%" ^
    --log-error="%backup_dir%%db_name%_dump_error.log"

if errorlevel 1 (
    echo [错误] 备份失败！
    if exist "%backup_dir%%db_name%_dump_error.log" (
        echo 错误日志:
        type "%backup_dir%%db_name%_dump_error.log"
    )
    pause
    exit /b 1
)

echo 结束时间: %time%
echo [成功] 数据库备份完成！

rem 获取文件大小
for %%f in ("%backup_file_full%") do (
    set "file_size=%%~zf"
    set "size_mb=!file_size:/1024/1024=!"
    set /a size_mb=!file_size!/1024/1024
    echo 文件大小: !file_size! 字节 (!size_mb! mb)
)

echo.
echo [步骤4] 验证备份文件...
rem 检查备份文件是否包含正确的结束标记
findstr /i "dump completed" "%backup_file_full%" >nul
if errorlevel 1 (
    findstr /i "-- dump completed" "%backup_file_full%" >nul
    if errorlevel 0 (
        echo [验证] 备份文件完整性检查通过
    ) else (
        echo [警告] 未找到标准结束标记，但文件已生成
    )
) else (
    echo [验证] 备份文件完整性检查通过
)

rem ============ 可选：压缩备份 ============
if "%compress%"=="true" (
    echo.
    echo [步骤5] 压缩备份文件...
    
    rem 检查7-zip
    set "zip_path=c:\program files\7-zip\7z.exe"
    if not exist "%zip_path%" (
        set "zip_path=%programfiles%\7-zip\7z.exe"
    )
    
    if exist "%zip_path%" (
        "%zip_path%" a -tzip -mx5 "%backup_file_full%.zip" "%backup_file_full%" >nul
        if errorlevel 0 (
            del "%backup_file_full%"
            echo [成功] 压缩完成: %backup_file%.zip
            set backup_file_final=%backup_file_full%.zip
        ) else (
            echo [警告] 压缩失败，保留原文件
            set backup_file_final=%backup_file_full%
        )
    ) else (
        echo [信息] 未找到7-zip，使用未压缩备份
        set "compress=false"
        set backup_file_final=%backup_file_full%
    )
) else (
    set backup_file_final=%backup_file_full%
)

rem ============ 清理旧备份 ============
echo.
echo [步骤6] 清理旧备份...

rem 方法1：按天数清理
echo 清理超过%keep_days%天的备份...
forfiles /p %backup_root% /s /m *.sql /d -%keep_days% /c "cmd /c echo 删除: @path && del @path" 2>nul
forfiles /p %backup_root% /s /m *.zip /d -%keep_days% /c "cmd /c echo 删除: @path && del @path" 2>nul

rem 方法2：按文件数量清理
echo 检查备份文件数量...
cd /d %backup_root%
for /f %%i in ('dir /b /s *.sql *.zip ^| find /c /v ""') do set count=%%i
echo 当前备份文件总数: %count%

if %count% gtr %max_backups% (
    echo 超出最大限制(%max_backups%)，清理最旧的文件...
    dir /b /s *.sql *.zip /o-d /t:c > "%backup_root%\backup_list.txt"
    
    setlocal enabledelayedexpansion
    set /a del_count=%count%-%max_backups%
    echo 需要删除!del_count!个文件
    
    for /f "skip=%max_backups% delims=" %%f in ('dir /b /s *.sql *.zip /o-d /t:c') do (
        if exist "%%f" (
            echo 删除: %%f
            del "%%f"
        )
    )
    endlocal
)

rem 清理空目录
for /f "delims=" %%d in ('dir %backup_root% /ad /b /s ^| sort /r') do (
    dir "%%d" 2>nul | findstr "^[0-9]" >nul || (
        rmdir "%%d" 2>nul && echo 清理空目录: %%d
    )
)

echo.
echo ============================================
echo           备份摘要
echo ============================================
echo 数据库: %db_name%
echo 备份时间: %date% %time%
echo 备份文件: %backup_file_final%
for %%f in ("%backup_file_final%") do (
    echo 文件大小: %%~zf 字节
)
echo 备份位置: %backup_dir%
echo 压缩状态: %compress%
echo ============================================
echo.

rem 记录日志
echo %date% %time% - 备份完成: %backup_file_final% >> "%log_file%"
for %%f in ("%backup_file_final%") do (
    echo 文件大小: %%~zf 字节 >> "%log_file%"
)

pause

@echo off
chcp 65001 >nul
title mysql 5.7 备份工具

rem mysql 5.7 默认安装路径
set mysql_path="c:\program files\mysql\mysql server 5.7\bin"
set backup_dir="d:\mysql_backup"

rem 数据库配置
set db_host=localhost
set db_user=root
set db_pass=mysql
set db_name=jfdzyl

rem 创建备份目录
if not exist %backup_dir% mkdir %backup_dir%

rem 生成备份文件名
set datestamp=%date:~0,4%%date:~5,2%%date:~8,2%
set timestamp=%time:~0,2%%time:~3,2%%time:~6,2%
set timestamp=%timestamp: =0%
set backup_file=%backup_dir%%db_name%_%datestamp%_%timestamp%.sql

echo 正在备份数据库 %db_name% ...
echo 请稍候...

cd /d %mysql_path%
mysqldump -h%db_host% -u%db_user% -p%db_pass% %db_name% > "%backup_file%"

if errorlevel 1 (
    echo 备份失败！
    pause
    exit /b 1
)

echo 备份成功！
echo 备份文件: %backup_file%
for %%f in ("%backup_file%") do (
    echo 文件大小: %%~zf 字节
)

rem 自动打开备份目录
explorer %backup_dir%
pause

@echo off
chcp 65001 >nul
cls

echo mysql 5.7 数据库备份验证工具
echo ==============================
echo.

set mysql_path="c:\program files\mysql\mysql server 5.7\bin"
set backup_dir="d:\mysql_backup"
set db_user=root
set db_pass=mysql
set db_name=jfdzyl

rem 生成备份文件
set timestamp=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%
set timestamp=%timestamp: =0%
set timestamp=%timestamp::=%
set backup_file=%backup_dir%%db_name%_%timestamp%.sql

echo 步骤1: 执行备份...
cd /d %mysql_path%
mysqldump -u%db_user% -p%db_pass^
    --databases %db_name%^
    --single-transaction^
    --routines^
    --triggers^
    --events^
    --set-gtid-purged=off^
    --result-file="%backup_file%"

if errorlevel 1 (
    echo [错误] 备份失败
    pause
    exit /b 1
)

echo 步骤2: 验证备份文件...
rem 检查文件大小
for %%f in ("%backup_file%") do set filesize=%%~zf
if %filesize% lss 1024 (
    echo [警告] 备份文件可能不完整 (大小: %filesize% 字节)
) else (
    echo [通过] 文件大小: %filesize% 字节
)

rem 检查文件内容
findstr /i "create table" "%backup_file%" >nul
if errorlevel 1 (
    echo [错误] 备份文件不包含表结构
) else (
    echo [通过] 包含表结构定义
)

findstr /i "insert into" "%backup_file%" >nul
if errorlevel 1 (
    echo [警告] 备份文件不包含数据插入语句
) else (
    echo [通过] 包含数据插入语句
)

rem 快速恢复测试（可选）
echo.
echo 步骤3: 快速完整性测试...
echo 测试表结构语法...
rem 这里可以添加更多验证逻辑

echo.
echo 备份验证完成！
echo 文件: %backup_file%
echo 大小: %filesize% 字节
echo.

pause

@echo off
chcp 65001 >nul
title 设置mysql 5.7定时备份

echo 正在创建mysql 5.7自动备份任务...
echo.

rem 创建备份脚本
(
echo @echo off
echo chcp 65001 ^>nul
echo set mysql_path="c:\program files\mysql\mysql server 5.7\bin"
echo set backup_dir="d:\mysql_backup"
echo.
echo set db_user=root
echo set db_pass=mysql
echo set db_name=jfdzyl
echo.
echo rem 生成带日期的文件名
echo for /f "tokens=1-3 delims=/ " %%a in ('date /t'^) do set date=%%a-%%b-%%c
echo set date=%%date:/=-%%
echo set backup_file=%%backup_dir%%%%db_name%%_%%date%%.sql
echo.
echo cd /d %%mysql_path%%
echo mysqldump -u%%db_user%% -p%%db_pass%% --databases %%db_name%% --routines --triggers --events --single-transaction ^> "%%backup_file%%"
echo.
echo echo 备份完成: %%date%% ^>^> "%%backup_dir%%\backup.log"
) > "%temp%\mysql_backup_task.bat"

rem 创建任务计划
schtasks /create /tn "mysql5.7_backup_%db_name%" ^
    /tr "%temp%\mysql_backup_task.bat" ^
    /sc daily /st 02:00 ^
    /ru "system" ^
    /rl highest ^
    /f

if errorlevel 1 (
    echo 任务创建失败，尝试使用管理员权限...
    echo 请右键以管理员身份重新运行此脚本
) else (
    echo 定时备份任务创建成功！
    echo 任务名称: mysql5.7_backup_%db_name%
    echo 执行时间: 每天 02:00
    echo 备份目录: d:\mysql_backup
)

echo.
pause

rem 如果遇到权限问题，可以尝试以下方法：

rem 1. 使用mysql配置文件存储密码
echo [client] > my.cnf
echo host=localhost >> my.cnf
echo user=root >> my.cnf
echo password=mysql >> my.cnf

rem 2. 然后使用
mysqldump --defaults-file=my.cnf jfdzyl > backup.sql

rem 3. 删除配置文件（安全）
del my.cnf