Tuesday, January 18, 2011

Backup MySQL databases in Windows using task scheduler


1. In Windows 7, Start->Control Panel->Administrative tools->task Scheduler
2. Click basic task
3. Following instructions step by step until Start a program: program/script
4. 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:
(there is no line break for 3 lines before  echo Done...)

@echo offset 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
--------------------------------------------------------------------------
5. Another method is to create cron.php and the script is "php cron.php". 

No comments:

Post a Comment