Connecting a Flask Application to AWS RDS (MySQL)

Project Description

Amazon Web Services (AWS) provides a managed SQL database service called Amazon Relational Database Service (Amazon RDS). Amazon RDS uses various database engines to manage the data, migrations, backup, and recovery.

In this project, we will create a database instance of SQL over RDS. After that, we will create a key pair value, use a security group and make an EC2 instance using Amazon Machine Image (AMI). Then, we’ll use the endpoint of that database to connect it with the EC2 instance and install the required packages for the Flask application. In the end, we’ll deploy the Flask application on EC2 and connect the application to use the AWS RDS (MySQL) database.

The AWS Command Line Interface (CLI) is a powerful utility to manage all AWS services through a shell prompt. You can use the CLI to access and configure various AWS services and automate tasks using scripts.

For your convenience, the AWS CLI has been downloaded and installed using the official link

Verify that the utility is properly configured by running the following command:

aws --version

When you’ve successfully run this command, you’ll see the version details of the AWS CLI. This ensures that the AWS CLI has been installed correctly.

Use the following command to list all commands available through the AWS CLI:

aws ec2 help

Press the “Q” key to exit and return to the shell prompt.

Note: In this project, you’ll create and export some environment variables using the 

environment.sh bash script.

#!/bin/bash

export SG_ID=''

export RDS_ADDRESS=''

export AMI_ID=''

export INSTANCE_ID=''

export PUBLIC_IP=''

Step 2: Configuration:

To configure the AWS account, run the configure command available through the AWS CLI and enter credentials, region, and output format.

A few sample regions available in the AWS CLI are listed in the following table:

Some sample output formats available through AWS CLI are shown below:

• json: The output is a JSON string.
• yaml: The output is a YAML string.
• text: Each line of the output is separated by a tab.
• table: The output is formatted as a table using the +|- characters to form cell borders.

Note: It’s recommended to create a new AWS AccessKeyId and SecretAccessKey by creating a new IAM User for this project. To learn how to generate these keys, follow this link. Make sure to set up the AmazonEC2FullAccess user policy for the IAM user.

Type the following command in the terminal:

aws configure

After executing this command, add the following parameters:

AWS Access Key ID [None]: <AccessKey>
AWS Secret Access Key [None]: <SecretAccessKey>
Default region name [None]: <YOUR_DEFAULT_REGION>
Default output format [None]: <ANY_OUTPUT_FORMAT>

Step 3: Create a New RDS

Amazon Web Services (AWS) provides managed SQL database services the called Amazon Relational Database Service (Amazon RDS). Amazon RDS provides various database engines to manage the data, migrations, backup, and recovery.

Let’s create a new database using the aws rds command. Add the following parameters while creating a new RDS:

• --db-instance-identifier: This defines the name of the instance. It must be in lowercase characters.
• --db-instance-class: This defines the memory capacity of the database.
• --engine: This defines the database engine.
• --master-username: This defines the username for the database.
• --master-user-password: This defines the password for the database.
• --allocated-storage: This defines the storage in gibibytes.

aws rds create-db-instance \
    --db-instance-identifier rdsprojectdb \
    --db-instance-class db.t3.micro \
    --engine mysql \
    --master-username admin \
    --master-user-password password \
    --allocated-storage 20

After creating the RDS instance, assign the value of VpcSecurityGroups.VpcSecurityGroupId to the SG_ID variable in the environment.sh file.

After adding the value to the file, use the following command to export the value:

source environment.sh

Step 4 : Describe DB Instances

Use the aws rds command to list all DB instances associated with the AWS account.

aws rds describe-db-instances --db-instance-identifier rdsprojectdb

After describing the RDS instance, assign the value of Endpoint.Address to the RDS_ADDRESS variable in the environment.sh file.

Note: The endpoint may take some time to show up because instances created in the previous task will take some time to be initialized.

Run the following:

source environment.sh

Step 5: Add a New Inbound Rule to the Security Groups

The security group has some default inbound and outbound rules. MySQL that will be running on port 3306. The inbound rule will specify the port number and IP range of the incoming traffic that the EC2 security group will allow. Add an inbound rule to the security group with the following parameters:

• protocol: TCP
• cidr: 0.0.0.0/0
• port: 3306

