The five minute MySQL backup to S3

This short tutorial will teach you how to backup your MySQL database to Amazon S3. What you are going to need is:

  1. MySQL
  2. Amazon S3 bucket configured (prepare you access keys to configure s3cmd)
  3. s3cmd utility installed on the target server (yum install s3cmd). Configure with s3cmd --configure

Step 1. Schedule a local backup first

First, you need root access on your server, then create a credentials file for root user to MySQL access (you do have a root password for MySQL, don't you?). So let's start by creating a new credentials, vim /root/mysql-extra-config 

[client]
user=root
password=super-secret-password

Then create a simple backup script (that you can later extend to more files), vim /root/backup.sh

#!/bin/sh
# Make sure we have a backup directory in the first place
mkdir -p /root/backups
# Backup all MySQL DBs
/bin/mysqldump --defaults-extra-file=/root/mysql-extra-config --all-databases | /bin/gzip > "/root/backups/mysql-`date "+%Y%m%d-%H%M%S"`.sql.gz"

Make the script executable chmod+x /root/backup.sh

Step 2. Schedule a backup & sync

Configure cron to regularly run your backup each night and sync the files to Amazon S3 bucket, crontab -e

30 4 * * * /root/backup.sh
45 4 * * * s3cmd -v sync -r /root/backups/ s3://backups

Save and the following night the cron job will fire. Don't forget next day to look for your backup in the Amazon S3 bucket!

Disclaimer: This is not the best way to do a backup! Definitely better than nothing, you should read here how to do a proper backup.