Load Balancing MySQL Database with Read Replicas on Alibaba Cloud

Abstract

Alibaba Cloud MySQL RDS Read Replicas provide enhanced performance and durability for database (DB) instances. This feature makes it easy to elastically scale out beyond the capacity constraints of a single DB Instance for read-heavy database workloads.

You can create one or more replicas of a given source DB Instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput. Read replicas can also be promoted when needed to become standalone DB instances. Read replicas are currently made available for MySQL RDS.

Introduction

With the creation of multiple replicas for distributing the read traffic for a MySQL database, managing the traffic for these instances could be a complicated task to perform.

The most common options to load balance traffic to the MySQL instances would be to either use load balancing using the client-side MySQL library or setting up an HAProxy in front of the read replicas. Both these options have their share of drawbacks and are difficult to manage.

This article will introduce a powerful feature provided by Alibaba Cloud ApsaraDB for RDS to automatically split the Read and Write requests between the instances.

Prerequisites

Existing Alibaba Cloud ApsaraDB RDS for MySQL database. If you have an existing MySQL database hosted elsewhere, you'll need to migrate your database to Alibaba Cloud.
Multiple Read Replicas enabled for the database.
The instance is MySQL 5.6 High-Availability Edition or Finance Edition, or MySQL 5.7 High-Availability Edition and is a master instance.
The instance has at least one read-only instances. To create a read-only instance, see Create read-only instance.
The database proxy is enabled for the instance. To enable the database proxy, see Database proxy.

Note: Currently the read/write splitting function does not support the instances located in Asia Pacific NE 1 (Japan), Germany 1 (Frankfurt), Asia Pacific SE 2 (Sydney), Middle East 1 (Dubai) or Singapore.

Setup

To enable Read/Write Splitting:

Go to the RDS Management Console, select the appropriate RDS instance.
Under Basic information, click on the link to request a Read / Write Address.

Once you are on the Connection Options Tab (You could skip the earlier step and come here directly), click on Enable Now.

You will now be provided with the list of Master and Read Replica instances.

You can either choose to follow the standard settings (No Read to Master Instance and equal split to all Read Replicas) or have a custom setting to distribute the load according to your requirement.

Note: Selecting the Internet Address option will enable an Internet Address along with the proxy setup

It might take a few minutes for the network interface to be enabled if internet option is selected. Once it's finished, the setup options can be reviewed from the Connection Options tab.

Conclusion

This is a very powerful feature from Alibaba Cloud ApsaraDB for RDS and will come handy when you must manage multiple read replica instances. This setup takes away the headache of manually managing the distribution of load to the read replicas. This setup can also ensure that the failed read replicas are not routed with any client traffic.

To learn more about read/write splitting on Alibaba Cloud, read the official documentation through this link.

Community

Load Balancing MySQL Database with Read Replicas on Alibaba Cloud

Abstract

Introduction

Prerequisites

Setup

Conclusion

Read previous post:

Read next post:

Sabith

You may also like

Comments

Sabith

Related Products

PolarDB for MySQL

AnalyticDB for MySQL

Database for FinTech Solution

ApsaraDB RDS for MySQL