Why am I seeing “ERROR 1130 (HY000): Host ‘192.168.0.2’ is not allowed to connect to this MySQL server”?

When you connect to a MySQL server, it checks it’s grants table (the “user” table in the “mysql” database on the MySQL server) against the IP address of the connecting MySQL client machine. If there are NO MATCHING ENTRIES in the “host” column in the “user” table in the “mysql” database, mysqld will IMMEDIATELY CLOSE THE CONNECTION with ERROR 1130.

For example, a client (192.168.0.2) tries to connect to it’s MySQL server (192.168.0.100) and gets:

[root@dbclient ~]# mysql -u jennifer --password=toughPassword -h 192.168.0.100 my_database
ERROR 1130 (HY000): Host '192.168.0.2' is not allowed to connect to this MySQL server
[root@dbclient ~]#

..a simple “telnet” TCP-connection test proves that the problem is not a firewall – the client is able to get to port 3306 (the MySQL port) on the database server:

[root@dbclient ~]# telnet 192.168.0.100 3306
Trying 192.168.0.100...
Connected to 192.168.0.100.
Escape character is '^]'.
GHost '192.168.0.2' is not allowed to connect to this MySQL serverConnection closed by foreign host.
[root@dbclient ~]#

If we login to the MySQL server, and run “mysql”, we can look at the grants table:

[root@dbserver ~]# mysql mysql

mysql> SELECT host,user FROM user;
+-----------------+-----------+
| host            | user      |
+-----------------+-----------+
| 192.168.0.6     | jennifer  |
| 192.168.0.7     | jennifer  |
| 127.0.0.1       | root      |
| ::1             | root      |
| localhost       | jennifer  |
| localhost       | root      |
+-----------------+-----------+
11 rows in set (0.00 sec)

mysql>

We can see that “jennifer” *is* authorized to connect from several IP addresses, but not from the client IP 192.168.0.2. So we add the GRANT for access from THAT IP:

mysql> GRANT ALL PRIVILEGES ON jennifer.* TO 'my_database'@'192.168.0.2' IDENTIFIED BY 'toughPassword';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

..and voila:

[root@dbclient ~]# mysql -u jennifer --password=toughPassword -h 192.168.0.100 my_database

mysql>
Share: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Twitter
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • Reddit
  • StumbleUpon