MySQL:能够连接到本地主机但不能连接到 127.0.0.1

Question

我猜我忘记了一些明显的东西，但我似乎无法使用 MySQL 连接到 127.0.0.1。通过 localhost 连接有效。

MySQL/Linux 版本:服务器版本:10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

这个有效:

$ mysql -h localhost -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 71982
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

这不有效:

$ mysql -h 127.0.0.1 -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

在我看来，MySQL 正在监听 127.0.0.1:3306:

$ netstat -plnt
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1841/mysqld 

我什至可以使用 Telnet 连接到端口 3306:

$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
j
5.5.5-10.0.31-MariaDB-0ubuntu0.16.04.2TGYk-=,d5-??wz}'Hr*s+u24mysql_native_password

以防万一，我还为主机 127.0.0.1 创建了一个具有授权选项的用户:

mysql > CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '<redacted>';
mysql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;

这是一些关于用户的信息(@brian-ecker 建议的查询):

MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-------------+
| User             | Host      | plugin      |
+------------------+-----------+-------------+
| root             | localhost | unix_socket |
| root             | 127.0.0.1 |             |
+------------------+-----------+-------------+

我还尝试创建一个由 unix_socket 标识的根用户，但没有它的帮助:

MariaDB [(none)]> CREATE USER 'root'@'127.0.0.1' IDENTIFIED VIA unix_socket;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-------------+
| User             | Host      | plugin      |
+------------------+-----------+-------------+
| root             | localhost | unix_socket |
| root             | 127.0.0.1 | unix_socket |
+------------------+-----------+-------------+

关于为什么我可以通过“localhost”而不是“127.0.0.1”连接，您有什么建议吗？

Answer 1

通过将此添加到 MySQL 配置来解决它:

skip-name-resolve       = 1

来自 MySQL documentation :

If it is OFF, mysqld resolves host names when checking client connections. If it is ON, mysqld uses only IP numbers; in this case, all Host column values in the grant tables must be IP addresses or localhost.