Host 'xxx.xx.xxx.xxx' is not allowed to co

Question

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.

Answer 1

simple way is to login to phpmyadmin with root account , there goto mysql database and select user table , there edit root account and in host field add % wild card . and then through ssh flush privileges

 FLUSH PRIVILEGES;

Answer 2

This answer might help someone...

All these answers didnt help, then I realised I forgot to check one crucial thing.. The port :)

I have mysql running in a docker container running on a different port. I was pointing to my host machine on port 3306, which I have a mysql server running on. My container exposes the server on port 33060. So all this time, i was looking at the wrong server! doh!

Answer 3

If this is a recent mysql install, then before changing anything else, try simply to execute this command and then try again:

flush privileges;

This alone fixes the issue for me on Ubuntu 16.04, mysql 5.7.20. YMMV.

Answer 4

Simple way:

Grant All Privileges ON *.* to 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD'; 

then

FLUSH PRIVILEGES;

done!