I just had to fix a tricky issue with one of my mysql boxes.  I’m posting a diagnosis below.  If you don’t live & breathe MySQL DB admin every day, probably better to move on now.

The issue is characterized by several ‘unauthenticated’ mysql users attempting to connect to the box on version 5.0.77.  Here’s a screenshot of mytop:

Here is the same diagnostic information in text form in mytop.  You can generate the same by issuing a ‘show processlist;’ from the mysql command line:

Id User Host/IP DB Time Cmd Query or State
— —- ——- — —- — ———-
1 system us 42992 Connec Waiting for maste
2 system us 0 Connec Has read all rela
527420 admin localhost XXXXXXX 0 Query show full process
528650 unauthent 60.191.170.XXX 0 Connec login
528654 unauthent 10.182.33.XXX 0 Connec login
528658 unauthent 10.182.33.XXX 0 Connec login
528659 unauthent 10.182.33.XXX 0 Connec login
528663 unauthent 10.182.33.XXX 0 Connec login
528664 unauthent 10.182.33.XXX 0 Connec login
528665 unauthent 10.182.33.XXX 0 Connec login
528668 unauthent 10.182.33.XXX 0 Connec login
528670 unauthent 10.176.71.XXX 0 Connec login
528671 unauthent 10.182.33.XXX 0 Connec login
528672 unauthent 10.176.71.XXX 0 Connec login
528676 unauthent 10.176.67.XXX 0 Connec login

Turns out the root cause has nothing to do with the client configuration (in this case, PHP 5.3).  The issue is replicable on internal mysql boxes being queried by another server.   The fix:

1. Start mysql with –skip-name-resolve .  Although you may have to change your mysql (mysql.user) permission tables.  They will need to use the IP addresses instead of hostnames.
2. Put the hostnames of your app servers into /etc/hosts

Hat tip MySQL Forums.  This issue took me several hours to diagnose, I hope this saves someone  that time.