Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
WestCoast_BC
Asked:
WestCoast_BC
  • 4
  • 3
1 Solution
 
arnoldCommented:
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......
0
 
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')
UNION
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

Open in new window


And then to import the data I would do:
LOAD DATA INFILE "/tmp/orders.csv"
INTO TABLE orders
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

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?
0
 
arnoldCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
arnoldCommented:
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.
0
 
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
0
 
arnoldCommented:
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..,......
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now