AWS RDS MySQL Multi AZ setup and Read Replicas

Stefan Pöltl

Feb 23·4 min read

In this post we take a look what AWS RDS offers to be a high performant and resilient database system. RDS has two capabilities called Multi AZ and read replicas to get this done.

What means multi AZ on RDS?

• Feature to keep your database service running and make it resilient
• Another RDS instance(replica) gets configured to work in another availability zone but in the same region as your primary instance
• Basically it’s a failover option for your primary RDS instance
• It’s not a read replica!
• Data replications works synchroniously between availability zones
• MySQL, Oracle, PostgreSQL and MariaDB support failover mechanism that is managed completely by AWS

What happens on failover?

AWS updates the DNS record in 60 to 120 seconds and points it to the failover replica.

When gets a failover scenario triggered?

• Patching maitenance
• Failure in the availablity zone
• Host failure
• DB instance class gets modified
• Instance rebooted with failure
• AWS triggers an event “RDS-EVENT-0025” when the failover is done (SNS Notification possible)

How to enable the multi-az option on my db?

With the AWS CLI it’s really easy:

aws rds modify-db-instance --db-instance-identifier my-db --multi-az

In the configuration tab in the management console you can see the following on your db instance:

Test failover

First of all we need to check the current availability zones with the aws cli:

aws rds describe-db-instances --db-instance-identifier my-db --output json --query='DBInstances[*].[AvailabilityZone,SecondaryAvailabilityZone]'

Output(availabilityzone, secondaryavailablityzone):

Let’s reboot this instance with the failover option and check the availability zone output again. Use the management console:

Or AWS CLI:

aws rds reboot-db-instance --db-instance-identifier my-db --force-failover

Now the availablity zones change and the secondary replica takes over:

The log output in the management console looks like this:

Read Replicas

• Are not used for failover to get a resilient DB service
• Allow read only access to your applications via a seperate instance
• Get rid of high load on the primary instance
• Available for MySQL, PostgreSQL, MariaDB, Oracle, SQL Server and Aurora

How to add a read replica to my database system?

As already seen above, the AWS cli offers the simplest way to create a read replica:

aws rds create-db-instance-read-replica --db-instance-identifier my-db-replica --source-db-instance-identifier my-db

For most of the cases this fits perfectly, because attributes like instance type or port are taken from the source db instance.

What happens when a read replica gets up and running?

• A snapshot is taken and a new instance starts in another AZ
• When there is a secondary replica available, the snapshot gets taken from this instance instead of the primary one to keep the primary instance performant
• The read replica keeps an asynchronous link to the primary instance

Benefits of Read Replicas

• Multiple Read Replicas are allowed per primary instance
• Scale up your read performance by adding more Read Replica instances
• Read Replicas can be deployed over different regions
• A Read Replica can take over and work as a primary instance in case of an incident
• If the primary instance fails and secondary replica takes over, RDS automatically redirects the Read replicas to the new primary instance as source
• Read traffic can still be served during a maintenance window of the primary instance

Which MySQL Versions and requirements are necessary for Read Replicas?

• Version 5.6 or higher
• Retention value for autmatic backups on the primary instance needs to be set to 1 or higher
• InnoDB storage engine, which you should use anyway :)

Summary

As you can see you don’t need to be a database admin with several years of experience to manage your database cluster. The managed database service RDS makes it really easy to get a faulty tolerant and high performant database up and running in almost no time.