Automatic MySQL Database Backups to Amazon S3 with S3SYNC

I am running an Ubuntu Gutsy based Slicehost VPS, and have a Macbook Pro development machine. On my server i am logged in as a user with sudo privileges. What i’d like to document in this blog post is how to create automatic MySQL database backups to Amazon S3 with S3SYNC.

Now there are a lot of options available to achieve all this, this is the way that i found easy and that worked for me. To create the automatic mysql database dumps, there is the automysqlbackup script available from SourceForge:

http://sourceforge.net/projects/automysqlbackup/

I downloaded this onto my Macbook Pro and used ‘scp’ to copy back to my server. I suppose you could use ‘wget’ directly form the server too if you prefer. You can put the file anywhere on the server you like, i just keep it in my home directory for now (/home/user/) – but i will move it later. First thing is to rename the file, at the time of writing version 2.5 was available:

sudo mv automysqlbackup.sh.2.5 automysqlbackup.sh

Now create a directory in which you want your backups to go to, and one where the restored backups from S3 will be downloaded to, eg /home/user/mysqlbackups/ and /home/user/mysqlrestores/:

mkdir mysqlbackups
mkdir mysqlrestores

The automysqlbackup script is of course just one file, and you can open and view it in a text editor. In fact i suggest that you do that next as it has pretty good instructions as to what it does and what parameters you need to add. Move to the directory in which you have the script and open it up, i use the nano editor:

sudo nano automysqlbackup.sh

Basically you need to add the names of your databases, mysql user, and mysql password info. The script will do daily, weekly, and monthly backups of your database into the specified backup folder of your choice – you can even have emails sent to you! Here is an excerpt:

# Username to access the MySQL server e.g. dbuser
USERNAME=root

# Username to access the MySQL server e.g. password
PASSWORD=********

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″
DBNAMES=”therailscoder_production”

# Backup directory location e.g /backups
BACKUPDIR=”/home/user/mysqlbackups”

Edit the files with your own settings, then save and exit!

Next edit the permissions on the script to give read, write, and execute permissions to the user, and not to to anyone else.

sudo chmod 700 automysqbackup.sh

You can test the script out manually as follows:

./automysqlbackup.sh

Move to your backups directory and you should see 3 sub-directories – daily, weekly, and monthly. In the daily directory you will find a gzipped sql file of you database backup! I will talk about how to actually automate this process a little later on…

Next we need to get the mysql backup’s over to Amazon S3. If you haven’t got an S3 account, sign up and get your Access Key ID and Secret Access Key ready:

http://www.amazon.com/s3

Next we need to setup s3sync:

http://s3sync.net

I want to credit John Eberly’s blog post from way back in 2006 for helping me set up s3sync. I have cheekily repeated most of his instructions in some of the steps below…

http://blog.eberly.org/2006/10/09/how-automate-your-backup-to-amazon-s3-using-s3sync/

s3sync requires ruby 1.8.4 or greater as well as libopenssl-ruby. I already had these setup on my server so i did not have to install them, if you don’t have these enter the command below to get them:

sudo aptitude install ruby libopenssl-ruby

In your /home/user/ directory download and install s3sync as follws:

wget http://s3.amazonaws.com/ServEdge_pub/s3sync/s3sync.tar.gz
tar xvzf s3sync.tar.gz
rm s3sync.tar.gz

You then need to create a directory for ssl certificates as well as download them. Move to the newly created s3sync folder and follow the commands below:

cd s3sync
mkdir certs
cd certs
wget http://mirbsd.mirsolutions.de/cvs.cgi/~checkout~/src/etc/ssl.certs.shar

Run this command form the certs directory:

sh ssl.certs.shar

Now go back to the main s3sync directory. You can create an S3 ‘bucket’ to store your database backup using s3sync eg:

ruby s3cmd.rb createbucket mysql.mydomain.com

