MySQL Debugging: Common Problems and Solutions#

When MySQL breaks, it falls into a handful of failure modes. Here are the diagnostic workflows, in order of frequency.

Access Denied Errors#

Access denied for user 'appuser'@'10.0.1.5' (using password: YES) means wrong password, user does not exist for that host, or missing privileges.

Diagnosis:

-- 1. Does the user exist for that host?
SELECT user, host, plugin FROM mysql.user WHERE user = 'appuser';
-- MySQL matches user+host pairs. 'appuser'@'localhost' != 'appuser'@'%'.

-- 2. Check grants
SHOW GRANTS FOR 'appuser'@'%';

-- 3. Auth plugin mismatch? Old clients can't handle caching_sha2_password:
ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

To reset a lost root password:

# Stop MySQL, start with --skip-grant-tables
sudo systemctl stop mysqld
sudo mysqld --skip-grant-tables --skip-networking &
mysql -u root

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';

Then restart MySQL normally.

Too Many Connections#

Error 1040: Too many connections. MySQL hit max_connections.

-- Check current state
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- See what connections are doing
SHOW FULL PROCESSLIST;
-- Look for: many connections in Sleep state (idle app connections),
-- or many connections in Query state (overloaded server)

Immediate fix:

-- Increase dynamically (does not persist across restart)
SET GLOBAL max_connections = 500;

The real question is why. Common causes: no connection pooling, idle connections not being closed, or burst traffic. Find and kill idle connections:

SELECT id, user, host, command, time FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 300;
KILL 12345;

Long-term: implement connection pooling (ProxySQL, HikariCP) and set wait_timeout to close idle connections automatically.

Slow Queries and SHOW PROCESSLIST#

When the application is slow, start here:

-- What is running right now?
SHOW FULL PROCESSLIST;
-- Look for queries with high Time values or State = 'Sending data',
-- 'Creating sort index', 'Copying to tmp table'

-- For more detail, use Performance Schema
SELECT * FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL\G

If you see a specific slow query, grab it and run EXPLAIN:

EXPLAIN ANALYZE <paste the slow query here>;

Common patterns:

type: ALL on a large table: missing index.
Using temporary; Using filesort: the query needs a temp table and sort. Consider adding an index that covers the ORDER BY/GROUP BY.
Subquery in WHERE clause scanning millions of rows: rewrite as a JOIN.

InnoDB Lock Waits#

Lock wait timeout exceeded; try restarting transaction – one transaction is waiting for a lock held by another.

-- MySQL 8.0+: find who is blocking whom
SELECT * FROM performance_schema.data_lock_waits\G
SELECT * FROM performance_schema.data_locks\G

-- Deeper analysis
SHOW ENGINE INNODB STATUS\G
-- Look for "LATEST DETECTED DEADLOCK" and "TRANSACTIONS" sections

Find the blocking transaction’s thread ID and kill it if necessary:

KILL 67890;

Common causes: long-running transactions left open (forgot to COMMIT), batch UPDATEs hitting many rows while OLTP queries run, and missing indexes causing lock escalation.

Replication Lag#

On a replica:

SHOW REPLICA STATUS\G
-- Key fields:
-- Seconds_Behind_Source: lag in seconds (NULL means replication is broken)
-- Replica_IO_Running: should be Yes
-- Replica_SQL_Running: should be Yes
-- Last_Error: error message if SQL thread stopped

If Replica_SQL_Running: No, check Last_Error. For duplicate key errors, skip the event: SET GLOBAL sql_replica_skip_counter = 1; START REPLICA;

If lag is high but replication is running, enable parallel replication:

SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';

Other causes: heavy writes exceeding replica disk throughput, or long queries on the replica blocking the apply thread.

Disk Full#

MySQL stops accepting writes when disk is full. The error log will show No space left on device.

df -h /var/lib/mysql
du -sh /var/lib/mysql/*
# Purge binary logs
mysql -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"
# Rotate slow log
mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old && mysqladmin flush-logs
# Check for long-running transactions bloating undo logs
mysql -e "SELECT trx_id, trx_started, trx_rows_modified FROM information_schema.innodb_trx;"

Crashed Tables and InnoDB Corruption#

If MySQL won’t start or you see InnoDB: corrupted page in the error log:

# Check the error log first
tail -100 /var/log/mysql/error.log

# Start in recovery mode (try levels 1-6, start low)
# Add to my.cnf:
# [mysqld]
# innodb_force_recovery = 1
sudo systemctl start mysqld

# Once started, dump the data immediately
mysqldump -u root -p --all-databases > emergency_dump.sql

# Then rebuild: stop MySQL, remove data, reinitialize, restore
sudo systemctl stop mysqld
sudo rm -rf /var/lib/mysql/*
sudo mysqld --initialize
sudo systemctl start mysqld
mysql -u root -p < emergency_dump.sql

Recovery levels: 1 (skip corrupt pages) through 6 (skip redo log). Higher levels risk data loss. Always dump and rebuild rather than running in recovery mode permanently.

For individual InnoDB table corruption, force a rebuild:

ALTER TABLE tablename ENGINE=InnoDB;  -- rebuilds the table in place

This resolves minor corruption and reclaims fragmented space. REPAIR TABLE only works for MyISAM.