Host … is not allowed to connect to this MySQL server

Fri, Aug 19, 2011

Suppose you’ve installed LAMP (Linux, Apache, MySQL, PHP) in your server. Everything is working fine. One day, there was a problem on your server. You don’t want to use the command line mode of MySQL because it is very difficult to debug.

So, you grab a tool, for example SQLyog to connect to that server. Surprisingly, you cannot connect and the sever give you a weird message:

ERROR 2003: Can't connect to MySQL server on 'host_name' (111)

This is a very common problem. You forgot to change the bind address on my.cnf. Use Vim, Nano or any text editor to open /etc/mysql/my.cnf as root and change the bind address from 127.0.0.1 (default) to 0.0.0.0 Now your MySQL server are open to the world (be careful!)

You might think now you will be able to connect. Things are not easy like that. You will get another error message:

Host ... is not allowed to connect to this MySQL server

If you have nmap installed, you can see your server is already open port 3306 (which is MySQL default port). So, why you cannot connect?

MySQL user table has something like 'user'@'host'. By default, there is only 2 entries: 'root'@'localhost' and 'root'@'127.0.0.1'. You have to add one more entry. Connect to MySQL server and type the following:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root-password' WITH GRANT OPTION;

Notice that ‘%’ is wildcard for “any host”. You will be able to login now.

Good luck!