Add support for running SQL statements on startup of RDS for MySQL Instance

hello, To add support for running SQL statements on startup of an RDS for MySQL instance, you can use an AWS Lambda function triggered by Amazon CloudWatch Events to perform the necessary tasks. Since the init_file parameter is not supported on RDS, this approach ensures the required SQL commands are executed when the instance starts.

1.Create an AWS Lambda Function:

• Write a Lambda function that connects to your RDS instance and runs the desired SQL statements. For example, truncating a table:

import pymysql
import os

def lambda_handler(event, context):
    connection = pymysql.connect(
        host=os.environ['DB_HOST'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        database=os.environ['DB_NAME']
    )
    cursor = connection.cursor()
    cursor.execute("TRUNCATE TABLE your_table_name")
    connection.commit()
    cursor.close()
    connection.close()

• Store database credentials in AWS Secrets Manager for security.

2. Configure Environment Variables:

• Set environment variables in the Lambda function for database connection parameters (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME).

3. Set Up CloudWatch Event Rule:

• Create a CloudWatch Events rule to trigger the Lambda function based on RDS instance state changes (specifically the RDS DB Instance State Change event).

4.Attach IAM Role to Lambda:

• Ensure the Lambda function has the necessary IAM role permissions to access RDS and Secrets Manager.

Hi,

The simplest way that I can think of is to have a Lambda function executing the statements that you need after it receives the EventBridge event notifying of the RDS instance start.

RDS-006 seems to be the best choice in the list of existing events: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.Messages.html#USER_Events.Messages.instance

Best,

Didier