不允许主机 'xxx.xx.xxx.xxx' 连接到此 MySQL 服务器

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

提问by concept47

This should be dead simple, but I cannotget it to work for the life of me.
I'm just trying to connect remotely to my MySQL server.

这应该非常简单，但我无法让它为我的生活工作。
我只是想远程连接到我的 MySQL 服务器。

connecting as

连接为

mysql -u root -h localhost -p  

works fine, but trying

工作正常，但正在尝试

mysql -u root -h 'any ip address here' -p

fails with the error

因错误而失败

ERROR 1130 (00000): Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL server

In the mysql.usertable, there is exactly the same entry for user 'root' with host 'localhost' as another with host '%'.

在mysql.user表中，主机为 'localhost' 的用户 'root' 的条目与主机为 '%' 的另一个条目完全相同。

I'm at my wits' end, and have no idea how to proceed. Any ideas are welcome.

我不知所措，不知道如何继续。欢迎任何想法。

回答by Yannick Motton

Possibly a security precaution. You could try adding a new administrator account:

可能是一种安全预防措施。您可以尝试添加一个新的管理员帐户：

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;

Although as Pascal and others have noted it's not a great idea to have a user with this kind of access open to any IP. If you need an administrative user, use root, and leave it on localhost. For any other action specify exactly the privileges you need and limit the accessibility of the user as Pascal has suggest below.

尽管正如 Pascal 和其他人所指出的那样，让具有这种访问权限的用户对任何 IP 开放并不是一个好主意。如果您需要管理用户，请使用 root，并将其保留在 localhost 上。对于任何其他操作，请准确指定您需要的权限，并按照 Pascal 在下面的建议限制用户的可访问性。

Edit:

编辑：

From the MySQL FAQ:

来自 MySQL 常见问题解答：

If you cannot figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain '%' or '_' characters). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this allows you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 5.4.4, “Access Control, Stage 1: Connection Verification”.

如果您无法弄清楚为什么会拒绝访问，请从用户表中删除所有主机值包含通配符的条目（包含“%”或“_”字符的条目）。一个非常常见的错误是插入一个新条目，其中 Host='%' 和 User='some_user'，认为这允许您指定 localhost 从同一台机器连接。这不起作用的原因是默认权限包括一个带有 Host='localhost' 和 User='' 的条目。因为该条目的主机值 'localhost' 比 '%' 更具体，所以从 localhost 连接时，它优先于新条目使用！正确的步骤是插入第二个条目，其中 Host='localhost' 和 User='some_user'，或者删除带有 Host='localhost' 和 User='' 的条目。删除条目后，请记住发出 FLUSH PRIVILEGES 语句以重新加载授权表。另请参见第 5.4.4 节“访问控制，第 1 阶段：连接验证”。

回答by Aditya P Bhatt

One has to create a new MySQL Userand assign privileges as below in Query promptvia phpMyAdmin or command prompt:

必须通过 phpMyAdmin 或命令提示符创建一个new MySQL User并分配如下权限Query prompt：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Once done with all four queries, it should connect with username / password

完成所有四个查询后，它应该连接 username / password

回答by Will

My error message was similar and said 'Host XXX is not allowed to connect to this MySQL server' even though I was using root. Here's how to make sure that root has the correct permissions.

我的错误消息是类似的，并说“主机 XXX 不允许连接到此 MySQL 服务器”，即使我使用的是 root。以下是确保 root 拥有正确权限的方法。

My setup:

我的设置：

• Ubuntu 14.04 LTS
• MySQL v5.5.37

• Ubuntu 14.04 LTS
• MySQL v5.5.37

Solution

解决方案

1. Open up the file under 'etc/mysql/my.cnf'

2. Check for:

   • port (by default this is 'port = 3306')
   • bind-address (by default this is 'bind-address = 127.0.0.1'; if you want to open to all then just comment out this line. For my example, I'll say the actual server is on 10.1.1.7)

3. Now access the MySQL Database on your actual server (say your remote address is 123.123.123.123 at port 3306 as user 'root' and I want to change permissions on database 'dataentry'. Remember to change the IP Address, Port, and database name to your settings)

   mysql -u root -p
   Enter password: <enter password>
   mysql>GRANT ALL ON *.* to root@'123.123.123.123' IDENTIFIED BY 'put-your-password';
   mysql>FLUSH PRIVILEGES;
   mysql>exit
   

4. sudo service mysqld restart

5. You should now be able to remote connect to your database. For example, I'm using MySQL Workbench and putting in 'Hostname:10.1.1.7', 'Port:3306', 'Username:root'

1. 打开'etc/mysql/my.cnf'下的文件

2. 检查：

   • 端口（默认情况下这是'port = 3306'）
   • bind-address（默认情况下这是'bind-address = 127.0.0.1'；如果你想对所有人开放，那么只需注释掉这一行。对于我的例子，我会说实际服务器在10.1.1.7上）

3. 现在访问您实际服务器上的 MySQL 数据库（假设您的远程地址是 123.123.123.123 端口 3306 上的用户“root”，我想更改数据库“dataentry”的权限。记住更改 IP 地址、端口和数据库名称到您的设置）

   mysql -u root -p
   Enter password: <enter password>
   mysql>GRANT ALL ON *.* to root@'123.123.123.123' IDENTIFIED BY 'put-your-password';
   mysql>FLUSH PRIVILEGES;
   mysql>exit
   

4. 须藤服务 mysqld 重启

5. 您现在应该能够远程连接到您的数据库。例如，我正在使用 MySQL Workbench 并输入“Hostname:10.1.1.7”、“Port:3306”、“Username:root”

回答by HimalayanCoder

You need to grant access to the user from any hostname.

您需要从任何主机名授予用户访问权限。

This is how you add new privilege from phpmyadmin

这就是您从 phpmyadmin 添加新权限的方式

Goto Privileges > Add a new User

转到权限 > 添加新用户

Select Any Hostfor the desired username

为所需的用户名选择任何主机

回答by minhas23

Just perform the following steps:

只需执行以下步骤：

1a) Connect to mysql (via localhost)

