Automatically Backup MySQL databases to Amazon S3

If you happen to run your own server you need to think about backups.

mysqlFor MySQL to backup a database is as simple as:

mysqldump -u user -ppassword my_database > /var/local/backups/mysql/my_database_dump.sql

Now you have a .sql file, which contains your database, stored into the /var/local/backups/mysql/ folder.

Is your database safe now? Well, no! What if your server dies? What if you loose all the data on it?

Better move the database’s dump somewhere else!

Amazon_S3Amazon S3 is a cloud storage service which ships with a very practical CLI tool.

In order to use it you need an amazon’s account and you need to install and configure the CLI tool.

Now that you have all the tools in place let’s see how we are going to automatically backup mysql databases to amazon s3:

  1. create a bucket on Amazon s3 (say my-backups)
  2. create a new folder inside that bucket (say my-backups/mysql)
  3. back up a database via mysqldump and store the dump into a folder in your server (say /var/local/backups/mysql/)
  4. synchronize that folder to Amazon s3’s my-backups/mysql

Steps 1 and 2 are done just the first time while 3 and 4 should be performed repeatedly (i.e. schedule them).

Here is a small bash script which does the job:


#!/bin/bash
#get the day of week as a number (i.e. 1 = Monday, 7 = Sunday)
DAY_OF_WEEK=$(date +%u)
#backup 'my_database' to the folder /var/local/backups/mysql/
#$DAY_OF_WEEK is appendend to the file's name in order to keep a weekly history of the backups
mysqldump -u user -ppassword my_database > /var/local/backups/mysql/my_database_$DAY_OF_WEEK.sql
#use aws (Amazon's tool) to sync your backups to Amazon S3
#/usr/local/bin/aws – absolute path to Amazon's CLI tool
#s3 – the service we want to use
#sync – the command to run
#/var/local/backups/mysql/ – local folder
#s3://my-backups/mysql/ – Amazon s3 folder
/usr/local/bin/aws s3 sync /var/local/backups/mysql/ s3://my-backups/mysql/

You can save this script into a .sh file, for example backup_mysql.sh, and then schedule every day in order to get an automatic backup. To do so, you need to add an entry to the crontab:

$ crontab -e

then add a line like the following at the bottom

00 00 * * * /bin/sh /path/to/backup_mysql.sh

this will run the script every day at midnight.

If you want to backup more than one database and keep backups separated you could add a parameter to the script:


#!/bin/bash
#get the day of week as a number (i.e. 1 = Monday, 7 = Sunday)
DAY_OF_WEEK=$(date +%u)
#backup the datababase passed as argument($1) to the folder /var/local/backups/mysql/
#$DAY_OF_WEEK is appendend o the file name in order to keep a weekly history of nthe backups
mysqldump -u user -ppassword $1 > /var/local/backups/mysql/$1/dump_$DAY_OF_WEEK.sql
#use aws (Amazon's tool) to sync your backups to Amazon S3
#/usr/local/bin/aws – the tool
#s3 – the service we want to use
#sync – the command to run
#/var/local/backups/mysql/ – local folder
#s3://my-backups/mysql/ – Amazon s3 folder
/usr/local/bin/aws s3 sync /var/local/backups/mysql/$1/ s3://my-backups/mysql/$1/

(pay attention ton the folders structure, it has to be consistent)

now you can pass the database name as a paramenter:

$ sh /path/to/backup_mysql.sh my_database