Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Backing up MYSql DB using coldfusion

Posted on 2015-02-16
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
Question by:WestCoast_BC
  • 4
  • 3
LVL 77

Expert Comment

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......

Author Comment

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')
(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?
LVL 77

Expert Comment

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.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

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.
LVL 77

Expert Comment

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.

Author Comment

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
LVL 77

Accepted Solution

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..,......

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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