Amazon Relational Database Service (RDS)

RDS is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. RDS is not a database, it's a service that manages databases. AWS RDS uses Amazon Aurora database. Amazon Aurora DB is a database which is optimized by AWS. There are many types of Databases available in AWS:

1. MySQL
2. MariaDB
3. PostgreSQL
4. MS SQL Server
5. Oracle
6. Amazon Aurora

The main difference between EC2 instance and Database instance is that we cannot login to DB instance via SSH.

Practical: Recover MariaDB database from a snapshot.

Fig: Recover MariaDB database from a snapshot

Steps we need to follow:

1. Launch a MariaDB database instance
2. Create a Jump Server
3. Install client for MariaDB
4. Access database instance
5. Create a database inside MariaDB instance
6. Take a snapshot
7. Delete 'mytestdb' from MariaDB database instance
8. Restore a new database from snapshot
9. Access new created database instance to find deleted database

Step 1: Launch a MariaDB database instance:

AWS ➔ Services ➔ RDS ➔ Create Database ➔ MariaDB ➔ [*] Only enable options eligible for RDS Free Usage Tier (Tick on this option if you want to go only for free tier) ➔ Next ➔ select DB engine version which you want ➔ DB instance class is automatically t2.micro as we click on go only for free tier ➔ Allocated storage: 20 GB ➔ enable Autoscaling if you want, otherwise just untick this ➔ DB instance identifier: MariaDB-instance (this is the instance name of MariaDB database) ➔ Master username: divakar (this is the root user of database) ➔ Master password: redhat123 (password for database root user) ➔ Confirm password: redhat123 ➔ Next ➔ …

Now we are going to configure advanced settings:

…VPC: webshack-vpc (select your VPC) ➔ Subnet group: leave default (here, we can choose the DB subnet group that defines which subnets and IP range the DB instance can use in the provided VPC) ➔ Public accessibility: No (if we select No, it is not being provided any public IP address and no EC2 instance or devices outside of the VPC will be able to connect and if we select Yes, we must also select one or more VPC security groups that specify which EC2 instances and devices can connect to the DB instance) ➔ Availability zone: choose AZ in which you want the DB instance created or if left blank it will select any AZ form that region ➔ VPC security groups: create a new security group or choose an existing one from drop-down list. As we click on choose an existing one, this will prompt for default SG, just delete that and select your SG ➔ …

Fig: Add Security Group

Database Options:

…Database name: MyTestDB this is the name of the database (up to 64 alpha-numeric characters) inside DB instance ➔ Port: 3306 (this is the port of the DB instance used for application connection) ➔ DB parameter group: using default ➔ Option group using default ➔ Encryption: selected engine or DB instance class does not support storage encryption ➔ Backup retention period: 7 days (these are the days for which automated backups are retained) ➔ Backup window ➔ Select window: the start time is the time on which backup will be active and Duration is the time till how much time AWS can take auto backup. This is the daily time range (in UTC) during which automated backups are created ➔ …

Fig: Backup Window

…Monitoring: use monitoring if you want to enable CloudWatch ➔ Log exports: select the logs which you want to monitor. Error logs are enabled by default.

Maintenance ➔ we can enable auto minor version upgrade if we want to enable automatic upgrades to new minor versions as they are released ➔ Maintenance Window: Select window ➔ this is the time period in which pending modifications or patches can be applied to the DB instance by Amazon RDS. If we click on Select window and do not provide a time period, Amazon RDS will assign a period randomly. So, this is the time on which server will be down and all patches and all will be applied. In my case I am saying reboot the system by 6 o'clock on every Monday for one hour ➔ …

Fig: Maintenance Window

…Deletion protection: we can enable deletion protection to protects the database from being deleted accidentally ➔ Create database.

Fig: Status of DB Instance

As we click on Create database, this is going to take some time. Right now the state will be creating state. After some time state will be Backing up. During backing up time, RDS is taking the first snapshot of database.

