Backing up MySQL in AWS using a read-replica

Background

We have several databases on AWS using RDS (Relational Database Service). We would like to backup those databases automatically. AWS provides „Snapshots“ for that, but they have several limits (like we can’t use them to restore a local database and it’s a bit harder to manage hundreds of them). Backing up a database is basically a simple task. However, we want to back-up the database with as least impact as possible to users (customers) of this database, so we can’t just connect to it and run a backup command.

TL;DR

We use the AWS CLI and IAM to create a read replica of an existing RDS DB instance (in this case MySQL). We use IAM to authenticate against this RDS instance and then backup databases using a pre-defined list. After we completed the backup, we remove the read replica. The backup is compressed as .tar.gz. The whole process is automated using bash.

Prerequisites

For this to success, you need available

• a RDS instace with IAM authentication enabled
• a bash terminal (we use a lot of Ubuntu/Linux these days)
• the AWS CLI (v1 is enough)
• jq for JSON parsing (see https://stedolan.github.io/jq/ )
• the RDS instance has a user that can authenticate with the AWSAuthenticationPlugin (see https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.DBAccounts.html).
• an IAM role that is able to create a read replica of the database you want to backup and that can connect to the read replica using IAM (see https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html )

Step 0: Environment

I’ll use several environment variables through this tutorial. I’ll assume you have the AWS CLI configured and the IAM user has enough access rights to create a read replica and delete it again afterwards.

# The unique identifier of the RDS read replica
RDS_REPLICA_IDENTIFIER=
# The unique identifier of the RDS instance to backup
RDS_SOURCE_IDENTIFIER=
# The username which the IAM user is allowed to use, i'll use backup here
DB_USER=
# The endpoint will be the hostname of the read replica
ENDPOINT=

Step 1: Create a read replica

First, we want to create a read replica using the AWS CLI. We make the replica publicly available so that we can access it from our computer. Use $RDS_REPLICATE_IDENTIFIER for the unique name of the replica and $RDS_SOURCE_IDENTIFIER for the unique name of the instance you want to backup. We’ll add a tag here, but that’s not necessary. You might also want to consider to upsize/downsize your read replica depending on the size of your master instance (a powerful replica might cost you not that much but might improve backup speed).

aws rds create-db-instance-read-replica \
        --db-instance-identifier $RDS_REPLICA_IDENTIFIER \
        --enable-iam-database-authentication \
        --source-db-instance-identifier $RDS_SOURCE_IDENTIFIER \
        --publicly-accessible \
        --tags Key=purpose,Value=backup

Step 2: Wait until the replica is ready

It will take some time until the read replica is ready and can be accessed using the MySQL CLI. So let’s use some bash magic and wait until the read replica has the „available“ state. We use jq here to extract the status from the JSON response of the AWS CLI.

STATUS=creating
while [["$STATUS" != "available"]]
do
        sleep 30
        echo " Checking..."
        STATUS=$( aws rds describe-db-instances \
                --db-instance-identifier $RDS_REPLICA_IDENTIFIER | jq -r ".DBInstances[0].DBInstanceStatus" )
        echo " -> Status is ${STATUS}"
done

If that went through smooth (probably after 5 to 10min), let’s move forward and find the information needed to connect to our instance.

Step 3: Describe the endpoint

We will need at least a hostname, a username, a password and a port to connect to the MySQL read replicate. So let’s find those informations. I’ll use again jq to extract the information from the JSON response of the AWS CLI. The last line generates a temporary access token for our IAM user, so that we can backup the database. Be careful, the token expires after 900s (15min), so if your backups take longer you might want to make sure you can renew the token.

ENDPOINT=$( aws rds describe-db-instances \
         --db-instance-identifier $RDS_REPLICA_IDENTIFIER )
DB_HOST=$( echo $ENDPOINT | jq -r ".DBInstances[0].Endpoint.Address" )
DB_PORT=$( echo $ENDPOINT | jq -r ".DBInstances[0].Endpoint.Port" )
DB_PASS=$( aws rds generate-db-auth-token \
   --hostname ${DB_HOST} \
   --port ${DB_PORT} \
   --region eu-central-1 \
   --username ${DB_USER} )

Step 4: Doing the backup

Now we will be able to backup our databases. Let’s again use some bash magic to dump not only one but several databases using mysqldump.

DATABASES=(
    # add/remove database names as you wish
    "db1"
    "db2"
)
for DATABASE in "${DATABASES[@]}"
do
    echo "-- Exporting database <${DATABASE}>"
    mysqldump \
            --enable-cleartext-plugin
            -h${DB_HOST} \
            -u${DB_USER} \
            -p${DB_PASST} \
            --port=${DB_PORT} \
            --databases $DATABASE | \
            sed -e 's/DEFINER[]*=[]*[^*]*\*/\*/' > ${RDS_SOURCE_IDENTIFIER}_${DATABASE}.sql
    echo " -> Dump ${DATABASE} complete, wait a few seconds..."
    # Experience tells, let's wait a few secs or you'll get errors
    sleep 3
done

This script will dump the databases from our RDS instance one-by-one and store them in a SQL file in the current directory. Depending on your use-case you might want to configure mysqldump with one of its many options. There are quite some important options there to make your backups both fast, reliable and complete.

Also, don’t forget to compress the sql files and move them somewhere safe. We’ll usually upload them to S3/Glacier, a very cost-effective storage. As sql files are plain-text, they are very small when compressed and this will not cost very much.

tar -czvf "${RDS_SOURCE_IDENTIFIER}.tar.gz" ./*.sql

Step 5: Removing the read replica

Last but not least, let’s remove the read replica so it won’t incurr any more costs to our AWS account. We can remove the read replica with the same command as we would remove a regular RDS instance. So be careful here and make sure your IAM user does not have rights to remove the master but only the replica. We use the same logic to wait for the delete to be finished as above. Make sure to skip final snapshots, as they are not available to read replicase (but the API expects you to provide the option).

aws rds delete-db-instance \
        --db-instance-identifier $RDS_REPLICA_IDENTIFIER \
        --skip-final-snapshot

STATUS=deleting
while [ "$STATUS" == "deleting" ];
do
        sleep 30
        echo "   Checking..."
        STATUS=$( aws rds describe-db-instances \
                --db-instance-identifier $RDS_REPLICA_IDENTIFIER || \
                echo '{"DBInstances":[{"DBInstanceStatus":"gone"}]}' | \
                jq -r ".DBInstances[0].DBInstanceStatus" )
        echo "   -> Status is ${STATUS}"
done

The AWS CLi will fail as soon as the instance is gone, so we’ll trick the script by returning some dummy JSON to indicate that the instance is gone.

Conclusion

Backup is an important task and should be done properly. I demonstrated how to automate backing up an AWS RDS (MySQL) instance using the AWS CLI and some bash. I’ll used a read replica to keep the impact on the production system low as the backup happens from a „detached“ database. This is a very cost-effective and performant option.

Going further, I would recommend detaching the read replica (making it independent from the master) and to add some better error handling. I also did not demonstrate how to backup those files (we usually move them to S3/Glacier), as this is dependent on your needs, your storage options and also cost.