Backup your MySQL database to Rackspace Cloud Files with Duplicity

I have just finished implementing Rackspace Cloud Files as the storage method for user uploaded product files on my Just1Registry online wedding registry service. I am very pleased with the results. Having the photos stored on a CDN has greatly improved page load time for my users and lessens the load on my servers.

Since the switch to Cloud Files worked so well, I thought that it would be great to use Cloud Files to store regular MySQL database dumps of my production server. I use Rackspace Cloud Servers for most of my sites, and yes, I know they have full image backups available (which I do use), but I wanted another layer of backup. More precisely, a simpler one. The only data on my servers that needs protecting is the database. The code is safe in my Springloops Subversion repository, and as I said previously, user uploaded product photos are tucked inside Cloud Files. So it seems a waste to have to restore my server to a new server instance just to grab a copy of my database.

My quest for Rackspace Cloud Files based backup solutions started where all my quests start . . . I asked Google.

I came across this article which put me on the right track, but it wasn’t the full solution I was looking for. After a little more research (specifically learning more about Duplicity), I put my own bash script together and was dumping my database to Cloud Files in no time.

The following is a step by step procedure to set up a script on Ubuntu 9.10 that will dump a MySQL database and upload the dump to Rackspace Cloud Files.

Things you’ll need:

  1. Rackspace has released source code for their API in multiple languages. In this instance we need the Python libraries. You can download python-cloudfiles here from github or use wget and the link I provide later on.
  2. Duplicity makes our job so much easier – it’s a beautifully simple tool. You could download it here but I’d recommend doing an apt-get.
  3. Get your Rackspace Cloud API key from your Rackspace Cloud account. If you don’t have an account . . .  get one!
  4. If you’re backing up a MySQL database you might want to make sure MySQL is installed on your system 🙂

The first step is to log in to your server. You will need root access for most of the following steps.
Next install duplicity.

sudo apt-get install duplicity

Next download python-cloudfiles from github. Extract the files and run setup.

wget http://github.com/rackspace/python-cloudfiles/tarball/1.7.0
tar -xzf rackspace-python-cloudfiles-8a1e850.tar.gz
cd rackspace-python-cloudfiles-8a1e850
python setup.py install

At this point, we are ready to create the script that will perform the MySQL dump and Cloud Files upload. You will need to have created a container in your Cloud Files account and you will also need your Cloud Files API Key and username.
Create the following script. Note the Cloud Files portion of this script was taken from here.

sudo nano /root/backup-mysql.sh

Contents of backup-mysql.sh

#!/bin/bash

# name of database to dump and username and password with access to that database
MYSQL_DB="mydatabase"
MYSQL_USER="username"
MYSQL_PASS="password"

#create output file name with database name, date and time
OUTPUT_PATH="/backup/mysql"
NOW=$(date +"%Y-%m-%d")
FILE=${MYSQL_DB}.$NOW-$(date +"%H-%M-%S").sql.gz

CLOUDFILES_CONTAINER="mysql-backup"
export CLOUDFILES_USERNAME=Your Cloud Files User name
export CLOUDFILES_APIKEY=API_KEY_YOU_GOT
export PASSPHRASE=The Passphrase for your encrypted backup

# dump the database and gzip it
mysqldump ${MYSQL_DB} -u ${MYSQL_USER} -p${MYSQL_PASS} | gzip -9 > ${OUTPUT_PATH}/${FILE}

duplicity ${OUTPUT_PATH} cf+http://${CLOUDFILES_CONTAINER}

Give script appropriate permissions:

sudo chmod +x /root/backup-mysql.sh

Create a cron job

sudo nano /etc/crontab

Add this line to /etc/crontab

15 * * * * root /root/backup-mysql.sh >/dev/null 2>&1

Since I already have daily backups I have set up my script to run every hour. This may be a problem in the future but seems to be fine for now. Note: The reason I am sending the output of the backup script to /dev/null is because even on successful backups Duplicity generates output which is automatically emailed to me when the job is run. I dislike getting hourly emails.
The script could use some work but it does its job at the moment.

