Simplifying Database Management: How to Connect Amazon RDS to EC2 for CRUD Operations

Introduction to Amazon RDS

• Amazon Relational Database Service (RDS) is a fully managed cloud database service provided by AWS.

• It simplifies the process of setting up, operating, and scaling a relational database in the cloud.

RDS supports multiple database engines, including MySQL, PostgreSQL, MariaDB, Oracle, and SQL Server, with features like automated backups, software patching, monitoring, and scaling.

Steps to Connect RDS with EC2 Instance to Perform CRUD Operations

Let's create a MySQL database in RDS and connect it to an EC2 instance to perform CRUD (Create, Read, Update, Delete) operations on a student information table with columns id, name, marks, and std.

Step 1: Launch an RDS Instance

1. Go to the AWS Management Console and navigate to RDS.

2. Click Create Database.

3. Choose MySQL as the engine.

4. In the settings:

   • Choose the instance type.

   • Set database credentials (username and password).

5. Configure VPC and security groups to allow inbound MySQL connections from your EC2 instance.

6. Launch the RDS instance.

Step 2: Launch an EC2 Instance

1. In the AWS Management Console, go to EC2.

2. Launch a new EC2 instance (Amazon Linux 2 or Ubuntu is recommended).

3. In the Security Group, allow inbound SSH access and MySQL (port 3306) from the IP address of the EC2 instance.

4. Once launched, SSH into your EC2 instance.

Step 3: Install MySQL Client on EC2

1. SSH into the EC2 instance and run the following commands:

    sudo yum update -y    # For Amazon Linux
    sudo yum install mysql -y
   

   Or, for Ubuntu:

    sudo apt update
    sudo apt install mysql-client -y
   

Step 4: Connect EC2 to RDS

1. Use the RDS endpoint to connect to your RDS instance from the EC2 instance:

    mysql -h <RDS_ENDPOINT> -u <USERNAME> -p
   

   Enter the password you set when creating the RDS instance.

Step 5: Create the Student Information Table

1. After connecting to the MySQL database, create a new database and the student table:

    CREATE DATABASE school;
    USE school;
   
    CREATE TABLE students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        marks INT,
        std VARCHAR(10)
    );
   

Step 6: Perform CRUD Operations

• Insert Data (Create):

    INSERT INTO students (name, marks, std) VALUES ('John Doe', 85, '10th');
    INSERT INTO students (name, marks, std) VALUES ('Jane Smith', 92, '9th');
  

• Read Data (Retrieve):

    SELECT * FROM students;
  

• Update Data (Update):

    UPDATE students SET marks = 90 WHERE id = 1;
  

• Delete Data (Delete):

    DELETE FROM students WHERE id = 2;
  

Step 7: Clean Up Resources

After completing the operations, remember to delete your EC2 instance and RDS database if you no longer need them to avoid unnecessary charges.

---

Conclusion

By using Amazon RDS for managing databases and connecting it to an EC2 instance, you can perform CRUD operations on relational data in the cloud. This setup allows you to scale, secure, and automate database management tasks while focusing on application logic.