How does ubuntu, mysql determine hostname?

Question

This is the specific Q: I have apache running on ubuntu in parallels and mysql running on this VM and on the host OS X. Since I try to emulate my production environments locally I connect to the remote mysql server on OS X. I do that successfully for some clients and now I have a new one.

I have umhef.ca, osx and dom.biz in my ubuntu hosts file. When I ping OS X from ubuntu, it reports success and the right IP. My drupal settings file is set the host to 'osx' and the user 'rootd'. The user is set up on the OS x mysql server with rights to this db. How do I know this? I can see other users that successfully set up in mysql and they appear to be the same as this user.

That's background to help me troubleshoot why I'm getting this error: PDOException: SQLSTATE[HY000] [1045] Access denied for user 'rootd'@'umhef.ca' (using password: YES)

WTF is this host coming from? It's legitimate host as it and the others point to the Os x VM. So to troubleshoot this I attempt this from ubuntu:

mysql --host=osx --user=rootd --password=mypassword
Access denied for user 'rootd'@'umhef.ca' (using password: YES)

I've tried commenting out my ubuntu hosts file, but why is ubuntu ignoring my host parameter? What else should I use to troubleshoot this? thx, sam

Answer 1

If you can log into MySQL as root@localhost run the following

SELECT USER(),CURRENT_USER();

What does this tell you:

• USER() reports how you attempted to authenticate in MySQL
• CURRENT_USER() reports how you were allowed to authenticate in MySQL

When you got the error

Access denied for user 'rootd'@'umhef.ca' (using password: YES)

The host part was determined by the table mysql.user. MySQL has a quirky but methodical way of doing user authentication. In your spare time, you can read my DBA StackExchange post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) on how it works.

Anyway, to try to cut to the chase, do this

SELECT user,host FROM mysql.user;

This will give you all possible CURRENT_USER() values (ways you are allowed to connect to MySQL). My guess is you were logging trying to login as rootd@osx and you probably do not have an entry in mysql.user. In other words, if you run

SELECT CONCAT(user,'@',host) userhost FROM mysql.user
WHERE user='rootd' AND host='osx';

you won't get anything. If you run

SELECT CONCAT(user,'@',host) userhost FROM mysql.user WHERE user='rootd';

you may see rootd@umhef.ca. If you don't, then mysqld assume you are coming in that way and no matching authentication exists in mysql.user.

If you made any host changes in the OS, be sure to restart mysqld so it can know those changes.