In this article, I am going to show you how to create and connect to an Amazon RDS MySQL Database via an EC2 instance.

In my 2nd article, I will show you how to create an RDS Proxy and connect it to an already existing Amazon RDS MySQL Database.

The same process can be applied to PostgreSQL as well.

Amazon RDS Proxy is a fully managed, highly available database proxy for Amazon RDS and Amazon Aurora and when applied to the applications, the applications become more scalable, and more resilient to database failures.

There is no code change, and you don’t need to provision or manage any additional infrastructure.

you pay per vCPU of the database instance for which the proxy is enabled.

When the applications use Lambda, they will have a large number of open connections to the database server, thus exhausting database memory and compute resources. In this situation, Amazon Proxy allows applications to pool and share connections established with the database, improving database efficiency and application scalability.

With RDS Proxy,

• failover times for Aurora and RDS databases are reduced by up to 66%, and
• database credentials, authentication, and access can be managed through integration with AWS Secrets Manager and AWS Identity and Access Management (IAM).

Please visit my GitHub Repository for Aurora articles on various topics being updated on constant basis.

Let’s get started!

Objectives:

1. Create an Amazon Virtual Private Cloud (Amazon VPC) Infra-structure

2. Create an Amazon RDS database in the custom vpc - myvpc

3. Create an Amazon EC2 instance within the same custom vpc - myvpc

4. Verify security groups - Allow myec2-sg as an inbound source to rds-sg

5. Verify RDS database connectivity

Pre-requisites:

• AWS user account with admin access, not a root account.
• AWS CLI installed.

Resources Used:

Creating a RDS MySQL DB instance

Create your EC2 resources

Steps for implementation to this project:

1. Create an Amazon Virtual Private Cloud (Amazon VPC) Infra-structure

• Create a custom VPC myvpc with CIDR 10.0.0.0/16 in us-east-1
• Create and attach an Internet Gateway - myvpc-igw
• Create a 2 Public subnets - myvpc-pubsub1 with CIDR 10.0.1.0/24 in us-east-1a and myvpc-pubsub2 with CIDR 10.0.2.0/24 in us-east-1b
• Create a Public Route Table myvpc-rt and associate it with the myvpc-pubsub1 and myvpc-pubsub2
• Add the public Route in the Route table (attach myvpc-igw).

2. Create an Amazon RDS database in the custom vpc - myvpc

• Create an an Amazon RDS database with the following parameters -
  Standard create MySQL engine, free-tier, database-1, <username> admin, <password>, db.t2.micro, myvpc, DB subnet group - default, public access - yes, VPC security group - create new - rds-sg, us-east-1a, under additional configuration - myrds

• Create

• myrds database endpoint

database-1.cgizjtuyxkda.us-east-1.rds.amazonaws.com

3. Create an Amazon EC2 instance within the same custom VPC - myvpc

• Create an EC2 Instance myec2 in us-east-1 with Amazon Linux 2 AMI, instance type - t2.micro, key_pair - NVirKey.ppk in the myvpc, Subnet - myvpc-pubsub2, myec2-sg with the Security group rules (SSH, TCP, 22, 0.0.0.0/0

4. Verify security groups - Allow myec2-sg as an inbound source to rds-sg

• ec2-sg:

  This security group is attached to the EC2 instance and allows only SSH connection inbound to the EC2 instance and any outbound connectivity.

• rds-sg:
  This security group is attached to the RDS instance and allows only TCP connection on port 3306 from the EC2 instance (shown as ec2-sg) and any outbound connectivity.

• Security groups - Select the rds-sg.

• Then select the Inbound Rules tab.

• Then select Edit.

• Edit inbound rules / In the Inbound Rules / Add rule

• In the Type column, select MySQL/Aurora (3306) from the drop down list.

• Then click into the Source column field and a drop down list will appear. Select the security Group ID of myec2 - myec2-sg

• Save rules

• verify the Source is the ec2-sg

5. Verify RDS database connectivity

• On EC2 console, choose myec2 instance from which you want to test connectivity to the RDS DB instance.

• Choose Connect.

• Or ssh into myec2

sudo su

yum install mysql

mysql -h <RDS DB endpoint> -P 3306 --user=admin --password

mysql -h database-1.cgizjtuyxkda.us-east-1.rds.amazonaws.com -P 3306 --user=admin --password

• When prompted, type your password and press Enter.

• A message showing that you have successfully connected to the RDS DB instance.

What we have done so far

We have successfully created and connected to an Amazon RDS MySQL Database via an EC2 instance.