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!
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: 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. for this question, I have spent nearly half a day on it, I am very appreciated for this article! Thanks Your great and simple article solved my issue. 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; Write Comment |
||||
Last Updated ( Saturday, 17 April 2010 ) |
Joomla stuff
Newsletter
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