|
Recent
Articles |
MySQL Optimization Tips
Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.
I should point out that using the...
Removing Nulls From A DataTable
The individual cells in a DataTable can have a null value in the form of System.DbNull.Value. If the DataTable is created by querying a database through ADO.NET, you can write the SQL statement in a way eliminates...
Compact An Access Database From .NET
Over time, a database becomes fragmented when it is used. This makes it grow in size and perform badly. Not the two qualities you want for your mission-critical data. Luckily, a lot of databases such as MS SQL Server...
Oracle Responds To Information Security Critics
Oracle the Database Company is taking time out on its corporate blog to address a number of critics on the security of Oracle Databases. Like any other application, the security of the application is only as good as how it was configured. It is very easy to take a default...
The What, Why, And How Of Database Cleansing
Firstly the "What" - Cleaning a database is done to: Remove duplicate records. Ensure your data is consistently formatted. Correct data that is obviously wrong e.g. wrong postcode for a known suburb.
Oracle & Red Hat's Symbiotic Relationship
There is a symbiotic relationship between Oracle & Red Hat now. Yes there was one before with Oracle's database business on Linux, but it's even more intertwined now. This relationship is similar to what...
Oracle After Enterprise Web 2.0 Blood
One of the sure signs that there is money to made in a technology sector is when the big sharks start gathering around spots where the little fish have been happily churning up some waves. The announcement that...
|
|
|
|
01.24.07
MySQL Administration Via ColdFusion
By
Raymond Camden
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 < 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:
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.
Comments
About the Author:
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.
|
