How to Query into RDS MySQL Using AWS Lambda

​

Overview

​

Lab Details

1. This lab walks you through the creation of an RDS MySQL Database, creating a table and inserting some values using MySQL Workbench. Now we will create a Lambda function and write MySQL queries to read and write data into the database.
2. Duration: 75 minutes
3. AWS Region: US East (N. Virginia) us-east-1

​

Introduction

1. Creating a MySQL database on Amazon RDS will configure the database instance with the desired specifications such as instance type, storage size, and security settings.
2. Connecting to RDS MySQL from Lambda: Within your Lambda function code, use the programming language’s MySQL library or a MySQL connector to establish a connection with the RDS MySQL database. Pass the RDS endpoint, username, password, and database name to establish the connection.
3. Test and deploy: Test your Lambda function locally and ensure that it can successfully query the RDS MySQL database. Once you are satisfied with the functionality, deploy the Lambda function to AWS and test it in the production environment. Monitor the logs and performance of your Lambda function to ensure it is functioning as expected.

​

Architecture Diagram

​

Task Details

1. Sign in to AWS Management Console
2. Create a security group for RDS Instance
3. Create RDS Database Instance
4. Connect to the RDS instance using MySQL Workbench
5. Create a Lambda function
6. Deleting AWS Resources

​

Prerequisites

1. For testing this lab, it is necessary to download the MySql GUI Tool, To download it, go to the Download MySQL Workbench page. Based on your OS, select the respective option under Generally Available (GA) Releases. Download and Install.

​

Launching Lab Environment

1. To launch the lab environment, click on the Start Lab button.
2. Please wait until the cloud environment is provisioned. It will take less than a minute to provision.
3. Once the Lab is started, you will be provided with IAM username, Password, Access Key, and Secret Access Key.

Note : You can only start one lab at any given time

​

Files you need

• Download Zip
• Lambda code 2

​

Lab guide

​

Lab Steps

​

Task 1: Sign in to AWS Management Console

1. Click on the Open Console button, and you will get redirected to AWS Console in a new browser tab.
2. On the AWS sign-in page,

• Leave the Account ID as default. Never edit/remove the 12 digit Account ID present in the AWS Console. otherwise, you cannot proceed with the lab.
• Now copy your User Name and Password in the Lab Console to the IAM Username and Password in AWS Console and click on the Sign in button

​

Task 2: Create a Security Group for RDS instance

1. Make sure you are in the N.Virginia us-east-1 Region.
2. Click on Services and select EC2 under the Compute section.
3. On the left panel menu, select the Security Groups under the Network & Security section.
4. Click on the Create Security Group.
5. We are going to create a Security group for RDS with a 3306 port number enabled.
   • Security group name : Enter RDS_lab_sg
   • Description: Enter Security group for RDS instance
   • VPC: Select Default VPC
6. Click on the Add rule button under Inbound rules.
   • Type: Select MYSQL/Aurora
   • Source: Select Anywhere-IPv4
   • In the textbox add 0.0.0.0/0
7. Leave everything as default and click on the Create Security Group.

​

Task 3: Create RDS Database Instance

• Database creation method : Standard create

• Engine options : Select MySQL

• Version : Default
• Templates : Select Sandbox Note: Selecting the Sandbox as a template is compulsory, else database won’t be created.

• DB instance identifier : mydatabaseinstance
• Master username: mydatabaseuser
• Credentials management: Select Self managed
• Master password and Confirm password: mydatabasepassword

• DB instance class : db.t3.micro — 2 vCPU, 1 GiB RAM

• Storage type : General Purpose (SSD)
• Allocated storage : 20 (default)
• Click on Additional storage configuration to expand.
• Enable storage autoscaling : Uncheck

• Public access : Select Yes
• VPC security groups : Select Choose existing Note: Remove the default one and select RDS_lab_sg instead