Or use an GUI interface such as S3 Browser, Cockpit, or Jungle Disk:

http://people.no-distance.net/ol/software/s3/
http://jets3t.s3.amazonaws.com/applications/cockpit.html
http://www.jungledisk.com/

Next create your upload and download scripts, and edit them as follows with your own parameters. Note the ‘database’ is just a name i gave as a sub-directory or folder within the bucket – you can call it what you want:

sudo nano upload.sh

#!/bin/bash
# script to upload local directory upto s3
cd /home/user/s3sync/
export AWS_ACCESS_KEY_ID=yourS3accesskey
export AWS_SECRET_ACCESS_KEY=yourS3secretkey
export SSL_CERT_DIR=/home/user/s3sync/certs
ruby s3sync.rb -r –ssl –delete /home/user/mysqlbackups/ mysql.mydomain.com:/database
# copy and modify line above for each additional folder to be synced

sudo nano download.sh

#!/bin/bash
# script to download local directory upto s3
cd /home/user/s3sync/
export AWS_ACCESS_KEY_ID=yourS3accesskey
export AWS_SECRET_ACCESS_KEY=yourS3secretkey
export SSL_CERT_DIR=/home/user/s3sync/certs
ruby s3sync.rb -r –ssl –delete mysql.mydomain.com:/database/ /home/user/mysqlrestores
# copy and modify line above for each additional folder to be synced

Change the permissions on the files, and check the ownership – i had to change this to the current user (me):

sudo chmod 700 upload.sh
sudo chmod 700 download.sh

sudo chown user upload.sh
sudo chown user download.sh

Set up some S3 configuration:

mkdir /etc/s3conf
cp s3config.yml.example /etc/s3conf/s3config.yml

Edit s3config.yml with your S3 details and ssl cert path:

sudo nano /etc/s3conf/s3config.yml

In your s3sync directory, to run the upload and download scripts manually enter:

./upload.sh
./download.sh

To check the upload use ruby scmd.rb as shown below, or use your preferred GUI software:

ruby s3cmd.rb list mysql.mydomain.com

To check the download just go to your mysqlrestores directory. I found that when i tried the download, i needed to also create a sub-directory called ‘database’ (/home/user/mysqlrestores/database/).

What we now have is the ability to create mysql database backup files, as well as being able to upload and download these backup files to and from S3. But everything is being done manually! I’ll finish up by showing how this can become an automated process…

To get the automysqlbackup script to function automatically there are a couple of options. On Ubuntu and most other Linux distributions you should have the following ‘cron’ directories:

/etc/cron.daily/
/etc/cron.weekly/
/etc/cron.monthly/

You can move the script to any one of these directories, and the script will run automatically every day, week, or month! On Ubuntu the .sh extension does not need to be there.

sudo mv automysqlbackup.sh /etc/cron.daily/automysqlbackup

But i prefer not to do this! This is because there are other default scripts or processes that run on a daily basis, and these will be run at the same time as your database backups. The default on my setup is 0625 am! I prefer to have my database dumps run at a different time, when there are no other scripts or processes running, and when my website is likely to have less traffic. The same goes for uploads to S3. The easiest way to do this is to create your own ‘crontab’, here you can specify what time to run your scripts.

Before i do this, i like to move the automyslbackup script to the s3sync directory, where it’s friends ‘upload’ and ‘download’ live !

sudo mv automysqlbackup.sh /home/user/s3sync/automysqlbackup.sh

Then from any directory open your own ‘crontab’:

crontab -e

Then paste in this code:

# m h dom mon dow command
0 1 * * * /home/user/s3sync/automysqlbackup.sh
30 1 * * * /home/user/s3sync/upload.sh

Save and exit. This will basically set things up so that the automysqlbackup script will run every day at 0100 am, and so that the s3sync uploads the backups to S3 at 0130 am every day! You might need to do a Google search on crontab to find out how to customize this to suit your own needs.

Done!

Advertisement