Host '****' is not allowed to connect to this MySQL server

版权声明:本文为博主hanchao5272原创文章,转载请注明来源,并留下原文链接地址,谢谢! https://blog.csdn.net/hanchao5272/article/details/79978694

错误信息

今天在VMWare上的Ubuntu上配置Solr服务时报错:

Caused by: java.sql.SQLException: null,  message from server: "Host 'DESKTOP-FG4HTMB' is not allowed to connect to this MySQL server"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1038)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2190)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2221)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2016)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
    at org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:192)
    at org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:172)
    at org.apache.solr.handler.dataimport.JdbcDataSource.getConnection(JdbcDataSource.java:528)
    at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:317)
    ... 14 more

错误分析与解决

报错原因:

本机的MySql服务只允许本机访问,未开启对其他计算机的访问权限。


进入MySql的cmd窗口,通过查询user表,可以看到当前的host模式:

mysql> use mysql
Database changed

mysql> select user,host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | localhost |
| mysql.sys | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

可以发现root用户的host=localhost,也就是说只允许本机访问。


通过update进行修改:

mysql> update user set host='%'where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | %         |
| mysql.sys | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

将localhost修改成%。

重启Mysql服务

问题解决。


host=%的意义

%是通配符,host标识允许访问的IP地址。

例如:

  • host=localhost:只允许本机访问
  • host=192.168.7.%:只允许以192.168.7.为前缀的IP地址访问
  • host=%:允许所有IP地址访问
阅读更多