Read Replica and Endpoints of Aurora Cluster

TOC

Read Replica and Endpoints of Aurora Cluster

This course is about designing a high-performance architecture, which is one of the topics covered by AWS SAA.
We will create an Aurora read replica and check the behavior of the Aurora endpoints that are provided by default.
By using different access endpoints, it will be possible to separate the instances to write to and read from.

Environment

Diagram of Read Replica and Endpoints of Aurora Cluster.

Create a total of three private subnets in two AZs.
Deploy the Aurora cluster so that it spans two private subnets with different AZs. Create two DB instances in the cluster. The Aurora cluster will be of type MySQL.
Place an EC2 instance on one of the private subnets. The EC2 instance will be based on the latest version of Amazon Linux2 AMI.
Create a VPC endpoint for S3, in order to install a client to connect to the Aurora cluster on the EC2 instance.

CloudFormation template files

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

https://github.com/awstut-an-r/awstut-saa/tree/main/02/006

Explanation of key points of template files

We will cover the key points of each template file to configure this architecture.

Creating read replica on Aurora cluster

Check the Aurora cluster.

Resources:
  DBCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      DatabaseName: !Ref DBName
      DBClusterIdentifier: !Sub ${Prefix}-DBCluster
      DBSubnetGroupName: !Ref DBSubnetGroup
      Engine: !Ref DBEngine
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBUser # cannot use "-".
      MasterUserPassword: !Ref DBPassword # cannot use "/@'"
      StorageEncrypted: true
      VpcSecurityGroupIds:
        - !Ref DBSecurityGroup

  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupName: !Sub ${Prefix}-dbsubnetgroup # must be lowercase alphanumeric characters or hyphens.
      DBSubnetGroupDescription: Test DBSubnetGroup for Aurora.
      SubnetIds:
        - !Ref PrivateSubnet2
        - !Ref PrivateSubnet3
Code language: YAML (yaml)

Unlike regular RDS, Aurora forms clusters.

An Amazon Aurora DB cluster consists of one or more DB instances and a cluster volume that manages the data for those DB instances. An Aurora cluster volume is a virtual database storage volume that spans multiple Availability Zones, with each Availability Zone having a copy of the DB cluster data.
Amazon Aurora DB clusters

The cluster is used to configure settings around database engine type and version, user information, and networking.
Specify the type and version of the DB instance to be created in the cluster in the Engine and EngineVersion properties. In this case, we will specify as follows to create a DB instance of the latest MySQL 8.0 version (as of March 2022).

Engine property: aurora-mysql
EngineVersion property: 8.0.mysql_aurora.3.01.0

Note that specifying “aurora” for the Engine property will create a MySQL 5.6 version instance.

Engine

The name of the database engine to be used for this DB cluster.
Valid Values: aurora (for MySQL 5.6-compatible Aurora), aurora-mysql (for MySQL 5.7-compatible Aurora), and aurora-postgresql
AWS::RDS::DBCluster

The MySQL user is configured with the MasterUsername and MasterUserPassword properties. In this case, both properties are set as follows.

MasterUsername property: testuser
MasterUserPassword property: Passw0rd

The DatabaseName property allows you to specify the name of the database to be created by default. In this case, we will specify to create a database named “testdb”.

In the DBSubnetGroupName property, set the subnet where the cluster will be deployed. This time, define the DB subnet group resource to specify two private subnets, and specify this in this property.

In the VpcSecurityGroupIds property, specify the security group to be applied to this cluster, allowing communication from EC2 instances to the MySQL standard port (3306/tcp).

Create Read Replica on Aurora cluster

Confirm the DB instance to be created in the Aurora cluster. The point is how to create a read replica.

Resources:
  DBInstance1:
    Type: AWS::RDS::DBInstance
    Properties:
      DBClusterIdentifier: !Ref DBCluster
      DBSubnetGroupName: !Ref DBSubnetGroup
      DBInstanceIdentifier: !Sub ${Prefix}-DBInstance1
      DBInstanceClass: !Ref DBInstanceClass
      Engine: !Ref DBEngine
      AvailabilityZone: !Sub ${AWS::Region}${AvailabilityZone1}
      PubliclyAccessible: false

  DBInstance2:
    Type: AWS::RDS::DBInstance
    Properties:
      DBClusterIdentifier: !Ref DBCluster
      DBSubnetGroupName: !Ref DBSubnetGroup
      DBInstanceIdentifier: !Sub ${Prefix}-DBInstance2
      DBInstanceClass: !Ref DBInstanceClass
      Engine: !Ref DBEngine
      AvailabilityZone: !Sub ${AWS::Region}${AvailabilityZone2}
      PubliclyAccessible: false
