Creating and Configuring Your Relational Database on Amazon RDS

A relational database refers to a collection of data items that are organized with a predefined structure or schema.

Amazon RDS is a managed database service that enables the configuration and operation of databases in the cloud. It revolves around the concept of a DB instance which serves as an environment, for running databases within the cloud. Essentially it acts as the building block of Amazon RDS.

The resources allocated to a database instance are determined by its database instance class while the type of storage utilized is based on the type of disks employed. When creating a DB instance you must initially specify which database engine you intend to use. Currently, Amazon RDS supports databases such as MySQL, Aurora Microsoft SQL Server, PostgreSQL MariaDB, and Oracle.

Advantages of utilizing RDS;

1. Automated Backups; During designated windows automated backups are generated for DB instances encompassing both data and transaction logs.

2. Scalability; Read replicas can be leveraged to distribute read traffic across database instances. This does not reduce the burden on your primary source database. Also allows for scalability, beyond the limitations of a single database instance.

3. High Availability; By implementing an AZ deployment strategy in another availability zone Amazon RDS creates a standby copy of your database ensuring its availability even if one availability zone encounters failure.

4. Security; RDS offers security features that assist in safeguarding your database data, including encryption and access control.

5. Affordability; RDS provides a range of pricing options to suit budgets enabling you to select the suitable option, for your financial constraints.

Projects’ Architecture, which consists of a VPC, private subnet, public subnet, RDS

Creating an RDS Instance

Choose a database engine

use MYSQL for the database engine

Select a DB instance class

Configure your DB instance

configure inbound rule

choose an existing Security Group with the appropriate configured rules

Create the DB instance

wait for the database to be configured

Connecting to Your RDS Instance

Connect (SSH) to the LinuxServer

ssh -i labsuser.pem ec2-user@<public-ip>

Install a MySQL client, and use it to connect to your db

install the mysql client

-- command to install mysql client
sudo yum install mysql

connect to your DB instance

-- command for connecting to the DB instance
-- -h parameter, substitute the DNS name (endpoint) for your DB instance. For the -P parameter, substitute the port for your DB instance. For the -u parameter, substitute the user name of a valid database user.
mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com -P 3306 -u mymasteruser -p

Configuring Your Database

Create a new Table

new table created

CREATE DATABASE AWS;
USE AWS;
CREATE TABLE RESTART (
  Student_ID INT NOT NULL AUTO_INCREMENT,
  Student_Name VARCHAR(255) NOT NULL,
  Restart_City VARCHAR(255) NOT NULL,
  Graduation_Date DATETIME NOT NULL,
  PRIMARY KEY (Student_ID)
);

Insert 10 rows into this table

-- to add data into a table
INSERT INTO RESTART (student_id, student_name, Restart_city, Graduation_date)
VALUES (1, 'mark', 'Lagos', '2023-12-15'),
(2, 'vincent', 'Lagos', '2023-12-15'),
(3, 'seyi', 'Lagos', '2023-12-15'), 
(4, 'adedayo', 'Lagos', '2023-12-15'), 
(5, 'john', 'Lagos', '2023-12-15'), 
(6, 'tobi', 'Lagos', '2023-12-15'), 
(7, 'tolu', 'Lagos', '2023-12-15'), 
(8, 'salam', 'Lagos', '2023-12-15'), 
(9, 'yinka', 'Lagos', '2023-12-15'), 
(10, 'segun', 'Lagos', '2023-12-15');

-- To display rows and columns query the table
SELECT *
FROM RESTART;

Use SQL joins to combine data from multiple tables

SELECT * FROM RESTART
INNER JOIN CLOUD_PRACTITIONER ON RESTART.Student_ID = CLOUD_PRACTITIONER.Student_ID;                                                                                                                                              -> INNER JOIN CLOUD_PRACTITIONER ON RESTART.Student_ID = CLOUD_PRACTITIONER.Student_ID;