Activities

October 2009
M T W T F S S
« Aug   Nov »
 1234
567891011
12131415161718
19202122232425
262728293031  

Automated MySQL backup for Windows

I have recently re-setup a full backup mechanism both for MySQL database and webfiles. I uses mysql dump for backing up the db and use normal copy command to backup web files.

backup structure is like this.
1. database will dump to a ftp location on daily wises after the compression and another remote program will pull the files from there.
2. Another database copy and full web files’s backup will copy to a Mapped drive which will connect and disconnect during the backup process as well.

Tools used
a. Mysqldump command
b. Gnu zip ( set the zip installation directory in system path)
c. DOS commands

OS : Windows 2003 Web edition

REM ################# Mysql backup scripts and set this as a windows scheduler #########
REM @echo off
set today=””
FOR /F “TOKENS=1* DELIMS= ” %%A IN (‘DATE/T’) DO SET CDATE=%%B
FOR /F “TOKENS=1,2 eol=/ DELIMS=/ ” %%A IN (‘DATE/T’) DO SET mm=%%B
FOR /F “TOKENS=1,2 DELIMS=/ eol=/” %%A IN (‘echo %CDATE%’) DO SET dd=%%B
FOR /F “TOKENS=2,3 DELIMS=/ ” %%A IN (‘echo %CDATE%’) DO SET yyyy=%%B
SET today=ggs-dbbackup-%date:~0,2%-%date:~3,2%-%date:~6,6%
echo %today%

set temp_dir=D:tempmysql
set temp_ftp=D:FTPLocalUserdbbkp_usrdaily-bkpdatabase
mysqldump -B mydatabase -r %temp_dir%%today%.sql -uusername -ppassword

rmdir /S /Q %temp_ftp%
mkdir %temp_ftp%

zip -rq D:FTPLocalUserdbbkp_usrdaily-bkpdatabase%today%.zip %temp_dir%
del /f %temp_dir%%today%.sql
net use Z: \10.10.6.8matthew-backup$ password /USER:matthew /PERSISTENT:YES
xcopy /E D:FTPLocalUserdbbkp_usrdaily-bkpdatabase%today%.zip Z:BackupDATABASE
net use /delete Z:

REM ####################### Backup completed ######

Note:
net use Z: \10.10.6.8matthew-backup$ password /USER:matthew /PERSISTENT:YES
This is a mapped backup location uses windows file sharing.
net use
Z: = mapped drive
\10.10.6.8matthew-backup$ = Hidden shares on the file server 10.10.6.8 ( $ will hide the share name listed in network)
password = access password
/USER: = user name


Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>