用户无法远程连接MySQL
问题
(Windows XP) LAN 上其他 IP 的用户突然无法连接到我的本地 MySQL 服务器。
背景
我已在本地 Windows 计算机上设置 MySQL，以便网络上的其他计算机可以访问 root 帐户。 我已将每个 IP 添加为 root 的主机。 直到几周前，一切都运行得非常完美，我可以通过编程方式并使用各种 MySQL 管理工具连接到服务器。 然而现在，MySQL 服务器只是拒绝来自这些 IP 的连接，我不明白为什么。
我所做的网络更改是：更改两台（三台）计算机的网卡并修改 MySQL 设置。 这些都不应该引起这个问题。 我尝试添加具有所有相关主机的新用户，但出现相同类型的错误：
MySQL 错误号 1045 访问被拒绝 对于用户“root”@“shop”（使用 密码：是）
奇怪的是，使用计算机名称“shop”而不是 IP。 我不知道为什么。
Problem
Users from other IPs on the (Windows XP) LAN suddenly cannot connect to my local MySQL server.
Background
I've set up MySQL on my local Windows computer so that other computers on the network have access to the root account. I've added each IP as a host for root. Up to some weeks ago, things worked flawlessly and I could connect to the server programatically and using various MySQL admin tools. Now, however, the MySQL server simply refuses connections from those IPs and I can't figure out why.
The network changes that I've done are: changing network card for two (of three) computers and fiddled around with MySQL settings. None of which should have caused this problem. I've tried adding a new user with all relevant hosts, but I get the same type of error:
MySQL Error number 1045 Access denied
for user 'root'@'shop' (using
passwords: YES)
The odd part is that the computer name, 'shop', is used instead of the IP. I don't know why.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
不知何故，IP 现在似乎已经解析并使用了主机名。 您是否授予 root@shop 访问权限？ 你刷新权限了吗？
Somehow, IPs seem to be resolved now and hostnames are used. Did you grant access to root@shop? Did you flush privileges?
首先想到的是 Windows 防火墙，如果您交换主机上的网卡，它可能会重新启用。
我的下一个建议是在主机上使用 Wireshark 之类的嗅探器，看看按数据包到底发生了什么。 您可以使用过滤器来减少输出 - 它们非常简单且易于使用。 这个工具为我节省了无数的调试时间。
-编辑-
另一个可能的原因可能是您的服务器以某种方式决定将 IP 解析为主机名，在这种情况下，IP 地址可能不再起作用 - 需要将主机名添加到允许的列表中。 但不确定它是否适用于 MySQL。
First thing that pops into mind is Windows Firewall, which could have got re-enabled if you swapped NICs on the host computer.
My next suggestion would be to use a sniffer like Wireshark on the host computer and see what exactly happens packet-wise. You can use filters to make to reduce the output - they're very simple and easy to use. This tool has saved me countless hours of debugging.
-EDIT-
Another possible cause might be that your server somehow decided to resolve IPs to hostnames, in which case ip addresses may no longer work - one would need to add hostnames to the allowed list. Not sure if it works this way for MySQL though.
你能关闭 MySQL 中的 TCP 连接吗？
另外，您的防火墙中的 MySQL 端口是否开放？
Could you have turned off TCP connections in MySQL?
Also, is the MySQL port open in your firewall?
如果您更改了 IP（DHCP？），并且将 mysqld 绑定到您的 LAN IP，请确保在 my.cnf 中更正它：
If you changed your IP (DHCP?), make sure to correct it in my.cnf if you bound mysqld to your lan ip: