Recycled Electrons

In a project I've been working on, I wanted a way to remotely backup a mySQL database on to Amazon S3. Because the site was hosted on a shared server, I also didn't have access to the command line.

There are probably dozens of different ways to go about doing this, but the solution I arrived at is incredibly simple and easily lends itself to a quick restore script.

First, we need to get a list of tables in the database:

<!--- Get data  --->
<cfquery name="qryTables" result="stcTables">
    SHOW TABLES
</cfquery>

We will then get all the data from each of those tables and create a WDDX packet out of each (which is a XML representation of ColdFusion query object). Each of these packets is written in to a text file.

<!--- Pull down data and export each table's data into WDDX file--->
<cfset variables.strTempDirectory = "#expandPath("./")#temp\" />
<cfloop query="qryTables">
    <cfset variables.strTableName = qryTables[listFirst(stcTables.ColumnList)] />
    <cfquery name="qryData">
        SELECT * FROM #variables.strTableName#
    </cfquery>
    <cfwddx
        action="cfml2wddx"
        input="#qryData#"
        output="variables.strData" />
    <cffile
        action="write"
        file="#variables.strTempDirectory##variables.strTableName#.txt"
        output="#variables.strData#"
        charset="utf-8" />
</cfloop>

Finally, we'll take all those text files and zip them into a single file, which will be sent to Amazon S3 (thanks to the CF 9.01 enhancement).

<!--- Package WDDX files into a ZIP file --->
<cfset variables.strBackupZip = "database-backup-#dateFormat(now(),"yyyy-mm-dd")#.zip" />
<cfzip
    action="zip"
    file="#variables.strTempDirectory##variables.strBackupZip#"
    source="#variables.strTempDirectory#"
    filter="*.txt" />

<!--- Send to Amazon S3 --->
<cffile
    action="copy"
    source="#variables.strTempDirectory##variables.strBackupZip#"
    destination="s3://your-bucket-name/database/#variables.strBackupZip#"/>