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

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

I was working on MySQL Server setup and modified the default location of MySQL datadir. After I restart the MySQL, I was unable to connect and receiving below error.

#mysql –u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

Cause:
When we restart mysql server, it creates a mysql.sock file under socket variable path. The default location is /var/lib/mysql/mysql.sock.

I have updated the new location in /etc/my.cnf file.

[mysqld]
datadir=/opt/mysql/data
socket=/opt/mysql/mysql.sock

I can see the new file under /opt/mysql/mysql.sock, but I still receive the same error and not able to connect to MySQL.

I have modified the socket path under [mysqld], but the parameter was missing under [client] section. When I was trying to connect using MySQL client, it could not able to find mysql.sock file.

Solution:
Add [client] section in /etc/my.cnf

[client]
socket=/opt/mysql/mysql.sock

I have restarted MySQL in order to take effect of new changes in my.cnf

# service mysql stop
# service mysql start

MySQL Client able to find mysql.sock file and I was able to connect.

[root@iwebsql etc]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If you don’t want to restart mysql then use below command to tell client to use /opt/mysql/mysql.sock file

#mysql -u root –socket=/opt/mysql/mysql.sock

You may receive the same error, if mysqld didn't start. You can try below solution.

# /etc/init.d/mysqld start
# mysql –u root

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