Add two more inbound rules to access the EC2 with SSH and the Flask application.

• Use the following parameters for Flask:
  • protocol: TCP
  • cidr: 0.0.0.0/0
  • port: 5000
• Use the following parameters for SSH:
  • protocol: TCP
  • cidr: 0.0.0.0/0
  • port: 22

Use a command from aws ec2 to add these three inbound rules to the security group.

• Type the following command to add an inbound rule for MySQL:

aws ec2 authorize-security-group-ingress --group-id $SG_ID --protocol tcp --port 3306 --cidr 0.0.0.0/0

Type the following command to add an inbound rule for Flask:

aws ec2 authorize-security-group-ingress --group-id $SG_ID --protocol tcp --port 5000 --cidr 0.0.0.0/0

Type the following command to add an inbound rule for SSH:

aws ec2 authorize-security-group-ingress --group-id $SG_ID --protocol tcp --port 22 --cidr 0.0.0.0/0

Step 6: Create a New Key Pair

In AWS, a key pair is a combination of public and private keys. The public key is encrypts data, while the private key decrypts data. These are the security credentials used to connect to the EC2 instance. Amazon EC2 instance stores the public key, and the user has their private key to connect to the instance.

Let’s create a new key pair in the account using the aws ec2 command. You need to pass a key name to the command to create a key pair. The name of the key pair must be unique.

aws ec2 create-key-pair --key-name rdsproject --query 'KeyMaterial' --output text > rdsproject.pem

Step 7: List all Key Pairs

Let’s verify the creation of key pairs by listing all available key pairs in the account using the aws ec2 command.

aws ec2 describe-key-pairs
aws ec2 describe-key-pairs --output table

Step 8: Run an EC2 Instance

Let’s launch a template using Amazon Machine Image (AMI) in this task. You can only launch instances of those AMIs for which you have permission. To launch an instance, get the AMI ID of the required operating system.

Add the AMI ID in the AMI_ID variable in the environment.sh file. Use the following command to export the value:

source environment.sh

After getting Amazon Ubuntu’s AMI ID, use the aws ec2 command and pass these parameters to launch the instance:

• --image-id: This is the AMI ID of Ubuntu.
• --count: This is the number of instances you want to run.
• --instance-type: This is the type of instance.
• --key-name: This is the key pair we’ve created.
• --security-group-id: This is the group ID we have copied from Task 2.

Type the following command to run an instance:

aws ec2 run-instances --image-id $AMI_ID --count 1 --instance-type t2.micro --key-name rdsproject --security-group-id $SG_ID

After running the instance, copy the InstanceId from the output and assign it to the INSTANCE_ID variable in the environment.sh file. Use the following command to export the values:

source environment.sh

Step 9: Check the state of the EC2 instance

After running an instance, we can check the state of the instance using the aws ec2 command. This will accept the InstanceId as the argument and output the complete details of the instance. Check the PublicIpAddress and State.name of the instance.

If the state is not running, wait for a while and list the attributes of the instance again. After two minutes, notice the status of the instance again. It should be in running now.

aws ec2 describe-instances --query 'Reservations[*].Instances[*].State'

Note: Copy the PublicIpAddress of the instance and place it in the environment.sh file in the PUBLIC_IP. Next, export the variable using the following command:

source environment.sh

Step 10: Copy the Data from a Local Machine to EC2 machine:

To deploy the Flask application over the EC2 instance, upload the application from a local machine to the EC2 instance. In this task, perform the following steps:

• Change the access permission of the .pem file to read-only.
• Use the copy command with the .pem file to upload data from the local machine to the EC2 instance.

GitHUB: https://github.com/skillupwithsachin/aws_rds_project_skill_up_with_sachin.git

https://github.com/skillupwithsachin/aws_rds_project_skill_up_with_sachin#

Type the following command to change the access permission of the file:

chmod 400 rdsproject.pem

Use the following command in the terminal to upload the zipped Flask application:

scp -i rdsproject.pem flask.tar.gz ubuntu@$PUBLIC_IP:flask.tar.gz

After these commands, the terminal will ask for your permission. Type yes and press enter to log in to the instance.

Step 11: Access the EC2 from the Local Machine

