Mysql: host not allowed to connect

Saturday, 17 April 2010

The issue I was encountering was that MySQL 5.1.3 on Ubuntu 9.10 was not allowing external connections, i.e. connections from a host different from the host the server is running on.

Okay, my dirty fix was(all the commands were run from a terminal):

First edit /etc/mysql/my.cnf:

user@ubuntu:~$ sudo nano /etc/mysql/my.cnf

and find a line like

 bind-address            = 127.0.0.1

,then add another line below stating your public address:

bind-address            =192.168.0.4

 

In my case 192.168.0.4 was the IP for which I wanted to enable remote connections.

127.0.0.1 is (always) the IP for localhost, I didn't touch that line so local applications(such as PHP) can connect as well.

After saving, login into mysql

mysql -u root -p

'root' being the username. You will be prompted for your password.

Now, we need to take a look at the MySQL users system table:

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| 127.0.0.1 | root             |
| localhost | debian-sys-maint |
| localhost | root             |
| ubuntu    | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

 Probably the best way to fix privileges is to add another user and follow the instructions from

MySQL :: MySQL 5.5 Reference Manual :: 2.11.3 Securing the Initial MySQL Accounts

What i did instead was:

mysql> update mysql.user set host='%' where host='127.0.0.1';

 

 and when checking the users table again i get:

 mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | debian-sys-maint |
| localhost | root             |
| ubuntu    | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

 

Things are fine, % stands for 'any host'. Now we logout from mysql with Ctrl+D and restart the server:

user@ubuntu:~$ sudo service mysql restart

All done, good luck!

 



Give us some social love (it really works now)!

Reddit! Del.icio.us! StumbleUpon! Yahoo! Swik!



Comments (6)
RSS comments
1. 08-12-2011 22:50

I have read many threads on how to fix the problem but none pointed out the fact that 127.0.0.1 in the user table would interfere with the records afterward. Thanks, it really helped.

2. 15-10-2011 08:59
Psuj

No need to restart - just enter in prompt: 
flush privileges; 
 
after update mysq.user table :)

3. OMG12-10-2011 02:37
john

I spent 3 days trying to figure it out. And my company paid for those 3 days. Thanks for your post.

4. thanks!04-09-2011 09:14

for this question, I have spent nearly half a day on it, I am very appreciated for this article! Thanks

5. Great Article!05-08-2011 05:24

Your great and simple article solved my issue. :grin

6. Great Article!09-03-2011 12:32
Alex

You probably had the same problem I had: the machine hosting MySQL could not resolve the IP of the incoming IP - that was solved by adding an entry on the hosts file; 
 
Also helps to check that mysql.user table has the correct settings for the user you are trying to connect (using IPs instead of hostnames) also helps... 
 
Yeah, it was painful....

Write Comment
  • Please keep the topic of messages relevant to the subject of the article.
  • Please don't use comments to plug your web site. Links are rel='nofollow'-ed
  • Please refresh the page if you're having trouble with the security image code
Name:
E-mail
Homepage
Title:
Comment:

:) :grin ;) 8) :p
:roll :eek :upset :zzz :sigh
:? :cry :( :x
Code:* Code

Last Updated ( Saturday, 17 April 2010 )
 

Europe freelancer directory

Newsletter

Subscribe to TeachMeJoomla's newsletter
Name:
Email:


Auto tags

Host is not allowed to connect to this MySQL server

is not allowed to connect to this MySQL server

mysql host is not allowed to connect to this mysql server

host not allowed to connect to this mysql server

not allowed to connect to this MySQL server

Host is not allowed to connect to this MySQL server

mysql host is not allowed to connect

mysql is not allowed to connect to this MySQL server

host is not allowed to connect to this mysql

mysql host not allowed to connect

is not allowed to connect to this MySQL

host localhost is not allowed to connect to this mysql server

mysql user host

localhost is not allowed to connect to this mysql server

mysql allowed hosts

mysql host not allowed

is not allowed to connect to this MySQL server in

host is not allowed to connect to mysql

mysql update user host

mysql host not allowed to connect to this mysql server

mysql add host

mysql connect from any host