Let's take a quick look at a demo that shows how easy it is to set up and use a single master Aurora database with multiple read replicas.

In this example, I’ll perform the following sequence:

1. Launch a single master Aurora MySQL database cluster with a single read replica within the AWS RDS console.
2. Connect to the master and create a new MySQL database named demo, and within it create a new table named course.
3. Confirm that when connecting using the writer connection endpoint that SQL inserts can be performed successfully.
4. Confirm that when connecting using the reader connection endpoint that SQL inserts cannot be performed.
5. Use the AWS RDS console to add an additional read replica to demonstrate how easy it is to scale out read capacity.
6. Connect to the new read replica and confirm that the data has been successfully replicated and can be returned.
7. Perform a failover - converting one of the read replicas into the primary.

Ok, let's begin. Starting off in the AWS RDS console - I’ll create a new Amazon Aurora MySQL single master with multiple readers database.

Under the Database features, I’ll select the “One writer and multiple readers” option. I’ll set the DB cluster identifier to be “cloudacademy-db-singlemaster”. I’ll configure the credentials to be admin with a password of cloudacademy. For instance size - I’ll select the burstable classes option and then simply choose the smallest size - which happens to be the db.t2.small instance.

Under the Availability and Durability section, I’ll choose the option to create and deploy a read replica.

I’ll then deploy it into an existing Multi AZ VPC. For security groups - I’ll simply allocate an existing one which allows inbound TCP connections to the default Mysql port 3306. Connections will be made from an existing bastion host which has the standard MySQL client already installed on it.

Ok with all that in place, I can now go ahead and click on the “Create Database” button at the bottom. Provisioning is fairly quick and takes just a matter of minutes to complete.

Now that the database is ready I’ll take a copy of the writer connection endpoint. Next, I’ll jump into my local terminal and connect to the bastion host using SSH.

Once connected I’ll use the MySQL client utility to connect to the master database instance using the writer connection endpoint just copied. Once authenticated into the master database instance - I’ll simply create a new database named demo and then create a new course table within it. With the course table created I’ll confirm that I can perform inserts to confirm that writes can be performed - which is indeed the case.

Next, let's now try connecting via the reader connection endpoint - and perform the same inserts. This time as expected we are unable to perform inserts since the reader connection endpoint connects to the read replica.

Next - I’ll add an additional read replica into the cluster. To do so I’ll jump back into the AWS RDS console and here I’ll select the “cloudacademy-db-singlemaster” database and then under the Actions drop down, select the Add Reader option. Within the Add Reader section, I’ll leave all of the defaults as is and give the new read replica the identifier “cloudacademy-db-replica” and then click the Add reader button at the bottom.

Refreshing the current page you can now see that the Aurora database cluster consists of a single master and 2 read replicas.

Checking the new read replica status we can see that it is still in Creating status. We need to wait until it reaches the Available status - once it does I’ll copy the instance endpoint and then connect to it using the MySQL client on the bastion host. Once connected I’ll simply perform a select all on the course table to confirm that the data has been successfully replicated - which it has.

Finally, I’ll jump back into the RDS console. With the new read replica still selected, I’ll perform a failover to it - by selecting the failover option within the Actions dropdown menu. On the confirmation page, I’ll proceed by clicking the failover button. This kicks off the failover which will take a minute or 2 to show up within the current page. Let’s now refresh it to confirm that the cloudacademy-db-replica has now been promoted to the role of writer which it has - and that the original master is now a read-only as per the reader role which has been allocated to it.

In summary, this demonstration highlighted the following:

1. How to provision a new Aurora MySQL single master database with multiple read replicas.
2. Confirmed that writes can only be made against the master and that the read replicas are for reading only.
3. How to add an additional read replica into the database cluster and have data automatically replicated into it.
4. And how to perform a failover from the master to one of the read replicas.

If you’ve followed along, don’t forget to terminate your database cluster to avoid ongoing charges.