Best Practices for Aurora MySQL Advanced Auditing with Amazon RDS Proxy

Author: Ramu Varanasi, Technical Account Manager, AWS Enterprise Support

---

Introduction

Many organizations running Amazon Aurora MySQL behind Amazon RDS Proxy rely on the init_connect parameter with stored procedures to capture session-level audit data for compliance. While this approach works, it creates a fragile dependency: the rdsproxyadmin internal user must retain EXECUTE privileges on the stored procedure. During proxy maintenance events, RDS may drop and recreate rdsproxyadmin, which can revoke these grants and cause a production outage — every new connection fails because the init_connect procedure cannot execute.

Aurora MySQL Advanced Auditing provides a robust, built-in alternative that eliminates this dependency entirely. It captures richer audit data (connections, queries, DDL, DML, DCL) at the engine level with zero reliance on database user privileges.

This post shows you how to:

• Configure Aurora MySQL Advanced Auditing as a replacement for init_connect-based audit logging
• Handle the RDS Proxy IP masking limitation using SQL comment tagging
• Filter out internal noise from rdsadmin and rdsproxyadmin to control log volume and cost
• Query audit logs efficiently using Amazon CloudWatch Logs Insights

---

Best Practice 1: Enable Advanced Auditing with the Right Event Types

Aurora MySQL Advanced Auditing uses the MariaDB Audit Plugin built into the engine. Configure it through a custom DB cluster parameter group.

Step 1: Create a custom cluster parameter group

1. Open the Amazon RDS console.
2. Choose Parameter groups, then Create parameter group.
3. Select aurora-mysql8.0 as the family, DB Cluster Parameter Group as the type, and give it a name (e.g., aurora-mysql-audit).

Step 2: Set the audit parameters

Modify the following parameters in your custom cluster parameter group:

Parameter	Value	Description
server_audit_logging	ON	Enables audit logging
server_audit_events	CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL	Event types to capture
server_audit_excl_users	rdsadmin,rdsproxyadmin	Exclude internal users (critical — see Best Practice 3)

Step 3: Enable CloudWatch Logs export

When creating or modifying your Aurora cluster, enable audit log export:

aws rds modify-db-cluster \
  --db-cluster-identifier your-cluster-name \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit"]}' \
  --region us-east-1

Step 4: Apply the parameter group and reboot

Associate the custom parameter group with your cluster and reboot the writer instance for changes to take effect.

What gets captured

Each audit log entry follows this format:

timestamp,server_id,username,host,connection_id,query_id,event_type,database,query,return_code

Example entries:

# Connection event
20260413 17:39:22,audit-test-mysql,admin,172.31.31.93,445,0,CONNECT,auditdb,,0

# DDL — table creation
20260413 17:39:22,audit-test-mysql,admin,172.31.31.93,445,100,QUERY,auditdb,'CREATE TABLE test_audit (id INT PRIMARY KEY, data VARCHAR(200))',0

# DML — insert
20260413 17:39:22,audit-test-mysql,admin,172.31.31.93,445,101,WRITE,auditdb,test_audit,

# DML — select
20260413 17:39:33,audit-test-mysql,admin,172.31.31.93,445,102,READ,auditdb,test_audit,

# DCL — password change (password automatically masked)
20260413 18:12:47,audit-test-mysql,admin,172.31.31.93,445,200,QUERY,auditdb,'ALTER USER admin IDENTIFIED WITH mysql_native_password BY <secret>',0

Notice that passwords are automatically masked as <secret> — no additional configuration required.

---

Best Practice 2: Identify Application Sources Through SQL Comment Tagging

The problem: RDS Proxy masks client IP addresses

When clients connect through RDS Proxy, the audit log records the proxy's ENI IP address instead of the original client IP:

Connection Path	Source IP in Audit Log	Client Identifiable?
Direct to Aurora	172.31.31.93 (actual client)	✅ Yes
Via RDS Proxy	172.31.4.38 (proxy ENI)	❌ No

For environments with multiple Lambda functions or microservices sharing a single proxy endpoint, this makes it impossible to trace a query back to its source using IP alone.

The solution: Embed identifiers in SQL comments

SQL comments are captured verbatim in audit logs. By prefixing queries with a structured comment, you can tag each query with its source:

/* lambda=order-processor, request_id=abc-123 */
INSERT INTO orders VALUES (1001, 'PENDING', NOW());

/* lambda=payment-service, request_id=def-456 */
UPDATE orders SET status = 'PAID' WHERE order_id = 1001;

/* lambda=user-auth, request_id=ghi-789 */
SELECT * FROM users WHERE user_id = 42;

Implementation in Lambda (Python example)

import os

FUNCTION_NAME = os.environ.get('AWS_LAMBDA_FUNCTION_NAME', 'unknown')

def execute_audited_query(cursor, sql, params=None):
    request_id = os.environ.get('AWS_LAMBDA_LOG_STREAM_NAME', 'unknown')
    tagged_sql = f"/* lambda={FUNCTION_NAME}, request_id={request_id} */ {sql}"
    cursor.execute(tagged_sql, params)

