Friday, March 23, 2012

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

The below are the most common errors, when connecting remotely to MySQL Server from MySQL Client.

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

Cause: The host that you are using to connect MySQL Server does not have privilege

Solution: Grant required privileges
GRANT ON *.* TO ‘user’@'hostname' IDENTIFIED BY ‘password’ ;
For ex:- if you are connecting as root then use below syntax
Mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Now you should able to see a new user in mysql.user table.
Mysql> select host,user from mysql.user;
+------+---------------------------
| User | Host
+------+---------------------------
| root | test-server
+------+---------------------------

If you want to grant privilege to a host on specific database try below syntax.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Error 1130: #HY000 Host ‘IP address’ is not allowed to connect to this MySQL server

Cause: The user host IP address is not allowed to connect to MySQL Server

Solution: Grant the privilege to host IP address
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘user’@’IPADDRESS’ IDENTIFIED BY ‘password’ ;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’x.x.x.x’ IDENTIFIED BY ‘test’ ;

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: NO)

Cause: The root user is password protected and you need to use password parameter in order to connect

Solution: - Try to connect MySQL Server using below command
# mysql -u root -h test-server –p

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: YES)

Cause: The 'root'@hostname' doesn’t have privilege to connect MySQL Server

Solution: - Grant permission using below command.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@hostname' IDENTIFIED BY ‘password’

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

3 comments:

  1. Dear Friends,

    Because of host name issues, If you're not able to open to MySql prompt itself and seeing below error,
    ERROR 1130 (HY000): Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server

    you might really stuck to apply above mentioned instructions. Then you can try below commands to overcome host name/localhost/127.0.0.1 naming issues and you'll be able to prompted @ MySql> and then you can grant permissions as mentioned above.

    mysql --no-defaults -h127.0.0.1 -uroot -p

    mysql --no-defaults -hlocalhost -uroot -p

    mysql --no-defaults -h -uroot -p

    One of above should work and prompts MySql to execute further queries.
    Hope this helps.

    Best Regards,
    Sandeep Bashetti

    ReplyDelete
  2. You should be able to connect MySQL Server as root and should be able to grant privileges

    Regards
    Satish

    ReplyDelete