Audit Trail

Audit Trail

🚀 Value Proposition

Achieve uncompromising security with our enterprise-grade Audit Trail. Gain transparency into every database operation. Track precise timings, OAuth identities, and real-time token economics. We use structured JSONL logs. Automatically capture pre-mutation state snapshots. Guarantee rapid recovery. Keep deployments fully compliant and bulletproof.

mysql-mcp delivers an enterprise-grade JSONL audit trail. It features token estimates, OAuth tracking, and outcome transparency. We always log write and admin tools. Opt-in to read-scoped tools via --audit-reads. Size-based rotation prevents unbounded growth.

---

🟢 Enable Audit Logging

CLI flags:

node dist/cli.js --audit-log /var/log/audit.jsonl --mysql-host localhost --mysql-user root
# Or using the shorthand:
mysql-mcp --audit-log /var/log/audit.jsonl --mysql-host localhost

# Omit tool arguments from entries
mysql-mcp --audit-log /var/log/audit.jsonl --audit-redact --mysql-host localhost

Environment variables:

Variable	Default	Description
AUDIT_LOG_PATH	./audit.jsonl	File path or stderr to enable audit logging
AUDIT_REDACT	true	Omit tool arguments from entries (privacy/compliance)
AUDIT_READS	false	Log read-scoped tool calls (compact entries)
AUDIT_LOG_MAX_SIZE	10485760	Max log file size in bytes before rotation (10MB)

Docker:

docker run --rm -p 3000:3000 \
  -e MYSQL_HOST=host.docker.internal \
  -e MYSQL_USER=user \
  -e MYSQL_PASSWORD=pass \
  -e AUDIT_LOG_PATH=/var/log/audit.jsonl \
  -v /host/logs:/var/log \
  docker.io/writenotenow/mysql-mcp:latest \
  --transport http --port 3000

---

📦 Capture Container Stderr

For Docker, Kubernetes, and any orchestrated environment, route audit entries directly to the container log stream:

--audit-log stderr
# or
AUDIT_LOG_PATH=stderr

The orchestrator (Docker log driver, Kubernetes Fluentd/Fluentbit, CloudWatch Logs, etc.) captures stderr natively—no volume mounts or log rotation needed. Entries are JSON-per-line, compatible with structured log parsers.

---

📜 Parse JSONL Format

Each line is a self-contained JSON object:

{
  "timestamp": "2026-03-23T05:21:50.760Z",
  "requestId": "75e9f60f-aad0-4519-81db-e0a79a57dd0e",
  "tool": "mysql_drop_table",
  "category": "write",
  "scope": "write",
  "user": "jane@example.com",
  "scopes": ["read", "write"],
  "durationMs": 15,
  "success": true,
  "args": {
    "table": "users",
    "ifExists": true
  },
  "backup": "snapshot-1647983421.json.gz",
  "tokenEstimate": 42
}

Field Reference

Field	Type	Description
timestamp	ISO 8601 string	UTC timestamp of the invocation
requestId	UUID string	Correlation ID for the MCP request
tool	string	MCP tool name (e.g., mysql_drop_table)
category	read/write/admin	Derived from the tool's OAuth scope group
scope	string	Required OAuth scope for this tool
user	string or null	OAuth sub claim (null for reads and no-OAuth)
scopes	string[]	OAuth scopes granted to the caller
durationMs	number	Execution time in milliseconds
success	boolean	Whether the tool completed without error
error	string?	Error message (present only on failure)
args	object?	Tool input arguments (omitted for reads and --audit-redact)
backup	string?	Filename of the pre-mutation snapshot (if applicable)
tokenEstimate	number?	Estimated token count of the tool response (~4 bytes/token)

---

🔍 Discover Audited Tools

Write and admin tools are always logged. Read-scoped tools are logged only when --audit-reads (or AUDIT_READS=true) is enabled.

Scope	Groups / Tools	Logged?
read	core (read-only), json, text, fulltext, performance, optimization, monitoring, schema, sysschema, stats, spatial, cluster, router, proxysql, introspection, docstore (reads), roles (reads), security (reads)	⚡ Opt-in (--audit-reads)
write	transactions, core (writes), docstore (writes), migration, vector	✅ Always
admin	admin, backup, partitioning, replication, events, shell, roles (grants/revokes), security (admin), codemode	✅ Always

