How to log and track every query in MySQL

Four ways, four answers

“Log every query” can mean different things. Pick the tool that answers your question, because each one costs something different:

• What did this connection just run? Performance Schema statement history. On by default in MySQL 8, in memory, no restart.
• What ran in the last few minutes, across all connections? The Performance Schema long history. One switch, in memory.
• Every statement, in order, with who sent it? The general query log. Complete, but heavy; use it for short windows.
• Every statement with its duration and rows examined? The slow query log with a threshold of zero.

The examples are for MySQL 8.0 and 8.4. Changing global settings and Performance Schema setup tables needs an administrative account.

1. Recent statements, no setup

Performance Schema keeps the last few statements of every connection in events_statements_history. Join it to threads to see the user and the connection id that SHOW PROCESSLIST shows:

SELECT t.PROCESSLIST_ID              AS connection_id,
       t.PROCESSLIST_USER            AS user,
       t.PROCESSLIST_HOST            AS host,
       h.CURRENT_SCHEMA              AS db,
       ROUND(h.TIMER_WAIT / 1e9, 2)  AS duration_ms,
       h.ROWS_EXAMINED,
       h.ROWS_SENT,
       LEFT(h.SQL_TEXT, 150)         AS sql_text
FROM performance_schema.events_statements_history AS h
JOIN performance_schema.threads AS t
  ON t.THREAD_ID = h.THREAD_ID
WHERE t.PROCESSLIST_USER IS NOT NULL
ORDER BY h.TIMER_START DESC
LIMIT 50;

Timer columns are in picoseconds, so dividing by 10⁹ gives milliseconds. The table holds only a small, fixed number of statements per connection; check how many with:

SHOW VARIABLES LIKE 'performance_schema_events_statements_history%';

2. A longer in-memory history

events_statements_history_long keeps the most recent statements across all connections. Its consumer is off by default; turning it on takes effect immediately and needs no restart.

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history_long';

SELECT h.THREAD_ID,
       h.CURRENT_SCHEMA              AS db,
       ROUND(h.TIMER_WAIT / 1e9, 2)  AS duration_ms,
       h.ROWS_EXAMINED,
       h.ERRORS,
       LEFT(h.SQL_TEXT, 150)         AS sql_text
FROM performance_schema.events_statements_history_long AS h
ORDER BY h.TIMER_START DESC
LIMIT 100;

• When the table is full, the oldest row is dropped for each new one, so on a busy server it covers seconds or minutes, not hours.
• Its size is set at startup with performance_schema_events_statements_history_long_size.
• The setting is lost on restart unless you also add performance-schema-consumer-events-statements-history-long=ON to the configuration file.

3. The general query log

The general query log records every connect, disconnect and statement the server receives, in the order received, with the user and connection. Writing it to a table makes it easy to query.

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

SELECT event_time,
       user_host,
       thread_id,
       command_type,
       LEFT(CONVERT(argument USING utf8mb4), 200) AS statement
FROM mysql.general_log
ORDER BY event_time DESC
LIMIT 100;

thread_id here is the connection id from SHOW PROCESSLIST. command_type is Connect, Query, Execute, Quit and similar.

SET GLOBAL general_log = 'OFF';
TRUNCATE TABLE mysql.general_log;

To keep it out of one session, for example your own while you read the log, run SET SESSION sql_log_off = ON; in that session.

4. Every query with timing

The general log has no durations. The slow query log does, and with a threshold of zero every statement meets it, so it logs each one with its query time, lock time and rows examined. Administrative statements such as ALTER TABLE are left out unless log_slow_admin_statements is on, and statements that examine fewer rows than min_examined_row_limit (0 by default) are skipped.

SET GLOBAL log_output = 'TABLE';
SET GLOBAL long_query_time = 0;
SET GLOBAL log_slow_admin_statements = ON;   -- also ALTER TABLE, CREATE INDEX and similar
SET GLOBAL slow_query_log = 'ON';

long_query_time set globally applies to connections opened afterwards; existing connections keep the value they started with. Reconnect the application, or test from a new session.

SELECT start_time,
       user_host,
       query_time,
       lock_time,
       rows_examined,
       rows_sent,
       db,
       LEFT(CONVERT(sql_text USING utf8mb4), 200) AS statement
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 100;

5. Only one user

If you only care about one application or one person, limit Performance Schema history to that user, which keeps the history tables from filling with everything else. setup_actors starts with one row that matches everyone; replace it:

-- see the current rows first so you can restore them
SELECT * FROM performance_schema.setup_actors;

DELETE FROM performance_schema.setup_actors
WHERE HOST = '%' AND USER = '%';

INSERT INTO performance_schema.setup_actors (HOST, USER, ROLE, ENABLED, HISTORY)
VALUES ('%', 'app_user', '%', 'YES', 'YES');

• The change applies to connections opened afterwards, not existing ones.
• To undo it, insert the '%', '%', '%', 'YES', 'YES' row again.
• It affects Performance Schema only. The general and slow logs have no per-user filter.

6. Managed services and audits

• Amazon RDS, Aurora, Cloud SQL and Azure do not allow SET GLOBAL for these settings. Set general_log, slow_query_log, long_query_time and log_output in the service's parameter or flag settings, and read file logs through its console or log export.
• For an audit trail that must be complete and tamper-evident, the general log is the wrong tool: it can be turned off by any administrator and has no filtering. Use an audit plugin, such as MySQL Enterprise Audit or the audit plugins in Percona Server and MariaDB.

The same steps in Quorum

Quorum reads Performance Schema for you and groups statements by fingerprint, so you see which queries ran, how often and how long they took without turning on a log.

• Top queries by fingerprint, with Performance Schema as the source.
• Baselines and comparisons, to see what changed against a normal window.
• Change timeline: deployments, jobs and incidents on one axis with the query activity.
• Scheduled queries, if you want a query such as step 1 to run on a timer.