Skip to main content

​
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:
ParameterValueNotes
general_log1 (ON)Logs all queries
slow_query_log1 (ON)Logs queries slower than long_query_time
audit_log1 (ON)Logs queries via audit plugin
log_outputTABLEStores logs in MySQL tables
long_query_time0.5–1Threshold for slow queries (adjust as needed)
  • 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;