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

5 Responses to “Easy Database Backup with Amazon S3”

1. Paul Stamatiou says:

   8/5/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?

   Reply
2. Elliott Back says:

   8/5/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.

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

   10/25/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. [...]

   Reply
4. duivesteyn says:

   7/6/2008 at 3:54 am

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

   hope it helps someone

   Reply
5. Eric Nagel says:

   10/29/2009 at 1:27 pm

   I wrote a PHP script to backup your htdocs & mysql databases to Amazon S3 or FTP. My Amazon S3 server backup script is available for free. Backing up my server now costs me about $1.50 / month.

   I hope someone else finds it useful!

   Reply