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:

mysqldump --user=USER --password=PASSWORD dbname > filename

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

<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:

mysql --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:

"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:

<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.