Windows下实现MySQL数据库自动备份脚本，自动删除一周前备份数据，并生成日志文件

2024-07-18 阅读：250 类型：后端分类：Mysql 标签： windows

1、生成SQL文件，新建back.bat脚本

@echo off
set MYSQL_PATH=D:\MySQL-8.0.26\bin
set DB_HOST=localhost
set DB_USERNAME=root
set DB_PASSWORD=root
set DB_NAME=mydb
set BACKUP_PATH=E:\MySqlBack\MySQL

rem 获取当前日期，并将其格式化为 YYYY-MM-DD 格式
for /f "tokens=2 delims==" %%I in ('wmic os get localdatetime /value') do set datetime=%%I
set YYYY=%datetime:~0,4%
set MM=%datetime:~4,2%
set DD=%datetime:~6,2%
set CURRENT_DATE=%YYYY%-%MM%-%DD%

cd D:\MySQL-8.0.26\bin
mysqldump -u %DB_USERNAME% -p%DB_PASSWORD% --quick %DB_NAME% --default-character-set=utf8 > E:\MySqlBack\MySQL\mydb_%CURRENT_DATE%.sql

rem 将完成信息写入日志文件
echo Backup completed at %DATE% %TIME% >> E:\MySqlBack\MySQL\backup_mydb_log_%CURRENT_DATE%.txt

rem 等待几秒钟后关闭窗口
timeout /t 10 >nul

exit

2、根据日期删除备份文件，新建delback.bat脚本

删除过去一周7天的数据

@echo off
setlocal enabledelayedexpansion

rem 设置变量
set DATA_FOLDER=E:\MySqlBack\MySQL
set DAYS_TO_KEEP=7

rem 获取当前日期，并计算一周前的日期
for /f "tokens=1-3 delims=/" %%a in ('echo %date%') do (
  set "YYYY=%%c"
  set "MM=%%a"
  set "DD=%%b"
)
set CURRENT_DATE=%YYYY%-%MM%-%DD%

rem 计算一周前的日期
powershell -Command "$oldDate=(Get-Date).AddDays(-%DAYS_TO_KEEP%); '{0:yyyy-MM-dd}' -f $oldDate" > temp.txt
set /p OLD_DATE=<temp.txt
del temp.txt

rem 构建一周前的文件名前缀
set PREFIX=mydb_%OLD_DATE%

rem 删除一周之前的文件
for /f "tokens=*" %%f in ('dir /b /a-d "%DATA_FOLDER%\%PREFIX%*.sql"') do (
  del "%DATA_FOLDER%\%%f"
  echo Deleted file: %%f
)

rem 完成删除操作
echo Deletion completed at %date% %time%

exit