问题描述

为什么哦为什么我连不上mysql?

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

在 my.cnf 中有以下内容

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

我还运行了以下...

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

我可以使用 mysql -u root -ptest101 在主机上访问，但不能使用 mysql -u root -ptest101 -h xxx.xxx.xxx.xxx

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

哇……为什么会这样?我是 ubuntj 12.04

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)

推荐答案

您的 root 帐户，此声明适用于任何帐户，可能只添加了 localhost 访问权限(推荐).

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';

如果您只看到 localhost 和 127.0.0.1 的结果，则无法从外部源进行连接.如果您看到其他 IP 地址，但不是您所连接的 IP 地址 - 这也是一种指示.

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.

您需要添加要授予访问权限的每个系统的 IP 地址，然后授予权限:

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';

如果您看到 %，那么，还有另一个问题，因为它是任何远程源".但是，如果您确实希望通过 root 连接任何/所有系统，请使用 % 通配符授予访问权限:

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;

这篇关于ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！