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
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 :-)
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 0700 /usr/local/bin/s3mysqlbackup.sh
# Run the script to make sure it's all tickety boo
/usr/local/bin/s3mysqlbackup.sh
Assuming the backup script is stored in /usr/local/bin we need to add a crontask to run it automatically:
# Edit the crontab
env EDITOR=nano crontab -e
# params for s3mysqlbackup.sh
mysqlpass="password"
bucket="s3://xxxxx-backups/rds"
mysqlhost="mysql-master"
# Add the following lines:
# Run the database backup script at 3am
0 3 * * * mysqlpass=$mysqlpass bucket=$bucket mysqlhost=$mysqlhost /usr/local/bin/s3mysqlbackup.sh /usr/local/bin/s3mysqlbackup.sh >/dev/null 2>&1