MySQL 8.4 Audit Log Filter: Selective Logging for Compliance

A healthcare SaaS company's security team handed their DBAs a 90-day deadline: every SELECT, INSERT, and DELETE touching the patients and claims tables had to be logged with user identity, timestamp, and the exact SQL — ready for their HIPAA audit. The team's first instinct was to enable MySQL's general query log, which promptly wrote 2.1 GB of log per hour and set the I/O subsystem on fire. MySQL 8.4's audit log plugin is the right tool for exactly this scenario: surgical event capture with per-user and per-event-class filters, structured JSON output, and a rotation API designed for log-shipping pipelines. This guide shows you how to configure it correctly from the ground up.

Installing the Audit Log Plugin

The MySQL audit log plugin ships as a shared library (audit_log.so on Linux, audit_log.dll on Windows). Install it at runtime without a server restart:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Verify the installation succeeded and check the plugin status:

SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME = 'audit_log';

-- Expected output:
-- PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_TYPE
-- audit_log   | ACTIVE        | AUDIT

To ensure the plugin loads on every restart, add it to my.cnf so it survives a MySQL restart:

[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_file   = /var/log/mysql/audit.log
audit_log_policy = ALL

The audit log plugin installs its management tables into the mysql system schema. Two tables drive the entire filter API:

• mysql.audit_log_filter — stores named filter definitions as JSON documents
• mysql.audit_log_user — maps filter names to MySQL accounts

Choosing the Audit Log Format: JSON vs OLD

MySQL offers two log format modes. For any modern audit or SIEM workflow, JSON is the only correct choice:

Set the format at plugin load time in my.cnf — it cannot be changed online on a running plugin:

audit_log_format = JSON

A JSON audit event for a SELECT statement looks like this:

{"timestamp":"2026-02-23T14:32:01 UTC","id":7412,"class":"general",
 "event":"status","connection_id":42,"account":{"user":"app_user","host":"10.0.1.5"},
 "login":{"user":"app_user","os":"","ip":"10.0.1.5","proxy":""},
 "general_data":{"command":"Query","sql_command":"select",
   "query":"SELECT id, name FROM patients WHERE dob > '1980-01-01'",
   "status":0}}

This structure maps directly to Splunk's HEC JSON format and Elasticsearch's NDJSON bulk API without transformation.

The Audit Log Filter API

MySQL 8.4 exposes filter management through a set of built-in functions. These functions read and write directly to the mysql.audit_log_filter and mysql.audit_log_user tables and apply changes to the running plugin immediately — no restart required.

Core Filter Functions

• audit_log_filter_set_filter(name, definition) — Creates or replaces a named filter with a JSON definition
• audit_log_filter_set_user(user, filter_name) — Assigns a filter to a specific MySQL account
• audit_log_filter_remove_filter(name) — Deletes a named filter
• audit_log_filter_remove_user(user) — Removes a filter assignment from a user
• audit_log_filter_flush() — Reloads filter tables from disk (use after direct table edits)

Defining Filters: Event Classes and JSON Syntax

Filter definitions are JSON documents that specify which event classes to capture, and optionally which SQL command types within those classes.

Event Classes Available in MySQL 8.4

• connection_event — Login, logout, failed authentication, and connection drops
• general_event — Any SQL statement execution (the broadest class)
• table_access_event — Reads, inserts, updates, and deletes at the storage engine level (fires per-table, not per-statement)
• command_event — Server commands such as COM_QUIT, COM_PING, and COM_CHANGE_USER

Filter 1: Log Everything (Baseline Audit)

The simplest possible filter captures all event classes. Use this for a canary account or an admin user where you want complete traceability:

SELECT audit_log_filter_set_filter(
  'log_all',
  '{ "filter": { "log": true } }'
);

Filter 2: Log Only Connection Events

For compliance requirements that only demand login audit trails — who connected, from where, and when — log only connection_event:

SELECT audit_log_filter_set_filter(
  'log_connections_only',
  '{
    "filter": {
      "class": {
        "name": "connection_event"
      }
    }
  }'
);

Filter 3: Log DML Only (INSERT, UPDATE, DELETE)

For data-change audits — where regulators care about mutations but not reads — use general_event filtered to DML command types. The sql_command field maps to the parsed command type from MySQL's parser:

SELECT audit_log_filter_set_filter(
  'log_dml_only',
  '{
    "filter": {
      "class": {
        "name": "general_event",
        "event": {
          "name": "status",
          "log": {
            "or": [
              { "field": { "name": "sql_command", "value": "insert" } },
              { "field": { "name": "sql_command", "value": "update" } },
              { "field": { "name": "sql_command", "value": "delete" } }
            ]
          }
        }
      }
    }
  }'
);

Filter 4: Log DDL Only (Schema Changes)

Change management workflows often need an independent DDL audit trail separate from DML. Capture only schema-altering statements:

SELECT audit_log_filter_set_filter(
  'log_ddl_only',
  '{
    "filter": {
      "class": {
        "name": "general_event",
        "event": {
          "name": "status",
          "log": {
            "or": [
              { "field": { "name": "sql_command", "value": "create_table" } },
              { "field": { "name": "sql_command", "value": "alter_table" } },
              { "field": { "name": "sql_command", "value": "drop_table" } },
              { "field": { "name": "sql_command", "value": "create_index" } },
              { "field": { "name": "sql_command", "value": "drop_index" } },
              { "field": { "name": "sql_command", "value": "truncate" } }
            ]
          }
        }
      }
    }
  }'
);

