🛡️ Setting Up MySQL Audit Logging With AI

I’ve watched a compliance auditor ask “who read the customers table last Tuesday?” and watched a team realize, in real time, that they had absolutely no way to answer. General query logs were off because they’re too heavy, the binary log only captures writes, and nobody had ever set up real audit logging. That conversation is why I now treat audit logging as table stakes for any database holding sensitive data. The setup is fiddly, the filter syntax is JSON, and the plugin you get depends on which MySQL you run. So I draft the filter rules with AI, use it to parse the resulting audit JSON, and then verify the whole pipeline against a replica before I trust it for compliance.

Which Plugin You Actually Have

The first thing to settle is which audit plugin your build supports, because they differ in licensing and syntax. MySQL Enterprise Audit (the audit_log plugin) ships only with the commercial MySQL Enterprise Edition and uses a rule-based JSON filter model. On Community Edition you don’t get it, so the common open-source paths are Percona Server’s audit_log plugin or, if you’re on MariaDB, the server_audit plugin. They cover the same need with meaningfully different configuration, and pretending otherwise leads to copy-paste rules that silently do nothing.

-- Check what's loaded and available
SELECT plugin_name, plugin_status
FROM information_schema.plugins
WHERE plugin_name LIKE '%audit%';

For this guide I’ll lead with the MySQL Enterprise Audit JSON-filter model because it’s the most expressive, and call out the open-source differences as we go.

Installing the Plugin

MySQL Enterprise Audit installs from a bundled SQL script that registers the plugin and creates its filter tables:

mysql -u root -p < /usr/share/mysql-8.0/audit_log_filter_linux_install.sql

-- Confirm it registered
SHOW VARIABLES LIKE 'audit_log_file';
SHOW VARIABLES LIKE 'audit_log_format';   -- set to JSON for machine-parseable logs

On Percona Server you load the plugin directly and configure it with audit_log_* system variables in my.cnf; on MariaDB you load server_audit and drive it with server_audit_events and server_audit_incl_users. The concepts (what to log, where to write, how to rotate) carry over, but the exact variable names do not, so always confirm against your build rather than assuming.

Defining Audit Filters in JSON

The Enterprise model is rule-based: you define a named filter as a JSON document describing which event classes to log, then assign that filter to users. This is where AI earns its keep, because the JSON is easy to get subtly wrong.

You are a senior MySQL 8.0 DBA writing a MySQL Enterprise Audit filter. Produce a JSON filter that logs all connection events, all DDL (CREATE/ALTER/DROP), and any SELECT/UPDATE/DELETE against the table app.customers, but excludes routine activity from the monitoring user. Return only the JSON filter definition.

{
  "filter": {
    "log": [
      { "event_class": "connection" },
      { "event_class": "general",
        "event_subclass": "status",
        "command": { "name": ["create_table","alter_table","drop_table",
                               "create_db","drop_db"] } },
      { "event_class": "table_access",
        "operation": ["select","update","delete"],
        "table": { "name": "customers", "db": "app" } }
    ],
    "exclude": [ { "user": { "name": "monitoring", "host": "%" } } ]
  }
}

I never apply that blind. I read it for over-logging (auditing every SELECT everywhere will drown you and hurt performance) and for the right exclusions, then I install it through the audit functions:

-- Register the filter and bind it to the accounts you care about
SELECT audit_log_filter_set_filter('sensitive_access', '{ ... JSON from above ... }');
SELECT audit_log_filter_set_user('%', 'sensitive_access');
SELECT audit_log_filter_set_user('monitoring@%', 'log_none');

The guiding principle for what to log: connections (who got in, from where, success or failure), DDL (schema changes are rare and always worth recording), and sensitive-table access (reads and writes against tables holding PII or financial data). Logging every statement on a busy server is a self-inflicted outage, so filter deliberately.

Rotation and Shipping to a SIEM

A JSON audit log that grows unbounded on the database host is its own incident waiting to happen. You want size-based rotation and a shipper that moves the events off-box to a SIEM where they’re tamper-resistant and searchable.

-- Size-based rotation, e.g. rotate at 1 GB
SET PERSIST audit_log_rotate_on_size = 1073741824;
SET PERSIST audit_log_max_size = 10737418240;   -- keep ~10 GB on disk before pruning

# Ship rotated JSON audit logs to the SIEM via a Filebeat-style tail
sudo tee /etc/filebeat/inputs.d/mysql-audit.yml >/dev/null <<'YAML'
- type: filestream
  id: mysql-audit
  paths:
    - /var/lib/mysql/audit.*.json
  parsers:
    - ndjson:
        target: mysql_audit
        add_error_key: true
YAML
sudo systemctl restart filebeat

Keep the database host as the short-term buffer and the SIEM as the system of record. If an attacker gains root on the database, you don’t want your only copy of the audit trail sitting next to them.

Parsing Audit JSON With AI

When the audit question finally comes (“show me every account that touched customers outside business hours”), the raw JSON is verbose and nested. This is the second place AI genuinely helps: turning a pile of events into an answer.

# Pull a day of customer-table access events into a flat table with jq
jq -r 'select(.table_access.table == "customers")
       | [.timestamp, .account.user, .account.host, .table_access.operation]
       | @tsv' /var/lib/mysql/audit.20260620.json | sort

I’ll paste a sample of that output and ask AI to summarize anomalies, off-hours access, or unfamiliar source hosts. It’s fast at pattern-spotting across hundreds of lines, and it writes the jq filter faster than I do. What I do not do is let it make the compliance determination; it surfaces candidates and I make the call.

Verifying on a Replica

Here’s the discipline that keeps audit logging honest: a filter that you think logs sensitive access is worthless if it silently doesn’t. So before any filter is declared production-ready, I install it on a replica, generate exactly the activity it’s supposed to catch, and confirm the events actually land.

# On a replica with the same filter installed, exercise the audited paths
mysql -h replica-audit -u tester -p app <<'SQL'
SELECT id, email FROM customers WHERE id = 42;     -- should be logged
CREATE TABLE _audit_probe (id INT);                 -- DDL, should be logged
DROP TABLE _audit_probe;
SQL

# Confirm each action produced an audit event
grep -E '"customers"|_audit_probe' /var/lib/mysql/audit.*.json | jq '.table_access // .general'

If a SELECT against customers doesn’t show up, the filter is wrong, and I’d much rather discover that on a replica than during an audit. Testing on a replica also means I can hammer the audited paths under load to gauge the performance cost without touching production traffic. Only once the replica proves the events are captured, rotated, and shipped end to end do I roll the filter to the primary.

The Division of Labor

The pattern is consistent with how I use AI across all database work: it drafts the JSON filter and the jq parsers, explains the event classes, and summarizes findings, all of which compress hours of fiddly syntax into minutes. But it cannot tell you whether your specific build actually loaded the plugin, whether the filter really caught the access, or whether the events reached your SIEM. A replica answers those questions, and those answers are the ones an auditor will hold you to.

For more hardening and operations guides, the MySQL category has the rest of the series, and the exact filter-drafting and log-parsing prompts I rely on live in my AI prompt collection. Set deliberate filters, ship the logs off-box, and verify the pipeline on a replica before you ever call your audit trail trustworthy.