Skill
MySQL

mysql · current version v1
Troubleshoot and support a MySQL server — connect, diagnose, and resolve problems across 5.7/8.0/8.4/9.x, on Docker, bare metal, systemd, a managed service, or Kubernetes. Covers the mysql client, mysqladmin, config paths, the caching_sha2_password auth trap, connections, InnoDB locks, slow queries, replication, and disk. Use when someone reports MySQL is down, slow, refusing connections, failing auth after an upgrade, deadlocking, lagging a replica, or out of disk.
12 downloads · published 2026-09-02
What this grants

skill mysql
Skill Card

License or terms: check the skill's own repository for license details (mysql on GitHub).
Security Audits

NanoInfra Scanner PASS no issues found
VirusTotal PASS no engines flagged this file (full report)
Version history

Version	Published	Status
v1	2026-09-02	published
Files

SKILL.md (8307 bytes)
SKILL.md

raw | preview
---
name: mysql
description: Troubleshoot and support a MySQL server — connect, diagnose, and resolve problems across 5.7/8.0/8.4/9.x, on Docker, bare metal, systemd, a managed service, or Kubernetes. Covers the mysql client, mysqladmin, config paths, the caching_sha2_password auth trap, connections, InnoDB locks, slow queries, replication, and disk. Use when someone reports MySQL is down, slow, refusing connections, failing auth after an upgrade, deadlocking, lagging a replica, or out of disk.
---

# 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

```bash
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:
```bash
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`:
```bash
docker exec -it <c> mysql -uroot -p
docker logs --tail 200 -f <c>
```
**systemd (bare metal or VM)**:
```bash
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):
```bash
# 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

```bash
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

```sql
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.
```sql
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**
```sql
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.