Backup important data, such as database dumps, securely to S3.

In addition to installing the AWS command line tool and automating a backup script, we'll cover creating an IAM role in AWS so you can easily restrict access and revoke API keys quickly and easily.Here's what is covered in this video.

First we cover a sample database already created.

mysql -u root -p -e "select * from example_db.users;"

This is a database with one simple table. You can recreate it with this SQL (in MySQL):

CREATE DATABASE `example_db` DEFAULT CHARACTER SET `utf8mb4`;

CREATE TABLE `users` (
    id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARACTER SET `utf8mb4`;

INSERT INTO `users` (`id`, `username`, `created_at`) VALUES (NULL, 'jane_doe', CURRENT_TIMESTAMP);
INSERT INTO `users` (`id`, `username`, `created_at`) VALUES (NULL, 'john_doe', CURRENT_TIMESTAMP);

Then we head into AWS and create a new S3 bucket named sfh-backup-25kdjds. This has a bit of randomness to it as bucket names need to be unique across AWS.

Then we head to the AWS users/security section to create a new IAM user. This user is given API access tokens (Key ID and Secret Access Key) to use to connect to the AWS api.

We want to restrict what this user can access, so we use the policy builder to create the IAM policy associatd with this user. We make this as restrictive as possible, only able to upload to this one S3 bucket.

The IAM policy created looks like this:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1420044805000",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:PutObjectAcl"
      ],
      "Resource": [
        "arn:aws:s3:::sfh-backup-25kdjds/*"
      ]
    }
  ]
}

The actions allowed are just "PutObject" and "PutObjectAcl". These actions are only applied to one resource - all the files found within our new bucket sfh-backup-25kdjds.

Next we'll install the AWS CLI tool using Python PIP, a Python package manager.

# Install Pip
sudo apt-get install -y python-pip

# Install AWS CLI tol globally
sudo pip install awscli

# Check to ensure it exists
which aws

# Configure AWS - adding the id, key, default zone and default output format
aws configure
> YOUR KEY HERE
> YOUR SECRET KEY HERE
> us-east-1
> json

We'll create a file and test uploading it to ensure it works.

touch testfile

# We use the 'cp' command to copy it to our bucket
aws s3 cp testfile s3://sfh-backup-25kdjds/testfile

Then we can create a .env file to hold our MySQL database credentials. Use `vim ~/backup/

mkdir ~/backup
cd ~/backup
vim .env

The .env file:

export MYSQL_USER=root
export MYSQL_PASSWORD=root

Then we can create our backup shell script:

#!/usr/bin/env bash

FILE_NAME="example_db-`date +%Y%m%d%H%M`.sql.gz"
SAVE_DIR="/home/ubuntu/backup"
S3BUCKET="sfh-backup-25kdjds"

# Get MYSQL_USER and MYSQL_PASSWORD
source /home/ubuntu/backup/.env

mysqldump -u ${MYSQL_USER} -p${MYSQL_PASSWORD} example_db | gzip > ${SAVE_DIR}/${FILE_NAME}

if [ -e ${SAVE_DIR}/${FILE_NAME} ]; then

    # Upload to AWS
    aws s3 cp ${SAVE_DIR}/${FILE_NAME} s3://${S3BUCKET}/${FILE_NAME}

    # Test result of last command run
    if [ "$?" -ne "0" ]; then
        echo "Upload to AWS failed"
        exit 1
    fi

    # If success, remove backup file
    rm ${SAVE_DIR}/${FILE_NAME}

    # Exit with no error
    exit 0
fi

# Exit with error if we reach this point
echo "Backup file not created"
exit 1

This will export the database, compress it, and upload it to our S3 bucket.

Lastly, we create a crontask for the current user to run this script once per day at midnight (server time).

# for current user
crontab -e

# for specific user:
crontab -u username -e

The crontab task definition, which outputs stdout and stderr to a log file:

0 0 * * * /usr/bin/env bash /home/ubuntu/backup/backup.sh &>> /home/ubuntu/backup/backup.log

Save and quit that and your backup should be run every day!