We rebooted a machine running mysql, and we're having some odd issues. Most processes from other machines are able to connect to without issue. We can also connect locally if we connect through the loopback address explicitly.
This works:
mysql -hlocalhost -utest_user -psecret_password When we run the command:
mysql -h192.168.1.10 -utest_user -psecret_password locally on the machine we get the following error:
ERROR 1045 (28000): Access denied for user `test_user`@`SERVER_123` We had the following users:
test_user@localhost test_user@% We have fixed the issue by adding the following user:
test_user@SERVER_123 Where SERVER_123 is the unqualified name of the machine. We don't know why we had to add that additional user, and we don't want to keep it, but we also don't know why adding it fixed the issue.
Why does connecting from the local machine, via IP (i.e. mysql -ha.b.c.d -utest_user -ppsswd) fail to match the test_user@% user, but will match test_user@machine_name, where machine_name is the unqualified name of the machine?
Here is the user table
anonymous % anonymous SERVER_123 anonymous localhost test_user_2 % test_user_3 % root ::1 root 127.0.0.1 root SERVER_123 root localhost
4 Answers
Answers 1
I would take a look at the sorting rules in this section of the MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/connection-access.html
It is possible for the client host name and user name of an incoming connection to match more than one row in the user table.
It could be that your user is identified as another user with a different password. What other entries are present in your user table?
Update based on new information: Your login was identified as the ''@'%' user. This user either has different credentials, or is missing the necessary permissions.
Answers 2
You can follow the MySQL documentation here, which talks about the DNS lookup optimization and also the process how this is achieved. To point out one point from the documentation is the following:
The MySQL server maintains a host cache in memory that contains information about clients: IP address, host name, and error information. The server uses this cache for nonlocal TCP connections. It does not use the cache for TCP connections established using a loopback interface address (127.0.0.1 or ::1), or for connections established using a Unix socket file, named pipe, or shared memory.
And there is another point that help you resolve this is with the introduction of the following parameter:
skip-name-resolve You can follow the article here that suggests that MySQL authenticates based on IP addresses or Hostnames - Article link here.
By default MySQL grants authentication on users based on IP address or hostnames. When there is a connection from a particular host, MySQL does a reverse DNS lookup and compares the hostname and IP address.
Hope this helps to answer your question!
Answers 3
First of all remember only 127.0.0.1, ::1, localhost etc... are considered as loopback addresses not 192.168.1.10 or SERVER_123 as you may be accepting.
Yes! 192.168.1.10 should match for wildcard host %, but unfortunately beside test_user_2 and test_user_3 I don't see test_user entry in provided user table data.
In addition on Linux you may use `/etc/hosts`` file to map ip address to hostname
Answers 4
Found the issue. There is an anonymous user which is matching before the specific user. I'm not sure what happened during the reboot that caused reverse resolution to change, but we had a user ''@'SERVER_123' which must have started matching. Removing the anonymous user fixed the issue, and we no longer need to have privileges for test_user@SERVER_123.
0 comments:
Post a Comment