RDS MySQL Audit Logging Setup Guide

This guide covers enabling audit logging for RDS MySQL instance database-1 with export to CloudWatch and querying examples.

Requirements

1. Capture all DML (Data Manipulation) operations performed against the database
2. Store logs in CloudWatch for 90 days
3. Query event history in CloudWatch to be able to audit where DML operations are coming from
   • Specifically, all DML against the item table by an IP address other than 172.31.67.209
4. Keep RDS disk storage footprint from Audit logging to a minimum

Step 1: Create Custom Option Group

Create a new option group with the AUDIT plugin and your specified parameters:

# Create the option group
aws rds create-option-group \
    --option-group-name database-1-audit-og \
    --engine-name mysql \
    --major-engine-version 8.0 \
    --option-group-description "Audit logging option group for database-1" \
    --region us-east-1

Step 2: Add MARIADB_AUDIT_PLUGIN Option to Option Group

Add the MariaDB Audit Plugin with your specified configuration:

# Add MARIADB_AUDIT_PLUGIN option with custom settings
aws rds add-option-to-option-group \
    --option-group-name database-1-audit-og \
    --options '[
        {
            "OptionName": "MARIADB_AUDIT_PLUGIN",
            "OptionSettings": [
                {
                    "Name": "SERVER_AUDIT_EVENTS",
                    "Value": "QUERY_DML_NO_SELECT"
                },
                {
                    "Name": "SERVER_AUDIT_FILE_ROTATE_SIZE",
                    "Value": "1000000000"
                },
                {
                    "Name": "SERVER_AUDIT_FILE_ROTATIONS",
                    "Value": "1"
                }
            ]
        }
    ]' \
    --apply-immediately \
    --region us-east-1

Step 3: Modify DB Instance for Audit Logging

⚠️ IMPORTANT:

• This step may cause downtime. Consider scheduling during a maintenance window.
• CloudWatch logs export configuration changes are always applied immediately and cannot be scheduled for the maintenance window.
• In my testing, my instance did not restart; However, a restart may happen depending on depending on specific configuration/pending maintenance.
• If you are already exporting other logs to Cloudwatch, update the --cloudwatch-logs-export-configuration accordingly
  • eg: --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","slowquery"]}'

# Modify the database instance to use the new option group and enable log exports
aws rds modify-db-instance \
    --db-instance-identifier database-1 \
    --option-group-name database-1-audit-og \
    --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit"]}' \
    --apply-immediately \
    --region us-east-1

Step 4: Set CloudWatch Log Group Retention

After the modification completes and audit logging is active, set the retention policy:

# Wait for the log group to be created by RDS, then set retention to 90 days
aws logs put-retention-policy \
    --log-group-name /aws/rds/instance/database-1/audit \
    --retention-in-days 90 \
    --region us-east-1

Step 5: Verify Audit Logging Setup

Check that the option group is applied and audit logging is active:

# Verify option group is applied
aws rds describe-db-instances \
    --db-instance-identifier database-1 \
    --query 'DBInstances[0].OptionGroupMemberships' \
    --region us-east-1

# Verify the log group was created by RDS
aws logs describe-log-groups \
    --log-group-name-prefix /aws/rds/instance/database-1/audit \
    --region us-east-1

# Check CloudWatch log streams
aws logs describe-log-streams \
    --log-group-name /aws/rds/instance/database-1/audit \
    --region us-east-1

Monitoring and Verification

Check Audit Log Content

# View recent audit log entries
aws logs get-log-events \
    --log-group-name /aws/rds/instance/database-1/audit \
    --log-stream-name $(aws logs describe-log-streams \
        --log-group-name /aws/rds/instance/database-1/audit \
        --order-by LastEventTime \
        --descending \
        --max-items 1 \
        --query 'logStreams[0].logStreamName' \
        --output text) \
    --region us-east-1


# Use CloudWatch Log Insights for more flexible querying
# Pass in Epoch/unix timestamp eg 1753881202 for start-time/end-time

# Step 1: Start the query
QUERY_ID=$(aws logs start-query \
    --log-group-name /aws/rds/instance/database-1/audit \
    --start-time $(date -v-1H +%s) \
    --end-time $(date +%s) \
    --query-string 'fields @timestamp, @message
| parse @message /(?<timestamp>\d{8} \d{2}:\d{2}:\d{2}),(?<hostname>[^,]+),(?<user>[^,]+),(?<ip>[^,]+),(?<connection_id>[^,]+),(?<query_id>[^,]+),(?<command_type>[^,]+),(?<command_class>[^,]*),(?<query>.*),(?<retcode>\d+)/
| filter ip != "172.31.67.209" and query like /item/
| sort @timestamp desc' \
    --region us-east-1 \
    --query 'queryId' \
    --output text)

# Step 2: Wait a moment for the query to complete, then retrieve results
sleep 5
aws logs get-query-results \
    --query-id $QUERY_ID \
    --region us-east-1

# Aggregation example: Count DML operations by IP address other than 172.31.67.209

QUERY_ID_AGG=$(aws logs start-query \
    --log-group-name /aws/rds/instance/database-1/audit \
    --start-time $(date -v-1H +%s) \
    --end-time $(date +%s) \
    --query-string 'fields @timestamp, @message
| parse @message /(?<timestamp>\d{8} \d{2}:\d{2}:\d{2}),(?<hostname>[^,]+),(?<user>[^,]+),(?<ip>[^,]+),(?<connection_id>[^,]+),(?<query_id>[^,]+),(?<command_type>[^,]+),(?<command_class>[^,]*),(?<query>.*),(?<retcode>\d+)/
| filter ip != "172.31.67.209" and query like /item/
| stats count() by ip
| sort count desc' \
    --region us-east-1 \
    --query 'queryId' \
    --output text)

sleep 5
aws logs get-query-results \
    --query-id $QUERY_ID_AGG \
    --region us-east-1
{
    "queryLanguage": "CWLI",
    "results": [
        [
            {
                "field": "ip",
                "value": "172.31.67.210"
            },
            {
                "field": "count()",
                "value": "100000"
            }
        ]
    ],
    "statistics": {
        "recordsMatched": 100000.0,
        "recordsScanned": 104314.0,
        "estimatedRecordsSkipped": 0.0,
        "bytesScanned": 32878001.0,
        "estimatedBytesSkipped": 0.0,
        "logGroupsScanned": 1.0
    },
    "status": "Complete"
}

The above shows that 100,000 DML operations were performed by IP address 172.31.67.210 against the item table.

Important Notes

1. Downtime: Step 3 may cause downtime for the database, perform this during a maintenance window
2. Audit Log Size: With 1GB rotation size and 1 rotation, monitor disk usage, but it should be minimal
3. Cost Impact: Audit logging and CloudWatch logs will incur additional costs
4. Performance: Audit logging may have a slight performance impact on high-traffic databases
5. Test First: Always test these steps first in a test environment

MIT License
Copyright (c) 2025 Adam North
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.