Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Schedule MySQL Database Backups to Amazon S3

DZone's Guide to

How to Schedule MySQL Database Backups to Amazon S3

· Cloud Zone
Free Resource

Learn how our document data model can map directly to how you program your app, and native database features like secondary indexes, geospatial and text search give you full access to your data. Brought to you in partnership with MongoDB.

I've recently launched a site using MySQL as a database, and wanted to be able to schedule database backups to Amazon S3

I did a bit of searching and found Ray Camden's blog post from way back in 2006 about using cfexecute to create the MySQL backups. I took what Ray had done and added some new functionality so that each database is backed up into a seperate file, zipped and then uploaded to Amazon S3.

Here is by final script, which is called via a scheduled task.

<!--- MySQL backup details --->
<cfset path = "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump">
<cfset zipdir = "C:\backup\mysql\">
<cfset backupdir = zipdir & "databases\">
<cfset username = "root">
<cfset password = "letmein">

<cfset datetime = DateFormat(Now(), "YYYYMMDD" ) & TimeFormat(Now(), "HHMMSS" )>
<cfset zipfilename = datetime & "_dbbackup.zip">

<!--- get MySQL databases --->
<cfquery name="qDatabases" username="#username#" password="#password#">
  SHOW DATABASES;
</cfquery>

<!--- loop and backup each database --->
<cfloop query="qDatabases">
  <cfset filename = "#datetime#_#qDatabases.Database#.sql">
  <cfexecute name='"#path#"'
    arguments=' --user=#username# --password=#password# --databases --log-error="#zipdir#" #qDatabases.Database#'
    outputfile='#backupdir##filename#'
    >
  </cfexecute>
  <cfoutput>backup: "#backupdir##filename#" successful<br></cfoutput>
</cfloop>

<!--- zip backups --->
<cfzip action="zip" source="#backupdir#" 
  file="#zipdir##zipfilename#" 
  overwrite="true">
  
<!--- upload to Amazon S3 --->
<cffile action="copy" 
  source="#zipdir##zipfilename#" 
  destination="s3://MyBucket/backup/">

My Amazon S3 credentials are defined in Application.cfc like so:

component
{
  this.name = Hash( getDirectoryFromPath( getCurrentTemplatePath() ) );
  // S3 details
  this.s3.accessKeyId = "A1B1C1D1E1F1G1H1;
  this.s3.awsSecretKey = "a1b2c3d4e5/A1B1C1D1E1F1G1H/1A2B";
}

 

Discover when your data grows or your application performance demands increase, MongoDB Atlas allows you to scale out your deployment with an automated sharding process that ensures zero application downtime. Brought to you in partnership with MongoDB.

Topics:

Published at DZone with permission of John Whish, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}