Audit Logging Options, General/Slow Logs, Privacy, and Forensic Considerations

Learning outcomes

A security incident review asks, “Who connected, what did they run, and when?” The team discovers that it has a slow query log on one server, no general log on another, and no policy-based audit facility in Community Edition. Those sources answer different questions. This lesson teaches how to use them as evidence without pretending they are interchangeable.

One log does not answer every security question

Forensics means building a timeline from multiple independent observations. A row in the general log can show a statement was received from a connection. A matching application request ID can connect it to a business operation. The binary log can demonstrate a durable data-changing event. None of those alone necessarily proves a human identity beyond the authentication and logging controls that produced the record.

Enterprise Audit: useful, but do not silently assume it exists

MySQL Enterprise Audit is supplied with MySQL Enterprise Edition as the audit_log plugin and related filtering functions. It can log and filter server activity and has dedicated privileges such as AUDIT_ADMIN. Community learners must not be told to install an Enterprise-only plugin as a required exercise.

SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARYFROM INFORMATION_SCHEMA.PLUGINSWHERE PLUGIN_NAME = 'audit_log';SELECT component_urnFROM mysql.componentORDER BY component_urn;

Zero rows for audit_log on Community Server are expected. That does not mean you have no observability; it means the mandatory lab must use standard logs and external/application controls. Enterprise Audit also has documented restrictions—for example, it records top-level SQL statements rather than every internal statement executed inside stored programs—so even an Enterprise audit trail requires careful interpretation.

Mandatory Community lab: build a small forensic timeline

-- Run as a local administrator on a disposable MySQL instance.CREATE DATABASE IF NOT EXISTS servicehub_security_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_security_lab.security_events (  event_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  actor VARCHAR(80) NOT NULL,  event_type VARCHAR(40) NOT NULL,  detail VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_security_lab.security_events(actor,event_type,detail)VALUES ('chapter12','LAB_START','Chapter 12 disposable security lab');SELECT COUNT(*) AS event_rowsFROM servicehub_security_lab.security_events;

SHOW GLOBAL VARIABLES WHERE Variable_name IN  ('log_output','general_log','general_log_file',   'slow_query_log','slow_query_log_file','long_query_time');

Run the next steps only on the disposable lab server. Write down the original values first; if your server was already configured differently for another purpose, restore those exact values afterward.

SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = ON;SET GLOBAL slow_query_log = ON;-- long_query_time is both global and session-scoped; make only THIS session easy to observe.SET SESSION long_query_time = 0;SELECT CONNECTION_ID() AS lab_connection_id, CURRENT_USER() AS effective_account;SELECT COUNT(*) AS security_event_countFROM servicehub_security_lab.security_events;INSERT INTO servicehub_security_lab.security_events(actor,event_type,detail)VALUES ('forensics-lab','TEST_QUERY','safe synthetic event');

The general log records connections and statements received. The slow log, with this session threshold temporarily at zero, can capture the lab statements that qualify under the current slow-log settings. Exact rows and timestamps depend on your server, so inspect rather than memorize.

SELECT event_time, user_host, thread_id, server_id, command_type, argumentFROM mysql.general_logWHERE event_time >= NOW() - INTERVAL 10 MINUTE  AND (argument LIKE '%servicehub_security_lab%'       OR argument LIKE '%forensics-lab%')ORDER BY event_time DESCLIMIT 30;SELECT start_time, user_host, query_time, rows_sent, rows_examined, db, sql_textFROM mysql.slow_logWHERE start_time >= NOW() - INTERVAL 10 MINUTEORDER BY start_time DESCLIMIT 20;

Reading the log tables requires administrative visibility. Do not grant the application account access to mysql.general_log just to make an observability dashboard convenient; export or aggregate evidence through an operator-controlled path.

SET GLOBAL general_log = OFF;SET GLOBAL slow_query_log = OFF;-- If your original log_output was not TABLE, restore the recorded original value.-- Example for a default disposable lab:SET GLOBAL log_output = 'FILE';

Failure case: turning on raw logging to “see everything”

MySQL normally rewrites passwords in certain statements before writing them to the general, slow, and binary logs. The --log-raw startup option can suppress rewriting for the general log, but Oracle explicitly warns against using it in production for security reasons.

Logs can contain SQL text, identifiers, customer values, hostnames, query timing, and other sensitive operational information even when passwords are rewritten. Protect log directories, central log access, exports, screenshots, and support bundles under the same data-classification policy as the database data they may reveal.

Retention, rotation, and tamper resistance

A useful forensic log must still exist when you need it, must have trustworthy timestamps, and must be protected against unauthorized alteration. File rotation prevents a single log from consuming the filesystem; retention defines how long old evidence remains; access controls define who can read or delete it. External centralized logging can improve resilience against host compromise, but it adds transport, storage, identity, privacy, and cost decisions.

The general log is especially expensive at busy scale because it can record every statement. Keep it as a targeted diagnostic tool unless your workload and policy justify it. The slow log is better suited to query-performance investigation. Compliance auditing normally requires a purpose-built audit facility or external controls rather than pretending either diagnostic log is a complete audit system.

Build a defensible timeline

SELECT event_id, event_time, actor, event_type, detailFROM servicehub_security_lab.security_eventsWHERE event_type = 'TEST_QUERY'ORDER BY event_time;-- Pair this with:-- 1. the general-log connection/thread and statement entries,-- 2. application request/correlation IDs if available,-- 3. binary-log evidence for data-changing events when enabled,-- 4. OS/central log timestamps and authentication events.

State confidence explicitly: “The authenticated MySQL account on thread N sent statement X at time T” is stronger than “Alice did X” unless your application and identity controls establish that the account/session maps uniquely to Alice. Shared database credentials weaken attribution even when logging is perfect.

Summary and bridge to Lesson 5

Logging is useful only when you know what each source records, what it omits, and how sensitive the record itself is. The final lesson combines transport, credentials, file access, account design, plugins, logs, backups, and patch level into a hardening process driven by observed risk.

Authoritative references

• MySQL 8.4 — MySQL Server Logs
• MySQL 8.4 — The General Query Log
• MySQL 8.4 — The Slow Query Log
• MySQL 8.4 — MySQL Enterprise Audit