Forensics

dbtrail can tell you what changed in your MySQL database and when. With forensics, it can also answer who made the change and where it came from.

What forensics can tell you

• "Who deleted this row?" — Trace the MySQL user and client application that executed a DELETE
• "What did user X change?" — See all recent SQL activity for a specific MySQL user
• "Is this connection from an authorized app?" — Check connection metadata including client program name, source host, and OS user
• "Who dropped this table?" — Find DDL operations with the user who executed them

How it works

dbtrail queries MySQL's built-in observability subsystems to correlate binlog changes with connection metadata.

performance_schema

MySQL's performance_schema provides real-time connection and statement metadata:

• Active connections — which users are connected, from where, running what
• Statement history — recent SQL statements per connection with duration and rows affected
• Client attributes — program name, OS user, connector version (reported by the MySQL client library)

Most MySQL 8.0+ installations have performance_schema enabled by default.

audit_log plugin (optional)

For deeper forensics, MySQL audit log plugins capture comprehensive query history:

• Percona Audit Log Plugin (free, open-source)
• MySQL Enterprise Audit (requires MySQL Enterprise Edition)
• MariaDB Audit Plugin (included with MariaDB)

dbtrail detects which (if any) audit plugin is installed and reports its configuration.

Using forensics via the dashboard

The forensics UI is available at /app/forensics in the dbtrail dashboard. From there you can:

1. Select a server — choose the MySQL server to investigate
2. Check capabilities — see what forensic data sources are available
3. Run investigations — use the four investigation modes (who changed, user activity, connection history, DDL history)

Results are displayed inline with the option to export or copy for further analysis.

Using forensics via MCP

Four MCP tools are available for forensic investigation. Use them in Claude Desktop, Claude Code, or any MCP client connected to your dbtrail server.

Check available data sources

Before investigating, check what forensic capabilities your MySQL server supports:

"Use forensics_capabilities for server <server_id>"

This tells you whether performance_schema is enabled, which consumers are active, and whether an audit log plugin is installed.

Trace who changed a row

"Use who_changed to find who modified row 42 in mydb.orders"

Queries binlog change events for the specified row and correlates with connection metadata when available.

Investigate a user's activity

"Use user_activity to show what user 'app_backend' has been doing"

Returns recent SQL statements from performance_schema.events_statements_history_long, including query text, duration, and rows affected.

Check connection history

"Use connection_history to see connections from host 10.0.1.50"

Shows active connections matching the filter, including current database, command state, and running query.

When forensic data is unavailable

Not all MySQL servers have performance_schema enabled or audit plugins installed. When forensic data sources are unavailable, dbtrail provides fallback SQL queries — ready-to-run SQL that you can execute directly against your MySQL server to investigate.

Example fallback response:

{
  "success": true,
  "events": [],
  "source": "fallback",
  "fallback_queries": [
    {
      "description": "Check current connections for this user",
      "sql": "SELECT * FROM information_schema.PROCESSLIST WHERE USER = 'app_user'"
    }
  ],
  "note": "performance_schema.events_statements_history_long is not available."
}

Enabling better forensics on your MySQL server

Enable statement history consumers

-- Enable for current session history (per-thread ring buffer)
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history';

-- Enable for global history (all threads, larger ring buffer)
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history_long';

Install Percona Audit Log Plugin (recommended, free)

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';

See the Percona Audit Log documentation for full setup instructions.

Limitations

• performance_schema is a ring buffer — statement history is kept for a limited time. For long-term forensics, enable the audit_log plugin.
• Thread IDs are reused — after a MySQL connection disconnects, its thread ID may be reused by a new connection. Time-range filtering is important for accurate correlation.
• Audit log file parsing — the current implementation detects audit plugins via SQL. File-based audit log parsing (for JSON/XML/CSV formats) is planned in a future release.