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: