Twitter: raymondcamden


Address: Lafayette, LA, USA

MySQL administration via ColdFusion

12-07-2006 8,283 views Development, ColdFusion 21 Comments

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.

21 Comments

  • Commented on 12-07-2006 at 8:37 AM
    I would be very interested in just such a CFC.
  • Chris H #
    Commented on 12-07-2006 at 8:53 AM
    i'd also be interested! would be quite a useful CFC
  • Commented 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 #
    Commented on 12-07-2006 at 4:39 PM
    How would this work for us OSX users? Replace with "/usr/local/mysql/bin/mysqldump", right?
  • Commented 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 #
    Commented on 12-07-2006 at 5:30 PM
    Ray--that CFC would be wonderful!
  • NeilB #
    Commented 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 :)
  • Commented on 12-07-2006 at 7:47 PM
    Do you mean of only certain tables?
  • NeilB #
    Commented 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.
  • Commented 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 #
    Commented on 12-11-2006 at 4:52 PM
    Yes, very interested.
  • Commented on 06-20-2008 at 4:07 PM
    How would one go about doing this on a Mac?
  • Commented 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 #
    Commented on 01-25-2011 at 6:05 PM
    Was a CFC ever made for this?
  • Commented on 01-25-2011 at 7:53 PM
    Nope.
  • Commented 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!
  • Commented 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.
  • Commented 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.
  • Roger #
    Commented on 11-11-2013 at 2:54 AM
    I tried running this as such:

    <cfexecute name="C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump"
       arguments="--h#dbserver# --u#dbusername# --p#dbpassword# #dbname#"
    outputfile="#backupFolder##dbfilename#"
    timeout="30"/>

    and it creates a file in the destination folder, but it's always 0 bites in size.

    When I run the mysqldump from the command prompt as administrator with the credentials replaced, it works. Any idea why cfexecute might not be working? this is on my local dev machine.

    I've even tried to change the coldfusion service to run as a user with admin rights.
  • roger #
    Commented on 11-11-2013 at 3:14 AM
    nevermind, it works now. I had the double '--' when I needed just a single one.
  • Marios #
    Commented on 01-24-2014 at 12:35 AM
    Hi, the command works great in linux too...
    my problem is that the database has tables with greek content and the generated .sql file all these information is shown as ???????? .
    i already added in the arguments the param --default-character-set=utf8.
    The tables is in utf-8 format.

    Any suggestions ?


    Thank you

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty