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

0

We would like to have a certain table truncated when the RDS for MySQL instance is started using the server parameter "init_file" which currently is NOT supported on RDS.

(The statements should be ran by the "admin" user in order to avoid "rdsadmin" superuser high privilege abuse)

profile picture
asked a month ago115 views
2 Answers
3

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.
  1. Configure Environment Variables:
  • Set environment variables in the Lambda function for database connection parameters (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME).
  1. 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.
profile picture
EXPERT
answered a month ago
1

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

profile pictureAWS
EXPERT
answered a month ago
profile picture
EXPERT
reviewed a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions