Easy backup of MySQL databases
Here is how I take backup of all databases on my MySQL server.
It is all done by a batch script, and can easily be triggered with the windows schedule service.
All you need to do is to copy the following code into a .bat file, edit the hostName, userName, passWord and ttl, and copy mysqldump.exe into the same folder as this script.
The ttl is how many days back you want to store the backup. After that amount of days, it will automatically be deleted.
@ECHO OFF SET hostName=localhost SET userName=username SET passWord=password SET ttl=7 set year=%date:~6,4% set yr=%date:~8,2% set month=%date:~3,2% set day=%date:~0,2% set hour=%time:~0,2% set hour=%hour: =0% set min=%time:~3,2% set sec=%time:~6,2% set tFileName=%year%-%month%-%day%__%hour%-%min%-%sec% echo Looking for over %ttl% days old files... FORFILES /P backup\ /S /D -%ttl% /M *.sql /C "cmd /c del @FILE| echo backup\@FILE is too old, deleting..." FORFILES /P log\ /S /D -%ttl% /M *.txt /C "cmd /c del @FILE| echo log\@FILE is too old, deleting..." echo. IF EXIST backup\ GOTO CHECKLOG echo Creating backup folder. mkdir backup\ :CHECKLOG IF EXIST log\ GOTO BACKUP echo Creating log folder. mkdir log\ echo. :BACKUP echo Taking backup, please wait... mysqldump --host %hostName% -u %userName% -p%passWord% --opt --all-databases > backup\%tFileName%.sql --log-error=log\%tFileName%.txt for /f "tokens=* delims= " %%a in (log\%tFileName%.txt) do ( SET logText=%%a ) echo. echo Backup complete. TIMEOUT 5 exit
The code will place a backup in the \backup\ sub-folder of where the batch file is located, and a log will be stored in \log\, if it is empty, it means that there was no errors from mysqldump