MySQL 用户 'test'@'ip' 访问被拒绝（使用密码：YES）

Access denied for user 'test'@'ip'(using password: YES)

提问by a3765910

I have found many posts for this error, tried everything but still getting same error.I am trying to connect to mysql on ubuntu server from my remote app and mysql client. Let me post whatever steps I have taken already :

我找到了很多关于这个错误的帖子，尝试了一切，但仍然得到同样的错误。我试图从我的远程应用程序和 mysql 客户端连接到 ubuntu 服务器上的 mysql。让我发布我已经采取的任何步骤：

1. Stop the firewall on ubuntu server : iptables -F. Also tried with sudo service ufw stop.
2. Commented "bind-address" in /etc/mysql/my.cnf and restarted mysql.
3. Added user in mysql : CREATE USER 'test'@'%' IDENTIFIED BY 'testpwd';
4. GRANT ALL PRIVILEGES ON .TO 'test'@'%' IDENTIFIED BY 'testpwd' WITH GRANT OPTION;
5. FLUSH PRIVILEGES;
6. I can see the entry of new user by "Select host, user from mysql.user";
7. restarted mysql again. Still the same error!
8. Now I thought maybe there is some problem still with firewall, so added :iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT Problem still persist. Help will be really appreciated otherwise I will be in serious trouble.

1. 停止 ubuntu 服务器上的防火墙：iptables -F。还尝试使用 sudo service ufw stop。
2. 在 /etc/mysql/my.cnf 中注释“bind-address”并重新启动 mysql。
3. 在 mysql 中添加用户：CREATE USER 'test'@'%' IDENTIFIED BY 'testpwd';
4. 授予所有特权。TO 'test'@'%' 由 'testpwd' 识别，并带有 GRANT 选项；
5. 同花顺特权；
6. 我可以通过“Select host, user from mysql.user”看到新用户的条目；
7. 再次重启mysql。还是一样的错误！
8. 现在我想也许防火墙仍然存在一些问题，所以添加了 :iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT 问题仍然存在。将非常感谢帮助，否则我将遇到严重的麻烦。

回答by LSerni

"Access denied for user 'test'@'ip'(using password: YES)" is a MySQL error.

“用户'test'@'ip'的访问被拒绝（使用密码：YES）”是一个MySQL错误。

This means that at the network level everything is working, because to be denied access as a given user, the server must have understood which user you were trying to connect as. So network, firewall, routing, and so on and so forth, must all be working; the server must be listening, etc..

这意味着在网络级别一切正常，因为要以给定用户的身份拒绝访问，服务器必须了解您尝试以哪个用户身份连接。所以网络、防火墙、路由等等，必须都在工作；服务器必须正在侦听等。

The problem lies "simply" in the authentication.

问题“仅”在于身份验证。

Try connecting locally to the database (to override the authentication) and inspect the privilege table:

尝试在本地连接到数据库（以覆盖身份验证）并检查权限表：

USE mysql;
SELECT User, Host, Password from user WHERE User = 'test';

and remember that the line you're interested in is the one mentioning the IP (since the error message specifies the IP, and notthe host name - in which case, it could have been a DNS problem; the host name is the hostname that the server believes you are coming from, not the hostname you are really coming from).

并记住您感兴趣的那一行是提到 IP 的那一行（因为错误消息指定了 IP而不是主机名 - 在这种情况下，它可能是 DNS 问题；主机名是主机名服务器相信您来自，而不是您真正来自的主机名）。

The user/host matching goes from more specificto less specific. So if you already had:

用户/主机匹配从更具体到不太具体。因此，如果您已经拥有：

user      host     password
test      1.2.3.4  foo

and ran,

跑了，

GRANT... TO test@'%' ... PASSWORD bar

...this grant would work from everywhere except1.2.3.4, where the password would remain 'foo'.

...此授权适用于除1.2.3.4之外的任何地方，其中密码将保留为“foo”。

From the manual (link above):

从手册（上面的链接）：

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means “any host” and is least specific. The empty string '' also means “any host” but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is indeterminate.

服务器使用排序规则，首先对具有最特定主机值的行进行排序。文字主机名和 IP 地址是最具体的。（文字 IP 地址的特殊性不受其是否具有网络掩码的影响，因此 192.168.1.13 和 192.168.1.0/255.255.255.0 被认为具有同等特殊性。）模式“%”表示“任何主机”并且是最不特殊的. 空字符串 '' 也表示“任何主机”，但在 '%' 之后排序。具有相同 Host 值的行首先使用最具体的 User 值进行排序（空白 User 值表示“任何用户”并且是最不具体的）。对于具有同样特定 Host 和 User 值的行，顺序是不确定的。

You might be forced to do

你可能被迫做

USE mysql;
DELETE FROM user WHERE User = 'test';
GRANT ALL PRIVILEGES ON database.* TO 'test'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

to ensure that there're no spurious lines in the grant table referring to the user 'test'.

以确保授权表中没有引用用户“test”的虚假行。

(Also, the GRANT should be, I think,

（此外，我认为 GRANT 应该是，

GRANT ALL PRIVILEGES ON databasename.*

)

)

---

---

Security doubt (unrelated to the answer)

安全疑问（与答案无关）

The manual above says: The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.

上面的手册说：文字IP地址的特殊性不受它是否有网络掩码的影响，因此 192.168.1.13 和 192.168.1.0/255.255.255.0 被认为是同等特殊的。

Now at a very first glance 127.0.0.1/0.0.0.0seems very specific (and harmless) for localhost. The netmask, if I'm not mistaken, ensures that it is equivalent to %, except that it is incredibly specific and will run first. Therefore

现在乍一看127.0.0.1/0.0.0.0似乎对localhost非常具体（且无害）。如果我没记错的话，网络掩码确保它等同于%，除了它非常具体并且将首先运行。所以

test     bar         %           
test     localfoo    127.0.0.1/0.0.0.0

means that the password for testfrom anywhere it's not "bar" at all, but it is "localfoo".

意味着test来自任何地方的密码根本不是“bar”，而是“localfoo”。

No one would insert such a grant by mistake, but there's mistake and mistake.

没有人会错误地插入这样的赠款，但有错误和错误。

回答by Lev Bystritskiy

First let's check - it's network/firewall problem or not. Start MySql server with --skip-grant-tables , then try to connect to it.

首先让我们检查一下 - 是否是网络/防火墙问题。使用 --skip-grant-tables 启动 MySql 服务器，然后尝试连接到它。

回答by Bazinga

I had the same error on my .net core console app, when trying to connect to my MariaDb database. I finally find that the uid in my connectionstring was not correct because it is case sensitive! Even if I felt quite stupid, it might help...

尝试连接到我的 MariaDb 数据库时，我在 .net 核心控制台应用程序上遇到了同样的错误。我终于发现我的连接字符串中的 uid 不正确，因为它区分大小写！即使我觉得自己很愚蠢，但它可能会有所帮助......