Published Tuesday 14th January 2014

Blog home

MySQL database backup script for Rackspace

We love Rackspace. They host our cloud of servers, their technicians are excellent, and they provide an additional set of amazing services to us. One of these services is a 'cloud backup agent' which we've configured to backup specific folders from our servers into the cloud. The costs are reasonable, the administration interface is intuitive, and the technical design gives us peace of mind; we know damned well that our backups aren't going to be lost.

There is one major downside to this technology though, it can only backup physical files. As any database administrator will tell you, copying a database by copying and pasting the files really isn't a good idea. Chances are that data is being written when your copy operation is performed so the resulting backup is very likely to corrupt.

The answer for us was to create static dumps of our databases via the mysqldump commandline tool that MySQL provides, and then configure the backup agent to copy these new files over instead of the raw database files. This can be achieved with a cron script and we found that a cool lady named Sonia Hamilton had already written one.

We wanted to modify her script to work on Plesk servers, rotate two instances of each dump and, more importantly, work without the need for any passwords to be contained within the script itself. The resulting code follows, and it's been running successfully for a number of months now. Simply save this to a .sh file and create a daily CRON job to run it before your scheduled backups are performed.

#!/bin/bash

# Create the directory /home/database-backups
# Then run this script on a daily cron to dump all databases
# Should work on any Plesk server as-is.

# Original script courtesy of Sonia Hamilton
# http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/

# Modified by QWeb Ltd to:
# - work more securely on Plesk servers
# - keep existing backups for 2 days
# http://www.qweb.co.uk/

# Plesk renames root to admin
USER="admin"

# Plesk stores the admin password here
PASSWORD="`cat /etc/psa/.psa.shadow`"

# mkdir this folder if it doesn't yet exist
OUTPUTDIR="/home/database-backups"

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# Clean up older backups (save space)
rm "$OUTPUTDIR/*bak2" > /dev/null 2>&1

# Get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# Dump each database in turn
for db in $databases; do
    # maintain backups for 2 days to prevent complete loss if server dies during backup
    mv "$OUTPUTDIR/$db.bak" "$OUTPUTDIR/$db.bak2"

    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
     --databases $db > "$OUTPUTDIR/$db.bak"
done

Blog posts are written by individuals and do not necessarily depict the opinions or beliefs of QWeb Ltd or its current employees. Any information provided here might be biased or subjective, and might become out of date.

Discuss this post

Nobody has commented yet.

Leave a comment

Your email address is used to notify you of new comments to this thread, and also to pull your Gravatar image. Your name, email address, and message are stored as encrypted text. You won't be added to any mailing list, and your details won't be shared with any third party.

Name: Email address:

Message:

Repeat the text :

This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.