MySQL — Troubleshooting & Support

A support runbook, not a tutorial. Establish the host with the detect-platform skill first, then work top to bottom: read before you write. Commands and output below were run against MySQL 8.4.11. This skill is MySQL; MariaDB is a separate product with its own skill — a box you think is "MySQL" may be MariaDB (its client is mariadb, not mysql), so confirm with the version string.

Lines that matter: 5.7 is EOL; 8.0 is the long-lived major; 8.4 is the current LTS; 9.x are innovation (short-support) releases. Diagnose against SELECT VERSION();.

0. Identify what you are on

mysql --version                                  # mysql  Ver 8.4.11 for Linux … MySQL Community Server
mysql -uroot -p -N -e 'SELECT VERSION();'        # 8.4.11 — and confirms it is MySQL, not MariaDB
mysqld --version

Config and data locations:

mysql -uroot -p -e "SHOW VARIABLES WHERE Variable_name IN ('datadir','log_error','slow_query_log_file','socket','port','bind_address');"
# config file: /etc/my.cnf (upstream image / RHEL) or /etc/mysql/my.cnf (+ conf.d) on Debian/Ubuntu
mysqld --verbose --help 2>/dev/null | grep -A1 'Default options'   # which files it actually reads

1. Connect, per deployment

Docker — error log to stderr → docker logs:

docker exec -it <c> mysql -uroot -p
docker logs --tail 200 -f <c>

systemd (bare metal or VM):

systemctl status mysql 2>/dev/null || systemctl status mysqld
journalctl -u mysql -n 200 --no-pager 2>/dev/null || journalctl -u mysqld -n 200 --no-pager
tail -f /var/log/mysql/error.log 2>/dev/null || tail -f /var/lib/mysql/*.err

Bare metal — the settings that bite (detect-platform gave you RAM and the cgroup cap):

# innodb_buffer_pool_size is the big one — ~50-70% of RAM on a dedicated host, but it must fit
# UNDER a container/cgroup memory limit or mysqld gets OOM-killed.
mysql -uroot -p -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS gb, @@max_connections;"
cat /proc/$(pgrep -o mysqld)/limits | grep 'open files'   # open_files_limit vs the OS fd limit

Managed — no host shell; the client connects, config is the provider's parameter group, restarts/failover/upgrades are console actions. SHOW, performance_schema, and the slow log (via console) still work.

Kubernetes (operator) — kubectl exec -it <pod> -- mysql -uroot -p; config is the CR; the root password is in a Secret.

2. Tools and arguments

mysql -h <host> -P 3306 -u <user> -p -D <db>    # -N skips the header, -B tab-separated, -e "SQL" one-shot
mysqladmin -uroot -p status                      # uptime, threads, questions, slow queries at a glance
mysqladmin -uroot -p processlist                 # like SHOW PROCESSLIST from the shell
mysqldump -uroot -p --single-transaction --routines --triggers <db> > db.sql   # consistent InnoDB dump
mysqldump … --all-databases | gzip > all.sql.gz
mysqlbinlog <binlog>                             # inspect/replay binary logs (PITR)

--single-transaction gives a consistent dump on InnoDB without locking writes; without it a dump locks tables.

3. Diagnostics — read-only first

SHOW GLOBAL STATUS LIKE 'Threads_connected';   SHOW VARIABLES LIKE 'max_connections';
SHOW FULL PROCESSLIST;                          -- who is connected and what they run (Time, State)
SELECT * FROM sys.session WHERE conn_id <> CONNECTION_ID();   -- friendlier processlist (sys schema)
SHOW ENGINE INNODB STATUS\G                     -- LATEST DETECTED DEADLOCK, lock waits, buffer pool
SELECT * FROM performance_schema.data_lock_waits;   -- current blocking (8.0+)
SELECT digest_text, count_star, avg_timer_wait/1e9 AS avg_ms
  FROM performance_schema.events_statements_summary_by_digest
  ORDER BY avg_timer_wait DESC LIMIT 10;        -- slowest statement shapes

Slow queries: SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=0.2; then read slow_query_log_file. Plans: EXPLAIN ANALYZE <query>; (8.0+) — watch for type: ALL (full scan) and a missing index.

4. Common problems → resolution

Auth fails after an 8.0/8.4 upgrade — the signature MySQL 8 trap MySQL 8 defaults the auth plugin to caching_sha2_password (confirmed: root uses it here). An old client or driver that only speaks mysql_native_password fails with Authentication plugin 'caching_sha2_password' cannot be loaded or an auth error over a non-TLS socket.

SELECT user, host, plugin FROM mysql.user;      -- what each account actually uses
-- fixes, in order of preference: upgrade the client/driver; use TLS (sha2 needs a secure channel
-- to send the password the first time); or, last resort, per-account:
ALTER USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY '…';

Note mysql_native_password is deprecated in 8.4 and must be explicitly enabled there — the real fix is a driver that supports sha2.

Cannot connect

• Can't connect … through socket '/var/run/mysqld/mysqld.sock' — connecting locally via socket but the server is down or the socket path differs; SHOW VARIABLES LIKE 'socket' or use -h 127.0.0.1 to force TCP.
• Host '…' is not allowed to connect — the account's host column does not match; accounts are user@host pairs.
• Too many connections — max_connections reached; a pooler is the right fix, not just raising it.

InnoDB locks / deadlocks

• Deadlock found when trying to get lock — InnoDB already rolled one transaction back; fix lock ordering in the app. SHOW ENGINE INNODB STATUS\G → LATEST DETECTED DEADLOCK shows the two.
• A stuck query holding locks: find it in SHOW PROCESSLIST and KILL <id> (§6).
• Lock wait timeout exceeded — a transaction waited past innodb_lock_wait_timeout; usually a long/abandoned transaction holds the row — find it in performance_schema.data_lock_waits.

Disk filling

• Binary logs accumulate if binlog_expire_logs_seconds is unset or a replica is not consuming them; SHOW BINARY LOGS; and PURGE BINARY LOGS BEFORE '…' (only once every replica has read past them — §6).
• InnoDB does not shrink the shared tablespace on delete; with innodb_file_per_table (default), OPTIMIZE TABLE rebuilds a table to reclaim, taking a lock.

Replication lag / broken

SHOW REPLICA STATUS\G     -- (SHOW SLAVE STATUS on 5.7): Seconds_Behind_Source, Replica_*_Running,
                          -- Last_Error. Both IO and SQL threads must be "Yes".

A stopped SQL thread with a Last_Error (often a row that already exists / is missing) has to be resolved deliberately — skipping an error silently diverges the replica.

Won't start

• The error log names it: a corrupt InnoDB redo log, a datadir permission problem (files must be owned by the mysql user), or a port already bound. On a crash, InnoDB recovers on start — let it finish; innodb_force_recovery is a last resort that can lose data (§6).

5. Before you run anything that writes

KILL, PURGE BINARY LOGS, OPTIMIZE TABLE, SET GLOBAL, ALTER USER, innodb_force_recovery, and restarts change data, availability, or config — on a nanoinfra deployment a mutate.remote capability (approval interactively, a standing grant unattended). Name the instance and the change.

• SET GLOBAL is runtime-only; persist in my.cnf (or SET PERSIST, 8.0+) or a restart loses it.
• KILL <id> drops a session; an in-flight write rolls back.
• PURGE BINARY LOGS / dropping logs: confirm every replica has consumed them, or you break replication.
• innodb_force_recovery > 0 starts read-mostly and can discard data — copy datadir first, and use the lowest level that lets you dump and rebuild.