The audit requirement landed the way audit requirements do: a spreadsheet from a compliance consultant with a row that said "all data access to cardholder-adjacent tables must be logged and retained for one year." The server was a MariaDB 10.11 primary doing about 14,000 queries a second at peak, a third of them writes. I enabled the server_audit plugin on a Friday afternoon with the settings from the first blog post I found — server_audit_events=QUERY, log everything, sort it out later — and went home. By Sunday night the box had written 41 GB of audit log, the log partition hit 100%, and the write path's p99 latency had climbed by roughly 30% because every audited event is a synchronous append on the query path and the disk was now also fighting a log firehose. Monday's incident review produced the configuration we still run: about 2 GB a day, latency indistinguishable from baseline, and the compliance row checked off. This article is the delta between those two configurations.
server_audit is a good plugin — it ships with the server, it is maintained, and it does exactly what it advertises. Everything that goes wrong with it is a configuration decision made under time pressure. So here is the decision list in the order you will face it: what to load, which event classes to log, who to exclude, where the bytes go, and what it costs.
How do you load and verify the plugin?
The plugin ships in the MariaDB server package but is not active by default. Loading it is one statement, but that statement does not survive a restart, so the real configuration lives in the config file from day one — I have audited more than one setup where someone ran INSTALL SONAME during an incident response and the audit logging silently vanished at the next reboot:
-- one-time load (still add plugin_load_add to my.cnf!)
INSTALL SONAME 'server_audit';
-- confirm it is actually there and ACTIVE
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME = 'SERVER_AUDIT';
-- runtime state at a glance
SHOW GLOBAL VARIABLES LIKE 'server_audit%';
SHOW GLOBAL STATUS LIKE 'server_audit%';In the config file the equivalent is plugin_load_add = server_audit under [mariadb], plus every server_audit_* variable you intend to set — the plugin's variables are global, most require SUPER to change, and several are effectively restart-time decisions because changing them mid-flight reconfigures logging for new connections. Treat the config file as the source of truth and SET GLOBAL as the exception path, and the "audit disappeared after maintenance" class of incident goes away entirely.
Which event classes should you actually log?
This is the decision that determines whether the plugin costs you anything. server_audit_events accepts a comma list of event classes, and the classes are not equally expensive. CONNECT is cheap and almost always worth logging — logins, failures, disconnects — because connection volume is tiny compared to query volume and auth failures are exactly what auditors ask about first. QUERY is the firehose: every statement, every connection, all day. That was my Friday mistake. TABLE logs access to specific tables you name in server_audit_tables, which sounds ideal for "cardholder-adjacent tables" until you learn it logs access through any query touching those tables, including joins you did not think about.
The class family that saved us is the query-type split: QUERY_DDL, QUERY_DCL, QUERY_DML, and QUERY_DML_NO_SELECT. Read it as: schema changes, permission changes, writes, and writes-without-DQL. Our compliant configuration is QUERY_DDL,QUERY_DCL,QUERY_DML_NO_SELECT plus CONNECT. The reasoning: a breach or misuse that matters is either a schema change, a grant, or a data modification, and the auditors agreed that logging SELECTs against the sensitive tables specifically was satisfiable with TABLE on a short table list rather than logging every read on the box. That one change took us from 41 GB a weekend to 2 GB a day, because SELECT volume dwarfed everything else combined. If your compliance scope genuinely requires logging all reads, say so plainly in the risk assessment and budget the disk and the I/O — but do not discover that price by accident on a weekend.
How do you keep noisy accounts out of the log?
Every production box has accounts whose activity is guaranteed, high-volume, and uninteresting: the monitoring user polling SHOW STATUS every ten seconds, the backup user running its nightly scan, the ETL service account hammering the same prepared statement. server_audit_incl_users and server_audit_excl_users are the valve — comma-separated MariaDB usernames (not host-qualified), mutually exclusive. We exclude the monitoring and backup accounts by name and log everything else.
Three practical notes. First, the user lists are evaluated per connection at query time, so changes apply to new events without a reload, but they match the username only — repl@10.0.0.5 and repl@10.0.0.6 are the same user for this filter, which is usually what you want. Second, audit your exclusions: an excl_users list is also a blind-spot list, and the quarterly review should ask whether any excluded account can touch the sensitive tables. Our monitoring user has SELECT on performance_schema and nothing on application schemas, which keeps the blind spot honest. Third, these settings interact with the broader privilege model in ways worth reviewing alongside the roles and grants you already have — the roles and privileges piece covers that side.
Where should the log bytes go, and how do they rotate?
Two output modes: file and syslog, selected by server_audit_output_type. File mode is the default and the one with operational teeth. The rotation variables are server_audit_file_rotate_size (default a mere 1 MB), server_audit_file_rotations (default 9 files), and server_audit_file_rotate_now to force a rotation on demand. Do the arithmetic before the first weekend, not after: at our 2 GB a day, a 100 MB rotate size with 200 rotations keeps roughly a week and a half on box while the shipper moves older files to object storage. Rotation count times rotate size is your retention budget; set it deliberately.
-- the configuration that survived contact with production
-- (these live in my.cnf; shown here for review)
-- plugin_load_add = server_audit
-- server_audit_logging = ON
-- server_audit_events = CONNECT,QUERY_DDL,QUERY_DCL,QUERY_DML_NO_SELECT
-- server_audit_excl_users = monpg_probe,backup_svc
-- server_audit_output_type = file
-- server_audit_file_path = /var/log/mysql/audit.log
-- server_audit_file_rotate_size = 104857600
-- server_audit_file_rotations = 200
-- server_audit_query_log_limit = 2048
-- force a rotation before archiving, then ship audit.log.N files
SET GLOBAL server_audit_file_rotate_now = ON;Syslog mode is the better answer if your compliance story requires logs to leave the machine immediately — server_audit_syslog_facility and server_audit_syslog_priority feed a local rsyslog that forwards to a remote collector, and a disk-full event on the database box can no longer eat the audit trail. The price is that rsyslog forwarding becomes part of your audit chain's reliability story, so monitor it like one. One trap in either mode: file rotation and external logrotate will fight each other if both are configured — pick the plugin's internal rotation or an external rotator, not both, and test the rotation path once with file_rotate_now before declaring the setup done.
What does it cost, and what breaks quietly?
The overhead is real and it is on the query path: each audited event is formatted and appended synchronously, so audit latency is disk latency multiplied by event rate. On our box, QUERY_DML_NO_SELECT at roughly 4,000 write events per second cost under 2% on p99 — measurable, not noticeable. The 30% from the QUERY-everything weekend was mostly the disk saturation from 41 GB of appends, not the formatting. If you are latency-sensitive, the lever is event classes and user exclusions, not hoping the disk keeps up. And watch the audit write errors counter — an audit log that cannot accept writes is either a compliance gap or, if you have configured the strict failure mode, a database that refuses queries; know which behavior you have chosen before the disk fills.
The quiet breakages are three. Truncation: server_audit_query_log_limit caps logged statement length (default 1024 bytes), so a long INSERT with the interesting values at the end logs a prefix and an ellipsis — fine for forensics on access patterns, possibly not fine for evidentiary completeness, so set the limit consciously. Passwords: DCL statements like CREATE USER and SET PASSWORD are logged with the password field masked, which is correct, but statements that embed credentials in other forms — a LOAD DATA from a URL with a token, say — are just text in a log file, so treat the audit log itself as sensitive data with its own access controls and retention. And restarts: anything you set with SET GLOBAL and forgot to write into my.cnf is gone after the next reboot, which loops back to the opening section — config file first, always. The per-account visibility angle pairs naturally with the counters from the userstat observability notes when you need to answer "which account would even generate this volume" before you tune the filters.
Where MonPG fits
The signals worth trending around audit logging are the boring operational ones: audit log write volume per day, rotation frequency, write errors, and the disk headroom on whatever partition or pipe receives the stream — the failure mode is always a full disk or a dead forwarder discovered by an auditor, never by a dashboard. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and audit-pipeline health is on the list of signals it is being built around: log volume, rotation cadence, and write-error counters surfaced continuously. Until that ships, the status variables and a calendar reminder to test rotation quarterly are your kit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.