Nightly Backup MySQL data to Amazon S3

Running within the Amazon Web Services ecosystem has some pretty awesome perks for system admins and developers alike. Since I run a lot of different systems on EC2 instances I have found it very useful to have the databases backed up on an automated schedule. Using AWS’ CLI tool it is very simple and straightforward to do this.

You’ll need to setup an API user on your AWS account with admin privileges before moving forward to get an Access Key and Secret Key for the CLI to sign requests on your behalf. To do this follow this guide.

Once you’ve retrieved your access key and secret, log into the machine you want to run the backup script from and install the AWS CLI library.

$ sudo yum install aws-cli

Then configure aws-cli to use your API credentials:

$ aws configure
AWS Access Key ID [None]: PASTE_YOUR_ACCESS_KEY
AWS Secret Access Key [None]: PASTE_YOUR_SECRET_KEY
Default region name [None]: 
Default output format [None]: 

Now the AWS library can connect and use your services. A custom Bash script will tie all of this together. Open a text editor:

$ sudo nano ~/dbbackup.sh

You might want a separate bucket for your database backups, but any bucket will do. Navigate to your S3 console and create a new bucket, or grab the name of one you want to use for this purpose. I suggest setting strict permissions here so that this data is not publicly accessible, or accessible to any users with low level privileges on your AWS account.

Add this script to dump, zip, move the backup to s3 then delete the local file:

#!/bin/bash
export HOME=/home/ec2-user
mysqldump -uyour_db_user --password='password' -h localhost --routines --opt your_db | gzip > ~/your_db.sql.gz
aws s3 cp ~/your_db.sql.gz s3://your.s3.bucket/
rm ~/your_db.sql.gz

Make the new shell script executable:

$ sudo chmod +x ~/dbbackup.sh

Test the script to make sure it works:

$ ~/dbbackup.sh
Warning: Using a password on the command line interface can be insecure.
upload: ../../../../home/ec2-user/your_db.sql.gz to s3://radius.db.bak/your_db.sql.gz

If everything went well, you should now see the backup SQL Zip file in your S3 bucket. To make this backup run nightly, we will add it to the crontab.

$ sudo nano /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name command to be executed
0 0 * * * ec2-user /home/ec2-user/dbbackup.sh

You’re all set, now make sure to check back tomorrow to make sure the process is running and everything is setup properly. If it is, you should see the ‘Last modified’ date update on your backup file.