Twitter: raymondcamden

Address: Lafayette, LA, USA

MySQL administration via ColdFusion

12-07-2006 8,489 views Development, ColdFusion

A user asked me if it was possible to backup and restore a MySQL database from ColdFusion. There are multiple ways of doing this, but the basic answer is that you can do this very easily. MySQL ships with a set of utilities that ColdFusion can run via CFEXECUTE to perform various tasks.

So for example, to backup a database you can use the MySQL dump command:

view plain print about
1mysqldump --user=USER --password=PASSWORD dbname > filename

From ColdFusion this would look look like so (username, passwords, and database names changed to protect the innocent):

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

This creates a nice file that contains not only the SQL needed to create your database but all the data as well. You could then use a zip utility and move/mail/do whatever with the file.

Restoring is a bit trickier. You have to do different things based on if your database exists or not. If your database does exist, then the restore will overwrite the existing tables, but not remove tables that don't exist in the backup file. If this doesn't concern you, you can do it with this command:

view plain print about
1mysql --user=USER --password=PASSWORD dbname < filename

Now I had a lot of trouble getting this to run from CFEXECUTE. I believe because of the <. So I used a bat file instead that looked like so:

view plain print about
1"c:\program files\mysql\mysql server 4.1\bin\mysql.exe" --user=xxx --password=yyy somebackup < "c:\apache2\htdocs\testingzone\ao.sql"

I then ran the bat file from ColdFusion:

view plain print about
1<cfexecute name="#expandPath("./restore.bat")#" timeout="30" variable="result"/>

Obviously you could make the bat file a bit more dynamic instead of hard coding everything.

For more information, check the MySQL 5 doc on backup and restoring databases.

Would folks be interested in a MySQL CFC wrapper? You know - in my spare time.