为什么 mysql 不接受来自本地程序的连接？

Question

"Host 'mydomain.com' is not allowed to connect to this MariaDB server"每当我的 CentOS 7 服务器上的程序尝试访问位于同一台计算机上的 MySQL/MariaDB 数据库时，我都会收到错误。当我hostname在终端中输入时，它会回复mydomain.com。

完整的堆栈跟踪是：

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2395)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2316)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:347)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at mainpackage.TestJDBC.main(TestJDBC.java:23)
Caused by: java.sql.SQLException: null,  message from server: "Host 'mydomain.com' is not allowed to connect to this MariaDB server"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1114)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2493)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2334)
... 13 more  

此服务器已运行具有本地数据库连接的应用程序一段时间。在我开始使用以下方式在服务器上安装 OpenVPN 后，发生了错误本教程中的说明。 此后，我几乎撤消了教程中的所有命令，但某些脚本调用除外，因为这些脚本调用的反函数未知。执行完到目前为止的步骤后，错误仍然存​​在。

为了让 MySQL 连接再次被本地接受，我需要做哪些更改？

-----------------------------------------------------------------------------------------

编辑＃1

根据@Dan 的要求，/etc/hosts没有改变，因此保持不变：

127.0.0.1 localhost.localdomain localhost
# Auto-generated hostname. Please do not remove this comment.
192.96.215.22 mydomain.com mydomain
::1     ip6-localhost ip6-loopback  

此外，应用程序的连接字符串仍然存在： "jdbc:mysql://localhost:3306/sometestdb?autoReconnect=true";

编辑＃2

根据@sufado 的建议，I want to confirm that /etc/hosts应该如下：

127.0.0.1 localhost.localdomain localhost mydomain.com
# Auto-generated hostname. Please do not remove this comment.
192.96.215.22 mydomain.com mydomain
::1     ip6-localhost ip6-loopback  

Answer 1

它明确地告诉你：

Host 'mydomain.com' is not allowed to connect to this MariaDB server

因此，您需要做的是在 mysql 数据库的用户表中添加 mydomain.com 作为允许的主机。例如：

mysql> select host,user,password from user;
+--------------------+--------------+-------------------------------------------+
| host               | user         | password                                  |
+--------------------+--------------+-------------------------------------------+
| localhost          | root         | *E99774447E62446243A1880831F8956BD8529ABF |
+--------------------+--------------+-------------------------------------------+

GRANT ALL PRIVILEGES ON *.* to root@"mydomain.com" IDENTIFIED BY 'XXXX' WITH GRANT OPTION;

mysql> select host,user,password from user;
+--------------------+--------------+-------------------------------------------+
| host               | user         | password                                  |
+--------------------+--------------+-------------------------------------------+
| localhost          | root         | *E99774447E62446243A1880831F8956BD8529ABF |
| mydomain.com       | root         | *E99774447E62446243A1880831F8956BD8529ABF |
+--------------------+--------------+-------------------------------------------+

您也可以只添加“%”，它就适用于任何主机，就像一个通配符。如果您甚至无法进入数据库进行上述更改，那么您应该将主机名从 mydomain.com 更改为 localhost。您允许的数据库连接至少应该是 localhost ip 127.0.0.1

更新：

You can disable DNS host name lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can use only IP addresses in the MySQL grant tables.

您收到 mydomain.com 错误的原因是因为操作系统正在将 DNS 主机名查找 127.0.0.1 解析为 mydomain.com。这不是 mysql 问题，而是网络问题。