Database

MySQL Remote access denied – Host is not allowed to connect to this MySQL server

At times when the MySQL server is residing on a different machine, and you want to access MySQL remotely you would get the error as access denied.

To resolve the issue,

First ensure that MySQL server is configured to accept remote connections from the user by verifying the My.cnf

Locate the my.cnf at

vim /etc/mysql/my.cnf, at times the configuration file could also be 
located at etc/mysql/mysql.conf.d/mysqld.cnf

comment out the lines 
#bind-address           = 127.0.0.1
#skip-networking

Restart the MySQL Server


Give permissions to the user who is trying to connect the MySQL Server
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Verify that the privileges have been assigned
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";

Finally flush the privileges to take effect.
FLUSH PRIVILEGES;

Just in case if mistakingly privileges have been given to revoke the privileges to the uer

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%';
Following will revoke all options for USERNAME from particular IP:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4';
Its better to check information_schema.user_privileges table after running REVOKE command.
 

 


 

Leave a Reply

Your email address will not be published. Required fields are marked *