Read entries are compact — args, user, and scopes are omitted to keep per-entry size around ~100 bytes.

---

🤖 Empower Agent Access

Security Requirement: When agents query the audit history using the security audit tool, at least one filter parameter must be provided to prevent massive payload bloat.

The mysql://audit resource exposes recent audit entries with a session summary to AI agents:

{
  "summary": {
    "totalTokenEstimate": 14200,
    "callCount": 47,
    "topToolsByTokens": [
      { "tool": "mysql_read_query", "calls": 12, "tokens": 8400 },
      { "tool": "mysql_execute_code", "calls": 3, "tokens": 3100 }
    ],
    "note": "Last 47 tool calls consumed ~14,200 tokens"
  },
  "entries": [{ "tool": "mysql_read_query", "tokenEstimate": 420, "..." }],
  "total": 47
}

The summary block gives agents session-level token consumption visibility without parsing individual entries. When audit logging is disabled, the resource returns an empty array with a status message.

🔄 Automate Log Rotation

The audit log rotates automatically when it exceeds AUDIT_LOG_MAX_SIZE (default: 10MB). On rotation, the current file cascades down a 5-tiered rotation chain (e.g., .4 becomes .5, and the active log rotates to .1). A maximum of 5 rotated archive files are securely kept natively.

For container deployments using --audit-log stderr, rotation is unnecessary—the orchestrator handles log management.

---

🚢 Ship Logs Anywhere

JSONL format is directly compatible with log aggregation platforms:

Platform	Method
Datadog	File tailing agent or stderr → Docker log driver
Grafana Loki	Promtail file/journal source
Elastic/ELK	Filebeat JSONL input
Splunk	Universal Forwarder or HEC (JSON)
CloudWatch Logs	awslogs Docker log driver (stderr mode)
Fluentd/Fluentbit	tail plugin with JSON parser

For container deployments, --audit-log stderr is the recommended approach—the orchestrator handles shipping, rotation, and retention.

---

📸 Capture Backup Snapshots

Pre-mutation DDL snapshots automatically capture the state of database objects before destructive or write operations. This enables point-in-time recovery and schema drift detection.

Enabling

# Enable audit log + backup snapshots
mysql-mcp --audit-log /var/log/audit.jsonl --audit-backup --mysql-host localhost

# Also capture sample data (up to 100 rows)
mysql-mcp --audit-log /var/log/audit.jsonl --audit-backup --audit-backup-data --mysql-host localhost

Variable	Default	Description
AUDIT_BACKUP	false	Enable pre-mutation snapshots
AUDIT_BACKUP_DATA	false	Include sample data rows in snapshots
AUDIT_BACKUP_MAX_SIZE	52428800	Max table size for data capture (50MB)

Snapshotted Tools

Snapshots are triggered by these tools (all others are audited but don't produce snapshots):

Tool	Snapshot Type
mysql_drop_table	Table DDL (+data)
mysql_optimize_table	Table DDL
mysql_repair_table	Table DDL
mysql_import_data	Table DDL (+data)
mysql_drop_schema	Schema object list
mysql_drop_view	View DDL
mysql_drop_partition	Table DDL
mysql_doc_drop_collection	Collection schema

Backup Management Tools

Three MCP tools provide agent access to backup snapshots:

Tool	Scope	Parameters	Description
mysql_audit_list_backups	read	limit (default: 10), target (optional string)	List snapshots with optional target filter
mysql_audit_restore_backup	admin	filename, includeData (bool), dryRun (bool)	Restore snapshot DDL+data
mysql_audit_diff_backup	read	filename	Compare snapshot DDL against live schema

Features: mysql_audit_restore_backup supports dryRun: true which returns the DDL that would be executed without actually modifying the database. mysql_audit_diff_backup enables agents to see exact schema drift over time.

Retention

Snapshots are cleaned up automatically during each snapshot capture cycle. The system currently enforces a hardcoded retention policy of 30 days maximum age or 1000 snapshots, whichever is reached first. The oldest snapshots exceeding either limit are deleted first.

---

🔗 Explore Related Topics

• OAuth — Authentication and access control
• Resources — mysql://audit resource details
• Code Mode — Admin scope requirements and native isolated-vm sandbox
• Tool Reference — Full tool list with scope groups