It’s already 2010, but the MySQL privilege system has been a mess for over a decade.
Most DBAs are aware that under heavy connection load, the MySQL internal resolver can have problems resulting in login failures, if you don’t use skip-name-resolve.
But I found what appears to be another serious bug …
After issuing a GRANT to create a new user with a wildcard hostname like ‘nagios’@'%.domain.com’ and REPLICATION CLIENT privilege recently to 10x 5.1.30-pro lightly-loaded slaves on CentOS 5.4 without skip-name-resolve, one of the slaves stopped accepting remote connections from any user name. (Local connections still worked fine for all users.)
That’s right … the only change was a GRANT.
Execute the GRANT command …
mysql> GRANT REPLICATION CLIENT on *.* to nagios@'%.domain.com' IDENTIFIED BY 'password';
On a remote server …
$ mysql -u user -ppassword -h hostname
Error 1045: Access denied for user 'user'@'hostname' (using password: YES)
The only thing a little odd about that machine was more than 1 hostname or domain name for that host.
So what can one do to lessen occurrences like this, or at least not get bitten as hard?
- disable hostname lookups with skip-name-resolve
- preconfigure grants before going into production
- expect the unexpected when changing grants in any way
- know how to quickly and cleanly shutdown the mysql instance and restart it, ideally with startup scripts.
How can one diagnose MySQL privilege bugs?
- try connections from localhost and remotely
- write a test script to attempt remote connections to help isolate problems
- do show full processlist and look for login states or other odd entries
- mysqladmin flush-hosts to reset the internal DNS host name cache.
MySQL Manual 5.0: 5.4.7. Causes of Access-Denied Errors
MySQL Manual 5.1: 5.4.7. Causes of Access-Denied Errors
MySQL Manual 5.1: 7.5.11. How MySQL Uses DNS
Jeremy Zawodny: Fixing Poor MySQL Default Configuration Values (2001)
