Using AWS Lambda MySQL: Accessing Data Simplified 101

Organizations today are focusing on cost-effective and auto-scale execution of applications or programs to increase productivity and performance. One such cost-effective and auto-scaling service provided by AWS is the AWS Lambda, where users can run their code, applications, or any backend service without managing the servers. 

AWS Lambda allows users to create functions and trigger them whenever there are any events in the databases. AWS Lambda functions can be used with many data storage systems like DynamoDB, AWS S3, AWS RDS, and more. In this article, you will learn to use AWS Lambda MySQL using the AWS RDS. You will learn to create a deployment package, and a Lambda function, test it and run it.

Table of Contents

• Prerequisites
• What is AWS Lambda?
  • Key Features of AWS Lambda
• What is MySQL?
  • Key Features of MySQL
• Configuring AWS Lambda MySQL to Access AWS RDS
  • Step 1: Create the Execution Role
  • Step 2: Create an AWS RDS Database Instance
  • Step 3: Create a Deployment Package
  • Step 4: Create the Lambda Function
  • Step 5: Test the Lambda Function
  • Step 6: Clean Up the Resources
• Conclusion

Prerequisites

• Basic understanding of serverless systems.

What is AWS Lambda?

Developed in 2014, AWS Lambda is a serverless computing service that allows you to run code for any application or backend service without the management of servers. AWS Lambda manages all the administration work like CPU utility, memory, server, resources, and more on its own. When you purchase a fixed server, you need to pay even when you are not using it to its full potential. But with AWS Lambda, you get the flexibility to pay only for the computation you use.

AWS Lambda executes your code only when it is required. As a result, AWS Lambda can automatically scale from a few requests per day to thousands per second. To know when the code is needed, you need to set triggers in AWS Lambda. AWS Lambda can connect with more than 200 AWS services and SaaS applications.

Key Features of AWS Lambda

Some of the main features of AWS Lambda are listed below:

• Functions Defined as Container Images: With AWS Lambda, users can use their favorite container image tooling, processes, and dependencies to develop, test and deploy the Lambda function.
• Lambda Extensions: Lambda extensions are used to enhance your Lambda functions by combining them with your selected tools for monitoring, security, observability, and governance.
• Database Accessibility: The database proxy controls a pool of database connections and transmits queries. As a result, the Lambda function can attain significant levels of concurrency without draining the database connections,
• Integration: AWS Lambda function integrates with a wide range of AWS services such as DynamoDB, API Gateway, S3, and more to develop functional applications.
• Code Signing: Code Signing enables users with trust and integrity controls that allow you to ensure that only unmodified code published by authorized developers is deployed to your Lambda services.
• Reduced Expenses: Since users need to only pay for their resources, the pay-as-you-go model avoids additional costs of unused time or storage.
• Function Blueprint: The function blueprints contain sample code demonstrating how to utilize the Lambda function with other AWS services or third-party applications. The blueprint also consists of function setup settings for Node.js and Python.

To know more about AWS Lambda, click here.

What is MySQL?

Developed in 1995, MySQL is a popular open-source SQL (Structured Query Language) database management system produced, distributed, and supported by Oracle Corporation.

MySQL runs on many systems, including Microsoft Windows, Oracle Solaris, AIX, Symbian, macOS, and Linux. It is also an important part of the modern LAMP stack that consists of a Linux-based operating system, Apache Web server, and PHP for processing.

Users can use SQL commands to retrieve, change or insert records in the MySQL tables. SQL is also used to define the table schema and configure the relationship between tables in MySQL databases.

Key Features of MySQL

Some of the main features of MySQL are listed below:

• Highly Scalable: MySQL can easily handle as many as 50 million rows and more using its multi-threading support making it a highly scalable Database.
• Clients and Tools: MySQL offers many utility tools and client programs with both command-line tools such as mysqlbackup and mysqldump and graphical interface programs such as Workbench.
• High Performance: MySQL is easy to configure and allows users to specifically configure Database for applications that increase performance.

To know more about MySQL, click here.

Configuring AWS Lambda MySQL to Access AWS RDS

In this article, you will launch the AWS Lambda MySQL Database to access AWS RDS engine instance in your default AWS VPC. You can create a sample database in the MySQL instance with a sample table. Create a lambda function for accessing the (ExampleDB) database in the MySQL instance.

Create a table called Employee, add a few records to it, and retrieve the records from the Employee table. Finally, invoke the lambda function and verify the results. The following steps for using AWS Lambda MySQL are listed below:

To create your function for AWS Lambda MySQL, you can follow the instructions for creating a Lambda function with the console. You can check the AWS version using the below command.

aws –version

Output:

Step 1: Create the Execution Role

You can create the AWS execution role, which gives your Lambda function to access AWS resources.

1. In the IAM console, open the roles page.
2. Click on Create role.
3. Create the role using the below property.

• Trusted entity – Lambda.
• Permissions – AWSLambdaVPCAccessExecutionRole.
• Role name – lambda-vpc-role.

The AWSLambdaVPCAccessExecutionRole consists of the permission that the function needs to manage network connections to a VPC.

Step 2: Create an AWS RDS Database Instance

The function for AWS Lambda MySQL creates a table Employee, which inserts a few records and then retrieves those records. The Employee table consists of the below schema.

Employee(EmpID, Name)

