Automatized daily mysql backups to S3 buckets

Automatized daily mysql backups to S3 buckets.

Easy and scalable solution for your MySQL backups by using Amazon AWS RDS Service

If you are not using RDS to host your databases, it is very probable that you are doing your own backups. In this article we'll see a very simple shell script to do automatic daily backups for your mysql databases into an S3 bucket.

What you need to upload your MySQL backups to S3

The shell script used to backup your database and upload it to S3

The idea is to create the following script and run it with the appropiate environment variables, and what it does is actually pretty simple.

First, it gets the short name for the current day (let's say mon, for monday), and then uses mysqldump to dump the database to a temporary file. The file is then compressed with gzip and last, it uploads the file to S3 by using the AWS CLI Tools for S3.

Note that this will allow you to have at most 7 backups, since every day the backup will overwrite the last backup corresponding to that day. You can change this behavior easily by editing the file variable to suit your needs (perhaps using a format like %Y-%m-%h).

Sample run of the backup script

Crontab the script to achieve automated and periodic MySQL backups in Amazon S3

Add the following line to your crontab:

This will be run every day at 12:00am. Notice how we are not specifying any environment variables here. This is to prevent that someone without enough privileges can see the passwords and other sensible information. The "crontabed" script can be only accessible by root and hide the passwords and other needed information to do the backup.

Adapt your MySQL database backup to use the scalable S3 storage

Yeah, it is very simple, but highly effective :) Hopefully this will help you automate your daily backups for you database.