How to send MySQL database backups to Amazon S3 every six hours

Tutorials
#1

Hello folks, I hope this is useful to some; this is a rough guide I’ve created to send a snapshot of my site’s MySQL database to an Amazon S3 bucket every six hours, based on this guide and this guide which both outline how to send PostgreSQL dumps to Amazon S3.

The below script is different to those guides as follows:

  • It works with MySQL/MariaDB’s mysqldump command
  • It provides more configuration options using variables (so you don’t have to deploy code changes to adjust your backup parameters), and
  • It runs every six hours, so your database snapshots will never be more than six hours out of date (but only one backup per day is saved to reduce space usage)
  • This guide outlines how to segregate your backups into a sub-folder in your S3 bucket, using an IAM user which only has permission to view those backups (useful if you want to back up more than one site to the same S3 bucket without allowing your sites access to each others’ database backups)
  • This guide will back up all of your environments’ databases - not just master - and dump file names include the environment name for easy identification.

How to

  • Log in to Amazon AWS with an account which has permission to administer S3 and IAM services

  • Choose a short name for your site - e.g. mywebsite - this will be used for your IAM username and the folder in your S3 bucket

  • In Amazon S3, create a bucket named e.g. my-platform-sh-backups

  • Create a folder in your backups S3 bucket with the site short name - i.e. /mywebsite/ (you will need administrator access to the bucket)

  • In Amazon IAM, create an IAM user with a name such as backup-user-mywebsite

  • Attach the following policy to the IAM user, replacing BUCKETNAME with your S3 bucket name e.g. my-platform-sh-backups, and SITE with e.g. mywebsite:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject"
      ],
      "Resource": [
        "arn:aws:s3:::BUCKETNAME/SITE/",
        "arn:aws:s3:::BUCKETNAME/SITE/*"
      ]
    },
    {
      "Sid": "VisualEditor1",
      "Effect": "Allow",
      "Action": "s3:ListAllMyBuckets",
      "Resource": "*"
    },
    {
      "Sid": "VisualEditor2",
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::BUCKETNAME"
    }
  ]
}

  • Make a note of your IAM user’s access key and secret - you’ll need these later, and if you don’t note them down now, you’ll need to re-create them

  • In Terminal, cd to your site’s build directory - you will need to have connected this directory to the site’s platform.sh project - e.g. by running platform project:set-remote

  • Use the following commands to create the environment variables which allow the backup script to run:

Note that you may only need to update the first four variables for your individual site.

For example, change: “IAM ACCESS KEY” to “JAIJDIJIANISASJAKJS” (if that’s your access key).

Set the IAM access key as a sensitive variable:

platform variable:create --name=AWS_ACCESS_KEY_ID --value="IAM_ACCESS_KEY" --level=project --json=false --sensitive=true --prefix=env --visible-build=true --visible-runtime=true --enabled=true

Set the IAM access secret as a sensitive variable:

platform variable:create --name=AWS_SECRET_ACCESS_KEY --value="IAM_SECRET_ACCESS_KEY" --level=project --json=false --sensitive=true --prefix=env --visible-build=true --visible-runtime=true --enabled=true

Set the folder in your S3 bucket into which you want to store backups, including the trailing slash:

platform variable:create --name=AWS_S3_FOLDER --value="myfolder/" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

Set your S3 bucket name:

platform variable:create --name=AWS_S3_BUCKET --value="my-amazon-s3-bucket" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

The database host name - default is database.internal:

platform variable:create --name=AWS_S3_DB_HOST --value="database.internal" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

The database port - default is 3306:

platform variable:create --name=AWS_S3_DB_PORT --value=3306 --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

The database username - default is main:

platform variable:create --name=AWS_S3_DB_USER --value="main" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

The mount location where backups will be saved temporarily - default is /app/backups if you use “backups” in your platform.app.yamp mounts section:

platform variable:create --name=AWS_S3_DB_DUMP_DIRECTORY --value="/app/backups" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

By default, your database password is an empty string; if it isn’t in your hosting environment, you will need to set it:

platform variable:create --name=AWS_S3_DB_PASSWORD --value="<your database password if not empty>" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

Add the following lines to your platform.app.yaml:

mounts:
  # ...your other mounts should be here...
  #
  # Temporary backups location.
  '/backups':
    source: local
    source_path: 'backups'

… and:

crons:
  #... any existing cron commands will be here...
  #
  # Automated database dumps to S3. Back up every six hours at 12 minutes past
  # the hour:
  backup_to_s3:
    spec: '12 1,7,13,19 * * *'
    cmd: |
      ./cron/forward_dumps_to_s3.sh

