Check AWS RDS – MariaDB Replication Lag/ Replication Error using Lambda Funcation

Create python script in lambda function to check SECONDS_BEHIND_MASTER from SHOW SLAVE STATUS; MariaDB

Python

import pymysql

def lambda_handler(event, context):
    """
    Lambda function to check the replication lag (SECONDS_BEHIND_MASTER) 
    from SHOW SLAVE STATUS in MariaDB.

    Args:
        event (dict): AWS Lambda event object.
        context (object): AWS Lambda context object.

    Returns:
        dict: A dictionary containing the replication lag or an error message.
    """
    try:
        # MariaDB connection details (replace with your actual values)
        conn = pymysql.connect(
            host="your_mariadb_host",
            user="your_mariadb_user",
            password="your_mariadb_password",
            database="your_mariadb_database"
        )

        # Create a cursor object
        cursor = conn.cursor()

        # Execute the SHOW SLAVE STATUS command
        cursor.execute("SHOW SLAVE STATUS")

        # Fetch the results
        slave_status = cursor.fetchone()

        # Extract SECONDS_BEHIND_MASTER (index may vary depending on MariaDB version)
        seconds_behind_master = slave_status[32]  

        # Close the cursor and connection
        cursor.close()
        conn.close()

        return {
            "seconds_behind_master": seconds_behind_master
        }

    except Exception as e:
        return {
            "error": str(e)
        }

Explanation:

1. Import pymysql: This line imports the necessary library for interacting with MariaDB.
2. lambda_handler function: This is the main function that AWS Lambda will execute.
3. Connection details: Replace the placeholder values with your actual MariaDB host, user, password, and database name.
4. pymysql.connect(): Establishes a connection to the MariaDB server.
5. cursor.execute(): Executes the “SHOW SLAVE STATUS” command to retrieve replication status.
6. cursor.fetchone(): Fetches the first row of the result set (which contains the slave status information).
7. Extract SECONDS_BEHIND_MASTER: The code extracts the value of SECONDS_BEHIND_MASTER from the result. Note that the index (32 in this example) might be different depending on your MariaDB version. It’s safer to access it by column name (see improved version below).
8. Close connection: Closes the cursor and database connection.
9. Return result: Returns a dictionary containing the seconds_behind_master value.
10. Error handling: Includes a try-except block to catch any potential errors and return an error message.

Improved version with column name access:

Python

import pymysql

def lambda_handler(event, context):
    """
    Lambda function to check the replication lag (SECONDS_BEHIND_MASTER) 
    from SHOW SLAVE STATUS in MariaDB.

    Args:
        event (dict): AWS Lambda event object.
        context (object): AWS Lambda context object.

    Returns:
        dict: A dictionary containing the replication lag or an error message.
    """
    try:
        # MariaDB connection details (replace with your actual values)
        conn = pymysql.connect(
            host="your_mariadb_host",
            user="your_mariadb_user",
            password="your_mariadb_password",
            database="your_mariadb_database",
            cursorclass=pymysql.cursors.DictCursor  # Use DictCursor
        )

        # Create a cursor object
        cursor = conn.cursor()

        # Execute the SHOW SLAVE STATUS command
        cursor.execute("SHOW SLAVE STATUS")

        # Fetch the results
        slave_status = cursor.fetchone()

        # Extract SECONDS_BEHIND_MASTER by column name
        seconds_behind_master = slave_status['Seconds_Behind_Master']  

        # Close the cursor and connection
        cursor.close()
        conn.close()

        return {
            "seconds_behind_master": seconds_behind_master
        }

    except Exception as e:
        return {
            "error": str(e)
        }

This improved version uses pymysql.cursors.DictCursor to fetch results as dictionaries, allowing you to access the Seconds_Behind_Master value by its column name, which is more reliable and readable.

Before running:

• Install pymysql: Make sure to install the pymysql library in your Lambda environment. You can do this by including it in your Lambda deployment package or by using Lambda layers.
• Configure security: Ensure that your Lambda function has the necessary permissions to connect to your MariaDB database. This might involve setting up VPC access or using IAM roles.

This script provides a basic framework for checking replication lag in MariaDB. You can further customize it to your specific needs, such as adding alerts or integrating it with other monitoring tools.

Send a SNS alert if seconds_behind_master is higher than 10minutes

Python

import pymysql
import boto3

# Configure SNS client
sns_client = boto3.client('sns')
sns_topic_arn = "your_sns_topic_arn"  # Replace with your SNS topic ARN

