This tutorial will show you how to quickly and easily backup and restore a MySQL database using just ColdFusion scripts and avoiding having to use CFEXECUTE which is normally disallowed on shared hosting platforms.

ColdFusion 8 has some handy tags that will provide database detail but what if you're running CF7? Normally you would have to resort to running the mysqldump command but this is restrictive if you wanted to provide your users with a method to backup their databases.

The script is in two parts, the backup file will convert a MySQL database into a WDDX object and zip it up for download by the user. The restore file will then unzip the file and restore the database all in one go. So lets crack on and take a look at the backup script first...

Database Backup

First create a file name dbbackup.cfm or whatever you wish. It's better to have these scripts in a sub-folder as we will also be uploading the restore file as well as performing some zipping...

You are about to create a database backup. This system will create an XML export file, zip it up on the server and then provide you with a link to download the file

It will overwrite an existing database backup file held on the server. The backup may take some time to complete and you will be warned if it could not continue so please be patient.

To continue, click the "Backup Database" button below:

Backup Progress

SELECT database() AS the_database_name

SHOW tables FROM #dbname#

Preparing backup...

Exporting table structure: #tblname#

SHOW CREATE TABLE #dbname#.#tblname#

DESCRIBE #dbname#.#tblname#

SELECT * FROM #tblname# Exporting data for: #tblname#

Creating export file...

Zipping up export file...

zipper = createObject("component", "zip"); zipper.newZip("#exportzipfile#"); zipper.addFile("#exportfile#"); zipper.createZip();

Database Backup Complete!

Click here to download the export file

The export has failed with one or more errors:

#cfcatch.message#

So now we have a complete backup of the database in a handy zip file so users can download it to their own computer for safe keeping. Next we need to provide a restore function so users can easily restore a backup file...

Database Restore

The database restore file will simply upload our zip file, unzip it on the server, extract the WDDX packet and restore the database. Create a file name dbrestore.cfm or whatever you like and enter the following:

You are about to restore a database backup file.

It will overwrite an existing database so please be sure you wish to continue. The restore may take some time to complete and you will be warned if it could not continue so please be patient.

To continue, click the "browse" button to locate the backup file on your computer and the "Restore Database" button to restore the database:

Click here to try again

">

Uploading restore file...

Click here to try again

"> Click here to try again

">

Unzipping restore file...

zipper = createObject("component", "zip"); zipper.extractZip("#zipfile#","#uploaddir#"); Click here to try again

">

Checking import validation...

Restoring database #qimport.backupdesc#...

Click here to try again

">

Beginning database restore actions...

Restoring #index#
Dropping table #index#...
DROP TABLE IF EXISTS `#index#` Table #index# dropped...
Click here to try again

"> Restoring Table structure

#QSCHEMA["CREATE TABLE"][1]# #PreserveSingleQuotes(createqry)# Table #index# restored...
Click here to try again

"> Inserting Database Data...

INSERT INTO `#index#` ( #ValueList(qDesc.field)# ) VALUES (

,

) Table data restored... Click here to try again

">

Restore Complete!

The restore has failed with one or more errors:

#cfcatch.message#

And that's it! We have now restored the complete database! The scripts above use a couple of extra functions that can be saved as a CFC but are required by the script.

DB Manipulation Functions

The purpose of this function is to look at the data type being inserted and ensure we use the correct ColdFusion cfqueryparam tag for inserting it onto the database.

ZIP functions

These functions just allow to create zip files on the server and also unzip files

init(); this.initialized = false; this.entries = arrayNew(1); this.zipFile = '; this.zipDirectory = '; this.compressionLevel = 9; this.initialized = true; this.entries = arrayNew(1); this.zipFile = getFileFromPath(zipFileAndPath); this.zipDirectory = stripPath(zipFileAndPath); if(listLen(pathAndFile, '\') gt 0){ return listDeleteAt(pathAndFile,listLen(pathAndFile, '\'), '\') & '\'; }else{ return listDeleteAt(pathAndFile,listLen(pathAndFile, '/'), '/') & '/'; } var e = arrayLen(this.entries) + 1; var newEntry = structNew(); var sucess = false; if(fileExists(fileAndPathToAdd)){ newEntry.sourceName = getFileFromPath(fileAndPathToAdd); newEntry.sourcePath = stripPath(fileAndPathToAdd); newEntry.storePath = pathToStore; newEntry.storeName = fileNameToStore; this.entries[e] = newEntry; sucess = true; } return sucess; outputPath = this.zipDirectory & this.zipFile; fileOut = createObject("java","java.io.FileOutputStream"); fileOut.init(outputPath); zipOut = createObject("java", "java.util.zip.ZipOutputStream"); zipOut.init(fileOut); byteArray = repeatString(" ", 1000).getBytes(); input = '; zipOut.setLevel(this.compressionLevel); for(i = 1; i lte arrayLen(this.entries); i = i + 1){ if(fileExists(this.entries[i].sourcePath & this.entries[i].sourceName)){ fio = createObject("java","java.io.File").init(this.entries[i].sourcePath & this.entries[i].sourceName); fileInput = createObject("java","java.io.FileInputStream").init(fio.getPath()); zipEntry = createObject("java","java.util.zip.ZipEntry").init(this.entries[i].storePath & this.entries[i].storeName); zipOut.putNextEntry(zipEntry); l = fileInput.read(byteArray); while (l GT 0) { zipOut.write(byteArray, 0, l); l = fileInput.read(byteArray); } zipOut.closeEntry(); fileInput.close(); }else{ writeOutput('FILE DOES NOT EXIST: ' & this.entries[i].sourcePath & this.entries[i].sourceName); } } zipOut.close(); inPath = zipFileAndPath; inputIO = createObject("java","java.io.FileInputStream").init(inPath); zipInput = createObject("java", "java.util.zip.ZipInputStream"); zipInput.init(inputIO); zipEntry = createObject("java","java.util.zip.ZipEntry").init("."); loopcount = 1; while(true){ zipEntry = zipInput.getNextEntry(); if(not isDefined("zipEntry")){ break; } byteArray = repeatString(" ", 1000).getBytes(); outPath = extractPath; outFile = zipEntry.getName(); outFileAndPath = outPath & outFile; fileio = createObject("java", "java.io.File").init(stripPath(outFileAndPath)); fileio.mkdirs(); output = createObject("java","java.io.FileOutputStream").init(outFileAndPath); n = zipInput.read(byteArray); while(n gte 0){ output.write(byteArray, 0, n); n = zipInput.read(byteArray); } output.close(); zipInput.closeEntry(); } zipInput.close();

So now you have a complete method for allowing users to backup and restore their MySQL databases without the need for CFEXECUTE!

Author: Phil Williams
http://www.openmindcommerce.co.uk