Elliott C. Back: In Aere Aedificare

Taking Paul’s great article How To: Bulletproof Server Backups with Amazon S3 a step farther here’s an easy way to automate your Wordpress database backups. First, follow all the steps to get ruby and the s3sync client installed. Once that is setup, create a text file somewhere with the databases you’re interested in. I called mine s3backup-db.txt:

[root s3sync]# cat s3backup-db.txt
db-one
db-one-user
db-one-pass
db-two
db-two-user
db-two-pass

Then you’ll need to make a folder for your backups, which I put in /home/s3backup/. The shell script that does the backups is as follows:

#!/bin/bash

BUCKET=your-bucket-here
BACKUP=/home/s3backup/
ROOT=/root/s3sync/
NOW=$(date +%m%d%y)

if [ $(($# % 3)) -ne 0 ]
then
echo “Wrong number of arguments!!”
exit
fi

i=0
while [ $# -gt 0 ]
do
DBNAME=$1; shift
DBUSER=$1; shift
DBPWD=$1; shift

echo “Backing up MySQL db ‘$DBNAME’ with ‘$DBUSER:$DBPWD’”
nice mysqldump -u $DBUSER -p$DBPWD -C -q $DBNAME | gzip -9 > $BACKUP$DBNAME.$NOW.sql.gz

cd $BACKUP
tar -r -f backup.$NOW.tar $DBNAME.$NOW.sql.gz
rm -f $DBNAME.$NOW.sql.gz
cd $ROOT

i=$(( $i + 3 ))
done

nice ruby ${ROOT}s3sync.rb -r –ssl –progress ${BACKUP} $BUCKET:
rm -f ${BACKUP}backup.$NOW.tar

This will dump a file called backup.040506.tar in the Amazon bucket you picked earlier containing the full database backups of the arguments you specified. You can run the script like this, and it will produce a little bit nicer output than Paul’s bare-bones backup:

[root s3sync]# cat s3backup-db.txt | xargs /root/s3sync/s3backup-db.sh
Backing up MySQL db ‘*****’ with ‘****:****’
Backing up MySQL db ‘*****’ with ‘****:****’
Update node backup.080507.tar

The next step is just to set it up with cron to run every night at midnight:

[root s3sync]# crontab -e
crontab: installing new crontab
[root s3sync]# crontab -l
0 0 * * * cat /root/s3sync/s3backup-db.txt | xargs /root/s3sync/s3backup-db.sh

If you want an easier way to backup all your databases than specifying some of them, just use your MySQL administrative password and username with mysqldump’s –all-databases parameter. However, on my host there are things like Plesk databases I’m not particularily interested in backing up, so this works better for me. Just make sure you make s3backup-db.txt u=rw so that no one but root can see those passwords:

-rw——- 1 root root 148 Aug 5 13:40 s3backup-db.txt

4 Responses to 'Easy Database Backup with Amazon S3'

1. Paul Stamatiou said:

   on August 5th, 2007 at 3:32 pm

   My script only handled one database backup and server files so I like your add-on for multiple db’s. Any reason you went with a txt file for db credentials rather than just hardcoding in the shell script, other than for ease of use?

2. Elliott Back said:

   on August 5th, 2007 at 3:40 pm

   It’s almost the same as your script, just for backing up a few DBs. Really, that *is* the only change and it’s just for ease of use.

3. Automatic wordpress backups to Amazon S3 | Brendan Nee said:

   on October 25th, 2007 at 2:31 am

   […] I found a variety of solutions to backing up files using S3, but none that exactly met my needs, so I decided to post my work here. […]

4. duivesteyn said:

   on July 6th, 2008 at 3:54 am

   I’ve written something similar at
   duivesteyn.net/2008/amazon-s3-backup-for-webserver-public_html-sql-bash/

   hope it helps someone

Your Thoughts Go Here:

Name (required)

Mail (will not be published) (required)

Website

Powered by WP Hashcash