How can I perform write operations to my Amazon RDS for MariaDB or MySQL DB instance read replica?

3 minute read
0

I want to perform both read and write operations in my Amazon Relational Database Service (Amazon RDS) for MariaDB or MySQL DB instance read replica. How can I do this?

Short description

Amazon RDS DB instance read replicas are read-only by design. In some scenarios, you might need to configure a DB instance read replica so that the replica is modifiable.

Resolution

If you're using Amazon RDS for MySQL or MariaDB, configure a DB instance read replica to be read/write. You can do this by setting the read_only parameter to false for the DB parameter group that is associated with your DB instance. The read_only parameter can't be modified when using other Amazon RDS engines, such as Amazon Aurora.

Note: Automations, such as backups, restore, and failover, are not impacted when you enable writes on the replica. But, if you perform writes without understanding the impact of the writes, this can cause inconsistency or replication failures.

To configure your Amazon RDS DB instance read replica to be read/write, follow these steps:

  1. Create a DB parameter group for your MySQL or MariaDB instance.
  2. Modify the parameter group.
  3. Associate your RDS DB instance with the DB parameter group.

Note: If you create a DB instance without specifying a DB parameter group, a default DB parameter group is created. This means that default parameter groups can't be modified. If you already have a custom parameter group that is associated with the instance, you don't need to create a new parameter group. For more information about DB parameter groups, see Working with parameter groups.

Create a DB parameter group

  1. Open the Amazon RDS console.
  2. In the navigation pane, from Parameter groups, choose Create parameter group.
  3. For Parameter group family, choose the parameter group family.
  4. For Type, choose DB Parameter Group.
  5. For Group name, enter the name of the new DB parameter group.
  6. For Description, enter a description for the new DB parameter group.
  7. Choose Create.

Modify the parameter group

  1. Open the Amazon RDS console.
  2. In the navigation pane, from Parameter groups, select the parameter group that you want to modify.
  3. Choose Parameter group actions, and then choose Edit.
  4. Edit the following parameter: read_only = 0
  5. Choose Save changes.

Associate your RDS DB instance with the DB parameter group

  1. Open the Amazon RDS console.
  2. In the navigation pane, from Databases, select the DB instance that you want to associate with the modified DB parameter group.
  3. Choose Modify.
    Note: The instance status is Modifying, and the parameter group is Applying.
  4. From Database options, choose the parameter group that you want to associate with the DB instance.
  5. After the instance status is Available and the parameter group is Pending-reboot, reboot the instance without failover.
    Note: The parameter group name changes immediately, but changes to the parameter aren't applied until you reboot the instance without failover.

Related information

Overview of Amazon RDS read replicas

How do I modify the values of an Amazon RDS DB parameter group?