Auto scaling a MySQL database to meet fluctuating application demands

with Amazon Aurora

Amazon Aurora is a MySQL and PostgreSQL-compatible relational database that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases. In this tutorial, you will learn how to create an Amazon Aurora database and configure it to scale automatically, by adding or removing read replicas, to meet fluctuating demands of your application.

This tutorial is not within the free tier and will cost you less than $1 provided you follow the steps in the tutorial and terminate your resources at the close of the tutorial.

About this Tutorial
Time 10-20 minutes                                           
Cost Less than $1
Use Case Databases
Products Amazon Aurora, Amazon RDS
Audience Database administrators, Developers
Level Intermediate
Last Updated July 2, 2019

Step 1: Create an Aurora DB cluster

1.1 - Open a browser and navigate to Amazon RDS console. If you already have an AWS account, login to the console. Otherwise, create a new AWS account to get started.

Already have an account? Log in to your account

1.2 - On the top right corner, select the region where you want to launch the Aurora DB cluster.

1.3 - Click on "Create database" in the Amazon Aurora window.

Before continuing, switch to the new database creation flow:

Engine options

1.4 - On Database engine, select “Amazon Aurora”.

1.5 - On Edition, select “Amazon Aurora with MySQL compatibility”.

1.6 - On Version, select the Aurora version you prefer.

1.7 - On Database Location, select “Regional”.

Database features

1.8 - Select “One writer and multiple readers”.

Templates

1.9 - Select “Production”.

Settings

1.10 - Choose an identifier for your Aurora DB cluster, e.g. "database-1".

DB instance size

1.11 - For the DB instance size, select a large instance (ending with .large).

Availability & durability

1.12 - Choose “Create an Aurora Replica/Reader”.

Connectivity

1.13 - Select the VPC where you want to create the database.

Note that once created, a database can't be migrated to a different VPC.

1.14 - Click on “Additional connectivity configuration”.

1.15 - Select the default value for Subnet group.

1.16 - On Publicly accessible, select “No”.

This means you will have to connect to the database from an EC2 instance within the same VPC.

1.17 - On VPC security group, select “Create new”. If you happen to have a security group that allows incoming TCP connections on port 3306, you can choose it instead. This security group will control ingress to your Aurora cluster.

1.18 - In New VPC security group name, type “aurora-tutorial”.

1.19 - Leave the default value for Database port.

Additional configuration

Leave the default values for “Additional configuration”.

The best practice is to enable the Deletion protection. If you want to delete the database at the end of the tutorial, you can leave the option unchecked.

1.20 - On “Deletion protection”, uncheck “Enable deletion protection”.

Review and create

After a quick review of all the fields in the form, you can proceed.

1.21 - Click on “Create database”.

While the instances are being created, you will see a banner explaining how to obtain your credentials. This is a good opportunity to save the credentials somewhere, as this is the only time you will be able to view this password.

1.22 — Click on “View credential details”.

1.23 - Save the username, password, and the cluster endpoint.

1.24 - After closing the credential details popup, click on the name of the database you created.

1.25 - Copy the Writer and Reader endpoints. You can direct any read/write traffic to the Writer endpoint, but it's a good practice to direct read-only traffic to the Reader endpoint.

Step 2: Add a scaling policy

Aurora Auto Scaling can create and remove replicas based on the scaling policies you define. When the workload or the number of connections to your database suddenly increases, Aurora Auto Scaling can add Aurora Replicas. Once the workload or the number of connections decrease, Aurora Auto Scaling removes the extra Aurora Replicas so that you don't spend on extra capacity.

2.1 - Select your Aurora DB cluster.

2.2 - Click on “Actions” and select “Add replica auto scaling”.

Policy details

2.3 - Select a Policy name, e.g. “policy-1”.

2.4 - Choose a metric to use for the auto scaling.

There are two Target metrics you can use: “Average CPU utilization of Aurora Replicas” and “Average connections of Aurora Replicas”. Aurora Auto Scaling creates and manages CloudWatch alarms that trigger the scaling policy and calculates the scaling adjustment based on the metric and target value. The scaling policy adds or removes Aurora Replicas as required to keep the metric close to the specified target value.

Which metric to use depends on the architecture and workload of your application. If you have to run CPU intensive database queries, measuring the CPU utilization can be a good idea. If your queries are simple but you need to scale reads and writes, then measuring the number of connections could be the way to go.

Keep in mind that a Scaling Policy can be based only on one metric, but you can create more than one Scaling Policies. For this tutorial, you can select “Average connections of Aurora Replicas”.

2.5 - For Target value, enter “20”.

It means Aurora Auto Scaling will add Aurora Replicas if the number of connections reaches the target value of 20, and will remove extra Replicas if it gets below that target value. In all cases, Aurora Auto Scaling only removes Aurora Replicas that it created—never those created by you.

Cluster capacity details

2.6 - For minimum capacity, enter "1".

2.7 - For maximum capacity, enter "2".

The numbers for minimum and maximum capacity can be modified later. Which values to use in a production environment will depend on your estimates for workload, number of connections, and budget. The Aurora Replicas created by Aurora Auto Scaling are the same DB instance class as the one used for the primary instance.

Review and continue

2.8 - Review the fields and click on “Add policy”.

Step 3: Modify a scaling policy

3.1 - Click on your Aurora DB cluster.

3.2 - Click on “Logs & events”.

3.3 - Select your Auto scaling policy.

3.4 - Click on “Edit”.

Cluster capacity details

3.5 - Change the maximum capacity to 4.

3.6 - Click on “Save”.

Step 4: Delete a scaling policy

4.1 - Click on your Aurora DB cluster.

4.2 - Click on “Logs & events”. 

4.3 - Select your Auto scaling policy.

4.4 - Click on “Delete”.

Step 5: Delete your cluster

To finish this tutorial, you will learn how to delete your Aurora DB cluster when it's not needed anymore. In order to delete your Aurora DB cluster, go to the RDS Dashboard and follow these instructions: 

5.1 - Select “Databases” on the left pane.

This will show you a list of all your Aurora DB clusters.

5.2 - Select the reader instance of the Aurora DB cluster you created for this tutorial.

5.3 - Click on “Actions” and select “Delete”.

5.4 - You will be asked to confirm the action. Type “delete me” and click on “Delete”.

5.5 - Select the writer instance of the Aurora DB cluster you created for this tutorial.

5.6 - Click on “Actions” and select “Delete”.

You will be asked if you want to create a final backup. That's usually a good idea, but it's not necessary for this tutorial.

5.7 - Uncheck the box for “Create final snapshot” and check the box for “I acknowledge...”.

5.8 - Type “delete me” and click on “Delete”.

The status of your cluster will change to “Deleting”. At this point, you can also delete the security group you created if you determine you won't need it anymore.

Congratulations

You have created an Aurora DB cluster with Auto Scaling. You have learned how to automatically adjust the capacity of the Aurora DB cluster by adding or removing read replicas based on the needs of your application.

Was this tutorial helpful?

Thank you
Please let us know what you liked.
Sorry to disappoint you
Is something out-of-date, confusing or inaccurate? Please help us improve this tutorial by providing feedback.