Achieve Scalability and Agility: Refactoring My Web App on AWS (Part 3)

E. DB Initialization

Executing SQL Queries on a Secure RDS Instance: A Step-by-Step Guide

Project Requirements and Source Code:

• The current project necessitates executing specific SQL queries on our RDS instance.
• The queries are located within our source code, accessible at [https://github.com/devopshydclub/vprofile-project].
• We’ll focus exclusively on the main branch.
• To view the queries, navigate to the src/main/resources directory and open the db.backup.sql file.

Accessing the RDS Instance:

• Since our RDS instance lacks direct public access, we’ll leverage a temporary EC2 instance within the same VPC to establish a connection.

Launching the EC2 Instance:

1. Accessing EC2:

• Navigate to the EC2 service within the AWS console.
• Ensure you’re operating in the appropriate region, where a default VPC exists.

1. Launching a New Instance:

• Click the “Launch Instance” button.
• Assign a descriptive name to your instance.
• Select your preferred operating system.
• Keep the instance type as t2.micro.
• Choose a key pair that you’ve used previously.

1. Network Configuration:

• Click “Edit” within the network settings.
• Create a new security group and give it a name.
• Establish a rule permitting SSH access from your IP address (port 22).
• Scroll down and initiate the instance launch.

Temporary Instance:

• Remember that this EC2 instance is solely for facilitating RDS connectivity and query execution.
• We’ll promptly terminate it upon completion of these tasks.

The steps to configure the backend security group:

1. Locate the Instance Security Group ID:

• Navigate to the Security Groups section within your cloud console.
• Find the security group assigned to the instance you created for executing SQL queries.
• Copy the security group ID for this instance.

2. Edit Inbound Rules for Backend Security Group:

• Access the Security Groups section again.
• Locate the specific backend security group you intend to configure.
• Click on the tab labeled “Inbound Rules.”
• Initiate editing of the rules by clicking the “Edit rules” button.

3. Add New Rule:

• Create a new inbound rule with the following specifications:
• Port: 3306
• Protocol: TCP
• Source: Paste the copied security group ID of the instance here.

4. Save the Rule:

• After completing the rule configuration, ensure you save the changes to apply them.

By following these steps, you’ll effectively configure the backend security group to permit connections specifically originating from the instance’s security group, enabling interaction with the relevant services on port 3306.

E.1. DB Execution

1. Open Your Terminal:

• Begin by accessing the terminal application on your computer. This is where you’ll input commands to connect to the instance.

2. Establish SSH Connection:

Once connected, update the package lists and install the MySQL client using the appropriate command for your operating system:

3. Update and Install MySQL Client:

For Ubuntu:

Bash

sudo apt update && sudo apt install mysql-client -y

For Amazon Linux or CentOS:

Bash

sudo yum install mariadb -y

4. Test the MySQL Connection:

• To test the connection to your MySQL database, use this command:

Bash

mysql -h yourendpoint.rds.amazonaws.com -u username -pyourpassword accounts

• Replace placeholders with your actual endpoint, username, and password.
• Specify the database name (here, “accounts”) if you wish to connect to a specific one.

5. Logging In:

• If the connection is successful, you’ll be prompted to enter your password (if not included in the command).
• Upon successful authentication, you’ll be logged into the MySQL database and able to execute queries and manage data.

Now here’s what you need to do:

1. Check the tables:

Type the following command and press Enter:

SQL

show tables;

This command will display a list of existing tables in the database. If the database is new, you’ll likely see an empty set.

2. Clone the source code:

• Type the following command and press Enter:

Bash

git clone https://github.com/devopshydclub/vprofile-project.git

This command will download a copy of the source code from a GitHub repository into a new folder named “vprofile-project” on your local machine.

3. Navigate to the project directory:

• Type the following command and press Enter:

Bash

cd vprofile-project/

This command changes your terminal’s working directory to the “vprofile-project” folder you just downloaded.

4. Import the SQL data:

• Type the following command and press Enter, replacing the placeholders with your actual values:

Bash

mysql -h your_endpoint.rds.amazonaws.com -u username -pyour_password accounts < src/main/resources/db_backup.sqlmysql -h your_endpoint.rds.amazonaws.com -u username -p your_passw

• Explanation:
• mysql: This is the command to connect to a MySQL database server.
• -h your_endpoint.rds.amazonaws.com: This specifies the hostname or IP address of your RDS endpoint.
• -u username: This specifies the username to connect to the database.
• -p: This tells the mysql command to prompt you for your password. (The actual password won't be shown while typing).
• your_password: Replace this with your actual database password.
• accounts: This is the name of the database you want to connect to.
• < src/main/resources/db_backup.sql: This part tells mysql to read the SQL commands from the file db_backup.sql located in the src/main/resources directory of the project and execute them on the "accounts" database.
• This command will import the data from the db_backup.sql file into your "accounts" database on Amazon RDS.

5. Check Tables Again

Bash

mysql -h yourendpoint.rds.amazonaws.com -u username -pyourpassword accounts
show tables;

• Now you should see the table.

6. Exit the MySQL command line:

• Once the import is complete, type exit and press Enter to exit the MySQL command line interface.

Once you’ve finished using MySQL for this task, you can terminate the instance you created.

Securing Your Endpoints: The Final Backend Touches

Almost there! The final step before diving into Beanstalk involves grabbing the connection details for our messaging service (Amazon MQ) and caching solution (ElastiCache). These endpoints are like secret handshakes, allowing our application to talk to these services.

Here’s how to snag them:

1. Amazon MQ: Head over to the MQ service and find the broker you created. Under the “Connections” section, you’ll see the endpoint. Copy it entirely, but make sure to remove the amqps:// part and the port number at the end. We only need the core address for later use.
2. ElastiCache: Now, navigate to the ElastiCache service and locate your cluster. Under “Cluster details,” you’ll find the endpoint. Similar to the MQ endpoint, copy the entire address, but remember to ditch the port number at the end. We just need the base address.

Remember: Keep these endpoints safe! We’ll be using them in the next steps to configure our application to work with these services.

By following these steps, you’ll have all the essential backend pieces in place, ready to be integrated with Beanstalk in the next part of our journey.

If you need to go back to Part 1:

Achieve Scalability and Agility: Refactoring My Web App on AWS (Part 1)

If you need to go back to Part 2:

Achieve Scalability and Agility: Refactoring My Web App on AWS (Part 2)