AWS Database Blog

Trigger an AWS Lambda function from Amazon RDS for MySQL or Amazon RDS for MariaDB using audit logs and Amazon CloudWatch

AWS Lambda is a serverless compute offering that helps you design event-driven architectures. It provides direct integration with multiple AWS services, including Amazon Aurora MySQL-Compatible Edition.

In this post, we show you how to invoke Lambda functions from Amazon Relational Databases Service (Amazon RDS) for MySQL and Amazon RDS for MariaDB using Amazon CloudWatch and messages published in audit logs. The same architecture can also be used with Amazon Aurora MySQL-Compatible Edition.

Solution overview

This solution consists of publishing RDS for MySQL or MariaDB audit logs to a CloudWatch log group, and creating a CloudWatch subscription filter for Lambda to trigger a Lambda function.

The following diagram illustrates the solution architecture and flow.

In this solution, audit logs generated by Amazon RDS for MySQL or Amazon RDS for MariaDB are published to a CloudWatch log group. The CloudWatch subscription filter filters the logs based on a user-defined pattern—when there is a pattern match, the subscription filter triggers the specified Lambda function and sends the log event to the Lambda function.

The logs received are base64 encoded and compressed with gzip format. We show you how to decode the log event to only get the desired payload for the Lambda function.

To deploy the solution, we complete the following steps (corresponding to the numbered components in the architecture diagram):

  1. Create a table in Amazon RDS for MySQL or MariaDB to trigger the Lambda function.
  2. Enable audit logs on the RDS for MySQL or MariaDB instance.
  3. Publish the audit logs to a CloudWatch log group.
  4. Create the Lambda function.
  5. Create a CloudWatch subscription filter.

Prerequisites

To follow along with this post, you should have an RDS for MySQL or MariaDB database instance for which you wish to trigger the Lambda function. For this post, our DB instance is named Lambda-trigger-mariadb. For instructions to create an RDS instance, refer to Create a RDS Instance.

Create a table in Amazon RDS for MySQL or MariaDB to trigger the Lambda function

In this step, we create a table with the same name as the Lambda function we need to trigger, with a single column to insert the JSON payload for the Lambda function. Complete the following steps to create your table:

  1. Connect to your RDS for MySQL or MariaDB Instance and select the database in which you wish to create the table.
  2. Create a table named MyLambda: 
    Create table MyLambda(json varchar(100));

Enable audit logs on the RDS for MySQL or MariaDB instance

You can capture DML queries running in the RDS instance by enabling audit logs. Unlike error logs, general logs, and slow query logs, there is no direct parameter in the parameter group to enable audit logs. You must use the MariaDB Audit Plugin using an option group to enable auditing in your RDS for MySQL or MariaDB instance. Complete the following steps:

  1. On the Amazon RDS console, create an option group.
  2. Add the MariaDB Audit Plugin.
  3. Modify your RDS instance to use the option group you just created.

You can enable audits logs in Amazon Aurora MySQL from the DB cluster parameter group by setting the parameter server_audit_logging to 1. Refer to Configuring an audit log to capture database activities for Amazon RDS for MySQL and Amazon Aurora with MySQL compatibility for detailed steps.

After you have enabled audit logs on the RDS instance, the logs are listed on the Amazon RDS console in the Logs section on the instance details page, as shown in the following screenshot.

Publish the audit logs to the CloudWatch log group

Now we need to publish the logs to CloudWatch. Complete the following steps:

  1. On the Amazon RDS console, select your RDS instance and choose Modify.
  2. In the Log exports section, select Audit log.
  3. Choose Continue.
  4. On the summary page, verify that Enable publish to CloudWatch logs has Audit log listed under New value.
  5. Choose Apply immediately.
  6. Choose Modify DB instance.

After you publish the audit logs to CloudWatch, the log group is listed on the CloudWatch console with the naming convention aws/rds/instance/<database-name>/audit.

Create the Lambda function

To create your Lambda function, complete the following steps:

  1. On the Lambda console, choose Functions in the navigation pane.
  2. Choose Create function.
  3. Choose Author from scratch.
  4. For Function name, enter a name (for this post, we use MyLambda).
  5. For Runtime, choose Python 3.12.
  6. For Architecture, choose x86_64.
  7. Choose Create function.
  8. In the Code section, enter the following code to get the JSON data inserted in the table as your Lambda function’s payload. You can modify the code as needed to perform other custom actions.

Note that the invocation payload size for an asynchronous Lambda function is 256 KB. For more details, refer to Lambda quotas.

