Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

manpreet Best Answer 3 years ago

This should be dead simple, but I cannot get it to work for the life of me.
I'm just trying to connect remotely to my MySQL server.

connecting as

mysql -u root -h localhost -p  

works fine, but trying

mysql -u root -h 'any ip address here' -p

fails with the error

ERROR 1130 (00000): Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL server

In the mysql.user table, there is exactly the same entry for user 'root' with host 'localhost' as another with host '%'.

I'm at my wits' end, and have no idea how to proceed. Any ideas are welcome.

manpreet 3 years ago

My error message was similar and said 'Host XXX is not allowed to connect to this MySQL server' even though I was using root. Here's how to make sure that root has the correct permissions.

My setup:

• Ubuntu 14.04 LTS
• MySQL v5.5.37

Solution

1. Open up the file under 'etc/mysql/my.cnf'

2. Check for:

   • port (by default this is 'port = 3306')
   • bind-address (by default this is 'bind-address = 127.0.0.1'; if you want to open to all then just comment out this line. For my example, I'll say the actual server is on 10.1.1.7)

3. Now access the MySQL Database on your actual server (say your remote address is 123.123.123.123 at port 3306 as user 'root' and I want to change permissions on database 'dataentry'. Remember to change the IP Address, Port, and database name to your settings)

   mysql -u root -p
   Enter password: <enter password>
   mysql>GRANT ALL ON *.* to root@'123.123.123.123' IDENTIFIED BY 'put-your-password';
   mysql>FLUSH PRIVILEGES;
   mysql>exit

4. sudo service mysqld restart

5. You should now be able to remote connect to your database. For example, I'm using MySQL Workbench and putting in 'Hostname:10.1.1.7', 'Port:3306', 'Username:root'