def lambda_handler(event, context):
    """
    Lambda function to check replication lag and send an SNS alert 
    if SECONDS_BEHIND_MASTER is higher than 10 minutes.

    Args:
        event (dict): AWS Lambda event object.
        context (object): AWS Lambda context object.
    """
    try:
        # MariaDB connection details (replace with your actual values)
        conn = pymysql.connect(
            host="your_mariadb_host",
            user="your_mariadb_user",
            password="your_mariadb_password",
            database="your_mariadb_database",
            cursorclass=pymysql.cursors.DictCursor
        )

        cursor = conn.cursor()
        cursor.execute("SHOW SLAVE STATUS")
        slave_status = cursor.fetchone()

        # Extract SECONDS_BEHIND_MASTER by column name
        seconds_behind_master = slave_status['Seconds_Behind_Master']

        # Check if lag is greater than 10 minutes (600 seconds)
        if seconds_behind_master > 600:
            # Create SNS alert message
            message = f"Replication lag on MariaDB server is high! " \
                      f"SECONDS_BEHIND_MASTER: {seconds_behind_master} seconds"

            # Publish the message to the SNS topic
            sns_client.publish(
                TopicArn=sns_topic_arn,
                Message=message,
                Subject="MariaDB Replication Lag Alert"
            )

        cursor.close()
        conn.close()

        return {
            "seconds_behind_master": seconds_behind_master,
            "message": "Replication lag checked. Alert sent if necessary."
        }

    except Exception as e:
        return {
            "error": str(e)
        }

Explanation of changes:

1. Import boto3: Imports the AWS SDK for Python to interact with AWS services.
2. Configure SNS client: Creates an SNS client object and sets the sns_topic_arn variable to your actual SNS topic ARN.
3. Lag threshold: Checks if seconds_behind_master is greater than 600 seconds (10 minutes).
4. Create and send alert: If the lag is high, it creates an alert message and publishes it to the specified SNS topic using sns_client.publish().

Before running:

• Create an SNS topic: Create an SNS topic in your AWS account and subscribe to it (e.g., with your email address) to receive the alerts.
• Grant IAM permissions: Ensure that your Lambda function has the necessary IAM permissions to publish messages to your SNS topic. You can do this by attaching an IAM role to your Lambda function with the AmazonSNSFullAccess policy (or a more restrictive policy with only sns:Publish permission).

Now, this Lambda function will not only check the replication lag but also send you an alert via SNS if the lag exceeds 10 minutes. This allows you to proactively monitor your MariaDB replication and take action if needed.

Check also if seconds_behind_master is NULL

Python

import pymysql
import boto3

# Configure SNS client
sns_client = boto3.client('sns')
sns_topic_arn = "your_sns_topic_arn"  # Replace with your SNS topic ARN

def lambda_handler(event, context):
    """
    Lambda function to check replication lag and send an SNS alert 
    if SECONDS_BEHIND_MASTER is higher than 10 minutes or NULL.

    Args:
        event (dict): AWS Lambda event object.
        context (object): AWS Lambda context object.
    """
    try:
        # MariaDB connection details (replace with your actual values)
        conn = pymysql.connect(
            host="your_mariadb_host",
            user="your_mariadb_user",
            password="your_mariadb_password",
            database="your_mariadb_database",
            cursorclass=pymysql.cursors.DictCursor
        )

        cursor = conn.cursor()
        cursor.execute("SHOW SLAVE STATUS")
        slave_status = cursor.fetchone()

        # Extract SECONDS_BEHIND_MASTER by column name
        seconds_behind_master = slave_status['Seconds_Behind_Master']

        # Check if lag is greater than 10 minutes (600 seconds) or NULL
        if seconds_behind_master is None or seconds_behind_master > 600:
            # Create a more informative alert message
            if seconds_behind_master is None:
                message = "Replication is not running or has an error! " \
                          "SECONDS_BEHIND_MASTER is NULL."
            else:
                message = f"Replication lag on MariaDB server is high! " \
                          f"SECONDS_BEHIND_MASTER: {seconds_behind_master} seconds"

            # Publish the message to the SNS topic
            sns_client.publish(
                TopicArn=sns_topic_arn,
                Message=message,
                Subject="MariaDB Replication Alert"
            )

        cursor.close()
        conn.close()

        return {
            "seconds_behind_master": seconds_behind_master,
            "message": "Replication lag checked. Alert sent if necessary."
        }

    except Exception as e:
        return {
            "error": str(e)
        }

Key change:

• Check for NULL: The code now explicitly checks if seconds_behind_master is None (which indicates NULL in the database) using seconds_behind_master is None.
• Informative message: The alert message is improved to provide more context about the issue (whether it’s high lag or replication not running).

With this modification, the Lambda function will now send an SNS alert if:

• SECONDS_BEHIND_MASTER is greater than 10 minutes.
• SECONDS_BEHIND_MASTER is NULL, indicating a potential replication problem.

This helps you monitor your MariaDB replication more effectively and be alerted about various replication issues.