Assistance Required for Configuring Audit Logs for Replication Events for RDS-MySQL

Hi Mayank,

Thank you for reaching out with your question. Configuring audit logs for replication events can be tricky, but with the right steps, we can get you closer to a solution.

---

Clarifying the Issue

You’re aiming to configure audit logs on an RDS MySQL replica node to capture specific replication events, such as INSERT and UPDATE queries performed on the primary node and replicated to the replica. Despite setting up the MariaDB audit plugin with the QUERY_DML_NO_SELECT event, these replication events are not being captured in your audit logs. Let’s address this gap.

---

Key Terms

• MariaDB Audit Plugin: A tool for logging query activities in MariaDB and MySQL-compatible databases.
• QUERY_DML_NO_SELECT: An event filter for logging data manipulation queries like INSERT, UPDATE, and DELETE, excluding SELECT statements.
• Binary Logs: Files that log all changes made to a MySQL database, used for replication and recovery.
• Replication Events: Database changes propagated from the source (primary) node to the target (replica) node during replication.

---

The Solution (Our Recipe)

Here’s a step-by-step guide to troubleshoot and configure replication audit logs effectively:

1. Verify Plugin Configuration:

   • Confirm the MariaDB audit plugin is enabled and working correctly. Run:

     SHOW VARIABLES LIKE 'server_audit%';

     Ensure server_audit_logging is set to ON and your filter (QUERY_DML_NO_SELECT) is applied correctly.

2. Understand Replication Logging Behavior:

   • Replication events are often treated differently since they are processed internally by the replica. The MariaDB audit plugin may not capture these events natively.

3. Leverage Binary Logs:

   • Enable binary logs on the replica to track detailed replication events. Configure the logging format to ROW or MIXED for a more granular view:

     SET GLOBAL binlog_format = 'ROW';

   • Keep in mind that binary logs are primarily designed for replication and disaster recovery but can provide valuable insights.

4. Implement Custom Logging:

   • Use database triggers on the replica to explicitly log replication events into a custom table. This is especially useful if audit plugins don’t meet your needs. Example:

     CREATE TRIGGER replication_log AFTER INSERT ON your_table
     FOR EACH ROW
     INSERT INTO audit_log_table (log_time, query_type, user)
     VALUES (NOW(), 'REPLICATION_INSERT', USER());

   • Triggers provide flexibility by capturing specific events directly in the database, bypassing the limitations of plugin-based logging.

5. Consider AWS Native Monitoring:

   • Explore Amazon CloudWatch or AWS Database Activity Streams. These tools might provide an alternative way to track database activity, including replication-related changes, with greater integration into AWS.

6. Engage AWS Support:

   • Open a support case with AWS to confirm whether replication-specific audit logging is supported. RDS environments have managed configurations, and AWS Support can provide clarity on any limitations or alternative solutions.

---

Closing Thoughts

Capturing replication events in audit logs often requires creative solutions due to the unique nature of replication in MySQL. By combining binary logs, triggers, and AWS monitoring tools, you can establish a comprehensive logging framework that meets your needs.

---

I hope this enhanced approach helps, Mayank! Feel free to reach out with additional questions. Best of luck with your setup!

---

Cheers, Aaron 😊