AuditLogsViewTable

​

RDS MySQL Audit Logging Setup

​

Steps:

• 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.

• 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.

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.

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.

• 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;