Mysql: host not allowed to connect

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)!

Be first to comment this article

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:
Code:*