EmpID is the primary key. For adding a few records to the Employee table, you need to launch an AWS Lambda MySQL with AWS RDS instance in your default VPC with the ExampleDB database. 

Follow the instructions in the AWS RDS User Guide to create a MySQL DB instance and connect to a database on the same MySQL DB instance.

Use the below AWS CLI command.

aws rds create-db-instance --db-name ExampleDB --engine MySQL 
--db-instance-identifier MySQLForLambdaTest --backup-retention-period 3 
--db-instance-class db.t2.micro --allocated-storage 5 --no-publicly-accessible 
--master-username username --master-user-password password

Write the database name, username, and password of the DB instance. You will also need a host address (endpoint) of the DB instance, which you will get from the RDS console.

Step 3: Create a Deployment Package

The below Python code for AWS Lambda MySQL runs a SELECT query against the Employee table in the AWS Lambda MySQL RDS instance created within VPC. 

app.py

import sys
import logging
import rds_config
import pymysql
#rds settings
rds_host  = "rds-instance-endpoint"
name = rds_config.db_username
password = rds_config.db_password
db_name = rds_config.db_name

logger = logging.getLogger()
logger.setLevel(logging.INFO)

try:
	conn = pymysql.connect(host=rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
except pymysql.MySQLError as e:
	logger.error("ERROR: Unexpected error: Could not connect to MySQL instance.")
	logger.error(e)
	sys.exit()

logger.info("SUCCESS: Connection to RDS MySQL instance succeeded")
def handler(event, context):
	"""
	This function fetches content from MySQL RDS instance
	"""

	item_count = 0

	with conn.cursor() as cur:
    	cur.execute("create table Employee ( EmpID  int NOT NULL, Name varchar(255) NOT NULL, PRIMARY KEY (EmpID))")
    	cur.execute('insert into Employee (EmpID, Name) values(1, "Joe")')
    	cur.execute('insert into Employee (EmpID, Name) values(2, "Bob")')
    	cur.execute('insert into Employee (EmpID, Name) values(3, "Mary")')
    	conn.commit()
    	cur.execute("select * from Employee")
    	for row in cur:
        	item_count += 1
        	logger.info(row)
        	#print(row)
	conn.commit()

	return "Added %d items from RDS MySQL table" %(item_count)

By executing pymysql.connect() outside of the handler. You can reuse the database connection for better performance.

The rds_config.py contains credentials for the RDS MySQL instance.

#config file containing credentials for RDS MySQL instance
db_username = "username"
db_password = "password"
db_name = "ExampleDB"

The deployment package for AWS Lambda MySQL contains a .zip file that includes your Lambda function code and dependencies. 

Dependencies

pymysql: It is the library used for accessing your MySQL instance.

You can install dependencies with PIP and create a deployment package for using AWS Lambda MySQL. For instructions, you can follow Deploy Python Lambda functions with .zip file archives.

Step 4: Create the Lambda Function

You can create the function for AWS Lambda MySQL using the create-function command. You will get the subnet IDs and security group ID for your default VPC in the AWS VPC console.

Create the function for AWS Lambda MySQL using the below command.

aws lambda create-function --function-name  CreateTableAddRecordsAndRead --runtime python3.8 
--zip-file fileb://app.zip --handler app.handler 
--role arn:aws:iam::123456789012:role/lambda-vpc-role 
--vpc-config SubnetIds=subnet-0532bb6758ce7c71f,subnet-d6b7fda068036e11f,SecurityGroupIds=sg-0897d5f549934c2fb

Step 5: Test the Lambda Function

Invoke the function created for AWS Lambda MySQL using the invoke command. The Lambda function executes the SELECT query against the Employee table in the RDS MySQL instance and prints the results.

Use the below command to invoke the function for AWS Lambda MySQL using the invoke command.

aws lambda invoke --function-name CreateTableAddRecordsAndRead output.txt

Verify the function created for AWS Lambda MySQL using the below steps.

1. Reviewing the output.txt file.
2. Reviewing the results in the AWS Lambda console.
3. Verifying the results in the CloudWatch Logs.

CloudWatch Logs is used for monitoring and troubleshooting your system, using your existing system, application, and log files.

Step 6: Clean Up the Resources

You can delete the AWS resources you are no longer using to prevent unnecessary charges to your AWS account.

Follow the below steps to delete the function for AWS Lambda MySQL.

1. Go to the functions page of the Lambda console.
2. Click on the function that you created.
3. Click on Actions. Then click on Delete.
4. Click on Delete.

Follow the below steps to delete the execution role for AWS Lambda MySQL.

1. Go to the roles page of the IAM console.
2. Select the execution role that you have created.
3. Click on the Delete role.
4. Click on ‘Yes, delete.’

Conclusion

In this article, you learned to use AWS Lambda MySQL. This article also focused on creating and applying the Lambda functions to the MySQL database and verifying the results. Organizations use AWS Lambda functions due to its flexible pricing and automatically scaling structure. AWS Lambda also allows organizations to add custom logic to their AWS resources like AWS S3 buckets, AWS DynamoDB, or MySQL database to apply easy computation to data in the cloud.

Visit our Website to Explore Hevo

MySQL Server stores valuable business data that can be used to generate insights. Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. Hevo Data is a No-code Data Pipeline Solution that helps to transfer data from 100+ data sources to desired Data Warehouse. It fully automates the process of transforming and transferring data to a destination without writing a single line of code.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about working with AWS Lambda MySQL in the comments section below!