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;
客户端测试连接：
————————————————————————————————————
Ending。
