Cross Region RDS MySQL DB Read Replication

We are creating read replica to resolve 'Highly Scalability' issue, which is easily not available in a traditional data center.

As the name shows, a Read Replica is the replica of the same database. But by this Read Replica, we can perform read operations only. We cannot write data from any Read Replica. In Read Replica, a snapshot is taken of the primary database. All traffic between the source and the destination database is encrypted for Read Replica.

Fig: Cross Region RDS

Important: For this practical, we must have one active Database Server, which you can create by using below-mentioned link:

https://redhatpanacia.blogspot.com/2019/07/rds-multi-az.html

To create a Read Replica, Auto-Backup should be enabled in the original database instance. To verify that auto backup is enabled or not navigate to:

AWS ➔ Services ➔ RDS ➔ Databases ➔ click on database ➔ click on 'Maintenance and backup' ➔ in the Backup section you can see that Auto-Backup is enabled or not.

As my original database is in N. Virginia region, now, I am going to create read replica in Ohio region.

Steps we need to follow:

1. Create read replica
2. Update Security Group
3. Create Jump Server in the Ohio Region
4. Login into the Jump server and access read replica instance using endpoint
5. Verify database and try to create one more table under 'webshack_database' database

Step 1: Create a read replica:

AWS ➔ Services ➔ RDS ➔ Databases ➔ select database ➔ Actions ➔ Create read replica ➔ Destination region: US East (Ohio) select the region in which you want to create read replica ➔ Destination DB subnet group: None ➔ Availability zone: No preference ➔ Publicly accessible: No ➔ DB instance class: t2.micro ➔ Multi-AZ deployment: No (this will act like only a replica of main database, so there is no need to make it Multi-AZ. But in future, if we convert this read replica into read write, then we need to provide Multi-AZ) ➔ Storage type: General Purpose (SSD)…

Settings:

…Read replica source: webshack-db-inst (this will by default select the database instance from which you are creating read replica) ➔ DB instance identifier: webshack-db-inst-replica1 ➔ Database port: 3306 ➔ IAM DB authentication: Disable ➔ Monitoring: Disable enhanced monitoring ➔ Maintenance: provide Yes if you want to allow auto minor version upgrade ➔ Create read replica ➔ our read replica creation has been initiated ➔ Close.

During the creation, AWS will copy the snapshot to the Ohio region, after that it will create database from this snapshot and syncing will be performed.

Step 2: Update Security Group:

AWS ➔ Services ➔ RDS ➔ "make sure you are in the Ohio region" ➔ Databases ➔ click on the database named 'webshack-db-inst-replica1' ➔ navigate to Connectivity & security tab ➔ here you can see your security group. If port number 3306 is not enabled, then update the port or create a new security group and add to the DB instance.

Step 3: Create Jump Server in the Ohio Region:

AWS ➔ Services ➔ EC2 ➔ instances ➔ Launch Instance ➔ [*] Free tier only ➔ select AMI: Amazon Linux 2 AMI (HVM) ➔ Next ➔ in Configuration instance details, Network: select your VPC ➔ Subnet: select any public subnet ➔ Advanced settings: User data: install mysql using script:

          #!/bin/bash
          yum install -y mysql

Next ➔Name tag: JumpServer-ohio ➔ Security group: create a new security group or select any existing one, but port number 22 and 3306 should be enabled ➔ Next ➔ Launch ➔ select key-pair ➔ Launch instance ➔ View instance.

Step 4: Login into the Jump server and access read replica instance using endpoint:

Login into the Jump server using public IP from EC2 in PuTTY ➔ verify mysql is installed or not by using command 'which mysql'. If as a result we receives binaries (/usr/bin/mysql) means mysql is installed by UDS which we provided.

Now access read replica instance:

AWS ➔ Services ➔ RDS ➔ "make sure you are in the Ohio region" ➔ Databases ➔ click on the database named 'webshack-db-inst-replica1' ➔ navigate to Connectivity & security tab and copy endpoint.

Now, go to the Jump Server terminal and use below-mentioned command to access read replica instance:

$ mysql -u divakar -h webshack-db-inst-replica1.csuosiat5ubb.us-east-2.rds.amazonaws.com -P 3306 -p
Enter password: redhat123

In the above command:
-u = username
-h = hostname (in our case we are using endoint path)
-P = port number
-p = password

Step 5: Verify database and try to create one more table under 'webshack_database' database:

List all the database:
MySQL [(none)]> show databases;

Change the database to webshack_database:
MySQL [(none)]> use webshack_database;
MySQL [(webshack_database)]>

List all tables under this database:
MySQL [webshack_database]> show tables;

Create one more table using command:
MySQL [webshack_database]> CREATE TABLE dlr_tbl(
   dlr_id INT NOT NULL AUTO_INCREMENT,
   dlr_firstname VARCHAR(100) NOT NULL,
   dlr_surname VARCHAR(100) NOT NULL,
   PRIMARY KEY ( dlr_id )
);

As we try to create a table we get an error stating that MySQL server is running with the --read-only option:

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

Fig: MySQL server is running with the --read-only option

But we are able to see the databases and can easily read tables. So, this instance is working for read-only queries. All write queries will be performed only by RDS master server.