Using CFDBINFO and CFZIP for quick database backups
All major database products have tools to let you backup their databases. MySQL makes it super simple with their command line tools. But what if you want to do it with ColdFusion? Doesn't everyone want to do everything with ColdFusion? I know I do! So let's look at a quick example.
My database backup code will work like so:
- Get a list of tables from a datasource.
- Select all rows from the table.
- Convert the query into WDDX.
- Zip the giant XML string and store the result.
The code for all this is incredibly simple:
<cfset datasource="blogdev">
<cfdbinfo datasource="#datasource#" name="tables" type="tables">
This code simply uses the new cfdbinfo tag to query the tables from the datasource.
<!--- One struct to rule them all... --->
<cfset data = structNew()>
I'm going to store all my queries in one struct.
<cfloop query="tables">
<!--- grab all data from table --->
<cfquery name="getData" datasource="#datasource#">
select *
from #table_name#
</cfquery>
<cfset data[table_name] = getData>
</cfloop>
Then I loop over each table and select *. Notice I store the query into the struct. By the way - the cfdbinfo tag also lets you get the columns from a database table. But since this is a "quickie" script, I don't mind using the select *.
<!--- Now serialize into one ginormous string --->
<cfwddx action="cfml2wddx" input="#data#" output="packet">
Then we convert the structure into one XML packet.
<!--- file to store zip --->
<cfset zfile = expandPath("./data.zip")>
<!--- Now zip this baby up --->
<cfzip action="zip" file="#zfile#" overwrite="true">
<cfzipparam content="#packet#" entrypath="data.packet">
</cfzip>
Next I store the string into a zip using the new cfzip and cfzipparam tags. Notice how I feed the string data to the zip using cfzipparam. I don't have to store the text into a temporary file.
<cfoutput>
I retrieved #tables.recordCount# tables from datasource #datasource# and saved it to #zfile#.
</cfoutput>
The last thing I do is output a simple result message so you know how much data was backed up. Here is the complete source in one listing:
<cfset datasource="blogdev">
<cfdbinfo datasource="#datasource#" name="tables" type="tables">
<!--- One struct to rule them all... --->
<cfset data = structNew()>
<cfloop query="tables">
<!--- grab all data from table --->
<cfquery name="getData" datasource="#datasource#">
select *
from #table_name#
</cfquery>
<cfset data[table_name] = getData>
</cfloop>
<!--- Now serialize into one ginormous string --->
<cfwddx action="cfml2wddx" input="#data#" output="packet">
<!--- file to store zip --->
<cfset zfile = expandPath("./data.zip")>
<!--- Now zip this baby up --->
<cfzip action="zip" file="#zfile#" overwrite="true">
<cfzipparam content="#packet#" entrypath="data.packet">
</cfzip>
<cfoutput>
I retrieved #tables.recordCount# tables from datasource #datasource# and saved it to #zfile#.
</cfoutput>
Comments
I was thinking about writing something similar to this, only generating the create and insert statements required to extract a DB in a form that could easily be moved to a different server.
Am I the only one? (I know u are on a mac too Raymond)
Um, I'd thin it would probably time out. You could add sanity checks in there - checking record count, etc.
<cfset badlist = "foo,moo">
Then in your cfloop, you just do:
<cfif not listfind(badlist, table_name)>
Ie, if you don't find the table in the bad list, carry on.
IMO, a "skip-list" seems like a great way to compromise between incremental back-ups, and full back-ups for tables that never change.
Thanks Ray!
Now that said (and it works great), is it possible to to save that backup data into a file other than a wddx packet?
say like a text file perhaps that is zipped up instead?
Just Curious...
i m gonna read a bit more about json and wddx packets :)
good one
As an aside for bigger DB's what would you or anyone else recommend as a way of maybe splitting the file into "emailable" chunks?
a) First, you want to convert the WDDX packet back into native data. That is as simple as running cfwddx again, but with action=wddx2cfml.
b) This gives us a structure with table names as keys, and queries as data. For each key you would:
c) Loop over the query and do an insert of the data back into the table. Remember that CF gives you functions to inspect queries (columnlist) and get the individual cells, so this is something you can handle easily enough. :)