import gzip
import json
import base64
def lambda_handler(event, context):
    try:
        encodedZippedData = event['awslogs']['data']#Locating the audit log event in the event object
        zippedData = base64.b64decode(encodedZippedData)#Decoding the event from base64
        byteData = gzip.decompress(zippedData)#Decompressing the gzip
        decodedData=byteData.decode("utf-8")#Decoding the data from byte to utf-8 string
        #Getting the Inserted value between the paranthesis 
        eventMessage=json.loads(decodedData) #Changing the string to json 
        eventMessage=eventMessage["logEvents"][0]['message']
        eventMessage=eventMessage.split("values(")
        eventMessage=eventMessage[1]
        eventMessage=eventMessage.split(")")
        eventMessage=eventMessage[0]
        eventMessage=eventMessage[2:-2]# Removing extra \\ 
        eventMessage=json.loads(eventMessage)
        print(eventMessage['payload1'])
        print(eventMessage['payload2'])
        #You have the payload in the eventmessage varaible write your own code to use it. 
    except Exception as e:
        print("Lambda execution failed due to error in the payload", e)


Create a CloudWatch subscription filter

A CloudWatch subscription filter lets you filter log data coming from a CloudWatch log group based on the terms or pattern you design and send it to Amazon Kinesis Data Streams, Amazon Kinesis Data Firehose, or Lambda. For this post, we create a filter on the audit logs for any INSERT operation that happens in the table named MyLambda. You can choose any tables or filter pattern published in the audit logs as needed for your use case.

Complete the following steps to create a CloudWatch subscription filter:

  1. On the CloudWatch console, under Logs in the navigation pane, choose Log groups.
  2. Choose the audit logs log group for your instance (aws/rds/instance/<database-name>/audit).
  3. On the Subscription filters tab, on the Create menu, choose Create Lambda subscription filter.
  4. For Lambda function, choose the Lambda function you created.
  5. For Log format, choose Other.
  6. For Subscription filter pattern, enter insert into MyLambda.
    The Subscription filter pattern filters the log entries for the exact string that you provide to trigger the Lambda function.

    Note Subscription filter is case-sensitive. It is not possible to add more than one pattern for a given filter; however, you can add more than one subscription filter in a log group.

  7. For Subscription filter name, enter a name of your choice – for this post, we use MyInsertFilter
  8. For Select log data to test, choose the name of your DB instance (for this post, we use Lambda-trigger-mariadb).
  9. Choose Test pattern to confirm that the filter pattern matches with entries in the log group.

    Note that the test pattern only shows the 50 most recent entries in the logs. Therefore, it’s possible that the complete logs have a matching pattern but the test returns zero matches.

  10. Choose Start Streaming.

The filter is now listed on the Subscription filters tab in the log group page on the CloudWatch console.

Verify that the solution is working

To verify the solution is working, connect to the RDS instance and run the query for which you created the metric filter.

In the following example, we connect to our MariaDB instance using the MySQL CLI client and run an insert query on the MyLambda table:

insert into MyLambda values('{"payload1":"data1","payload2":"data2"}');

We can see the Lambda function is triggered and a message is printed in the logs in the CloudWatch log group. The log group is listed on the CloudWatch console with the naming convention aws/lambda/<lambda-function-name>.

Tips and troubleshooting

In place of audit logs, you can use any of the other MySQL or MariaDB logs (such as general logs, slow query logs, or error logs) to trigger the Lambda function using the same architecture illustrated in this post.

Note than when using general or slow query logs, you have to set the log_output parameter as FILE from the RDS parameter group for both Amazon RDS for MySQL and Amazon RDS for MariaDB to push the logs to CloudWatch.

The example in this post shows how to trigger a Lambda function and get the payload. You can also use the Lambda function as a router function and trigger other Lambda functions from it-simply pass the name of the function to be triggered as the payload in JSON.

If the Lambda function does not trigger, check the following:

  • The logs are being generated and pushed to the CloudWatch log group
  • The subscription filter is created as expected with the correct Lambda function

Clean up

When you’re done with the solution, delete the resources you created to avoid ongoing charges.

  1. Disable the audits logs.
  2. Modify the RDS instance to stop publishing the logs to CloudWatch.
  3. Delete the CloudWatch log group.
  4. Delete the CloudWatch subscription filter.
  5. Delete the Lambda function.

Conclusion

In this post, we showed how to deploy a solution to trigger a Lambda function from your RDS for MySQL or MariaDB instance.

Try it out today and leave a comment if you have any questions or suggestions.

About the Author

Asad Aazam is a Solutions Architect at AWS with expertise in AWS Security services and AWS Database technologies such as Amazon Aurora, Amazon RDS, and Amazon DynamoDB. He helps homogeneous and heterogeneous database migrations and optimizations in the AWS Cloud. He currently holds 11 of 12 AWS Certificates. When not working, he likes to go on bike rides, travel, and enjoy the beauty of nature.