17.1 MySQL Forensics: Internal Architecture, Data Directory, Information Schema & Utility Programs

17.1 MySQL Forensics: Internal Architecture, Data Directory, Information Schema & Utility Programs

Quick Answer: Blueprint Domain 3 requires the internal architecture of MySQL, the structure of the data directory, data storage in SQL Server and MySQL, database evidence repositories, MySQL forensics, viewing the Information Schema, and MySQL utility programs for forensic analysis; Domain 5 adds MySQL forensics for a WordPress website database. The data directory (datadir, typically /var/lib/mysql) holds one .ibd tablespace per table, the shared ibdata1 system tablespace, and the binary log — which is the highest-value artifact because it records every data-modifying operation with a timestamp and server ID.

---

MySQL Server Architecture in Forensic Terms

MySQL separates a server layer (connection handling, parser, optimizer, query cache in older versions) from a pluggable storage engine layer. For forensics, the storage engine determines where the bytes are:

[!IMPORTANT] Never trust a default path. Resolve the real one from the running server or the configuration file: SELECT @@datadir, @@log_bin_basename, @@general_log_file, @@slow_query_log_file, @@version; On Linux the configuration is /etc/my.cnf or /etc/mysql/my.cnf; on Windows it is my.ini beside the install. Document the values in the case notes — a relocated datadir on a separate volume is common and easy to miss when imaging.

---

Structure of the Data Directory

MySQL 8.0 changed schema forensics. In 5.x, every table had a readable .frm definition file, so an examiner could reconstruct dropped tables' structure from loose files. In 8.0 the dictionary moved into mysql.ibd, so a dropped table's definition disappears from the filesystem and must instead be recovered from the binary log's DDL statements or from a backup.

---

The Logs, Ranked by Forensic Value

1. Binary Log — the Transaction Ledger

The binary log records every statement or row change that modified data, with timestamps, server ID, thread ID, and (in ROW format) the complete before-and-after image of each changed row.

# Render a binary log as readable SQL with timestamps
mysqlbinlog --base64-output=DECODE-ROWS --verbose binlog.000017 > /evidence/binlog17.sql

# Constrain to the incident window
mysqlbinlog --start-datetime="2026-09-14 02:00:00"             --stop-datetime="2026-09-14 06:00:00"             --base64-output=DECODE-ROWS --verbose binlog.0000*

File header magic number: unencrypted binary logs begin with FE 62 69 6E (0xFE + bin); encrypted binary logs begin with FD 62 69 6E. That one byte tells the examiner immediately whether the log can be parsed directly or requires the server's keyring.

2. General Query Log — Everything, When Enabled

Records every statement received, including SELECT, plus connect and disconnect events with the client host. It is usually off in production for performance reasons, but when an attacker enables it, or when a security-conscious deployment leaves it on, it is the closest thing to a database keylogger.

3. Slow Query Log

Captures statements exceeding long_query_time. Mass-extraction queries — an unbounded SELECT * FROM wp_users or a blind-SQLi timing payload using SLEEP() or BENCHMARK() — are slow by nature, so this log frequently captures injection activity that no other log retained.

4. Error Log

Startup and shutdown times (which bracket periods where the server could not have been altered through SQL), crashes, Access denied failures, and aborted connections. A sudden restart immediately after a compromise often marks the attacker loading a plugin or a UDF.

---

Viewing the Information Schema

INFORMATION_SCHEMA is a set of read-only views over the data dictionary. These queries are the standard forensic sweep of a live or restored instance:

-- Schema inventory with creation and last-update times
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, CREATE_TIME, UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY CREATE_TIME DESC;

-- Accounts and their hosts, including anonymous or wildcard-host accounts
SELECT user, host, plugin, account_locked, password_last_changed FROM mysql.user;

-- Who holds dangerous global privileges
SELECT GRANTEE, PRIVILEGE_TYPE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE IN ('SUPER','FILE','PROCESS','GRANT OPTION','CREATE USER');

-- Stored routines and triggers, a classic persistence hiding place
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, CREATED, LAST_ALTERED, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES ORDER BY LAST_ALTERED DESC;
SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE, ACTION_STATEMENT, CREATED
FROM INFORMATION_SCHEMA.TRIGGERS;

CREATE_TIME on a table created during the intrusion window, a mysql.user row with host = '%', a FILE-privileged account, or a trigger that re-creates a backdoor row whenever it is deleted are all findings these queries surface directly.

[!WARNING] FILE privilege is the pivot from database compromise to host compromise. It enables SELECT ... INTO OUTFILE to write a web shell into the document root and LOAD_FILE() to read /etc/passwd. Any account holding FILE that should not, and any INTO OUTFILE statement in the binary or general query log, is a server-compromise indicator, not merely a database one.

---

MySQL Utility Programs for Forensic Analysis

---

Worked Scenario: WordPress Database Compromise

WordPress is the blueprint's named case because it is the most common real-world MySQL victim.

Tables that matter: wp_users (login, user_pass as a phpass/bcrypt hash, user_email, user_registered), wp_usermeta (holds wp_capabilities, which is what actually grants the administrator role), wp_options (siteurl, home, active_plugins, plus _transient_ rows attackers abuse for payload storage), wp_posts (injected spam or SEO-poisoning content), and wp_comments.

Investigation sequence:

1. Acquire the data directory from a forensic image, not by querying the live compromised host, and hash every file.
2. Run INFORMATION_SCHEMA.TABLES ordered by CREATE_TIME and UPDATE_TIME — an administrator account inserted during the intrusion window shows as a recent wp_users update.
3. Parse the binary log across the window with mysqlbinlog --base64-output=DECODE-ROWS --verbose and search for INSERT INTO wp_users, UPDATE wp_usermeta ... wp_capabilities, and UPDATE wp_options targeting siteurl or active_plugins.
4. Check wp_usermeta for a wp_capabilities value of a:1:{s:13:"administrator";b:1;} on an account whose user_registered timestamp falls inside the window — the standard privilege-escalation artifact.
5. Search wp_options and wp_posts for Base64 or hex-encoded blobs, eval(, and injected <script> or hidden-link markup — the SEO-poisoning payload described in the GOOTLOADER chain.
6. Correlate each database timestamp to the Apache/IIS access log to identify the request that carried the injection, and to the filesystem for a web shell dropped via INTO OUTFILE.

MySQL timestamp caution: TIMESTAMP columns are stored in UTC and converted to the session time_zone on read, while DATETIME columns are stored literally with no zone conversion. Record SELECT @@global.time_zone, @@session.time_zone at acquisition, or the reconstructed timeline will be wrong by the offset.