Automating MySQL to Amazon S3 backups on Ubuntu

This is a quick guide on how to set up a cronjob to backup your MySQL database on S3.

I had to do this recently for Crealdo and although there’s already a few tutorials out there on this subject, some of them are a little too concise so I figured I’d try to make a better one.

Setting up S3

  1. Sign up here for an Amazon account.
  2. Navigate to the AWS console and click on S3 -> create bucket, choose a name and a region for your bucket. You can activate logs if you like.
  3. Now we’re going to create an IAM user on AWS that has only the necessary rights for the backups. Click on your name on the top right -> Security Credentials -> Users -> Create new user -> Permissions -> Add Policy. Search for AmazonS3FullAccess and add it.
  4. Now click on the user you just created -> Security Credentials and download the access key.

Setting up the server

  1. Install s3cmd
    ~$ wget -O- -q http://s3tools.org/repo/deb-all/stable/s3tools.key | sudo apt-key add -
    ~$ sudo wget -O/etc/apt/sources.list.d/s3tools.list http://s3tools.org/repo/deb-all/stable/s3tools.list
    ~$ sudo apt-get update && sudo apt-get install s3cmd
  2. Copy the code from this gist and upload it to /usr/local/bin/ and call it s3backup.sh or something like that. Remember to change the “mysqlpass” and “bucket” values.
  3. Make it executable with “chmod +x s3backup.sh” and then run it by typing “./s3backup.sh”. Answer the questions and choose a password.
  4. Finally, run “crontab -e” to add a cronjob. Add a new line: “0 0,6,12,18 * * * bash /usr/local/bin/s3backup.sh >/dev/null 2>&1”. This will backup your database every 6 hours. Google how to configure cronjobs if you need more/less frequent backups.
  5. Profit.
Note: if you’re in Europe and you’d like your data not to leave the EU, I recommend choosing Ireland as your region. I tried first with Frankfurt but s3cmd was throwing an error (“Please use AWS4-HMAC-SHA256”).