Backing up MYSql DB using coldfusion

I have a Coldfusion site that uses a MYSql database. I would like to create a function that will allow the user to backup and restore their database. Are there any tools that do this?

If I need to create something myself do you recommend that I save the data to an XML file, to a CSV file, or to some other format?

My site is on a shared server so I cannot use CFExecute

Thank you for your help
Who is Participating?

Improve company productivity with a Business Account.Sign Up

arnoldConnect With a Mentor Commented:
You should however, make sure to test the backup used by your host regularly to be familiar with what is involved.
Errors are often made when in a rush, a restore is triggered affecting more than was necessary leading to data loss.
inquiring what options are available i.e. having a replication to or having the mysql setup with a point in time restore option (including binary log) full db backup, + transactional records between the full/differential/incremental backups..,......
Does the user already have this option using phpmyadmin?
What options do you want to make available?
you can mimic what mysqldump but you might not have thought this through.
What is the destination of the backup? i.e. do you want it to be a file download?

you would have to query, show create database database name. and the same for each table. Then when getting the data out of the table, you have to convert it into insert into <table name> .....

the restore part will depend on how you handle the backup part.
The issue with restore, if you export the drop database directive, the restore part .....

I would think you already have a backup of mysql setup just in case......
WestCoast_BCAuthor Commented:
The backup function has to be done from within the site so phpmyadmin is not an option.

The destination of the backup will be a directory on the server.

I read on one site that I could dump the contents of each table into its own file as a CSV. To reload it I could use LOAD INI command.

I haven't tried this yet but I was considering to do the following to dump each table to a csv file by doing the following:
(SELECT 'Order Number','Order Date','Status')
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE '/tmp/orders.csv'

Open in new window

And then to import the data I would do:
LOAD DATA INFILE "/tmp/orders.csv"

Open in new window

Is this a solution that will work using Coldfusion? Is this a reasonable solution? Is there a better way to do this?
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

You could do that, note /tmp on a unix/linux is emptied on reboot.
The issue with allowing a user to backup to the local drive, would mean that your code must verify that there is enough space on the destination to which you are baking up.

The other risk dealing with backing up individual tables deals with the design of the database foreign keys, constraints, etc. could be royally screwed up....

The issue I see is that in your example the data you are loading is in effect in addition to the existing data there versus a replacement as a backup/restore would be, i.e. the data in orders will first need to be deleted, and then the backup data loaded.

there are many complexities that have to be evaluated/considered when trying to add functionality that is often handled externally from the application that uses the data.

index, constraints, foreign keys, etc. would be affected by a change to an individual table.

Even running the process sequentially could lead to loss of data integrity.
WestCoast_BCAuthor Commented:
My plan would be to delete the contents of all of the tables before restoring the data so that after restoring the database should be in the same state as when the restore data was created.

The above code is simply an example, I am not planning on using the tmp directory.
I understand/realise that.
In a specifically structured db/table the restore has to follow those rules.
I.e table 2 depends on tables 1 and 4, that depends on table 3, etc.  if you leave constraints/triggers etc. ......

Given this type of control/access to a user will lead to undesired results.
Using external backup with binary logging allows you the admin, a more granular control that a user may make an error and would restore to the prior backup wiping a significant amount of data versus what the other backup scheme makes available. Then the issue you hoped to resolve will be coming back and really biting you.

Or are you also contemplating full, incremental, differential types of backup?

IMHO the risk is not comparable to the reward (you might actually get more calls not less)
Relying on a user to handle the backups also not so good. As you might know, the user will only be concerned with the backup after something goes wrong.
WestCoast_BCAuthor Commented:
It is clear to me that it does not make sense to create a function to backup my database. Since my host backs up everyday I will have to rely on their backup if I ever need to restore my database
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.