/PLAYBOOK · 2026-09-07

MySQL binlog Audit in Practice: Tracing Abnormal Database Operations

binlog is a zero-cost, operation-level audit source you can enable on MySQL. Full audit setup: ROW full-image format, binlog_row_image choice, mysqlbinlog time-window parse, bulk-delete screening script, retention and disk control, operator attribution, and the audit blind spot for pure-read exfil.

Applicable scenarios

MySQL binlog audit in practice covers three needs. One: security-incident reconstruction. After abnormal deletes or data tampering, restore who did what to which table when. Two: data-exfil discovery. When sensitive tables see write scans outside business hours other than bulk SELECT, binlog is the lowest-level operational evidence. Three: change-management audit. Ops or dev bypasses approval and runs DDL or large transactions on prod—leave a trail for accountability. For small-to-mid teams with no enterprise audit plugin and no dedicated database audit appliance, binlog is an operation-level audit source you can enable at zero extra license cost.

Prerequisites

MySQL 5.7 or 8.0, admin account with SUPER or SYSTEM_VARIABLES_ADMIN
Permission to edit my.cnf and a mysqld restart window (some parameters can be changed online)
Disk headroom for binlog. Size at ~7× daily increment

How it works

binlog (binary log) records all committed data-change statements. Foundation for replication and recovery; can be used in reverse as audit. Audit value depends on format. ROW records a full before/after image per changed row, including every row's old and new values. Even SQL that went through stored procedures or triggers reconstructs to row level. STATEMENT records SQL text only; dynamic SQL can defeat semantic reconstruction. MIXED is a compromise. For audit, prefer ROW plus binlog_row_image=FULL so every row change carries a full before/after image. Limitation: binlog does not record SELECT. Pure-read exfil does not show up. Supplement with general_log or network-layer audit. binlog is produced by the MySQL server; no directly connected account can bypass it. That is its value as last-resort evidence.

Procedure

Step 1: Enable and standardize binlog

Edit my.cnf. Add these under [mysqld], then restart the instance:

[mysqld]
server-id = 1
log-bin = /data/mysql/binlog/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
# Individual binlog file size cap; too small causes frequent rotation
max_binlog_size = 512M
# Retention days: for audit, recommend at least 30 days (8.0 uses binlog_expire_logs_seconds)
binlog_expire_logs_seconds = 2592000

On 8.0+, binlog_format and binlog_row_image can be changed online: `SET GLOBAL binlog_format = 'ROW'; SET GLOBAL binlog_row_image = 'FULL';`. Applies to new sessions only. Run in a trough to avoid restart.

Step 2: Parse with mysqlbinlog

# Parse all operations in the specified time window; output readable SQL
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
  --start-datetime="2026-09-07 00:00:00" \
  --stop-datetime="2026-09-07 12:00:00" \
  /data/mysql/binlog/mysql-bin.000123 > audit_0907.sql

# View changes for a single table: parse first, then filter
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
  /data/mysql/binlog/mysql-bin.000123 | grep -B 5 -A 15 "orders"

In ROW output, ### @1= lines are per-column before/after images. For UPDATE, the WHERE clause is the old values, SET is the new values. DELETE has old values only. Combine server id and thread id to distinguish sources. On 8.0, `-vv` prints column names—easier to locate sensitive fields.

Step 3: Build an anomaly-screening script

Script the high-frequency inspection. Daily dump of suspicious change summaries:

#!/bin/bash
# audit_binlog.sh: screen bulk deletes outside business hours (00:00-06:00)
BINLOG_DIR=/data/mysql/binlog
YESTERDAY=$(date -d "yesterday" +%F)
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
  $BINLOG_DIR/mysql-bin.[0-9]* \
  --start-datetime="${YESTERDAY} 00:00:00" \
  --stop-datetime="${YESTERDAY} 23:59:59" |
  awk '/### (DELETE FROM|UPDATE)/ {print}' |
  sort | uniq -c | sort -rn | head -50 > /var/log/binlog_audit_${YESTERDAY}.log

Cron it at 07:00 daily. When output exceeds a threshold (e.g. >1000 deletes on one table), fire the existing alert channel.

Verification

Run these checks in order. `SHOW VARIABLES LIKE 'binlog_format';` must return ROW. `SHOW VARIABLES LIKE 'binlog_row_image';` must return FULL. `SHOW BINARY LOGS;` confirms binlog is rotating. Then end-to-end: create a test table, run one UPDATE, parse the latest binlog with mysqlbinlog, confirm a row-level record with before/after images. Finally verify retention: `SHOW VARIABLES LIKE 'binlog_expire%';` matches the configured value.

FAQ

FAQ 1: Disk exploded after switching to ROW? Full-image ROW can be tens of times STATEMENT volume on bulk UPDATE/DELETE. Order of operations: first compromise with `binlog_row_image = MINIMAL`—only before/after of changed columns. Audit readability drops slightly, size drops a lot. Then tighten retention to 14 days and enable compression (8.0.30+ supports `binlog_transaction_compression=ON`). Last, move the binlog directory to a dedicated disk so log growth cannot squeeze datadir and stall writes.

FAQ 2: Can binlog tell you who ran the SQL? Stock binlog does not record the client account. Thread and server id only. `log_bin_use_v1_row_events` does not fix this. Two real paths: high-audit databases enable the MySQL Enterprise Audit plugin or an open-source stand-in (MariaDB audit plugin is MySQL-compatible; records account, host, SQL text). Lightweight path: with a unified application DB account, use `init_connect` to write a source identifier into a side table, or record session ownership at a DB proxy, then join on binlog timestamps.

Summary

binlog audit is the lowest-cost, highest-trust operation trail in a MySQL security stack. ROW full-image format reconstructs changes to the row. Server-side generation means it cannot be bypassed. Three landing points: enable ROW + FULL and retain 30 days; parse with mysqlbinlog for a time-windowed row-level view; script daily inspection to surface abnormal changes. binlog does not cover SELECT-style exfil. Complete audit still needs least-privilege accounts plus sampled query logging—an evidence loop on both read and write paths.