Configure Amazon RDS with MySQL engine

 Configure Amazon RDS with MySQL engine and establish connection from an EC2 instance.

Objectives:

1. Learn RDS and deploy MySQL database engine using RDS service.

2. Test RDS/MySQL database connection from EC2 instance.

Step 1: In EC2 service console, go to Security groups in side panel. Click on Create security groups.

Create a Security Group for Linux Server with following configuration:

Security group name: LinuxSG

Description: Security Group for Linux Server

Type: SSH

Source: 0.0.0.0/0

Click on Create security group button in bottom right corner. Confirm that it is created.

Go back to EC2 service console, go to Security groups in side panel. Click on Create security groups.

Provide Security group name as RDS-SG.

Provide Description as Security Group for Database.

Provide following Inbound rules:

1. Type: SSH

Source:0.0.0.0/0

2. Type: MySQL/Aurora

Source:0.0.0.0/0

3. Type: MySQL/Aurora

Source: LinuxSG (security group that would be used to create the EC2 instance)

Click on Create security group button in bottom right corner. Confirm that it is created.

Step 2: Go to RDS service console. Click on Create database.

Choose a Standard create database creation method.

Select the MySQL radio button in Engine options. Confirm MySQL 8.0.20

Select the Dev/Test in Templates.

Under Settings:

Give DB instance identifier as mydatabase1.

Provide Credentials Settings as per your choice and store it in a secure place. We refer to following values for this document:

• Master Username: rdsuser123

• Master password: rdspass123

Let the DB instance class be Standard Classes and size be db.m5.xlarge.

Confirm the default Storage settings as Storage type: General type- General Purpose (SSD) and Allocated storage 20 GiB.

Uncheck the Enable storage autoscaling.

Under Availability & durability select Do not create a standby instance radio button.

Connectivity section will have Default VPC selected.

Click on Additional connectivity configuration for drop down.

• Select default-vpc Subnet group.

• Select Yes radio button under Public access.

• Select Choose Existing radio button in VPC security group.

• In the Existing VPC security groups default will be selected. Remove this security group by clicking on the cross sign. Select the RDS-SG created in previous step.

• In Availability Zone select 1a which should be same as the AZ of Linux Instance.

• The Database port will be default 3306.

Database authentication is set to Password authentication.

Click on Additional configuration drop down.

Provide Initial database name as cloudPlusPlusLab.

DB parameter group and Option group will be default mysql8.0.

In Backup the Enable automatic backups will be checked.

Backup retention period will be 0 days.

Backup window will have No preference selected.

Uncheck Copy tags to snapshots.

Uncheck Enable Encryption.

Uncheck Enable Performance Insights.

Uncheck Enable Enhanced monitoring.

Uncheck all options under Log Exports.

Uncheck Enable auto minor version upgrade.

Select No preference for Maintenance window.

Uncheck Enable deletion protection.

Ensure no additional cost is being incurred in the Estimated monthly cost section.

Click on Create database button in bottom right corner.

Confirm that the database is created and Available.

Step 3: Go to EC2 in AWS Console. Click on Launch Instance. Select the Linux2 AMI.

In Step 2: Choose an Instance Type keep the default t2.micro and go to next step.

In Step 3, select a subnet same as the one in which RDS is created. In our case we select ap-south-1a.

Keep the defaults for Step 4: Add Storage. And go to Step 5. Provide the Key as Name and Value as LinuxForSQL.

In Step 6 select the existing Linux Server Security Group LinuxSG created earlier in this exercise. For more information refer to our blog in Linux Server Configuration here. Review, acknowledge the Key-pair and Launch the Instance. Make sure it is running.

Step 4: SSH into the instance.

Run the following command to establish root user access:

sudo su

Run the next command to install mysql on your instance:

yum install mysql

During installation, you would be prompted at following screen. Type Y and press Enter to proceed.

The installation will be complete.

Go to RDS in console, click on your RDS instance and copy the endpoint.

Run the following command in SSH where the blue part will be replaced by your end point and user name:

mysql --user rdsuser123 --password --host mydatabase1.xyzabcd.ap-south-1.rds.amazonaws.com

You will be prompted to enter password in which you enter the password provided during creation of RDS instance:

If the command, endpoint and password is entered correctly, the following will be prompted.

Run the following SQL queries to test the database, here the blue part will be replaced by your database name that was provided during creation of RDS instance:

CREATE TABLE cloudPlusPlusLab.employee (firstname text, lastname text, phone text);

INSERT INTO cloudPlusPlusLab.employee VALUES ("Harry", "Potter", "123-4567");

INSERT INTO cloudPlusPlusLab.employee VALUES ("Virat", "Kohli", "987-6543");

SELECT * FROM cloudPlusPlusLab.employee;

Thus we have successfully established connection with RDS using the Linux EC2 Instance.

Note: Delete RDS instance, Security groups and terminate the instances if you no longer need them.