How To Make My RDS MySQL Read Replica Writable?

We know that we create a Read replica from the RDS DB instance for many reasons.

• To have replica for the master RDS DB instance for data HA.

• To balance the load by forwarding the read queries to read-replica DB instance.

• To promote read replica as master in case of master Failure.

We have discussed about how to create read replica for RDS DB instance here.

But there are some scenarios where we want our read replica to be writable.

In this guide , We will learn How to make the read replica writable in few steps.

Making Read Replica Writable

Modifying the read-replica to be writable is only supported for MySQL and MariaDB because the parameter group cannot be modified for Amazon Aurora.

To configure the Amazon RDS read replica to be read and write , We need to execute the following steps.

• Create a new DB parameter group for the RDS DB instance
• Modify the DB parameter group
• Change the parameter group of the RDS Instance

By default when we create an RDS instance , It will use the default parameter group which cannot be modified.

But If you already have an RDS instance attached with custom parameter group you can directly modify it without creating a new DB instance.

Creating A New DB Parameter Group

To create a new DB parameter group , Login to RDS console .

To check whether we are using default or custom parameter group.

Click the RDS instance , Click Configuration

There you can find the parameter group currently used by the RDS instance.

if you see that the DB instance is using the default parameter group , We need to create a new DB parameter group only then we will be able to make the RDS read replica writable by making the configuration changes in the parameter group.

To create a new DB parameter group , In the left navigation pane , Choose Parameter groups

Then click Create parameter group

Under Create parameter group , It will ask for the DB engine version currently used by the RDS read replica instance.

Provide a name for the group and then click Create

We have now created a custom DB parameter group. We have to modify the custom parameter group

Modifying The Custom DB Parameter Group

To modify the custom parameter group , choose the parameter group you have created.

and then click Edit parameters

Search for the following key : read_only

and set the value to 0 . , i.e read_only = 0

and then click Save changes

Now its time to change the parameter group of the RDS instance.

Modifying The RDS Instance Parameter Group

In the navigation pane , Choose Databases ,

Select the DB instance for which you want to change the parameter group.

Choose Modify , From Database options , Choose the parameter group that you want to associate with the RDS instance.

Scroll down and Click Continue

And click Modify DB Instance.

Now that the instance state will be in Modifiying state ,

and then instance will be Available with the new parameter group.

But the parameter group wont take effect unless we reboot the RDS instance.

The parameter group says Pending-reboot

To reboot the RDS instance , Under Actions , Choose Reboot

Once the instance state becomes available , The new DB parameter group will take effect which in turn makes the read replica DB instance – Writable.

Conclusion

We have successfully configured the read-replica to accept both read and write actions.

Hope you find it helpful.

Please check out my other publications.