Posted: 14th January 2014

Author: Ric

Tagged: Tutorials

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 written by former QWeb employees are not necessarily an accurate indication of the current opinions of QWeb Ltd and the information provided in tutorials might be biased or subjective, or might become out of date.

Discuss this post

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 and you won't be added to any mailing list and your details won't be shared with any third party.