MySQL administration via 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:
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.
Comments
I would be very interested in just such a CFC.
i'd also be interested! would be quite a useful CFC
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.cfmI 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.
How would this work for us OSX users? Replace with "/usr/local/mysql/bin/mysqldump", right?
Yep. My plan for the CFC is to make the init method ask for the root folder.
Ray--that CFC would be wonderful!
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 :)
Do you mean of only certain tables?
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.
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.
Yes, very interested.
How would one go about doing this on a Mac?
The Mac's mysql files work the same. Of course, the path is different and you use mysqldump, not mysqldump.exe.