CertGrid CertGrid
Concepts·MySQL

MySQL Diagnostic Toolkit

Where to look, in what order, when something is wrong: the error log for the server, the process list for now, status variables for since-startup, and `performance_schema` for the detail underneath.

Troubleshooting Guide 40 of 45 Intermediate

Written against the versions above. Ubuntu writes the error log to `/var/log/mysql/error.log` via a config setting. On other builds it may go to the data directory or to syslog - `@@log_error` is the reliable answer.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Find the error log, then read it

    Ask the server rather than guessing the path - it differs by distribution and by config.

    Every line carries a timestamp, a thread id (0 for the server itself), a severity, and an error code like MY-010931 that is searchable and stable across versions and translations.

    This tail shows normal startup, and two things worth recognising. CA certificate ca.pem is self signed - MySQL generates its own certificates on first start, so TLS works but is not verifiable by a client; real certificates are a deliberate later step. And IP address '192.168.0.82' could not be resolved for both replicas, which is reverse DNS failing in a lab with no PTR records. Harmless here, but on every new connection the server waits for that lookup - which is why skip_name_resolve is standard advice for a server with IP-based accounts.

    bash Example session 
    sudo mysql --table -e "SELECT @@log_error AS error_log"+--------------------------+| error_log                |+--------------------------+| /var/log/mysql/error.log |+--------------------------+sudo tail -6 /var/log/mysql/error.log2026-08-27T13:06:39.684656Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.2026-08-27T13:06:39.684678Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.2026-08-27T13:06:39.698069Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock2026-08-27T13:06:39.698152Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.10-0ubuntu0.26.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).2026-08-27T13:07:49.961450Z 15 [Warning] [MY-010055] [Server] IP address '192.168.0.82' could not be resolved: Name or service not known2026-08-27T13:09:43.719410Z 27 [Warning] [MY-010055] [Server] IP address '192.168.0.83' could not be resolved: Name or service not known

    Expected resultThe log path, then startup lines with MY- codes and two warnings.

    Success conditionYou can find and read the server's own account of itself.

  2. Ask the sys schema which statements cost the most

    performance_schema collects an enormous amount and is painful to query directly. The sys schema is a set of views over it that are meant to be read by people, and sys.statement_analysis is the one to start with.

    Statements arrive normalised - literals replaced by ?, so the same query with different parameters aggregates into one row rather than thousands. That is what makes exec_count meaningful.

    Read rows_examined_avg against rows_sent_avg, the same ratio the slow query log guide used. The difference here is that this needs no configuration and no log file, and it covers every statement rather than only the slow ones - so it finds the query that takes 5ms and runs a hundred thousand times, which the slow log never will.

    bash Example session 
    sudo mysql --table -e "SELECT query, exec_count, ROUND(avg_latency/1000000000,3) AS avg_sec, rows_examined_avg, rows_sent_avg FROM sys.statement_analysis WHERE db = 'appdb' ORDER BY avg_latency DESC LIMIT 3\G"*************************** 1. row ***************************            query: SELECT `CONCAT` ( COUNT ( * )  ... ) ) ) , ? ) ) FROM `customers`       exec_count: 1          avg_sec: 0rows_examined_avg: 10    rows_sent_avg: 1*************************** 2. row ***************************            query: SELECT @@`version_comment` LIMIT ?       exec_count: 9          avg_sec: 0rows_examined_avg: 1    rows_sent_avg: 1*************************** 3. row ***************************            query: SELECT COUNT ( * ) AS `after_crash` FROM EVENTS       exec_count: 1          avg_sec: 0rows_examined_avg: 0    rows_sent_avg: 1

    Expected resultNormalised statements with execution counts and average rows examined.

    Success conditionYou can find expensive statements without enabling anything.

  3. Read the counters that describe the server since it started

    The since-startup view, and the numbers worth watching.

    Uptime is the anchor - every other counter is a total since then, so a low uptime means an unnoticed restart and makes the rest of the row meaningless. Here it is short because the crash recovery guide killed the server.

    Questions is statements handled; divided by uptime it is your queries per second. Slow_queries counts those over long_query_time whether or not the log is on. Innodb_row_lock_waits and Table_locks_waited are contention - both 0 here, on an idle server. Aborted_clients counts connections that went away without closing cleanly, and the 1 here is the connection killed two guides ago.

    None of these is useful as a single reading. Take them twice, a minute apart, and the deltas are the diagnosis.

    bash Example session 
    sudo mysql --table -e "SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Uptime','Questions','Slow_queries','Innodb_row_lock_waits','Innodb_deadlocks','Aborted_clients','Table_locks_waited')"+-----------------------+----------------+| VARIABLE_NAME         | VARIABLE_VALUE |+-----------------------+----------------+| Aborted_clients       | 1              || Innodb_row_lock_waits | 0              || Questions             | 92             || Slow_queries          | 0              || Table_locks_waited    | 0              || Uptime                | 195            |+-----------------------+----------------+

    Expected resultUptime, Questions, Slow_queries, lock waits and Aborted_clients.

    Success conditionYou can characterise a server's workload from counters alone.

Troubleshooting

Official sources

← All MySQL guides