… and lastly, add the following build hook - you may already have the line which installs the platform.sh CLI, so this build hook incorporates that command:

hooks:
  # ... any other build hooks here...
  #
  # 1. Install the platform.sh CLI.
  # 2. Install the AWS client if the access key and secret are set.
  # The deploy hook runs after your application has been deployed and started.
  build: |
    curl -sS https://platform.sh/cli/installer | php
    if [ ! -z "$AWS_ACCESS_KEY_ID" ] && [ ! -z "$AWS_SECRET_ACCESS_KEY" ]; then
        pip install futures
        pip install awscli --upgrade --user 2>/dev/null
    fi

If you don’t want to add the platform.sh CLI at build time, this build hook should work for you:

hooks:
  # ... any other build hooks here...
  #
  # Install the AWS client if the access key and secret are set.
  # The deploy hook runs after your application has been deployed and started.
  build: |
    if [ ! -z "$AWS_ACCESS_KEY_ID" ] && [ ! -z "$AWS_SECRET_ACCESS_KEY" ]; then
        pip install futures
        pip install awscli --upgrade --user 2>/dev/null
    fi

Create the shell script

Create the file ./cron/forward_dumps_to_s3.sh with the following code:

#!/usr/bin/env bash

# To use this script.
#
# 1: Create AWS IAM user for this site
#
# 2: Set user policy so they can only read from/write to a single folder in the
#    bucket
#
# 3: Create project-level variables using the following commands:
#
# platform variable:create --name=AWS_ACCESS_KEY_ID --value="<IAM ACCESS KEY>" --level=project --json=false --sensitive=true --prefix=env --visible-build=true --visible-runtime=true --enabled=true; \
# platform variable:create --name=AWS_SECRET_ACCESS_KEY --value="<IAM SECRET ACCESS KEY>" --level=project --json=false --sensitive=true --prefix=env --visible-build=true --visible-runtime=true --enabled=true; \
# platform variable:create --name=AWS_S3_FOLDER --value="<folder name with trailing slash e.g. mywebsite/>" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true; \
# platform variable:create --name=AWS_S3_DB_HOST --value="database.internal" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true; \
# platform variable:create --name=AWS_S3_DB_PORT --value=3306 --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true; \
# platform variable:create --name=AWS_S3_DB_USER --value="main" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true; \
# platform variable:create --name=AWS_S3_DB_DUMP_DIRECTORY --value="/app/backups" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true
#
# By default, your database password is an empty string; if it isn't in your
# hosting environment, you will need to set it:
# platform variable:create --name=AWS_S3_DB_PASSWORD --value="<your database password if not empty>" --level=project --json=false --sensitive=false --prefix=env --visible-build=true --visible-runtime=true --enabled=true

# We set the filename to just today's date; this means we can take a database
# dump every hour but overwrite previous dumps for today; this leaves us with
# one backup per day.
DATE=$(date +%Y-%m-%d)
DUMP_FILE_NAME="backup--${PLATFORM_ENVIRONMENT}--${DATE}--all-databases.sql"

# If we have all the variables set, take the backup and send it to S3.
if [[ ! -z "$AWS_S3_DB_HOST" ]] && [[ ! -z "$AWS_S3_DB_PORT" ]] && [[ ! -z "$AWS_S3_DB_USER" ]] && [[ ! -z "$AWS_S3_DB_DUMP_DIRECTORY" ]] && [[ ! -z "$AWS_S3_BUCKET" ]]; then
  echo "Backing up databases to S3 with the following settings:
