Backup MySQL to Amazon S3

This is a simple way to backup your MySQL tables to Amazon S3 for a nightly backup - this is all to be done on your server :-)

Sister Document - Restore MySQL from Amazon S3 - read that next

1 - Install s3cmd

this is for Centos 5.6, see http://s3tools.org/repositories for other systems like ubuntu etc

# Install s3cmd
cd /etc/yum.repos.d/
wget http://s3tools.org/repo/CentOS_5/s3tools.repo
yum install s3cmd
# Setup s3cmd
s3cmd --configure
    # You’ll need to enter your AWS access key and secret key here, everything is optional and can be ignored :-)

2 - Add your script

Upload a copy of s3mysqlbackup.sh (it will need some tweaks for your setup), make it executable and test it

# Add the executable bit
chmod +x s3mysqlbackup.sh
# Run the script to make sure it's all tickety boo
./s3mysqlbackup.sh

# !/bin/bash

# get the day of week as a number (i.e. 1 = Monday, 7 = Sunday)
DAY_OF_WEEK=$(date +%u)

# backup 'my_database' to the folder /var/local/backups/mysql/
#$DAY_OF_WEEK is appendend to the file's name in order to keep a weekly history of the backups
mysqldump -u user -ppassword my_database > /var/local/backups/mysql/my_database_$DAY_OF_WEEK.sql

# use aws (Amazon's tool) to sync your backups to Amazon S3
# /usr/local/bin/aws - absolute path to Amazon's CLI tool
# s3 - the service we want to use
# sync - the command to run
# /var/local/backups/mysql/ - local folder
# s3://my-backups/mysql/ - Amazon s3 folder
# /usr/local/bin/aws s3 sync /var/local/backups/mysql/ s3://my-backups/mysql/

3 - Run it every night with CRON

Assuming the backup script is stored in /var/www/s3mysqlbackup.sh we need to add a crontask to run it automatically:

# Edit the crontab
env EDITOR=nano crontab -e
    # Add the following lines:
    # Run the database backup script at 3am
    0 3 * * * bash /var/www/s3mysqlbackup.sh >/dev/null 2>&1

4 - Don't expose the script!

If for some reason you put this script in a public folder (not sure why you would do this), you should add the following to your .htaccess or httpd.conf file to prevent public access to the files:

### Deny public access to shell files
<Files *.sh>
    Order allow,deny
    Deny from all
</Files>

You can save this script into a .sh file, for example backup_mysql.sh, and then schedule every day in order to get an automatic backup. To do so, you need to add an entry to the crontab:

#$ crontab -e
#then add a line like the following at the bottom

00 00 * * * /bin/sh /path/to/backup_mysql.sh
#this will run the script every day at midnight.

If you want to backup more than one database and keep backups separated you could add a parameter to the script:

#!/bin/bash

#get the day of week as a number (i.e. 1 = Monday, 7 = Sunday)
DAY_OF_WEEK=$(date +%u)

#backup the datababase passed as argument($1) to the folder /var/local/backups/mysql/
#$DAY_OF_WEEK is appendend o the file name in order to keep a weekly history of nthe backups
mysqldump -u user -ppassword $1 > /var/local/backups/mysql/$1/dump_$DAY_OF_WEEK.sql

#use aws (Amazon's tool) to sync your backups to Amazon S3
#/usr/local/bin/aws - the tool
#s3 - the service we want to use
#sync - the command to run
#/var/local/backups/mysql/ - local folder
#s3://my-backups/mysql/ - Amazon s3 folder
/usr/local/bin/aws s3 sync /var/local/backups/mysql/$1/ s3://my-backups/mysql/$1/