Solved

Backing up MYSql DB using coldfusion

Posted on 2015-02-16
7
117 Views
Last Modified: 2015-03-03
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
Comment
Question by:WestCoast_BC
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
 

Author Comment

by:WestCoast_BC
Comment Utility
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
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:WestCoast_BC
Comment Utility
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
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
 

Author Comment

by:WestCoast_BC
Comment Utility
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
 
LVL 76

Accepted Solution

by:
arnold earned 500 total points
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now