RDS MySQL Audit Logging Setup

Purpose: Enable logging in RDS MySQL for auditing and troubleshooting. This allows you to track which user executed which query and monitor slow or general queries.

Steps:

1. Create or Modify a Custom DB Parameter Group

• Create or use an existing custom DB parameter group for your RDS MySQL instance.
• Set the following parameters:

• Attach the parameter group to your DB instance.
• Reboot the RDS instance for changes to take effect.

2. View General Query Log

• General queries are stored in the mysql.general_log table.
• Format of user_host column: username[username] @ [IP]
• Retrieve last queries executed by a specific user:

SELECT event_time,
       user_host,
       command_type,
       CONVERT(argument USING utf8) AS sql_text
FROM mysql.general_log
WHERE command_type = 'Query'
  AND user_host LIKE '%username[%] @%'
ORDER BY event_time DESC
LIMIT 20;

Replace username with the MySQL username. CONVERT(argument USING utf8) converts hex-encoded queries to readable SQL.

3. View Slow Queries

SELECT start_time,
       user_host,
       query_time,
       sql_text
FROM mysql.slow_log
WHERE user_host LIKE '%username[%] @%'
ORDER BY start_time DESC
LIMIT 20;

• Use this to identify queries exceeding the long_query_time threshold.

4. View Audit Log Queries

SELECT event_time,
       user_host,
       action,
       sql_text
FROM mysql.audit_log
WHERE user_host LIKE '%username[%] @%'
ORDER BY event_time DESC
LIMIT 20;

• Shows all queries executed by the user via the audit plugin.
• Useful for auditing and compliance purposes.

5. Notes

• Queries executed before enabling logging will not appear in logs.
• Using TABLE logging makes queries easy to inspect but logs can grow fast.
• Periodically truncate logs to save space:

TRUNCATE TABLE mysql.general_log;
TRUNCATE TABLE mysql.slow_log;