Backup MySQL db with Coldfusion

Question

What I want to do is run a backup task in Coldfusion (probably in a scheduled task) which will back up the structure and data in a MySql database.

The hosting server I use always blocks the use of cfexecute for security purposes so I can't use mysqldump.

e.g.

<cfexecute name="c:\program files\mysql\mysql server 4.1\bin\mysqldump"
 arguments="--user=xxx --password=yyy dharma" 
 outputfile="#expandPath("./ao.sql")#" timeout="30"/>

(From Raymond Camden)

Are there any other options available to me ?

Answer 1

Backing up database files is a good idea, but if you back them up to the same drive, and the drive fails, you are screwed. I backup my databases daily to my local system. Here is the script I use in a .bat file

@ECHO OFF


@REM Set dir variables. Use ~1 format in win2k

SET basedir={directory where zip files will be put}
SET workdir={Working directory}
SET mysqldir=c:\PROGRA~1\mysql\mysqls~1.5\bin
SET gzipdir=c:\PROGRA~2\GnuWin32\bin
SET mysqlpassword={db password}
SET mysqluser={db user}
SET host={host IP or domain name}
for /f "tokens=1-4 delims=/ " %%a in ('date/t') do ( 
set mm=%%a
set dd=%%b
set yy=%%c
)

ECHO Check connection
PING -n 1 %host%|find "Reply from " >NUL
IF NOT ERRORLEVEL 1 goto :SUCCESS
IF ERRORLEVEL 1 goto :END

:SUCCESS
ECHO Connection found, run backup

@REM Change to mysqldir
CD %mysqldir%

@REM dump database. This is all one line
mysqldump -h %host% -u %mysqluser% -p%mysqlpassword% --databases {space delimited list of databases to backup >%workdir%\backup.sql

@REM Change to workdir  
CD %workdir%

@REM Zip up database
%gzipdir%\gzip.exe backup.sql

@REM Move to random file name
MOVE backup.sql.gz %basedir%\%yy%_%mm%_%dd%_backup.gz

@REM Change back to base dir
CD %basedir%

:END
ECHO No connection, do not run

I use Windows task scheduler to run this every night. You could probably update it to remove older backups.

You will need to make sure you have gzip installed.

This will put copies of the DB on your local system - I then use a backup service to back up the backups to another offsite system.