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

Question

I'm was just trying to connect to my MySQL server remotely.

connecting as

mysql -u root -h localhost -p  

Works fine, but trying

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

Fails with the below error

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

In the mysql.user table, it was the same info for the user 'root' with the host 'localhost' as another with host '%'.

please help with this.

Answer 1

You have to create a new MySQL User and assign the required privileges as below in the Query prompt via the phpMyAdmin or using the command prompt:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Once completed with all these above four queries, it should be connecting with username / password

Hope this helps !

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.

For more information visit :