ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server

Why oh why can I not connect to mysql?

mysql -u root -ptest101 -h xxx.xxx.xxx.xxx
ERROR 1130 (HY000): Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server

In my.cnf I have the below

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 0.0.0.0

I also ran the below...

'UPDATE mysql.user SET Password = PASSWORD('test101') WHERE User = 'root';
FLUSH PRIVILEGES;

I can access on the host machine using mysql -u root -ptest101 but not using mysql -u root -ptest101 -h xxx.xxx.xxx.xxx

Wow...why is this happening? I am n ubuntj 12.04

mysql> SELECT host FROM mysql.user WHERE User = 'root';
+---------------------------------------------+
| host                                        |
+---------------------------------------------+
| %                                           |
| 127.0.0.1                                   |
| ::1                                         | |
| localhost                                   |
+---------------------------------------------+
5 rows in set (0.00 sec)

Your root account, and this statement applies to any account, may only have been added with localhost access (which is recommended).

You can check this with:

SELECT host FROM mysql.user WHERE User = 'root';

If you only see results with localhost and 127.0.0.1, you cannot connect from an external source. If you see other IP addresses, but not the one you're connecting from - that's also an indication.

You will need to add the IP address of each system that you want to grant access to, and then grant privileges:

CREATE USER 'root'@'ip_address' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip_address';

If you see %, well then, there's another problem altogether as that is "any remote source". If however you do want any/all systems to connect via root, use the % wildcard to grant access:

CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

Finally, reload the permissions, and you should be able to have remote access:

FLUSH PRIVILEGES;

Following two steps worked perfectly fine for me:

1. Comment out the bind address from the file /etc/mysql/my.cnf:

   #bind-address = 127.0.0.1

2. Run following query in phpMyAdmin:

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; FLUSH PRIVILEGES;

$mysql -u root --host=127.0.0.1 -p

mysql>use mysql

mysql>GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'redhat@123';

mysql>FLUSH PRIVILEGES;

mysql> SELECT host FROM mysql.user WHERE User = 'root';

> mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'passWord'; Query OK, 0 > rows affected (0.00 sec) > > mysql> GRANT ALL PRIVILEGES ON . TO 'name'@'%'; Query OK, 0 rows > affected (0.00 sec) > > mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) > > mysql>

1. Make sure you have your name and % the right way round
2. Makes sure you have added your port 3306 to any firewall you may be running (although this will give a different error message)

hope this helps someone...

For those who are able to access cpanel, there is a simpler way getting around it.

1. log in cpanel => "Remote MySQL" under DATABASES section:

2. Add the IPs / hostname which you are accessing from

3. done!!!

Go to PhpMyAdmin, click on desired database, go to Privilages tab and create new user "remote", and give him all privilages and in host field set "Any host" option(%).

there an easy way to fix this error

just replace the files on the folder : C:\xampp\mysql\data\mysql

with the files on : C:\xampp\mysql\backup\mysql