1

I have a server (MySQL 8.0.21) that replaces a host name or address with a name that does not exist as a host and thus does not connect. An example:

PS G:\workspace> mysql -u root -p -h geolaptop
Enter password: ********
ERROR 1045 (28000): Access denied for user 'root'@'gut-monitor' (using password: YES)
P

or

PS G:\workspace> mysql -u root -p -h 192.168.1.69
Enter password: ********
ERROR 1045 (28000): Access denied for user 'root'@'gut-monitor' (using password: YES)
PS G:\workspace>

I have set skip-name-resolve to On. I have queried performance_schema.host_cache:

mysql> use performance_schema;
Database changed
mysql> select * from host_cache;
Empty set (0.00 sec)

So how does the host get changed, or better, how to make it stop?

Edit: server's C:\Windows\System32\drivers\etc\hosts:

...
127.0.0.1       match
127.0.0.1       diet
127.0.0.1       diet-fork
127.0.0.1       df2
127.0.0.1       pma
127.0.0.1       test
127.0.0.1       cr2
127.0.0.1       whodat

192.168.1.69    geolaptop

geolaptop`s host file:

...
127.0.0.1       castlerockhoa
127.0.0.1       hw_errors
192.168.1.76    gwb-10
192.168.1.76    diet-fork
192.168.1.76    gut-monitor
geoB
  • 147
  • 8
  • I'm confused. You have a "server", which apparently is different from geolaptop, and yet you're trying to connect to geolaptop? Shouldn't you be connecting to the server? Also, the hostname in the error message is that of the client, so I don't think the server is replacing anything. – mustaccio Jul 27 '23 at 17:13
  • I need to make clear that the command mysql -u root -p -h geolaptop is entered at a different computer than geolaptop named gwb-10. It is an attempt to connect to a remote system. If I'm at geolaptop, I can connect to gwb-10 without any problem. One should be able to connect to a remote MySQL server from the command line. – geoB Jul 27 '23 at 19:01
  • I'm afraid that didn't make things any clearer. – mustaccio Jul 27 '23 at 19:31

1 Answers1

0

Please note the message

ERROR 1045 (28000): Access denied for user 'root'@'gut-monitor' (using password: YES)

You are wondering "Where is gut-monitoring coming from" ?

That comes from MySQL. How ??? Plese run the following query:

SELECT
    USER() How_I_Am_Coming_Into_MySQL,
    CurrentUser() How_MySQL_Allowed_Me_To_Come_Into_MySQL
;

What are those functions ???

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

What MySQL does is look for the user 'root'@'gut-monitor' in the mysql.user table.

If it does not find that exact user, MySQL will look for other entries in mysql.user with the closest match to your username and host.

Once a match is found, then it checks your password.

Please see my Jan 18, 2012 post (MySQL error: Access denied for user 'a'@'localhost' (using password: YES)) for the gory details on MySQL's authentication process.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • If I understood your suggestion correctly, after entering at the command line mysql -u root ..., I then had to log in to mysql, again at the command line, which gave identical results for USER() & CURRENT_USER() as root@::1. If instead I do mysql> select user from mysql.user; I get a list of users, none of which is gut_monitor. – geoB Jul 27 '23 at 18:55
  • In your case, root@:: is the best match mysqld came up with when looking for credentials and coming up with a partial match. Your hostname is not being changed in any way. mysqld sees you coming from gut-monitor so mysqld is looking for root@gut-monitor. If it cannot find it, it looks for other root users with less explicit hostname patterns as explained in my 2012 post (which comes from the MySQL Certification Book). – RolandoMySQLDBA Jul 27 '23 at 20:18
  • I discovered that I've been looking in all the wrong places. I can now accept your answer. Thank you very much. – geoB Jul 27 '23 at 20:34