MySQL troubleshooting
Can't connect to MySQL server
Check systemctl status mysql or mariadb.
Test with mysqladmin ping. Verify bind-address in
config and ss -tlnp | grep 3306. Read
/var/log/mysql/error.log for startup failures.
Access denied for user
MySQL matches user and client host ('user'@'host').
Check grants: SHOW GRANTS FOR 'user'@'host';. The host pattern
must match the connecting IP. Reset password or grant as admin if locked out.
Too many connections
Hit max_connections. Check SHOW STATUS LIKE 'Threads_connected';.
Kill idle sessions with KILL id;. Use connection pooling
(ProxySQL, application pool) for long-term relief.
Lock waits and deadlocks
Find blockers with SHOW PROCESSLIST (state
Waiting for ... lock). Full detail in
SHOW ENGINE INNODB STATUS\G under LATEST DETECTED DEADLOCK.
Keep transactions short; ensure indexes support update/delete predicates.
Disk full
Binlogs, InnoDB tablespaces, and temp tables can fill the disk. Check sizes
in information_schema.tables and binlog directory. Purge old
binlogs on primary only when replicas have applied them:
PURGE BINARY LOGS BEFORE '2024-01-01';. See the
disk volumes lab.
Replication stopped — SQL thread error
SHOW REPLICA STATUS\G — if Replica_SQL_Running: No,
read Last_SQL_Error. Common: duplicate key from writes on replica,
missing table, or schema drift. Fix data or skip one event with care
(SET GLOBAL sql_replica_skip_counter = 1; — legacy; prefer GTID
SET GTID_NEXT=...; BEGIN; COMMIT; only when you understand the risk).
Replication stopped — IO thread error
Replica_IO_Running: No — network, wrong credentials, or primary
unreachable. Check Last_IO_Error. Test from replica host:
mysql -h primary -u repl -p -e "SELECT 1". Verify replication
user grants and firewall on port 3306.
Replication lag (Seconds_Behind_Source high)
Replica cannot apply events as fast as primary generates them. Causes: large
batch writes on primary, missing indexes on replica, single-threaded SQL
applier (check replica_parallel_workers), or network latency.
Monitor with SHOW REPLICA STATUS\G.
Accidental writes on replica
Replicas should be read-only: read_only=1 and
super_read_only=1. Writes cause replication conflicts and
duplicate key errors. Find errant transactions with GTID:
SELECT @@gtid_executed on replica vs primary.
Replication debugging workflow
1. Replica status
SHOW REPLICA STATUS\G
-- Check: Replica_IO_Running, Replica_SQL_Running,
-- Last_IO_Error, Last_SQL_Error, Seconds_Behind_Source2. Primary binlog position
-- On primary:
SHOW MASTER STATUS;
SELECT @@gtid_executed;3. Compare GTID sets (if enabled)
SELECT @@gtid_executed; -- on primary and replica
-- Replica should be subset of primary (plus any errant local transactions)General debugging workflow
1. Server up?
mysqladmin ping
systemctl status mariadb2. What's running?
mysql -e "SHOW FULL PROCESSLIST"3. Disk usage
df -h /var/lib/mysql
du -sh /var/lib/mysql/*Practice scenarios
Hands-on MySQL scenarios on live Linux VMs: mysql