Guided Lab: Creating an Amazon RDS database

1. Go to EC2 → Network & Security → Security Groups, then create a security group with the following configurations.

• Security group name: db-access
• Description: Allows MYSQL access to developers
• VPC: Choose the default VPC

• Add an Inbound rule with the following configurations:
  • Under “Type”, Choose “MYSQL/Aurora” from the dropdown menu.
  • Specify the IP addresses that should have access to your RDS DB instance. You can choose “Anywhere” for testing purposes, but it’s recommended to restrict access to known IP addresses for security reasons. For this lab, choose My IP.

2. Click the “Create security group” button.

To enhance the security of your instance, it is important to only authorize a specific IP address or range of addresses when setting up a rule to access it. Using 0.0.0.0/0 will allow all IPv4 addresses to access your instance. Similarly, using ::/0 will enable all IPv6 addresses to access your instance. To avoid these two options and provide a more secure solution, it is recommended to specify a particular IP address or range of addresses. You can also reference another security group. For example, instead of using an EC2 instance’s IP address, you can specify the security group associated with it.

1. In the AWS Management Console, search for “RDS” using the search bar and select the RDS result under Services.

2. Click on the “Create database” button. You will be prompted to configure your database.

3. Choose Database Creation Method. In the “Create database” page, ensure that “Standard Create” is chosen.

4. Choose Database Engine: In the “Engine options” section, choose either “MySQL” as your database engine.

5. Choose DB Instance Size: In the “Templates” section, choose “Free tier”. This will automatically select the “db.t2.micro” or “db.t3.micro” instance type.

6. Configure DB Instance:

• DB instance identifier: Give your DB instance a unique name.
• Master username & password: Set the master username and password for your database.

7. Set the Allocated storage to 20 GiB only.

8. Under Connectivity, enable the Public Access option.

9. Under Connectivity, choose the created security group.

10. Expand the other sections, and take a moment to review the available configurations. It is not necessary to make any changes to the remaining configurations. You can leave them as they are set by default.

11. Click on the “Create database” button at the bottom of the page to create your MySQL database.

12. After clicking the ‘Create database’ button, a confirmation will appear to let you know that the process has started and the database has been created.

1. Once the database is ready (this might take a few minutes). you can connect to it using any MySQL client. In this lab, we’ll be using MySQL Workbench.
Download and install this on your computer: MySQL: Download MySQL Workbench

2. Start the MySQL Workbench application on your computer.

3. Click on the “+” icon next to “MySQL Connections” to create a new connection.

4. Configure Connection Details:

• Connection Name: Give your connection a unique name.
• Hostname: Use the endpoint provided in the RDS console.
• Port: Use the port provided in the RDS console.

• Username & Password: Use the master username and password you set earlier to establish the connection.

5. Click “OK”, then test the connection by clicking the “Test Connection” button.

6. If the test connection is successful, you can click the “OK” button to save the connection settings.

7. In the MySQL Workbench home screen, you should now see your newly created connection under “MySQL Connections.”

8. Double-click on your connection to establish a connection to the RDS DB instance.

9. Once connected, you can browse and manage your RDS database using the MySQL Workbench interface.

1. Creating a Database.

Create DATABASE EmployeesDB;

Run the command by clicking the Lightning icon.

2. Choose the database that has been created.

USE EmployeesDB;

Run the command by clicking the Lightning icon.

3. Creating a table.

CREATE TABLE Employees ( ID INT PRIMARY KEY, Name VARCHAR(50), Age INT, Salary DECIMAL(10, 2) );

Run the command by clicking the Lightning icon.

This command creates a table called Employees with four columns: ID, Name, Age, and Salary. The ID column is set as the primary key.

4. Writing to the Table

INSERT INTO Employees (ID, Name, Age, Salary)
VALUES (1, 'Jose Rizal', 25, 50000.00);

INSERT INTO Employees (ID, Name, Age, Salary)
VALUES (2, 'Andres Bonifacio', 25, 50000.00);

INSERT INTO Employees (ID, Name, Age, Salary)
VALUES (3, 'Emilio Aguinaldo', 25, 50000.00);

Run the command by clicking the Lightning icon.

This command inserts a new row into the Employees table. Each row represents an employee with their respective ID, Name, Age, and Salary.

5. Reading from the Table

SELECT * FROM Employees;

Run the command by clicking the Lightning icon.

This command selects all rows and columns from the Employees table.

Congratulations! You just learned how to create a MySQL database using Amazon RDS, configure security groups to manage access to an RDS DB instance, and connect to an RDS DB instance using MySQL Workbench.