How does mysql determine the hostname of its clients?

Solution 1:

It uses a reverse DNS lookup. It takes the IP address of the client and uses whatever PTR record is returned for that name.

In my opinion doing authentication based on the name is not very useful at all, I suggest you consider using IP addresses instead.

See this document about how Mysql uses DNS.

Solution 2:

MySQL will be doing a reverse DNS look up on the IP address to get the host name. If you are running in AWS EC2 then you can assign an elastic IP to your server (this does not cost any extra) and then ask amazon to set up reverse DNS for the elastic IP to go to your hostname.

Also is your DB server also in EC2? Because if so it will be using the private IP address of the instance, otherwise it will be using the public ip address. It looks from you post as thou appserver-lan is the 10.XXX.XXX.XXX private ip assigned to your server, not the putlic one.

I'm not sure which IP address would be used if communicating with a different region though as I have only had servers within the same region.

Solution 3:

I've just had a similar problem, where the mysql server appeared to be doing reverse DNS lookups incorrectly.

The problem I had was that the server had permissions for 'user'@'1.2.3.4' as well as 'user'@'reverse.dns'. The user with just the IP address had minimal permissions, but the mysql server was using the permissions of that user rather than the one with the hostname, and returning the message "Access denied for user 'user'@'1.2.3.4'". Deleting the user with the IP address fixed the problem and forced the serer to use the other user at the hostname.