Monday, February 14, 2011

“Host is not allowed to connect to this mysql server” [Solved]

Depending on your setup, MySql may be locked down pretty tight. This is good. However, today I needed to connect to a database from another host. Googling eventually yielded the appropriate commands but to document the solution for my future self, I’m logging them here.

The issue is that MySql, when properly configured, only allows connections from a very limited set of hosts. Often this is simply “localhost”, which satisfies the very common case of apps/db all on one machine. If you need to access the system from another machine you need to do two things:

  1. Enable MySql to listen on an address (typically TCP/IP port 3306)
  2. Enable a remote user/host to connect with some privileges

Item 1 is covered elsewhere in depth, and in my case is configurable through the MySQL Server Instance Config Wizard. Done.

The solution to item 2 however, was surprisingly difficult to uncover. Here’s the error that probably brought you here:

$ telnet mysql_server 3306

Host: ‘’ is not allowed to connect to this MySQL server

Connection to host lost.

When the connection works you get a handshake request, which fails unless you speak MySQL but the point is you can connect.

What you need to do is enable the host listed in the error message to connect as a particular user. Login to your MySQL server and open a local connection:

$ mysql -uroot -p
Enter password: ************************
Welcome to the....


To see who's already enabled, run this query:

mysql> select host, user from user;
| host               | user    |
|          | root    |
| localhost          |         |
| localhost          | foobar  |
| localhost          | root    |
4 rows in set (0.00 sec)
Now we need to add a new record. I'm interested in simply reading data from my remote host so I'm granting "select" privilges. If you need more, adjust the command accordingly, up to giving the host everything with the "all" keyword:
mysql> grant select on urDatabase.* to urUser@'' identified by 'urPassword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)

And now we're in the user list:

mysql> select host, user from user;
| host                  | user    |
|             | root    |
| localhost             |         |
| localhost             | redmine |
| localhost             | root    |
| | urUser  |
5 rows in set (0.00 sec)

The above applies to MySQL 5, and is probably adaptable to other nearby versions.


Sourabh said...

Thanx.... Really helped me....

Anonymous said...



jyotish varma said...
This comment has been removed by the author.
Anonymous said...

Thank you so much.

Anonymous said...

Thanks !!

Damien said...

You Sir are a hero.
Absolutely genius! Thanks a lot - you saved me from a night shift ;)

Anonymous said...

Thanx for this. I have a problem. In urUser@'', what is urUser & I am trying to have connection between 2 system in a LAN. Want to connect to MySQL is running on Please help.

Michael said...

Thanks. It helped a lot.

Raul Libório said...

Thanks bro! Very good!

Dan said...

Thanks for this. I followed your advice - which is also recommended by several on Stack Overflow.

Logging in with the skip-grant-tables option works. Modifying the records in the user table works.

mysql> select host,user from mysql.user;
| host | user |
| | root |
| localhost | root |

But once the skip-grant-tables option is disabled, the same error returns: localhost is not allowed to connect...

any other ideas?

sk said...

hello ... very good post; thank you VERY much.
Clean crisp **and** explanatory.
Keep these kind of posts coming
- sanjiv singh

Taibai Li said...

qihang0611coach outlet store online
pandora jewelry
michael kors outlet
oakley sunglasses
coach factory outlet
christian louboutin sale
coach outlet
true religion jeans
toms shoes
nike air max
chanel online shop
ralph lauren
ray ban sunglass
polo ralph lauren
tory burch outlet
burberry outlet online
gucci outlet online
ray ban uk
kate spade uk
cheap ray ban sunglasses
ray ban glasses
cheap jordans
coach outlet
true religion
toms outlet
michael kors bags
michael kors outlet online
coach outlet online
michael kors bag

Anonymous said...

Very neat post, saved a lot of my time! Thanks a ton :)

mao qiuyun said...

juicy couture outlet
soccer jerseys
ralph lauren outlet
hermes bags,
christian louboutin outlet
louis vuitton bags,
louis vuitton handbags
cheap nfl jerseys,
fitflop shoes
ugg boots,
oakley sunglasses wholesale,
michael kors uk
michael kors outlet
the north face clearance
manchester united jersey
iphone 6 cases
michael kors uk
rolex watches
coach outlet store,
giuseppe zanotti outlet
converse all star
air max 2015
mcm handbags
michael kors factory outlet,
michael kors factory outlet,
cheap soccer jerseys
true religion jeans,
tory burch shoes
hermes birkin
coach outlet
tory burch outlet online
oakley sunglasses