Code language: YAML (yaml)

This time, since we are creating two DB instances, we define two instances with similar settings.
When you create a second or later instance in an Aurora cluster, it will automatically act as the read replica.
One will act as the primary instance and the other as the read replica. Which role it will actually operate in must be confirmed after startup.

Install MySQL client with user data

Check the EC2 instance. The point is the initialization process of the instance by user data.

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

The role of the EC2 instance this time is to act as a client to access a MySQL-type RDS instance. Therefore, we need to install a MySQL client.
On Amazon Linux 2, the MySQL client can be installed from the mariadb package. This package can be obtained from the Amazon Linux repository built on the S3 bucket. In this case, the repository is accessed via the VPC endpoint for S3.
For information on client tools and connection methods for RDS instances, please refer to the following page

There are several EC2 instance initialization processes, but this time we will use user data.
User data is set with the UserData property. This time, after installing the repository containing the MySQL client, we will describe the process of installing the client. This section describes the installation process of the mariadb package described above.
For details on the initialization process for instances containing user data, please also refer to the following page

Architecting

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

Create CloudFormation stack and check resources in stack

Create a CloudFormation stack.
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 in this case is as follows

Aurora cluster: saa-02-006-dbcluster
DB instance 1: saa-02-006-dbinstance1
DB instance 2: saa-02-006-dbinstance2
EC2 instance: i-0a58e05aa21df2ea8

The AWS Management Console also checks the status of resource creation.
First, check the resources for the entire RDS.

We can see that an Aurora cluster has indeed been created, and that two DB instances have been created within it.
We can also see that the following two endpoints have been created in the cluster　

Cluster endpoint: saa-02-006-dbcluster.cluster-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
Leader endpoint: saa-02-006-dbcluster.cluster-ro-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com

In addition, you can see the roles of the two instances.
DB instance 1 is the primary instance, as it is labeled “Writer instance”.
DB instance 2 is the “Reader instance,” which means it is a read replica.

The status of each instance is also checked.

Individual endpoints (instance endpoints) are also created for each instance.

DB instance 1 instance endpoint: saa-02-006-dbinstance1.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
DB instance 2 instance endpoint: saa-02-006-dbinstance2.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com

Checking MySQL Client

Now that we are ready, we can check Aurora’s behavior.
The check will be performed after accessing the EC2 instance.
To access an EC2 instance, use SSM Session Manager. For details, please refer to the following page.

% aws ssm start-session --target i-04eaedaa85d35cb48

Starting session with SessionId: root-0ab3667141ef0d68d
sh-4.2$
Code language: Bash (bash)

Check the installation status of the packages configured in the 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
Code language: Bash (bash)

The mariadb package is indeed installed.

Check the mysql client version.

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

The version is displayed and the client ran successfully.

Resolving Name of Aurora Cluster Endpoints

Let’s try name resolution for the cluster endpoint we just checked.

sh-4.2$ nslookup saa-02-006-dbcluster.cluster-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
Server:		10.0.0.2
Address:	10.0.0.2#53

Non-authoritative answer:
saa-02-006-dbcluster.cluster-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com	canonical name = saa-02-006-dbinstance1.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com.
Name:	saa-02-006-dbinstance1.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
Address: 10.0.2.40
Code language: Bash (bash)

sh-4.2$ nslookup saa-02-006-dbcluster.cluster-ro-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
Server:		10.0.0.2
Address:	10.0.0.2#53

Non-authoritative answer:
saa-02-006-dbcluster.cluster-ro-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com	canonical name = saa-02-006-dbinstance2.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com.
Name:	saa-02-006-dbinstance2.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com
Address: 10.0.3.206
Code language: Bash (bash)

