Perform write operations to MySQL Database instance Read Replica

In any situation, if the original database instance is down or the entire region is down, we can provide read-write permission to the Read-only replica. In that case, that Read Replica will act like Database Master Server. Then, we need to make that database instance available in multiple availability zones using Multi-AZ service. Also, we need to create one more replica for that server to resolve highly scalability issue.

To perform this practice, we must have one database instance and also we should have that database instance's Read Replica.

Fig: Convert Read Replica to Read/Write Database Instance

Create a database instance using below-mentioned link:

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

Create Read Replica of that database instance using below-mentioned link:

https://redhatpanacia.blogspot.com/2019/07/cross-region-rds.html

Steps we need to follow:

1. Delete the database named 'webshack-db-inst' from N. Virginia
2. Create a Parameter group
3. Update the Parameter Group
4. Apply this Parameter group to the Database instance
5.     Put some data in the database 'webshack-database'

Step 1: Delete the database named 'webshack-db-inst' from N. Virginia:

AWS ➔ Services ➔ RDS ➔ Databases ➔ click on 'webshack-db-inst' database ➔ Actions ➔ Delete ➔ untick Create final snapshot ➔ tick on I acknowledge… ➔ in the text bar type 'delete me' ➔ Delete.

This will take some time to delete the database

Step 2: Create a Parameter group:

AWS ➔ Services ➔ RDS ➔ Parameter groups ➔ Create parameter group ➔ Parameter group family: select the version for your application ➔ Group name: webshack-read-write-db ➔ Description: Used to escalate read-only DB to read write ➔ Create.

Step 3: Update the Parameter Group:

AWS ➔ Services ➔ RDS ➔ Parameter groups ➔ Select parameter group ➔ click on Parameter group actions ➔ Edit…

Fig: Update Parameter Group

…in search bar search 'read_only' ➔ tick in front of read_only option ➔ click on the dropdown list written as {TrueIfReplica}…

Fig: Provide write access

…update from {TrueIfReplica} to 0 ➔ Save changes.

But these changes are performed in the Parameter group only. Now we will apply this parameter group in the database instance. Then, that database instance will be converted from read-only to read-write.

Step 4: Apply this Parameter group to the Database instance:

AWS ➔ Services ➔ RDS ➔ Databases ➔ Select Database ➔ Modify ➔ navigate to DB parameter group ➔ click on the drop-down list ➔ select 'websahck-read-write-db' ➔ Continue ➔ click on Apply Immediately (otherwise the update will reflect on the next scheduled maintenance) ➔ Modify DB instance.

This will take some time to modify the database. Once the database instance is updated, we will be able to put some data in our database named 'webshack-database'.

Step 5: Put some data in the database 'webshack-database':

Login into the Jump Server, using PuTTY in Ohio region ➔ login into MySQL and use commands:

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 )
);

This will run and provide the output:

          Query OK, 0 rows affected (0.01 sec)

Fig: Create a table in the database

Note: To make this instance Multi-AZ we need to go to the 'Modify' and then select Multi-AZ also you need to enable auto-backup.

Enjoy!