In setting up a new IIS7/Windows 2008 server, I had a need to automate the backup of my MySQL database and so pieced together a little batch file that I then scheduled via the Task Scheduler. The file first removes all backup files that are more than a week old, then performs a sql dump and finally zips the dump. Files are named according to the date and time they were created. Anyway, it took me quite a while to find all the pieces and parts I needed, so thought I'd post it here as a starting point for others who may be wanting to do the same.
Oh, one prerequisite for this particular script to function: You have to have a copy of winzip installed (I have version 12) WITH the optional command line utility, which is a separate download (http://www.winzip.com/downcl.htm). Hey, the whole deal costs $29 bucks...spring for it!
MySQLBackup.BAT
@echo off
for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i
for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
rem Killing all files older than a week old...
forfiles /D -8 /M *.zip /C "cmd /c del @fname.zip"
"C:\mysql\bin\mysqldump" -u username -p"password" dbname >C:\mysqlbackup\%DATE_DAY%_%DATE_TIME%_database.sql
wzzip C:\mysqlbackup\%DATE_DAY%_%DATE_TIME%_database.zip C:\mysqlbackup\%DATE_DAY%_%DATE_TIME%_database.sql -mex
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
if it's because I'm using Windows 2003 instead of 2008. I decided I
don't need to zip the files (plenty of disk space for keeping a few
backup files around). So for some reason your forfiles line kept
giving me "Could not find..." errors. To fix it I added .sql after
the @fname, and it works like a charm.
I was not looking for a backup script, but since you offered one I
decided to put it to use. :)