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):
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
Now I had a lot of trouble getting this to run from CFEXECUTE. I believe because of the "c:program filesmysqlmysql server 4.1binmysql.exe" --user=xxx --password=yyy somebackup
I then ran the bat file from ColdFusion:
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.
Tag:
Digg
Reddit
Furl Bookmark Murdok:
Raymond Camden, ray@camdenfamily.com
http://ray.camdenfamily.com
Raymond Camden is Vice President of Technology for roundpeg, Inc. A long
time ColdFusion user, Raymond has worked on numerous ColdFusion books
and is the creator of many of the most popular ColdFusion community web
sites. He is an Adobe Community Expert, user group manager, and the
proud father of three little bundles of joy.
