简单的mysqldump备份（windows）

备份

小数据库用mysql自带的mysqldump就可以完成备份，写一个简单的适用于windows下跑的脚本。

策略

每天凌晨1点全备，保留7天

备份脚本dbbak.bat

set day=%date:~0,4%%date:~5,2%%date:~8,2%
set localip=xxx.xxx.xxx.xxx
set backup_path=D:\dbbak\%localip%
set backup_dir=D:\dbbak\%localip%\%day%
set uname=root
set password=xxxxxxxx
set port=3306
set mysqlhome=D:\apps\app\mysql
set mysqlhomebin=%mysqlhome%\bin

:: create backup_dir
if not exist %backup_dir% ( 
md %backup_dir% 2>nul 
) 
if not exist %backup_dir% ( 
echo Backup path: %backup_dir% not exists, create dir failed. 
goto exit 
) 

:: locate mysql_home/bin
cd /d %mysqlhomebin%
mysqldump -h %localip% -P %port% -u %uname% -p%password% --all-databases --single-transaction --set-gtid-purged=OFF > %backup_dir%\mysqldump_%localip%_%day%.sql


rem --- if you need to compress the dumpfile by winrar and delete source dumpfile, unmark rem
set rar="C:\Program Files\WinRAR\WinRAR.exe"
cd /d %backup_dir%
%rar% a -df mysqldump_%localip%_%day%.rar mysqldump_%localip%_%day%.sql

rem --- remain day for dmp file
set remain_day=7
rem --- delete files before 7 days
forfiles /p "%backup_path%" /s /d -%remain_day% /c "cmd /c del /f @path"

加入定时任务msyql_cron_add.bat

@echo off 

:: ------- 配置项 ------- 

:: 要执行的文件名 
set FILE=D:\dbbak\dbbak.bat 

:: 计划频率类型 
set FREQUENCY=DAILY 

:: 频率，与上面的计划频率类型对应 
set MODIFIER=1 

:: 该计划执行的时间（24 小时制） 
set DATETIME=00:30:00 

:: 计划的名字 
set NAME="Mysql Backup Cron Job" 

:: 计划执行用户，不建议修改 
set USER="System" 

:: ------- 以下请勿修改 ------- 

schtasks /Create /RU %USER% /SC %FREQUENCY% /MO %MODIFIER% /ST %DATETIME% /TN %NAME% /TR %FILE% 
pause 
 
其中的 

:: 计划频率类型，可选项： 
:: MINUTE 分钟 
:: DAILY 天 
:: HOURLY 小时 
:: WEEKLY 周 
:: MONTHLY 月 

:: 频率，与上面的计划频率类型对应 
:: MINUTE: 1 到 1439 分钟 
:: HOURLY: 1 - 23 小时 
:: DAILY: 1 到 365 天 
:: WEEKLY: 1 到 52 周 
:: MONTHLY: 1 到 12，或 FIRST, SECOND, THIRD, FOURTH, LAST, LASTDAY