RDS Read Replica using CFN

TOC

Create RDS Read Replica using CloudFormation

The following pages deal with the placement of Multi-AZ.a

One of the features offered by RDS is read replica.

Updates made to the primary DB instance are asynchronously copied to the read replica. You can reduce the load on your primary DB instance by routing read queries from your applications to the read replica. Using read replicas, you can elastically scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads.
Working with read replicas

In this case, we will use CloudFormation to create a read replica.
In addition, we will also review how to promote the read replica.

構築する環境

Diagram of RDS Read Replica using CloudFormation

Create two RDS DB instances.
One is the primary instance and the other is the read replica instance.
DB instance type is MySQL.

Create an EC2 instance as well.
It will be used as a client to connect to the DB instance.
The instance is the latest version of Amazon Linux 2.

Create two types of VPC endpoints.

The first is for SSM.
This is for connecting to an EC2 instance in a private subnet using SSM Session Manager.

The second is for S3.
This is for accessing yum repositories built on the S3 bucket.

CloudFormation template files

The above configuration is built with CloudFormation.
The CloudFormation templates are located at the following URL

https://github.com/awstut-an-r/awstut-fa/tree/main/111

Explanation of key points of the template files

RDS

Resources:
  PrimaryInstance:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    Properties:
      AllocatedStorage: !Ref DBAllocatedStorage
      DBInstanceClass: !Ref DBInstanceClass
      DBInstanceIdentifier: dbinstance
      DBSubnetGroupName: !Ref DBSubnetGroup
      Engine: !Ref DBEngine
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBMasterUsername
      MasterUserPassword: !Ref DBMasterUserPassword
      VPCSecurityGroups:
        - !Ref DBSecurityGroup

  ReadReplicaInstance:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    Properties:
      DBInstanceClass: !Ref DBInstanceClass
      SourceDBInstanceIdentifier: !Ref PrimaryInstance
Code language: YAML (yaml)

The first resource is the primary instance.
No special configuration is required to create a read replica.

The second resource is the read replica instance.
Specify the primary instance in the SourceDBInstanceIdentifier property.

(Reference) EC2 instance

Resources:
  Instance:
    Type: AWS::EC2::Instance
    Properties:
      IamInstanceProfile: !Ref InstanceProfile
      ImageId: !Ref ImageId
      InstanceType: !Ref InstanceType
      NetworkInterfaces:
        - DeviceIndex: 0
          SubnetId: !Ref InstanceSubnet
          GroupSet:
            - !Ref InstanceSecurityGroup
      UserData: !Base64 |
        #!/bin/bash -xe
        yum update -y
        yum install -y mariadb
Code language: YAML (yaml)

To access a DB instance from an EC2 instance, a client package must be prepared.
In this case, we will use user data to install the package.

For more information on user data, see the following page

For more information about client packages to connect to various RDS from Amazon Linux 2, please refer to the following page

Architecting

Use CloudFormation to build this environment and check the actual behavior.

Create CloudFormation stacks and check resources in stacks

Create CloudFormation stacks.
For information on how to create stacks and check each stack, please refer to the following page

After checking the resources in each stack, information on the main resources created this time is as follows

EC2 instance: i-08ac3e2191dbd318d
Primary DB instance: dbinstance
Endpoint DNS name for primary DB instance: dbinstance.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
Read replica DB instance: fa-111-rdsstack-1lrqk70uga1zi-readreplicainstance-whcb2xnwbhfb
Endpoint DNS name for the read replica DB instance: fa-111-rdsstack-1lrqk70uga1zi-readreplicainstance-whcb2xnwbhfb.cl50iikpthxs.ap-northeast-1.rds. amazonaws.com

Check each resource from the AWS Management Console.

Check RDS.

Two instances have been created.
Looking at the Role, one is the primary and the other is the read replica.

Check the details of each.

You can see that the two DB instances are located on subnets in different AZs.

Check Action

Accessing Primary Instance

Connect to the primary instance from the EC2 instance.
Use SSM Session Manager to access the EC2 instance.

% aws ssm start-session --target i-08ac3e2191dbd318d
...
sh-4.2$
Code language: Bash (bash)

For more information on SSM Session Manager, please see the following page

Check the execution status of the EC2 instance initialization process with user data.

sh-4.2$ sudo yum list installed | grep mariadb
mariadb.aarch64                       1:5.5.68-1.amzn2                 @amzn2-core
mariadb-libs.aarch64                  1:5.5.68-1.amzn2                 installed

sh-4.2$ mysql -V
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (aarch64) using readline 5.1
Code language: Bash (bash)

You can see that the MySQL client package has been successfully installed.

Use this client package to connect to the DB instance.

sh-4.2$ mysql -h dbinstance.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com  -P 3306 -u testuser -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.28 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 
Code language: Bash (bash)

Connection made.

Create a test database and tables and store test data.

MySQL [(none)]> CREATE database test;

MySQL [(none)]> use test;

MySQL [test]> CREATE TABLE planet (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY(id));

MySQL [test]> INSERT INTO planet (name) VALUES ("Mercury");

MySQL [test]> select * from planet;
+----+---------+
| id | name    |
+----+---------+
|  1 | Mercury |
+----+---------+
Code language: Bash (bash)

It worked fine.

Accessing Read Replica instance

Then connect to the read replica instance.

sh-4.2$ mysql -h fa-111-rdsstack-1lrqk70uga1zi-readreplicainstance-whcb2xnwbhfb.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com  -P 3306 -u testuser -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.28 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [testdb]>
Code language: Bash (bash)

This connection was also successful.

Try it out and see how it works on read/write.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

MySQL [(none)]> use test;

MySQL [test]> select * from planet;
+----+---------+
| id | name    |
+----+---------+
|  1 | Mercury |
+----+---------+

MySQL [test]> INSERT INTO planet (name) VALUES ("Venus");
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
Code language: Bash (bash)

The read was successful.
The contents written in the primary instance are also reflected in the read replica.

Writing, on the other hand, failed.
This is because this instance is read-only.

Read Replica promotion

Read Replicas can be promoted.

After you promote the read replica to a new DB instance, it’s just like any other DB instance.

Because the promoted DB instance is no longer a read replica, you can’t use it as a replication target.
Promoting a read replica to be a standalone DB instance

This time, we will use the AWS CLI to elevate the read replica.

$ aws rds promote-read-replica \
--db-instance-identifier fa-111-rdsstack-1lrqk70uga1zi-readreplicainstance-whcb2xnwbhfb
Code language: Bash (bash)

Once again, check the status of the read replica side.

The Role is “Instance”, which indicates that it has been promoted from a read replica to a regular instance.

The event log also reads that a restart was performed after the instance was promoted to a normal instance.

Connect to this instance again.

sh-4.2$ mysql -h fa-111-rdsstack-1lrqk70uga1zi-readreplicainstance-whcb2xnwbhfb.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com  -P 3306 -u

MySQL [(none)]> use test;

MySQL [test]> INSERT INTO planet (name) VALUES ("Venus");

MySQL [test]> select * from planet;
+----+---------+
| id | name    |
+----+---------+
|  1 | Mercury |
|  2 | Venus   |
+----+---------+
Code language: Bash (bash)

This time the write operation was successful.
The write operation is now possible because it has been promoted from a read replica to a normal instance.

Summary

A read replica was created using CloudFormation.
In addition, we reviewed how to promote the read replica.