1a）连接到mysql（通过本地主机）

mysql -uroot -p

1b) If the myslq server is running in Kubernetes (K8s) and being accessed via a NodePort

1b) 如果 myslq 服务器在 Kubernetes (K8s) 中运行并通过 NodePort 访问

kubectl exec -it [pod-name] -- /bin/bash
mysql -uroot -p

2) Create user

2）创建用户

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

3) Grant permissions

3) 授予权限

 GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;

4) Flush priviledges

4) 冲洗特权

FLUSH PRIVILEGES;

回答by Dennis McLaughlin

The message *Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL serveris a reply from the MySQL server to the MySQL client. Notice how its returning the IP address and not the hostname.

该消息*Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL server是 MySQL 服务器对 MySQL 客户端的回复。注意它是如何返回 IP 地址而不是主机名的。

If you're trying to connect with mysql -h<hostname> -u<somebody> -pand it returns this message with the IP address, then the MySQL server isn't able to do a reverse lookup on the client. This is critical because thats how it maps the MySQL client to the grants.

如果您尝试连接mysql -h<hostname> -u<somebody> -p并返回带有 IP 地址的此消息，则 MySQL 服务器无法在客户端上进行反向查找。这很关键，因为这就是它将 MySQL 客户端映射到授权的方式。

Make sure you can do an nslookup <mysqlclient>FROM the MySQL server. If that doesn't work, then there's no entry in the DNS server. Alternatively, you can put an entry in the MySQL server's HOSTS file (<ipaddress> <fullyqualifiedhostname> <hostname><- The order here might matter).

确保您可以nslookup <mysqlclient>从 MySQL 服务器执行。如果这不起作用，则 DNS 服务器中没有条目。或者，您可以在 MySQL 服务器的 HOSTS 文件中放置一个条目（<ipaddress> <fullyqualifiedhostname> <hostname><- 这里的顺序可能很重要）。

An entry in my server's host file allowing a reverse lookup of the MySQL client solved this very problem.

我的服务器主机文件中的一个条目允许反向查找 MySQL 客户端解决了这个问题。

回答by user5510975

Simple way:

简单的方法：

Grant All Privileges ON *.* to 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD'; 

then

然后

FLUSH PRIVILEGES;

done!

完毕！

回答by Pascal Thivent

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGESstatement to tell the server to reload the grant tables.

如果您手动修改授权表（使用 INSERT、UPDATE 等），您应该执行一条FLUSH PRIVILEGES语句来告诉服务器重新加载授权表。

PS: I wouldn't recommend to allow anyhost to connect for anyuser (especially not the rootuse). If you are using mysql for a client/server application, prefer a subnet address. If you are using mysql with a web server or application server, use specific IPs.

PS：我不建议允许任何主机为任何用户连接（尤其是不root使用）。如果您将 mysql 用于客户端/服务器应用程序，请首选子网地址。如果您将 mysql 与 Web 服务器或应用程序服务器一起使用，请使用特定的 IP。

回答by BabaNew

Just use the interface provided by MySql's GUI Tool (SQLyog):

只需使用MySql的GUI工具（SQLyog）提供的界面：

Click on User manager:

点击用户管理器：

Now, if you want to grant access FOR ANY OTHER REMOTE PC, just make sure that, just like in the underneath picture, the Host field value is % (which is the wildcard)

现在，如果您想为任何其他远程 PC 授予访问权限，只需确保如下图所示，Host 字段值为 %（这是通配符）

回答by Erica Kane

Most of the answers here show you creating users with two host values: one for localhost, and one for %.

此处的大多数答案都显示您使用两个主机值创建用户：一个为localhost，一个为%。

Please note that except for a built-in localhost user like root, you don't need to do this. If you simply want to make a new user that can log in from anywhere, you can use

请注意，除了像 root 这样的内置 localhost 用户，您不需要这样做。如果您只是想创建一个可以从任何地方登录的新用户，您可以使用

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
GRANT <whatever privileges are appropriate> ON <relevant tables> TO myuser;

and it will work just fine. (As others have mentioned, it's a terrible idea to grant administrative privileges to a user from any domain.)

它会工作得很好。（正如其他人所提到的，向来自任何域的用户授予管理权限是一个糟糕的主意。）