5

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 ?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Pat Dobson
  • 3,249
  • 2
  • 19
  • 32
  • 1
    (You may have done this already, but ...) Did you ask your host if they provide any tools for *automated* database backups? – Leigh Jan 10 '14 at 13:19
  • They don't. There's a manual mechanism for backing up but I really can't be bothered to use that for 20 sites on a regular basis . . . – Pat Dobson Jan 10 '14 at 13:23
  • Ugh... the only scalable alternative I am aware of requires createObject("java") which is probably disabled too. Plus it is a little more complicated. Hopefully someone else will chime in with an option I have not thought of.. – Leigh Jan 10 '14 at 13:36
  • I have a process that runs locally on my desktop and uses Windows Task SCheduler. It grabs a MySQL dump from the server and stores the data locally. I do not have access to the script at the moment. Will post it in a bit. – Scott Stroz Jan 10 '14 at 13:38
  • 3
    Maybe a change of hosts is in order. – Dan Bracuk Jan 10 '14 at 13:43
  • @Dan Bracuk - change of hosts isn't an option. I quite like the security. As CF doesn't run too well in a shared environment the restrictions ensure far more up-time than other servers I've used... – Pat Dobson Jan 10 '14 at 14:02
  • Maybe another option is to use either php or .NET (both available on the same server) to run backups (and nothing else) ? Any ideas on that ? – Pat Dobson Jan 10 '14 at 14:03
  • @Pat, I'm not so sure about your security. Really tight security would prevent you from doing backups of your DB via ColdFusion wouldn't it? DDL type language and access to the file system from a cfquery tag? Not sure you want to do that. Scott's answer below will run from the task scheduler - probably your best option. – Mark A Kruger Jan 10 '14 at 15:23

1 Answers1

7

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.

Scott Stroz
  • 7,510
  • 2
  • 21
  • 25
  • I might need some extra guidance with this. I'm using a shared server so I have no access to roots or cfadmin etc. I can create scheduled tasks for individual domains so how would I go about implementing this ? – Pat Dobson Jan 10 '14 at 15:34
  • 1
    You could simply create a new script for each DB you want backed up. Or you can simply backup every database at once (that is what I do). Pretty sure all you need is a username/password that can access the DB and can log on remotely (that may be your biggest issue). – Scott Stroz Jan 10 '14 at 15:53