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

Leave a Reply