Backup your Mysql databases installed on windows with a batch file and task scheduler

If you have mysql running on windows platform for example for development/testing purposes, you are probably encountered the backing up issue
here are small bat file that can help you.

First of all you will need 7-zip for archiving SQL dumps, you can download it and use for free, from here

Then create text file with extension .bat let say mybackup.bat, and copy paste following code:


@echo off
set backupdir="d:\temp"
set mysqldir="c:\WebServers\usr\local\mysql5\"
set mysqldatadir="c:\WebServers\usr\local\mysql5\data"
set dbuser="root"
set dbpass=""
set zip="c:\Program Files\7-Zip\"
set endtime=0

:DODIR
DIR %backupdir%

:GETTIME
for /F "tokens=2-4 delims=/ " %%i in ('date /t') do (
set mm=%%i
set dd=%%j
set yy=%%k
)

for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do (
set hh=%%i
set ii=%%j
set ss=%%k
)

if "%endtime%"=="1" goto END
set fn=_%dd%-%mm%-%yy%_%hh%-%mm%-%ss%
pushd %mysqldatadir%
for /d %%f in (*) do (
if not exist %backupdir%\%%f\ (
echo Making Directory %%f
mkdir %backupdir%\%%f
) else (
echo Directory %%f Exists
)
echo Backing up database %%f%fn%.sql.zip
%mysqldir%\bin\mysqldump.exe --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --complete-insert  | %zip%\7z.exe a -tgzip -si%%f%fn%.sql %backupdir%\%%f\%%f%fn%.sql.zip
echo Done...
)
set endtime=1
goto :GETTIME
:END
popd
EXIT


Don't forget change variables in the beginning of the script according to your apps location.
  • +1
  • January 14, 2010, 11:23pm
  • t0lkman

Comments (0)

RSS Collapse / Expand

Only registered and authorized users can leave comments. Login or Register