MySQL:error 1130 – Host ‘xxx’ is not allowed to connect to this MySQL server
有时候,在使用MySQL的过程中,你可能会遇到这样的错误:
即本文标题所示的:1130 – Host ‘xxx’ is not allowed to connect to this MySQL server
该错误通常发生在安装完MySQL后,希望通过工具远程的访问MySQL服务的时候。
导致该问题的原因是因为MySQL的系统库(mysql)中的数据表“user”的配置不当导致的。
我的当前配置(不恰当的配置)
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select user,host,password from user; +------+-----------------+-------------------------------------------+ | user | host | password | +------+-----------------+-------------------------------------------+ | root | localhost | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | center-system | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | 127.0.0.1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | ::1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | 192.168.195.128 | *2447D497B9A6A15F2776055CB2D1E9F86758182F | +------+-----------------+-------------------------------------------+ 5 rows in set (0.00 sec) mysql> |
Way One(Update Table[mysql.user])。
将host列修改为需要连接到MySQL的客户端主机的IP地址:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> update user set host='192.168.195.1' where host='192.168.195.128'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select user,host,password from user; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | center-system | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | 127.0.0.1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | ::1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | 192.168.195.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F | +------+---------------+-------------------------------------------+ 5 rows in set (0.00 sec) mysql> mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> |
这时候,从设定的客户端连接MySQL,就可以成功了:
注意,如果你希望从任意客户端均可连接到MySQL,则可以将上述hosts列中的客户端IP设置为“%”,命令如下:
1 |
update user set host='%' where host='192.168.195.128'; |
这里需要关注两点:
1.上述所说的任意客户端的真实意义是:任何可以访问到MySQL服务所在的主机的任意客户端。技术上说,如果无法ping通MySQL所在的服务器,则跟user表的设定没有关系(还没有到这一步)
2.既然设定为了任意客户端都可以访问,那么安全问题就很明显了。
另一种修改方式(update user)。
上述修改host是通过Update数据表实现的,也可以通过如下的方式做到:
1 |
update user set host='192.168.195.1'; |
第三种方式:Grant新用户。
具体操作如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
// 旧的记录 mysql> select user,host,password from user; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | % | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | center-system | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | 127.0.0.1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | ::1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | 192.168.195.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F | +------+---------------+-------------------------------------------+ 5 rows in set (0.00 sec) mysql> mysql> grant all privileges on *.* to 'adamhuan'@'%' identified by 'mysql' with grant option; Query OK, 0 rows affected (0.00 sec) // 修改后的记录 mysql> select user,host,password from user; +----------+---------------+-------------------------------------------+ | user | host | password | +----------+---------------+-------------------------------------------+ | root | % | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | center-system | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | 127.0.0.1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | root | ::1 | *9017FF48A1740718224EFDD610251DFF2A2DB9A9 | | adamhuan | % | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | | root | 192.168.195.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F | +----------+---------------+-------------------------------------------+ 6 rows in set (0.00 sec) mysql> |
你可以看到通过“grant …”指令,实际上是给user数据表中新增了一条记录。
就和上文所说的,也可以将“%”改为具体的客户端IP,如下:
grant all privileges on *.* to ‘adamhuan’@’192.168.195.1’ identified by ‘mysql’ with grant option;