Enable MySQL Audit Logging for Compliance and Forensics (2026)

MySQL audit logging is surprisingly less about security and more about reconstruction after a breach or to prove you weren’t breached.

Let’s see this in action. Imagine a simple SELECT statement hitting a database:

SELECT * FROM users WHERE id = 123;

If audit logging is enabled, a record of this event is generated. The specific plugin and configuration determine the output format, but a typical log entry might look something like this (from the audit_log plugin):

{
  "timestamp": "2023-10-27T10:30:00.123456Z",
  "serverid": 1,
  "threadid": 12345,
  "auditlog": {
    "class": "connection",
    "type": "CONNECT",
    "record": "normal",
    "error": 0,
    "login": "myuser",
    "connection_id": 67890,
    "ip": "192.168.1.100",
    "db": null
  }
}
{
  "timestamp": "2023-10-27T10:30:05.789012Z",
  "serverid": 1,
  "threadid": 12345,
  "auditlog": {
    "class": "query",
    "type": "SELECT",
    "record": "normal",
    "error": 0,
    "login": "myuser",
    "connection_id": 67890,
    "db": "mydatabase",
    "sql_command": "SELECT * FROM users WHERE id = 123;"
  }
}

This log tells us who connected (myuser from 192.168.1.100), when they connected, and what query they ran (SELECT * FROM users WHERE id = 123;). For compliance, this is gold. For forensics, it’s the breadcrumbs you need to trace activity.

The core problem audit logging solves is the "I don’t know what happened" scenario. Without it, you might only know a table was modified, but not by whom, when, or with what specific data. This makes regulatory compliance (like GDPR, PCI DSS) and internal investigations incredibly difficult, if not impossible. Audit logging provides a verifiable, immutable (or at least tamper-evident) record of database operations.

Internally, MySQL audit logging works by hooking into the server’s event processing. When a specific event occurs (like a connection, a query execution, a DDL statement, or even a failed login), the audit plugin intercepts it. It then formats the relevant information into a log entry and writes it to a designated location. This can be a file (plain text, JSON, or binary) or even sent directly to a syslog server or a SIEM.

The exact levers you control are primarily through the audit plugin’s configuration. Most modern MySQL versions (8.0+) use the audit_log plugin, which is highly configurable. Key parameters include:

• audit_log_format: Defines the output format. JSON is common for its readability and ease of parsing.
• audit_log_policy: How logs are managed. ALL logs everything. LOGINS logs only connection-related events. NONE disables logging.
• audit_log_file: The path to the log file. Ensure the MySQL user has write permissions.
• audit_log_exclude_accounts: A comma-separated list of accounts whose activity should not be logged. Useful for internal monitoring accounts.
• audit_log_include_accounts: A comma-separated list of accounts whose activity should be logged (overrides audit_log_exclude_accounts if both are used).
• audit_log_flush: Forces a log file rotation.

To enable it, you typically modify your my.cnf (or my.ini) file and restart the MySQL server. For example, to log all events in JSON format to /var/log/mysql/audit.log:

[mysqld]
plugin_load_add = audit_log.so
audit_log_format = JSON
audit_log_policy = ALL
audit_log_file = /var/log/mysql/audit.log
audit_log_flush = 1

Then, restart MySQL: sudo systemctl restart mysqld.

A common misunderstanding is that audit logging is a performance killer. While it does add overhead, the impact is often less than feared, especially with modern SSDs and efficient logging plugins. The key is to be judicious about what you log. Logging every single SELECT statement from every user can indeed be burdensome. However, logging only DDL, DML on sensitive tables, failed logins, and administrative actions is typically manageable. The audit log plugin also has features like audit_log_statement_policy and audit_log_query_policy which allow for fine-grained control over which statements or queries are logged based on their type or content, further reducing overhead.

The audit log file itself can grow very large, very quickly. You need a robust log rotation strategy. This often involves using logrotate on Linux systems, configured to handle the audit log file, which can be set up to compress, archive, and delete old logs based on age or size.

The next step after setting up audit logging is often integrating it with a centralized logging system or SIEM for analysis and alerting.