• Initial database name: mydatabase
• DB parameter group: default
• Option group: default
• Enable automated backups: uncheck

Note: Leave all the other setting as default

4. Click on Create database
5. Navigate to Databases.
6. On the RDS console, the details for the new DB instance appear. The DB instance has a status of creating until the DB instance is ready to use. When the state changes to Available, you can connect to the DB instance. It can take up to 20 minutes before the new instance status becomes Available.

​

Task 4: Connecting to RDS Database on a DB Instance using the MySQL Workbench

1. To connect to a database on a DB instance using MySQL monitor, find the endpoint (DNS name) and port number for your DB Instance.
   • Navigate to Databases and click on mydatabaseinstance.
   • Under Connectivity & security section, copy and note the endpoint and port. Example:
     • Endpoint : mydatabaseinstance.c81x4bxxayay.us-east-1.rds.amazonaws.com
     • Port : 3306
2. Open MySQL Workbench. Click on the MYSQL Connection plus icon IN your left side.
   • Connection Name: Enter a sample name MyDatabseConnection
   • Connection Method: Select Standard (TCP/IP)
   • Host Name: Enter your RDS endpoint : mydatabaseinstance.c81x4bxxayay.us-east-1.rds.amazonaws.com
   • Port: 3306
   • Username: mydatabaseuser
   • Password: Click on Store in Vault and enter password mydatabasepassword Click on the OK button.
   • Now click on the test connection button. It shows success connection message.
   • Now click on OK button and then click on the OK button in the main screen.
3. A database connection will be created in MySQL Workbench.
4. Wait for a few seconds to connect to your RDS Machine.

6. Now copy the below MySQL command and paste it in the Query tab.

7. MySQL Query Explanation:

• Create a Database StudentDB.

CREATE DATABASE StudentDB;

• Select the database

Use StudentDB;

• Create a table student with fields - student id, studnentName, Course and Semester.

CREATE TABLE students (studentId INT AUTO_INCREMENT, studentName VARCHAR(50) NOT NULL, Course VARCHAR(55),Semester VARCHAR(50) NOT NULL,PRIMARY KEY (studentId));

• Insert three values to the table.

INSERT INTO students(studentName, Course, Semester) VALUES ('Paul', 'MBA', 'Second');

• View the table data.

SELECT * FROM students;

8. Now Click on the Execute icon to start the execution and wait for a few minutes.

9. Now You will be able to see the students table with these following values.

​

Task 5: Create a Lambda function

1. Navigate to the Services menu at the top, then click on Lambda under the Compute section.
2. Click on Create function
   • Select Author from Scratch
   • Function Name : Enter MyRDSLambda
   • Runtime : Select the latest version of Python
   • Permissions :
     • Change default execution role : Select Use an existing role
   • Existing Role : Select task182_role_<RANDOM_NUMBER>. This role has already been created for you.
3. Click on Create Function Button.
4. Once the Lambda Function is created successfully, it will look like the screenshot below:

5. Click on this link to download the Lambda zip code
6. Click on the Upload from button and select the .zip file option
   • Click the Upload button and upload the zip file that we just downloaded and the click on the Save button.
   
7. Now in the code section, go to zip file and select lambda_function.py
8. In the code, go to line 5 and replace the endpoint, username and password in the python code with your values and click on the Deploy button. Wait till the deployment gets success.
9. Lambda code explanation :
   • The zip file contains pre installed python module used for performing MySQL queries in Python.
   • First we provide the RDS endpoint, username, password and database values.
   • Create a  connection to the RDS instance.
   • Perform MySQL query “SELECT * FROM students” to fetch the table data.
   • Create a json value and print the table data in the lambda function.
10. Now click on the Test button and select Create new test event,
    • Event name : Enter TestRDS
    • Leave everything as default
    • Click the Save button.