NOTE:
• If we want to access a service on a computer, locally, we can use port number.
• If we want to access a service on a network, we can use socket, which is an IP address and port number and
• If we want to access a service on the cloud, we can use Endpoint.

At the time when our database will be available, this will provide an endpoint. So to access the database we need to access this connection Endpoint.

Step 2: Create a Jump Server:

AWS ➔ Services ➔ EC2 ➔ Instances ➔ Launch instance ➔ [*] Free tier only ➔ select Amazon Linux 2 AMI (HVM) AMI ➔ Next ➔ Configure instance details ➔ Network: select your VPC ➔ Subnet: select public subnet ➔ Next ➔ Tags ➔ Name: lin-jump-srv ➔ Security Group ➔ create a new or select an existing SG which opens port number 22 ➔ Review and Launch  ➔ Launch.

Step 3: Install client software for MariaDB:

MariaDB is an additional development branch of MySQL. So, we need to install mysql client in jump server.
Login to Jump server using PuTTY. Use below-mentioned command to install mysql package.

$ sudo yum install -y mysql

Step 4: Access database instance:

To access the database instance we can use Connect Endpoint from Databases dashboard.

AWS ➔ Services ➔ RDS ➔ Databases ➔ Click on your database ➔ Copy Endpoint from Connectivity & security ➔ …

Fig: Connectivity & Security

Now use below-mentioned command in Jump server:

Syntax:
  $ mysql -u <DB username>  -h <Endpoint path> -P <port number> -p

In my case full command will be:

  $ mysql -u divakar -h mariadb-instance.c4ev2etf3evc.us-east-1.rds.amazonaws.com -P 3306 -p
  Enter Password: redhat123

Fig: Access Database Instance

This command will prompt MariaDB [(none)]>

It means access to MariaDB instance is successful.

Use command 'show databases;', this will list all databases available in MariaDB database instance.

Fig: Show Databases

Step 5: Create a database inside MariaDB instance:

We are going to create a database named 'mytestdb', using command 'cerate database <database name>':

MariaDB [(none)]> create database mytestdb;

Now, verify by using 'show databases;' command. This will list mytestdb.

This is the time to take a snapshot and after this, we will delete mytestdb database and will try to recover using snapshot.

Step 6: Take a snapshot:

AWS ➔ Services ➔ RDS ➔ Databases ➔ Select your database ➔ Actions ➔ Take snapshot ➔ Snapshot name: mytestdb-ss1 ➔ Take snapshot.

This will take some time to be in available state.

Step 7: Delete 'mytestdb' from MariaDB database instance:

Login to the jump server ➔ access MariaDB database instance ➔ sue below-mentioned command to delete 'mytestdb' database:

MariaDB [(none)]> drop database mytestdb;

Fig: Delete Database

We cannot restore 'mytestdb' within the same database.

Step 8: Restore a new database from snapshot:

AWS ➔ Services ➔ RDS ➔ Databases ➔ Select database ➔ Actions ➔ Restore snapshot (after clicking on this we can see, there are kind of same questions which we use to create snapshot) ➔ DB instance class: t2.micro ➔ Multi-AZ Deployment: choose Yes or No accordingly ➔ DB Instance Identifier: ss-recovered-instance (this is the name of the database which will be created by snapshot)

We can perform any configuration change here, then finally click on 'Restore DB Instance'.

This will create one more database instance from a snapshot. As initially, this will also take some time to be in the available state.

Step 9: Access new created database instance to find deleted database:

AWS ➔ Services ➔ RDS ➔ Databases ➔ click on newly created database instance from snapshot, named 'ss-recovered-instance' ➔ copy Endpoint URL.

Now, use this URL in Jump server to access DB instance. So, access the database instance and use 'show databases' command to list the command:

Fig: Connect Recovered Database

Enjoy!

If we have only one RDS instance in an AZ, then we will not be able to get high availability, because if we use single database installation and unfortunately that AZ is down, 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.