Filter 5: Table-Access-Level Auditing

For the highest-resolution data access audit — every row-level read and write at the storage engine — use table_access_event. This fires once per table per statement, giving you the exact table touched even for multi-table JOINs:

SELECT audit_log_filter_set_filter(
  'log_table_access',
  '{
    "filter": {
      "class": [
        { "name": "table_access_event" },
        { "name": "connection_event" }
      ]
    }
  }'
);

Assigning Filters to Users

Filters become active when you assign them to MySQL accounts using audit_log_filter_set_user(). The account format is 'user'@'host', matching MySQL's standard account identifier syntax. The special account '%'@'%' acts as the default filter for any user without an explicit assignment:

-- Assign DML-only logging to the application service account
SELECT audit_log_filter_set_user('app_user@10.0.1.%', 'log_dml_only');

-- Assign full audit to all DBA accounts
SELECT audit_log_filter_set_user('dba_alice@%', 'log_all');
SELECT audit_log_filter_set_user('dba_bob@%', 'log_all');

-- Log only connection events for all other users (default catch-all)
SELECT audit_log_filter_set_user('%@%', 'log_connections_only');

Verifying Active Filters

Query the system tables directly to see what filters are defined and how they are assigned:

-- List all defined filter names and their JSON definitions
SELECT NAME, FILTER
FROM mysql.audit_log_filter
ORDER BY NAME;

-- List all user-to-filter assignments
SELECT USER, FILTERNAME
FROM mysql.audit_log_user
ORDER BY USER;

What Changed in MySQL 8.4: Simplified Filter Management

MySQL 8.4 LTS introduced two meaningful improvements over the 8.0 audit log implementation that production teams will notice immediately.

First, filter function calls in 8.4 are fully transactional with respect to the mysql.audit_log_filter and mysql.audit_log_user tables. In MySQL 8.0, a crash during a audit_log_filter_set_user() call could leave the filter tables in an inconsistent state that required manual repair. In 8.4, the write to the system table and the in-memory filter update are applied atomically.

Second, the internal filter evaluation path in 8.4 was rewritten to use a lock-free read path for the common case (filter lookup by user). At 10,000 QPS with per-user filters assigned, the 8.4 engine adds approximately 0.3% overhead per query versus the 1.1% measured in 8.0 on equivalent hardware. For most workloads this is negligible, but it matters for latency-sensitive OLTP at scale.

Rotating Audit Logs

Audit log files grow continuously. Rotate them on a schedule to keep individual files at a manageable size for log shippers and to comply with retention window requirements:

-- Rotate the current audit log file (closes current file, opens a new one)
-- The old file is renamed with a timestamp suffix automatically
CALL sys.audit_log_rotate();

For MySQL 8.4, the preferred approach is the audit_log_rotate() component function, which is equivalent:

SELECT audit_log_rotate();

Automate rotation with a cron job or an Event Scheduler task:

-- Rotate audit logs every 6 hours via MySQL Event Scheduler
CREATE EVENT rotate_audit_log
  ON SCHEDULE EVERY 6 HOUR
  DO
    SELECT audit_log_rotate();

Integrating with SIEM: Splunk, Elasticsearch, and Datadog

The JSON audit log format is designed for direct ingestion by log aggregation platforms. The integration pattern is identical across tools: ship the file, parse the NDJSON, index on the key fields.

Splunk via Universal Forwarder

Add a monitor stanza to your Splunk Universal Forwarder's inputs.conf pointing at the audit log file:

[monitor:///var/log/mysql/audit.log]
disabled   = false
sourcetype = mysql:audit:json
index      = security

Create a props.conf to tell Splunk the file is NDJSON with the timestamp field carrying the event time:

[mysql:audit:json]
KV_MODE          = json
TIME_PREFIX      = "timestamp":"
TIME_FORMAT      = %Y-%m-%dT%H:%M:%S %Z
TRUNCATE         = 0

Elasticsearch via Filebeat

Configure a Filebeat input pointing at the audit log, and add a JSON decode processor:

filebeat.inputs:
  - type: log
    paths:
      - /var/log/mysql/audit.log
    json.keys_under_root: true
    json.add_error_key: true
    fields:
      source: mysql-audit
      environment: production

output.elasticsearch:
  hosts: ["https://elk.internal:9200"]
  index: "mysql-audit-%{+yyyy.MM.dd}"

Datadog via the Datadog Agent

Add a custom log collection configuration for the audit log file in /etc/datadog-agent/conf.d/mysql.d/conf.yaml:

logs:
  - type: file
    path: /var/log/mysql/audit.log
    service: mysql
    source: mysql
    sourcecategory: database
    log_processing_rules:
      - type: multi_line
        name: new_log_start
        pattern: '^\{"timestamp"'

Working with JusDB on MySQL Audit and Compliance

JusDB manages MySQL audit log configuration, filter design, and SIEM integration for engineering teams operating under PCI-DSS, HIPAA, SOC 2, and ISO 27001 requirements. Our DBAs design per-role filter sets that capture exactly what your auditors need, instrument log rotation and shipping pipelines, and validate audit coverage against your compliance control framework — before the auditors arrive.

Explore JusDB managed MySQL services at jusdb.com