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
- Sign up here for an Amazon account.
- 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.
- 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.
- Now click on the user you just created -> Security Credentials and download the access key.
Setting up the server
- 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
- 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.
- Make it executable with “chmod +x s3backup.sh” and then run it by typing “./s3backup.sh”. Answer the questions and choose a password.
- 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.
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”).