MySQL permission denied from local but can connect remotely

Question

I am trying to connect to my mysql database on a remote server (via ssh) through the command:

mysql -u me -h mydomain.com -p

But it fails with a ERROR 1045 (28000): Access denied for user.. error

While

mysql -u me -h localhost -p

Works

Now this isn't just because I have not setup permissions, because the permissions to this database are set for % or any host for the me user.

This is proved by the fact that I can connect correctly from my local machine to the server, using the same user. i.e. running the following command from my local machine works:

mysql -u me -h mydomain.com -p

So my question why does this happen and how can I fix it? Why can I not connect to my mysql server from my server when I use the domain name instead of localhost, even though the permissions are setup to accept connections from any host.

Answer 1

This happens because of the way MySQL handles permission grants.

When you connect from a remote host (or from the local host via an external IP), it will match the me@% entry (if there is no specific grant for the particular host you're using!). But when you connect via the loopback interface (the "localhost" IP) or a socket, it will use the me@localhost grant. So you must have two GRANT PRIVILEGES; one for me@localhost and one for me@%.