Connect to RDS from EC2

In the world of Amazon Web Services, connecting your EC2 (Elastic Compute Cloud) to RDS (Relational Database Service) is like building a bridge between two important places. This guide is your map to that bridge, showing you step by step how to link these two services. Think of it as connecting your computer to a powerful storage room for information. Following these simple steps ensures your computer and the storage room can talk to each other, making things easier for your apps and data. This connection is a big deal because it lets your apps access and manage information smoothly and securely. By the end of this guide, you’ll have a solid link between EC2 and RDS, setting the stage for smoother, more powerful, and efficient use of these Amazon services.

Prerequisites

1. Have an AWS account. If you don’t have one, sign up here and enjoy the benefits of the Free-Tier Account
2. View project files in my GitHub portfolio

Launch an EC2 Instance

1. On the AWS console, search for EC2 and click on it to open the EC2 console

2. Click on Launch instance and launch an instance with the following configuration:

• Image: Amazon Linux 2 AMI
• instance type: t2.micro
• security group: Allow ssh(Port 22) and MySQL/Aurora(Port 3306)

Create an RDS Instance

1. On the AWS Console, search for RDS

2. Click on Create database

3. Specify DB details:

• Database creation method — Standard create
• Engine options — MySQL
• Version — Default

• Templates — Free tier
• DB Instance identifier — database-instance (You can set your own preferred DB Instance identifier)
• Master username — rdsuser (You can set your own preferred Master username)
• Master password — Pa55w0rd (You can set your own preferred Master password)

• Instance configuration — db.t2.micro
• Storage type — General Purpose SSD (gp2)
• Allocated storage — 20
• Enable storage autoscaling — uncheck

• Public access — No
• VPC Security Group — Choose existing
• Existing security groups — remove the default and add the security group you used for the EC2 (Allows SSH and MYSQL/Aurora traffic)

Under Additional configuration, set up the following:

• Initial database name — mydb
• DB parameter group — default
• Option group — default
• Enable automated backups — uncheck
• Log exports — uncheck all
• Leave all other settings as default

Scroll to the bottom and click on Create database

Create a connection to the Amazon RDS database from the EC2 instance

1. Connect to the ec2 instance via ssh

2. Change to root user

sudo su

3. Download some Linux packages

sudo amazon-linux-extras install epel -y

4. Install the MySQL repository package. Enter y wherever asked.

sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm

5. Install the MySQL community server. Enter y wherever asked.

sudo yum install mysql-community-server

6. Verify the version for MySQL.

mysql --version

If all installed well, the command should show the MySQL version installed

7. Get your rds instance Endpoint. On the database instance console, under the connectivity tab, copy the Endpoint

8. Run the following command on the terminal: mysql -h <mysql-instance-dns> -u <username> -p

In our case, it will be:

mysql -h database-instance.cfjitlrhaxvx.us-east-1.rds.amazonaws.com -u rdsuser -p

Press enter then enter the password

Create a Database, Table and Insert data for testing

1. Create a database:

CREATE DATABASE SchoolDB;

2. You can see the created database through the following command:

SHOW databases;

3. Switch to the database named SchoolDB

use SchoolDB;

4. Create a sample table consisting of Subjects.

CREATE TABLE IF NOT EXISTS subjects (subject_id INT AUTO_INCREMENT,subject_name VARCHAR(255) NOT NULL,teacher VARCHAR(255),start_date DATE,lesson TEXT,PRIMARY KEY (subject_id)) ENGINE=INNODB;

5. Insert some details into the table:

INSERT INTO subjects(subject_name, teacher) VALUES ('English', 'John Taylor');
INSERT INTO subjects(subject_name, teacher) VALUES ('Science', 'Mary Smith');
INSERT INTO subjects(subject_name, teacher) VALUES ('Maths', 'Ted Miller');
INSERT INTO subjects(subject_name, teacher) VALUES ('Arts', 'Suzan Carpenter');

6. Let’s check the items we added into the table:

select * from subjects;

Congratulations! By following the steps in this guide, you’ve successfully connected your EC2 and RDS within Amazon Web Services. This connection acts like a superhighway between your computing power and your data storage, allowing your applications to run more efficiently. Now that you’ve set up this connection, you’re all set to manage your data and apps more smoothly within Amazon’s world. This link is key for seamless data operations and application performance. Keep exploring and using this powerful connection to innovate and create within the Amazon ecosystem. It’s your gateway to building robust and scalable systems, opening doors to endless possibilities within AWS.