11. Now click on the Invoke button and you will be able to see the students table values in the Lambda output (JSON).
12. Scroll down the Execution result : (String)
13. Go to Function code, inside the lambda_function.py replace the existing code with the below:
    import json
    import pymysql
    
    RDS_endpoint = "myrdsinstance.cdegnvsebaim.us-east-1.rds.amazonaws.com"
    UserName = "mydatabaseuser"
    Password = "mydatabasepassword"
    DatabaseName = "StudentDB"
    
    connectionString = pymysql.connect(RDS_endpoint, user=UserName, passwd=Password, db=DatabaseName)
    
    def lambda_handler(event, context):
        header = []
        row_json = []
        table_json = {}
        pointer = connectionString.cursor()
        pointer.execute("INSERT INTO students(studentName, Course, Semester) VALUES ('Elizabeth', 'Art', 'first')")
        connectionString.commit()
        connectionString.close()
        return ("Insertion Success")
    
14. On line number 5, replace the endpoint, username and password in the python code with your values and click on the Deploy button.
15. Lambda code explanation :
    • This code is used to insert a value into the students table.
    • First we provide the RDS endpoint, username, password and database values.
    • Create a  connection to the RDS instance.
    • Perform MySQL query “INSERT INTO students(studentName, Course, Semester) VALUES (‘Elizabeth’, ‘Art’, ‘first’)” to insert value into the table.
16. Click on the Test button and select TestRDS and click the Invoke button, you will get an insertion success message in lambda response.
17. Now navigate to the MySQL Workbench application and replace the existing query with the below.
    Use StudentDB;
               
    SELECT * FROM students;
    
    
18. Now Click on the  Execute icon to start the execution and wait for a few minutes.

Note : Since the MySQL workbench was not active for a long time, it will take a few minutes to complete the execution. if you find difficulty then please close the connection and reconnect to RDS again from MySQL Workbench.

21. You will be able to see the new row that we just added to the table through the Lambda function.

DO you know?

MySQL is a popular open-source relational database management system (RDBMS) that is widely used for managing structured data. MySQL in RDS refers to the MySQL database engine offered as a fully managed service within the AWS ecosystem. It allows you to run MySQL databases in the cloud without having to worry about the underlying infrastructure management tasks, such as hardware provisioning, database setup, patching, backups, and automatic software updates.

1. Once the lab steps are completed, please click on the Validation button on the left side panel.

​

Task 6: Delete AWS Resources

Delete Lambda Function

1. Make sure you are in the US East (N. Virginia) us-east-1 Region.
2. Navigate to Lambda by clicking on the Services menu at the top, then click on Lambda in the Compute section.
3. Select the lambda function MyRDSLambda and click Actions and click Delete button.
4. Enter the confirm in the required field and click the Delete button to confirm deletion.

Delete RDS Instance

1. Make sure you are in the US East (N. Virginia) us-east-1 Region.
2. Navigate to RDS by clicking on the Services menu available under the Database section.
3. Select Databases from the left side menu.
4. Select the RDS instance mydatabaseinstance and click on Actions and select Delete.
5. Confirmation Prompt :
   • Uncheck the option : Create the final snapshot
   • Check the I Acknowledge option
   • In textbox : Enter delete me
   • Now click on the Delete button.
6. Now click on the refresh button and you will be able to see that the status of the RDS instance is deleting. It will take 10 to 20 Minutes to delete the instance.

​

Completion and Conclusion

1. You have successfully logged into AWS management console.
2. You have successfully created a security group for RDS instance.
3. You have successfully created a RDS Instance.
4. You have successfully connected to RDS using MySQL workbench.
5. You have successfully created a Lambda function.

​

End Lab

1. Sign out of AWS Account.
2. You have successfully completed the lab.
3. Once you have completed the steps, click on End Lab from the IP Lab Portal dashboard.

​

What gets checked

• Create an AWS Lambda Function — Check whether a Lambda Function is created or not
• Check whether RDS database Engine type is mysql — Check whether allowed database type mysql is deployed or not