To restore files from Cloud Files use the following script:

#!/bin/bash

# path to restore files to
OUTPUT_PATH="/backup/mysql"

CLOUDFILES_CONTAINER="mysql-backup"
export CLOUDFILES_USERNAME=Your Cloud Files User name
export CLOUDFILES_APIKEY=API_KEY_YOU_GOT
export PASSPHRASE=The Passphrase for your encrypted backup

duplicity cf+http://${CLOUDFILES_CONTAINER} ${OUTPUT_PATH}

The duplicity command has many powerful options, you can view duplicity man page here.

12 comments

  1. Thanks! Nice tutorial. I was able to get this working on CentOS 5.2 without issue. First as you outlined for mysql dump backup, and second for a folder of customer generated images that needs to be backed up. I run these daily as well as a rackspace scheduled image 1x per week.

    Python 2.4 was already installed, so I did

    “yum install duplicity”

    to install that with all its dependencies.

    Then I grabbed the latest python-cloudfiles from the downloads page here (which is now 1.7.2):
    http://github.com/rackspace/python-cloudfiles/downloads

    and ran the commands you specified.

    Thanks again for sharing.

  2. Great tutorial, thanks alot for sharing!!!!

    I have almost got this working on Debian Lenny. I installed python 1.7.2 (latest version). instead of your snippet of code I had to use

    wget http://github.com/rackspace/python-cloudfiles/tarball/1.7.2
    tar -xzf 1.7.2
    cd rackspace-python-cloudfiles-ed7ae1e
    python setup.py install

    The duplicity version for Debian is 0.4.11

    It didn’t work straight of, so I used “bash -x /root/backup-mysql.sh” to debug the shell script.

    It thrown up this error: –

    + duplicity /backup/mysql cf+http://mysql-backup
    Unknown scheme ‘cf+http’

    I can’t seem to find a work around for this. I believe the issue is with the version of Duplicity.

    Any suggestions would be greatly appreciated.

  3. If you are using the London datacentre, consts.py file needs to be edited. It is located at => /usr/lib/python2.5/site-packages/cloudfiles/consts.py

    Change to this => default_authurl = ‘https://lon.auth.api.rackspacecloud.com/v1.0’

  4. Some notes..

    1. In case you use Centos/RedHat -> get a python-cloudfiles via yum.

    yum install python-cloudfiles

    2. Bit better solution for London datacentre users: instead of modifying the consts.py just add:

    export CLOUDFILES_AUTHURL=”https://lon.auth.api.rackspacecloud.com/v1.0″

    Below CLOUDFILES_APIKEY or similar.

  5. I could not install Duplicity on my Centos because of all the dependencies issues so I wrote a python of my own

    #!/usr/bin/env python
    #Import the CloudFiles Python API ensure this is located in your lib directory for Python.
    import cloudfiles
    import time
    import datetime
    import commands

    #DB info
    MYSQL_DB=’XX’
    MYSQL_USER=’YY’
    MYSQL_PASS=’****’

    #output info
    OUTPUT_PATH=’/a/’
    NOW = datetime.datetime.now().strftime(“%Y-%m-%d_%H:%M:%S”)
    FILE= MYSQL_DB+’.’+ NOW + ‘.sql’

    #Lets Connect to CloudFiles. and get the container
    print “Connecting to cloud files”
    conn = cloudfiles.get_connection(‘——‘,’***********************************************’)
    cont = conn.get_container(‘mysql-backup’)

    #Dump DB
    print “Dumping DB file”
    commands.getstatusoutput(‘mysqldump -u—— -p************** –databases ta > ‘ + OUTPUT_PATH + FILE)

    #upload file
    print “Uploading file to cloud files”
    my_db_object = cont.create_object(FILE)
    my_db_object.load_from_filename(OUTPUT_PATH + FILE)

    #deleting dump temporery file
    #commands.getstatusoutput(‘rm -f ‘ + OUTPUT_PATH + FILE)

    print “Backup process done successfully”

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.