Automate database backups with Amazon S3
Introduction
I have a number of websites hosting in a VPS (Ubuntu Linux 8.04). Most of them are powered by WordPress. Most of the files in /var/www/ will not change often. So, the important things I need to backup on a weekly basis are databases. The following is how I automated the database backups to Amazon S3.
The software I used include mysqldump and s3sync (a Ruby program for interacting with Amazon S3 over a command line). You should already have an Amazon S3 account if you want to following the following steps.
Step 1: Install Ruby and s3sync
Run the following commands using SSH:
apt-get install ruby libopenssl-ruby wget http://s3.amazonaws.com/ServEdge_pub/s3sync/s3sync.tar.gz tar xvzf s3sync.tar.gz rm s3sync.tar.gz cd s3sync mkdir certs cd certs wget http://mirbsd.mirsolutions.de/cvs.cgi/~checkout~/src/etc/ssl.certs.shar sh ssl.certs.shar cd .. mkdir s3backup
When you successfully complete this step, you should have Ruby and s3sync installed. Check this with ‘ruby -v’ and ‘ls -ls’ commands.
Step 2: Give s3sync your S3 access identifiers.
In the s3sync folder, create s3config.yml with the following content.
aws_access_key_id: aaa aws_secret_access_key: bbb ssl_cert_dir: /root/s3sync/certs
Replace ‘aaa’ with your S3 Access Key ID and ‘bbb’ with S3 Secret Access Key. You may need to modify the directory for SSL certs if you install s3sync in other location.
You may also need to update s3config.rb by modifying the confpath as follows. It adds “./” to tell that the configuration file is in the current directory.
confpath = ["./", "#{ENV['S3CONF']}", "#{ENV['HOME']}/.s3conf", "/etc/s3conf"]
Step 3: Backup MySQL databases
In this step, you can choose either 1) backup all databasea at once, or 2) backup individual database separately. To backup all database, you need to specify the ‘all-databases’ option in the mysqldump command. I choose to backup each database separately, as it is more flexible when I need to restore a database for some reasons.
Run the following command to get a list of databases in the server:
mysql -u root -h localhost -pYourPassword -Bse 'show databases'
Run the following commands to backup databases you specified:
cd s3sync/s3backup mysqldump --opt -u root -pYourPassword myblog | gzip -9 > myblog_backup$(date +_%d%b%Y).sql.gz
Replace ‘YourPassword’ with your root password, and ‘myblog’ with the database you want to backup. Repeat the second command for each other database.
Step 4: Send the backups to the bucket in your Amazon S3 account:
Run the following commands:
cd .. ruby s3sync.rb -r ––ssl s3backup/ your_bucket_in_s3: cd s3backup rm -f *
Replace ‘your_bucket_in_s3′ with the bucket you created in S3.
Step 5: Make the shell script to automate
Steps 3 and 4 are for testing purpose. If everything goes right, you can automate the job by creating a shell script and adding a cron job.
Go into the ~/s3sync folder, and create the script with the content similar to the following:
#!/bin/bash # directory structure: # ~/s3sync has scripts # ~/s3sync/s3backup is a folder for temp backup files NOW=$(date +_%d%b%Y) BUCKET=your_bucket_in_s3 cd ~/ cd s3sync/s3backup mysqldump --opt -u root -pYourPassword database01 | gzip -9 > database01_backup$NOW.sql.gz mysqldump --opt -u root -pYourPassword database02 | gzip -9 > database02_backup$NOW.sql.gz mysqldump --opt -u root -pYourPassword database03 | gzip -9 > database03_backup$NOW.sql.gz mysqldump --opt -u root -pYourPassword database04 | gzip -9 > database04_backup$NOW.sql.gz cd .. ruby s3sync.rb -r ––ssl s3backup/ $BUCKET: cd s3backup rm -f *
Replace ‘your_bucket_in_s3′ with the bucket you created in S3. Replace ‘database01′, ‘database02′, etc with the databases you want to backup. Replace ‘YourPassword’ with your database password.
Step 6: Test the actual backup script
Run the following commands:
cd ~/s3sync sudo chmod +x s3backup.sh sudo ./s3backup.sh
Step 7: Implement the automation
Finally, run the backup script on a weekly basis with cron. Run the following commands:
sudo su
crontab -e
Add the following entry:
0 2 * * 0 /root/s3sync/s3backup.sh
In the above example, I schedule the script to be executed at 02:00 on Sundays.
Remark:
S3fox (Firefox add-on) is the tool I used to create buckets and check my backups in Amazon S3.
Reference:
