如何在MySQL RDS表插入记录时发送AWS SNS通知？

Hey there! Setting up SNS notifications for new inserts in your MySQL RDS table is totally doable, and there are a couple of solid approaches depending on your needs. Let's walk through each one with clear steps and code examples.

Approach 1: MySQL Triggers + AWS Lambda (Polling a Middle Table) #

This is great if you need to capture the exact content of each inserted record and want a straightforward setup that doesn't require extra managed services beyond Lambda.

Step 1: Create a Middle Event Table in RDS #

First, we need a place to log insert events since MySQL can't call SNS directly. Create a table to store details about each insert:

CREATE TABLE insert_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source_table VARCHAR(255) NOT NULL,
    inserted_data JSON NOT NULL,
    event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed BOOLEAN DEFAULT FALSE
);

Step 2: Add an Insert Trigger to Your Target Table #

Create a trigger that fires after every insert on your target table, writing the new record's data to our event table. Replace your_target_table and the column references with your actual table/fields:

DELIMITER //
CREATE TRIGGER after_target_table_insert
AFTER INSERT ON your_target_table
FOR EACH ROW
BEGIN
    INSERT INTO insert_events (source_table, inserted_data)
    VALUES ('your_target_table', JSON_OBJECT(
        'id', NEW.id,
        'username', NEW.username,
        'created_at', NEW.created_at -- Add all columns you want to include in the notification
    ));
END //
DELIMITER ;

Step 3: Build a Lambda Function to Process Events & Send SNS Notifications #

Create a Lambda function that connects to your RDS instance, fetches unprocessed events, sends them to your SNS topic, and marks them as processed. Make sure your Lambda's security group allows outbound access to your RDS port (usually 3306), and that your Lambda execution role has permissions to publish to SNS and connect to RDS.

Here's a Python example for the Lambda logic:

import boto3
import mysql.connector
from mysql.connector import Error

def lambda_handler(event, context):
    # Initialize SNS client and define your topic ARN
    sns_client = boto3.client('sns')
    SNS_TOPIC_ARN = 'arn:aws:sns:us-east-1:123456789012:your-notification-topic'
    
    # Connect to RDS MySQL
    db_connection = None
    try:
        db_connection = mysql.connector.connect(
            host='your-rds-endpoint.rds.amazonaws.com',
            database='your_database_name',
            user='your_db_user',
            password='your_db_password'
        )
        
        if db_connection.is_connected():
            cursor = db_connection.cursor(dictionary=True)
            
            # Fetch unprocessed insert events
            cursor.execute("SELECT * FROM insert_events WHERE processed = FALSE")
            events = cursor.fetchall()
            
            for event in events:
                # Craft the SNS message
                notification_msg = (
                    f"New record inserted into {event['source_table']}\n"
                    f"Timestamp: {event['event_timestamp']}\n"
                    f"Data: {event['inserted_data']}"
                )
                
                # Send the notification
                sns_client.publish(
                    TopicArn=SNS_TOPIC_ARN,
                    Message=notification_msg,
                    Subject="RDS: New Record Inserted"
                )
                
                # Mark event as processed to avoid duplicates
                cursor.execute(
                    "UPDATE insert_events SET processed = TRUE WHERE id = %s",
                    (event['id'],)
                )
                db_connection.commit()
                
            print(f"Processed {len(events)} insert events")

    except Error as db_err:
        print(f"Database connection error: {db_err}")
    finally:
        if db_connection and db_connection.is_connected():
            cursor.close()
            db_connection.close()
    
    return {
        'statusCode': 200,
        'body': f"Successfully processed {len(events)} events"
    }

Step 4: Schedule the Lambda to Run #

Use Amazon EventBridge (formerly CloudWatch Events) to set up a scheduled rule that triggers your Lambda function at your desired frequency (e.g., every 1 minute for near-real-time notifications).

Approach 2: AWS DMS CDC + Lambda + SNS #

If you want to avoid adding triggers to your database (to minimize performance impact) or need to capture all data changes (not just inserts), this CDC-based approach is ideal.

Step 1: Prepare RDS for CDC #

First, enable binary logging on your RDS instance:

• Modify your RDS parameter group to set binlog_format = ROW
• Ensure your RDS instance has automated backups enabled (CDC relies on binary logs)

Step 2: Set Up AWS DMS Task #

• Create a DMS replication instance with access to your RDS source
• Configure the source endpoint as your MySQL RDS instance
• Set the target endpoint to Amazon Kinesis Data Streams (or you can use Lambda directly as the target, but Kinesis adds buffering and retry capabilities)
• Create a DMS task with CDC enabled, filtering for only INSERT operations if you don't need other changes

Step 3: Trigger Lambda from Kinesis #

• Create a Lambda function that triggers from your Kinesis stream
• In the Lambda, parse the CDC records to extract insert events
• Use the SNS SDK to send notifications with the inserted data

Step 4: Handle Idempotency #

Since Kinesis delivers records at least once, make sure your Lambda handles duplicate events (e.g., track processed record IDs in a DynamoDB table) to avoid duplicate notifications.

Key Things to Remember #

• Permissions: Ensure your Lambda execution role has AmazonSNSFullAccess (or a more restricted policy allowing publish to your specific topic) and access to RDS/Kinesis as needed.
• Performance: Triggers add minimal overhead to insert operations, but if you have high throughput, the CDC approach is more scalable.
• Message Format: Format your SNS messages clearly (JSON is great for machine-readable notifications) so recipients can easily parse the inserted data.

内容的提问来源于stack exchange，提问作者Somashekar Muniyappa