Tampa Tampa - 7 months ago 70
SQL Question

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)

Answer

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. If you see %, well then, there's another problem altogether as that is "any remote source".

You should be able to add this remote access with:

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