Collecting MySQL Diagnostic Reports with MySQL Shell 8.0.31 util.debug

MySQL performance issues often require diagnostic reports; common methods include manual scripts, Percona Toolkit's pt‑stalk, the sys library's diagnostics procedure, and MySQL Shell's util component introduced in version 8.0.31.

The util.debug component provides three built‑in functions: collect_diagnostics (captures data from a single instance, replica set, or InnoDB Cluster), collect_high_load_diagnostics (repeatedly gathers data to detect load spikes), and collect_slow_query_diagnostics (analyzes slow‑query logs collected by the first function).

collect_diagnostics gathers a wide range of information, including tables without primary keys, dead indexes, error logs, binary log metadata, replica status, InnoDB Cluster metrics, lock information, session data, memory statistics, status variables, slow‑query logs, and OS metrics such as CPU, memory, I/O, and network usage.

Typical usage examples are:

util.debug.collect_diagnostics('/tmp/cd1')

Collects a basic report and stores it as /tmp/cd1.zip .

util.debug.collect_diagnostics('/tmp/cd2', {"slowQueries": true})

Enables slow‑query collection (requires the slow‑query log to be enabled and output in TABLE format).

util.debug.collect_diagnostics('/tmp/cd3', {"customSql": ["select * from ytt.t1 order by id desc limit 100"]})

Executes custom SQL statements during collection.

util.debug.collect_diagnostics('/tmp/cd4', {"customShell": ["ps aux | grep mysqld"]})

Runs custom shell commands and includes their output.

util.debug.collect_diagnostics('/tmp/cd5', {"allMembers": true})

Collects diagnostics from all members of a replica set or InnoDB Cluster, producing separate files for each instance.

After extraction, the zip files contain TSV and YAML reports; files prefixed with 0. belong to a single instance, while 1. , 2. , etc., correspond to each member in a cluster.

The enhanced diagnostic collection in MySQL Shell 8.0.31 reduces the need for third‑party tools, simplifying DBA workflows.