簡單的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