You can see that the two endpoints are configured with CNAME.
When you access the cluster endpoint, you are accessing the primary instance, DB instance 1.
When you access the leader endpoint, you are accessing DB instance 2, which is the read replica.

Accessing Cluster Endpoint

Now it is time to access the Aurora cluster.
First, access the cluster endpoint.

sh-4.2$ mysql -h saa-02-006-dbcluster.cluster-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p testdb
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.23 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)

We have successfully accessed testdb on the Aurora cluster.

Create a table for verification.
The columns to be created in the table are defined to be of type datetime for the purpose of storing the current date and time.

MySQL [testdb]> create table testtable (datetime datetime);
Query OK, 0 rows affected (0.03 sec)

MySQL [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.00 sec)
Code language: Bash (bash)

The table is ready.
We will actually write the current date and time and read the contents of the table.

MySQL [testdb]> insert into testtable (datetime) values (now());
Query OK, 1 row affected (0.01 sec)

MySQL [testdb]> select * from testtable;
+---------------------+
| datetime            |
+---------------------+
| 2022-03-23 08:28:46 |
+---------------------+
1 row in set (0.00 sec)
Code language: Bash (bash)

The write was successful.
From the above, we know that we can read and write to the database by accessing the cluster endpoint.

Accessing Reader Endpoint

Access the reader endpoint to check its behavior.

sh-4.2$ mysql -h saa-02-006-dbcluster.cluster-ro-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p testdb
Enter password:
...
MySQL [testdb]>
Code language: Bash (bash)

Execute the read process for testtable.

MySQL [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.00 sec)

MySQL [testdb]> select * from testtable;
+---------------------+
| datetime            |
+---------------------+
| 2022-03-23 08:28:46 |
+---------------------+
1 row in set (0.01 sec)
Code language: Bash (bash)

It can indeed be read.

Try to write to it.

MySQL [testdb]> insert into testtable (datetime) values (now());
ERROR 1836 (HY000): Running in read-only mode
Code language: Bash (bash)

An error has occurred.
A message is output stating that the write operation cannot be executed because the access is read-only.

From the above, we know that accessing the reader endpoint allows read-only access to the database.

Accessing Instance Endpoints 1

We will also access the endpoints provided in the instance to check the behavior.
First, access the primary instance.

sh-4.2$ mysql -h saa-02-006-dbinstance1.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p testdb
Enter password:
...
MySQL [testdb]>
Code language: Bash (bash)

Successfully accessed.

Execute read/write.

MySQL [testdb]> select * from testtable;
+---------------------+
| datetime            |
+---------------------+
| 2022-03-23 08:28:46 |
+---------------------+
1 row in set (0.00 sec)
Code language: Bash (bash)

MySQL [testdb]> insert into testtable (datetime) values (now());
Query OK, 1 row affected (0.01 sec)

MySQL [testdb]> select * from testtable;
+---------------------+
| datetime            |
+---------------------+
| 2022-03-23 08:28:46 |
| 2022-03-23 08:33:02 |
+---------------------+
2 rows in set (0.00 sec)
Code language: Bash (bash)

From the above, we know that we can read and write to the database by accessing the endpoint of the primary instance, which is for writing.

Accessing Instance Endpoints 2

Then access the read replica instance.

sh-4.2$ mysql -h saa-02-006-dbinstance2.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p testdb
Enter password:
...
MySQL [testdb]>
Code language: Bash (bash)

Successfully accessed.

Execute read/write.

MySQL [testdb]> select * from testtable;
+---------------------+
| datetime            |
+---------------------+
| 2022-03-23 08:28:46 |
| 2022-03-23 08:33:02 |
+---------------------+
2 rows in set (0.00 sec)
Code language: Bash (bash)

MySQL [testdb]> insert into testtable (datetime) values (now());
ERROR 1836 (HY000): Running in read-only mode
Code language: Bash (bash)

Reads were successful, but writes failed.
From the above, we can see that accessing the endpoints of the read replica instance allows only reads to the database.

Summary

The following is a summary of the verification results.

Endpoints	Read	Write
Cluster Endpoint	●	●
Reader Endpoint	●	×
Instance Endpoint (Primary Instance)	●	●
Instance Endpoint (Read Replica Instance)	●	×