Solved

Backing up MYSql DB using coldfusion

Posted on 2015-02-16
7
142 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 78

Expert Comment

by:arnold
ID: 40613903
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
ID: 40614318
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 78

Expert Comment

by:arnold
ID: 40614366
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:WestCoast_BC
ID: 40614640
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 78

Expert Comment

by:arnold
ID: 40614665
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
ID: 40643007
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 78

Accepted Solution

by:
arnold earned 500 total points
ID: 40643047
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Creating and Managing Databases with phpMyAdmin in cPanel.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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