hen the instance’s state is running, we can log in to the instance using the key pair value created in a previous task.

Use the .pem file and log in to the PublicIpAddress of the instance using ubuntu as the username.

ssh -i rdsproject.pem ubuntu@$PUBLIC_IP

Step 12: Install Necessary Packages

To run the Flask application and to connect it with the MySQL database instance created on your RDS, install the following packages:

• pip
• flask_sqlalchemy
• PyMySQL
• python3-flask
• mysql-client-core-8.0

sudo apt update
sudo apt install pip
python3 -m pip install flask
python3 -m pip install PyMySQL
python3 -m pip install flask_sqlalchemy
sudo apt install python3-flask
sudo apt install mysql-client-core-8.0
tar xvf flask.tar.gz

Step 12: Access the RDS using EC2 CLI:

After successfully installing all the packages, access the RDS from the EC2 instance using the MySQL command line tool. This requires the following parameters:

• The arn of the RDS
• Port number
• Username
• Password

After logging in to the RDS, create a new database applicationdb using SQL commands.  You‘ll use this database in the Flask application.

Run following to log-in:

mysql -h <RDS Address> -P 3306 -u admin -p password

Replace the <RDS Address> with the endpoint address of the RDS in the environment.sh file.

After logging in, use the following command to create a database:

createdatabaseapplicationdb;

Note: After creating a database, type exit and press “Enter” to close the MySQL console.

Step 13: Configure the Flask Application

In this task, you’ll change the Flask application to use the database available on the RDS instead of using the SQLAlchemy available locally. To do that, you’ll need to change the database address in the elearning/website/__init__.py file. Update the database URI to the endpoint of the database in this file. Perform the following operations:

• Open the elearning/website/__init__.py file in the terminal.
• Change the URI in that file.

Use the following command to open the file:

nano elearning/website/__init__.py

Replace the <Endpoint> and <DBName> in the following line with the RDS address from the environment.sh and the database name (applicationdb) respectively:

application.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://admin:password@<EndPoint>/<DBName>?charset=utf8mb4'

Step 14: Run the Flask Application:

In this task, you’ll run the Flask application by exporting the elearning/application.py file as the FLASK_APP and run the application on EC2’s localhost. This will allow you to access the application over the public IP/DNS of the EC2.

cd elearning
export FLASK_APP=application.py
flask run --host=0.0.0.0

The application is now up and running. You can test it by accessing the public IP/DNS of EC2 on your local browser. Update <Your Public IP> with the public IP you have saved in the environment.sh file. Use the following address to load data:

<Your Public IP>:5000/loaddata

After the success message, use the following address to view the data:

<Your Public IP>:5000

Step 15: Clean Up

We can clean up everything by terminating the EC2 instance. Use an aws ec2 command to terminate the instance. This command requires the InstanceId as an argument. After the termination of the EC2 instance, delete the RDS instance as well.

Let’s terminate the EC2 instance, delete the RDS instance, and revoke the security group inbound rules using the aws commands.

Use the following steps:

• Terminate the Flask application by pressing “Ctrl+C”.
• Type logout to log out from the EC2 instance.
• Use the following command to terminate the instance:aws ec2 terminate-instances –instance-ids $INSTANCE_ID
• Use the following command to delete RDS:

aws rds delete-db-instance --db-instance-identifier rdsprojectdb --skip-final-snapshot

• Use the following command to delete the key pair:aws ec2 delete-key-pair –key-name rdsproject
• Use the following commands to revoke all security group ingress:

aws ec2 revoke-security-group-ingress --group-id $SG_ID --protocol tcp --port 22 --cidr 0.0.0.0/0
 aws ec2 revoke-security-group-ingress --group-id $SG_ID --protocol tcp --port 3306 --cidr 0.0.0.0/0 
aws ec2 revoke-security-group-ingress --group-id $SG_ID --protocol tcp --port 5000 --cidr 0.0.0.0/0

Congratulations on completing this project.

You can now create an RDS instance using the aws rds and manage Amazon Elastic Compute Cloud using the aws ec2 command line interface. You’ve connected the RDS with EC2 and deployed a Flask Application on EC2 that uses the database instance from RDS. You can also try deploying different applications over EC2 as well.