Amazon RDS Multi-AZ Authentication

Drawbacks of single database instance installation:

1. A database is in the single subnet, means its only one AZ specific. So, no Highly Availability.
2. In case, if there is anything wrong with this database, then there is no other activity which can take care of this.
3. If there is a lot of write requests, then there could be a latency issue.

Solutions:

1. For high availability, we can perform Multi-AZ authentication.
2. For high scalability, we can create Read Replicas.
3. For write requests, we can change the instance type of the database instance.

If we have only one RDS (Relational Database Service) instance in an AZ, then we will not be able to get high availability, because if unfortunately that AZ is down or there is a hardware failure, then our production will hamper. So, we need to protect this. 

To resolve this single point of failure, we need to use Multi-AZ installation with multiple subnets. If we are using multi-AZ service but using only one AZ for each subnet, then also this will act like single point of failure. So, the primary requirement is that every subnet should be in different AZs.

Steps we need to follow:

I am going to do this practice in N. Virginia region. But Read Replica we will create in Ohio region to access Cross Region Relational Database Service (RDS).

1. Create two private subnets
2. Create a Subnet Group
3. Create Database
4. Create Jump Server
5. Login into the database instance using endpoint from RDS dashboard
6.     Create a table in 'webshack_database' database

Step 1: Create two private subnets:

AWS ➔ Services ➔ VPC ➔ Subnets ➔ Create Subnet ➔ Name tag: webshack-sub5-pvt ➔ VPC: webshack-vpc ➔ Availability Zone: us-east-1a ➔ IPv4 CIDR block: 10.0.5.0/24 ➔ Create ➔ Close.

Use same exercise to create one more private subnet. In my case, I have two private subnets, named 'webshack-sub2-pvt' in subnet 'us-east-1b' and 'webshack-sub5-pvt' in subnet 'us-east-1a'.

Step 2: Create a Subnet Group:

We are creating a Subnet Group so that we could interact with multiple Availability Zones using Master-Slave Architecture.

Fig: Master-Slave Architecture

AWS ➔ Services ➔ RDS ➔ Subnet Groups ➔ Create DB Subnet Group ➔ Name: myproddb_subgrp ➔ Description: Used for deploying Master-Slave architecture ➔ Select VPC ➔ Add subnets: Availability Zone: select availability zone in which you have subnet ➔ Subnet: select your private subnet ➔ click on Add subnet ➔ Create.

Similarly, do for another subnet as well.

Fig: Add subnets to 

Step 3: Create Database:

AWS ➔ Services ➔ RDS ➔ Databases ➔ Create Database ➔ click on any Engine option as I am using MySQL ➔ do not tick on 'Only enable options eligible for RDS Free Usage Tier'. If you tick on this option, Multi-AZ deployment will not be highlighted ➔ Next…

Specify DB Details:

…License Model: general-public-license (default) ➔ DB engine version: MySQL 5.7.22 (select according to your application requirement) ➔ DB instance class: select t2.micro from drop-down list ➔ Multi-AZ deployment: click on Create replica in different zone ➔ Storage type: General purpose (SSD) ➔ Allocate Storage: 20 GiB (minimum 20 GiB need to be allocated) ➔ Storage autoscaling: (if 20 GiB is not enough for the database, it will automatically scale the size)…

Settings:

…DB instance identifier: webshack-db-inst ➔ Master username: divakar (you can use any) ➔ Master password: redhat123 (you can use any) ➔ Confirm password: ********* ➔ Next…

Configure Advanced Settings:

…VPC: select your VPC ➔ Subnet Group: myproddb_subgrp (select your subnet group, which you created in step 2) ➔ Public accessibility: choose yes only when you want to share the database publically. In my case, I am selecting option 'No' ➔ Availability zone: no need to select an availability zone in the case of Multi-AZ ➔ VPC security groups: Create a new or choose an existing one but port number 3306 should be enabled. As you click on the existing one, this will select the default Security Group. Delete that security group.

Fig: Select Security Group

…Database name: webshack_database ➔ Port: 3306 ➔ Parameter Group and Option Group are used for optimization of database, which we will use when we update read replica to read and write ➔ IAM DB authentication: Disable (enable it only when you want to Enable IAM user to access the database) ➔ Backup: Backup retention period: this is the time till when our database's snapshot will be safe. Click on select window to provide the start time and duration for backup ➔ Enhance monitoring: enable if you want to use monitoring but as we are creating this only for learning purpose, I am disabling monitoring ➔ tick on log types, which you want to publish to Amazon CloudWatch logs ➔ Enable auto minor version upgrade, if you want to automatic upgrades to new minor versions as they are released…

Deletion protection:

…Enable deletion protection if you want to secure the database from accidental deletion ➔ Create Database ➔ View DB instance details.

Now, DB instance 'webshack-db-inst' is in creating status:

Step 4: Create and login into the Jump Server:

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

          #!/bin/bash
          yum install -y mysql

Next ➔Name tag: JumpServer ➔ Security group: create a new security group or select an existing one ➔ Next ➔ Launch ➔ select key-pair ➔ Launch instance ➔ View instance.

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

Step 5: Login to the database instance using endpoint from RDS dashboard:

AWS ➔ Services ➔ RDS ➔ Databases ➔ click on the database ➔ copy Endpoint under Connectivity and security tab.

Use below-mentioned command in Jump server to access this database instance:

$ mysql -u divakar -h webshack-db-inst.c4ev2etf3evc.us-east-1.rds.amazonaws.com -P 3306 -p

(This will prompt for master user password. Provide the password which you used at the time of creating the database instance)

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

Fig: Access Database Server

By using command 'show databases;' we can see that our database named, webshack_database is listed here.

Step 6: Create a table in 'webshack_database' database:

List all databases in this instance:
          MySQL [(none)]> show databases;

Use webshack_database database:
          MySQL [(none)]> use webshack_database;
          MySQL [webshack_database]>

We can see that database is changed from none to webshack_database.

Create table inside this database:
          MySQL [webshack_database]> CREATE TABLE cus_tbl(
             cus_id INT NOT NULL AUTO_INCREMENT,
             cus_firstname VARCHAR(100) NOT NULL,
             cus_surname VARCHAR(100) NOT NULL,
             PRIMARY KEY ( cus_id )
          );

By this command, a table named 'cus_tbl' is created inside 'webshack_database' database.

Use 'show tables' command to view the table:

Fig: Create Table

Special Note:

If we want to verify that our database is Multi Az or not, just go to:

AWS ➔ Services ➔ RDS ➔ Databases ➔ select database ➔ in the last of that pan we can see that Multi-AZ is 'Yes'.

Fig: Show Multi-AZ status

So, now our database belongs to highly availability criteria.