Connecting to RDS Database from EC2 instance and creating tables

This guide provides step-by-step instructions on how to connect to an RDS database from an EC2 instance and create tables.

Hafeez Baig
31 steps
2 minutes

Amazon Web Services

Refer to the following guide to learn how to use IAM in the cloud\ <https://scribehow.com/shared/Creating_an_IAM_User_and_User_Group_in_AWS_Management_Console__hWNiiXlcRJKFqYZZEJD-cg>

Sign in to **AWS Management Console** as an **IAM User** **Pre-requisite** - Ensure you have selected the AWS region closest to your location\ For this guide, we will be using us-east-2 (Ohio) as a preferred choice

Type **RDS** in the search bar and click on **Aurora & RDS** to view the Aurora & RDS Dashboard

**What is Aurora & RDS in AWS?** **Aurora & RDS** are managed database services by AWS that simplify the setup, operation, and scaling of relational databases in the cloud, with **Aurora** providing a high-performance, MySQL- and PostgreSQL-compatible engine for superior speed and reliability.

On the **Aurora & RDS** dashboard, click on the **Databases** from the left bar

**Databases** wizard will open, click on the DB identifier **mysqldatabase**

Refer to this guide to learn how to create an Amazon RDS Database\ <https://scribehow.com/shared/Creating_Your_First_RDS_Database_in_AWS__npccXKYTRse0Ba8bG3fncg>

**mysqldatabase** wizard will open, scroll the page and click on the **Connectivity & security** tab to view the Endpoint of the Database

Open the text editor and copy Endpoint, user, password and databasename from the **Connectivity & security** section

Type **EC2** in the search bar and click on **EC2** to view the EC2 Dashboard

**What is EC2 in AWS?** Amazon EC2 (Elastic Compute Cloud) is a web service provided by Amazon Web Services (AWS) that offers resizable compute capacity in the cloud. It allows users to rent virtual servers, known as instances, and run their applications on them. EC2 instances can be easily scaled up or down to accommodate changing workload demands, and users only pay for the compute capacity they consume.

On the EC2 dashboard click on **Instances** from the left bar to view the EC2 instances

**Instances** wizard will open, select the **RDS Instance** instance

Refer to this guide to learn how to create an EC2 instance for RDS Database\ <https://scribehow.com/shared/Creating_an_EC2_instance_to_connect_to_RDS_Database__eVVBl7QmRFiYSohIAJ9KTw>

On the top right corner click on the **Connect** button

**Connect to instance** wizard will open, click on the **Connect** button

On the EC2 Instance Connect page type the command [[sudo yum update]]

**What is the sudo yum update command?** "**Sudo yum update**" updates software packages on Linux systems using RPM Package Manager, granting administrative privileges with "sudo."

Type the command [[sudo su ]]to get the root user access

Type the command [[dnf -y install mysql mysql-community-client ]]to install mysql client

**What is MySQL Client?** A MySQL client is a software tool for interacting with MySQL database servers, facilitating tasks like querying and managing databases.

Type the command [[dnf -y localinstall https://dev.mysql.com/get/mysql180-community-release-e19-4.noarch.rpm]]

Type the command [[mysql --version ]]to check the mysql version

Open the text editor and type the command [[mysql --host=\[paste database endpoint\] --user=admin --password todos ]]and copy this command

mysql --host=**\[paste database endpoint\]** --user=admin --password **\[database name\]**

Paste the command in the EC2 Instance command line interface

Enter the database password in the **Enter password** prompt

It will time out because of no IPv4 inbound rule, lets try to add it

Open the EC2 dashboard in new tab, click on the **Security Groups** from the left bar

Select the Security group name **rds-security-group**

Scroll the page and click on the **Inbound rules** tab

On the right side click on the **Edit inbound rules** button

Make sure the Type you selected from the dropdown is **MYSQL/Aurora**

From the **Source** dropdown select the option **Anywhere-IPv4** and click on the **Save rules** button

**What is Type MYSQL/Aurora in the security group inbound rule?**\ Type the **MYSQL/Aurora** in a security group inbound rule permits inbound network traffic specifically for MySQL or Amazon Aurora database instances.

Return to the EC2 Instance Connect tab and re-enter the command [[mysql --host=\[paste database endpoint\] --user=admin --password todos ]]and enter the database password

Successfully connected to mysql client

Type the query [[create table users( id integer, username varchar(30) );]]

**What is [[create table users( id integer, username varchar(30) ); ]] query?** The query "CREATE TABLE users (id INTEGER, username VARCHAR(30));" creates a new table named "users" with two columns: "id" of type INTEGER and "username" of type VARCHAR with a maximum length of 30 characters.

Type the query [[select \* from users;]], it will return output **Empty set** because there are no rows in the table

**What is [[select \* from users;]] query?** The query "SELECT \* FROM users;" retrieves all rows and columns from the "users" table, returning the entire dataset stored in that table.

Type the query [[insert into users values(1, "Hafeez"); ]],It will return **Query OK, 1 row affected**

**What is [[insert into users values(1, "Hafeez"); ]]query?** The query "INSERT INTO users VALUES(1, 'Hafeez');" inserts a new row into the "users" table with the values 1 for the "id" column and 'Hafeez' for the "username" column.

Type the query [[select \* from users; ]], it will return the table with 1 row and 2 columns saying **1 row in set** and the table has been successfully created in Mysql

Congratulations on successfully Connecting to RDS Database from EC2 instance, creating tables, and completing this lab! Well done!

Connecting to RDS Database from EC2 instance and creating tables

This guide provides step-by-step instructions on how to connect to an RDS database from an EC2 instance and create tables.

Hafeez Baig
31 steps
2 minutes