- DUMP_FILE_NAME: $DUMP_FILE_NAME
- AWS_S3_DB_HOST: $AWS_S3_DB_HOST
- AWS_S3_DB_PORT: $AWS_S3_DB_PORT
- AWS_S3_DB_USER: $AWS_S3_DB_USER
- AWS_S3_DB_PASSWORD: (not shown)
- AWS_S3_DB_DUMP_DIRECTORY: $AWS_S3_DB_DUMP_DIRECTORY
- AWS_ACCESS_KEY_ID: $AWS_ACCESS_KEY_ID
- AWS_SECRET_ACCESS_KEY: (not shown)
- AWS_S3_BUCKET: $AWS_S3_BUCKET
- AWS_S3_FOLDER: $AWS_S3_FOLDER
"

  echo "
  Running command: mysqldump --host=\"$AWS_S3_DB_HOST\" --port=\"$AWS_S3_DB_PORT\" --user=\"$AWS_S3_DB_USER\" --password=\"$AWS_S3_DB_PASSWORD\" --all-databases > \"$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME\""

  mysqldump --host="$AWS_S3_DB_HOST" --port="$AWS_S3_DB_PORT" --user="$AWS_S3_DB_USER" --password="$AWS_S3_DB_PASSWORD" --all-databases > "$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME"

  echo "
  Running command: tar -czvf \"$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME.tar.gz\" \"$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME\""

  tar -czvf "$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME.tar.gz" "$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME"

  echo "
  Removing dump file $AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME:"
  rm "$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME"

  echo "
  Updating latest file: _latest.txt with backup filename $DUMP_FILE_NAME"

  echo "$DUMP_FILE_NAME.tar.gz" > "$AWS_S3_DB_DUMP_DIRECTORY/_latest.txt"

  echo "
  Running command: aws s3 sync \"$AWS_S3_DB_DUMP_DIRECTORY\" \"s3://$AWS_S3_BUCKET/$AWS_S3_FOLDER\""

  aws s3 sync "$AWS_S3_DB_DUMP_DIRECTORY" "s3://$AWS_S3_BUCKET/$AWS_S3_FOLDER"

  echo "
  Removing backup file $AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME.tar.gz:"

  rm "$AWS_S3_DB_DUMP_DIRECTORY/$DUMP_FILE_NAME.tar.gz"

else
  echo "Unable to backup; one of the following settings variables is empty:
- DUMP_FILE_NAME: $DUMP_FILE_NAME
- AWS_S3_DB_HOST: $AWS_S3_DB_HOST
- AWS_S3_DB_PORT: $AWS_S3_DB_PORT
- AWS_S3_DB_USER: $AWS_S3_DB_USER
- AWS_S3_DB_PASSWORD: (not shown)
- AWS_S3_DB_DUMP_DIRECTORY: $AWS_S3_DB_DUMP_DIRECTORY
- AWS_ACCESS_KEY_ID: $AWS_ACCESS_KEY_ID
- AWS_SECRET_ACCESS_KEY: (not shown)
- AWS_S3_BUCKET: $AWS_S3_BUCKET
- AWS_S3_FOLDER: $AWS_S3_FOLDER
"
fi

In Terminal, mark this new file executable:

cd path/to/your/platform.app.yaml
chmod +x cron/forward_dumps_to_s3.sh

Commit and push your changes, ensuring the forward_dumps_to_s3.sh file is executable (you may need to run git config core.fileMode 1 to have git recognise this).

You will need to redeploy your master environment for the new variables to become available to the backup script:

platform redeploy -e master

Assuming your platform.sh variables are all set correctly, you should be able to test your script by SSHing into the master environment and running the backup script manually:

platform ssh -e master

… followed by:

./cron/forward_dumps_to_s3.sh

… which should give you output similar to:

Backing up databases to S3 with the following settings:
- DUMP_FILE_NAME: backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql
- AWS_S3_DB_HOST: database.internal
- AWS_S3_DB_PORT: 3306
- AWS_S3_DB_USER: main
- AWS_S3_DB_PASSWORD: (not shown)
- AWS_S3_DB_DUMP_DIRECTORY: /app/backups
- AWS_ACCESS_KEY_ID: XXXXQRJ5PRUFG2ENXXXX
- AWS_SECRET_ACCESS_KEY: (not shown)
- AWS_S3_BUCKET: my-s3-bucket
- AWS_S3_FOLDER: myfolder/

  Running command: mysqldump --host="database.internal" --port="3306" --user="main" --password="" --all-databases > "/app/backups/backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql"

  Running command: tar -czvf "/app/backups/backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql.tar.gz" "/app/backups/backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql"
tar: Removing leading `/' from member names
/app/backups/backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql

  Updating latest file: _latest.txt with backup filename backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql

  Running command: aws s3 sync "/app/backups" "s3://my-s3-bucket/myfolder/"
upload: backups/backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql.tar.gz to s3://my-s3-bucket/myfolder/backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql.tar.gz

  Removing backup file /app/backups/backup--ENVIRONMENTNAME--2021-01-19--all-databases.sql.tar.gz:

Lastly, you can use the same IAM key and secret to log into the S3 bucket e.g. with Cyberduck to confirm the backup file is present.

Hopefully the above proves useful - please let me know if anything doesn’t work as it should, or you have any questions :slight_smile:

Alex

3 Likes