Querying tagged audit logs in CloudWatch Insights

fields @timestamp, @message
| filter @message like /lambda=order-processor/
| sort @timestamp desc
| limit 100

This approach gives you per-function traceability without requiring direct connections or proxy changes.

---

Best Practice 3: Exclude Internal Users to Control Log Volume and Cost

The problem: Internal users generate massive audit noise

Without exclusion filters, two internal users flood your audit logs:

• rdsadmin — Aurora's internal management user runs heartbeat checks (rds_heartbeat2 inserts), replica status queries, plugin checks, and configuration reads every 1–2 seconds.
• rdsproxyadmin — RDS Proxy's health check user runs SELECT ... FROM information_schema.replica_host_status and SHOW STATUS LIKE 'Uptime' approximately 6 times per second per proxy ENI.

Volume impact: In testing, rdsproxyadmin alone generated an estimated 500,000+ audit entries per day — all health checks with zero compliance value.

Here is a sample of what rdsadmin noise looks like in raw audit logs:

1768172702962891,...,rdsadmin,localhost,13,25721,QUERY,mysql,
  'INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1768172702959)
   ON DUPLICATE KEY UPDATE value = 1768172702959',0
1768172704954418,...,rdsadmin,localhost,11,25741,QUERY,,
  'SELECT durable_lsn, current_read_point, server_id, last_update_timestamp
   FROM information_schema.replica_host_status',0

These entries repeat every 1–2 seconds and provide no audit value for compliance.

The solution: Always set server_audit_excl_users

server_audit_excl_users = rdsadmin,rdsproxyadmin

This single parameter change:

• Eliminates hundreds of thousands of daily log entries
• Reduces CloudWatch Logs ingestion and storage costs significantly
• Keeps only application-relevant audit data

Important: This parameter accepts a comma-separated list with no spaces. It is applied at the cluster level and takes effect without a reboot.

---

Best Practice 4: Use CloudWatch Logs Insights for Audit Analysis

Audit logs are exported to the CloudWatch log group /aws/rds/cluster/<cluster-name>/audit. Aurora writes to multiple parallel log streams (audit.log.0 through audit.log.3), and logs typically appear within 30 seconds of query execution.

Useful CloudWatch Insights queries

All failed queries (syntax errors, permission denied):

fields @timestamp, @message
| filter @message like /,1064$/ or @message like /,1045$/
| sort @timestamp desc
| limit 50

All DDL operations (schema changes):

fields @timestamp, @message
| filter @message like /QUERY_DDL/ or @message like /CREATE/ or @message like /DROP/ or @message like /ALTER/
| sort @timestamp desc

Connection activity for a specific user:

fields @timestamp, @message
| filter @message like /CONNECT/ and @message like /app_user/
| sort @timestamp desc

Tail audit logs from the CLI (excluding internal users):

aws logs tail /aws/rds/cluster/your-cluster/audit \
  --region us-east-1 --since 5m --follow \
  | grep -v rdsproxyadmin | grep -v rdsadmin

---

Why Advanced Auditing Is More Resilient Than init_connect

The key architectural difference:

Aspect	init_connect Approach	Advanced Auditing
Mechanism	Stored procedure called on every connection	Engine-level MariaDB Audit Plugin
Dependency on rdsproxyadmin	✅ Requires EXECUTE grant	❌ None
Survives proxy maintenance	❌ Grant can be revoked	✅ Always active
Data captured	Only what the procedure logs	Connections, queries, tables, DDL, DML, DCL
Configuration	Database-level (procedure + trigger)	Cluster parameter group
Failure mode	Connection failures (production outage)	Silent — worst case is missing logs

Advanced Auditing is configured as a cluster parameter, not a database object. It has zero dependency on any database user's privileges. Proxy maintenance events that drop and recreate rdsproxyadmin have no impact on audit logging.

---

Conclusion

Aurora MySQL Advanced Auditing provides a more resilient, comprehensive, and operationally simpler approach to database audit logging than init_connect-based solutions — especially when using RDS Proxy. By following these best practices:

1. Enable auditing with the right event types via cluster parameter groups
2. Tag queries with SQL comments to maintain source traceability through RDS Proxy
3. Exclude internal users (rdsadmin, rdsproxyadmin) to control log volume and cost
4. Use CloudWatch Logs Insights for efficient audit log analysis

You eliminate the fragile dependency on rdsproxyadmin privileges that can cause production outages during proxy maintenance, while gaining richer audit data with built-in password masking and near-real-time CloudWatch integration.

Next steps

• Aurora MySQL Advanced Auditing documentation
• Amazon RDS Proxy documentation
• CloudWatch Logs Insights query syntax

---

Author bio

Ramu Varanasi is a Technical Account Manager at AWS Enterprise Support specializing in database services for financial services customers. With expertise in Aurora, RDS, and database migration strategies, Ramu Varanasi helps customers design resilient, compliant database architectures on AWS.

Suggested tags: Amazon Aurora, MySQL, Advanced Auditing, RDS Proxy, CloudWatch Logs, Compliance, Database Security, Best Practices