MySQL设置成所有ip可以访问解决Host is not allowed to connect to this MySQL server问题

发表于 | 阅读次数：
| 本文字数： 1.5k | 阅读时长 ≈ 1 分钟

连接数据库的时候报如下错误，原因是本机的ip未被允许访问该数据库服务。我们可以修改为所有ip都可以访问。

1
java.sql.SQLException: null,  message from server: "Host '192.168.1.103' is not allowed to connect to this MySQL server"

Click and drag to move

我们先看下用户表

首先切到mysql数据库

1
2
mysql> use mysql;
Database changed

Click and drag to move

然后查看user表下的用户及允许访问的host

和我猜测的一样，目前我的数据库所有账号的host都设置成localhost，只允许本机访问

1
2
3
4
5
6
7
8
9
10
11
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| Him              | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

Click and drag to move

我们把Him这个账号的host改成%，表示允许所有ip访问

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

Click and drag to move

我们再查询一次，这时候host已经改成%了

1
2
3
4
5
6
7
8
9
10
11
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| Him              | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

Click and drag to move

我们再访问一次，可是却还是连接不了，还是报java.sql.SQLException: null, message from server: “Host ‘192.168.1.103’ is not allowed to connect to this MySQL server”错误

查资料发现是未刷新，执行以下命令即可

1
2
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Click and drag to move

大功告成，再次连接提示成功。