Raymond Camden's Blog Rss

MySQL administration via ColdFusion

18

Posted in Development, ColdFusion | Posted on 12-07-2006 | 5,206 views

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.

Comments

[Add Comment] [Subscribe to Comments]

Michael Brennan-White said on 12-07-2006 at 8:37 AM #
I would be very interested in just such a CFC.
Chris H said on 12-07-2006 at 8:53 AM #
i'd also be interested! would be quite a useful CFC
Brian Rinaldi said on 12-07-2006 at 9:04 AM #
FYI, I have this listed on the free but not open source section of the open source list, but there is a CFMyAdmin application at http://www.cfmyadmin.com/index.cfm

I have never personally checked it out, but it may also have some of these features....just figured I would throw it out there. If anyone does try it, I would be interested in hearing how it goes.
Lola Lee Beno said on 12-07-2006 at 4:39 PM #
How would this work for us OSX users? Replace with "/usr/local/mysql/bin/mysqldump", right?
Raymond Camden said on 12-07-2006 at 4:45 PM #
Yep. My plan for the CFC is to make the init method ask for the root folder.
Lance said on 12-07-2006 at 5:30 PM #
Ray--that CFC would be wonderful!
NeilB said on 12-07-2006 at 7:22 PM #
Ray,

Know what would be even *cooler*? A CFC that allowed you to do a *selective* restore Now that I would actually pay good money for :)
Raymond Camden said on 12-07-2006 at 7:47 PM #
Do you mean of only certain tables?
NeilB said on 12-07-2006 at 7:50 PM #
Yes. For example, if I have 500 tables, and for whatever reason I hosed one of them (and I have, believe me), I would love to be able to restore just that one table without killing the others.
Raymond Camden said on 12-07-2006 at 7:55 PM #
Neil - I'll have to see if mysql supports exporting just a table. Otherwise - it is going to be hard to import _just_ the table. I mean sure you can string parse - but I hate to rely on string parsing. On the other hand - MySQL constantly amazes me.
Jeff Smallwood said on 12-11-2006 at 4:52 PM #
Yes, very interested.
Brad Fraser said on 06-20-2008 at 4:07 PM #
How would one go about doing this on a Mac?
Raymond Camden said on 06-21-2008 at 9:16 AM #
The Mac's mysql files work the same. Of course, the path is different and you use mysqldump, not mysqldump.exe.
Brad said on 01-25-2011 at 6:05 PM #
Was a CFC ever made for this?
Raymond Camden said on 01-25-2011 at 7:53 PM #
Nope.
Bryan Rice said on 03-10-2011 at 8:52 AM #
Probably a dumb question, but can cfexecute run mysqldump if it is on another machine? In other words, we have our MySQL server and ColdFusion instances running on different machines. Would the MySQL server need to have CF running locally in order for CF to cfexecute mysqldump or is there another way?

Thanks as always!
Raymond Camden said on 03-10-2011 at 8:57 AM #
You can't cfexecute a program on another server. But - all the MySQL command line programs (afaik), allow you to specify a server to run against. So if the tools were installed on the CF machine but MySQL was some other server, then it should be possible.
Bryan Rice said on 03-10-2011 at 9:12 AM #
Aha! Sweet. Thanks so much. I will look into this and report back on how it goes, I may even take a go at creating a cfc.

[Add Comment] [Subscribe to Comments]