MySQL MySQL无法连接远程服务器

MySQL unable to connect with remote server

提问by TBA

We have a MySQL server in one of the remote Virtual Machine (Windows Server 2008). Till yesterday we were able to connect to the MySQL server, with the help of workbench installed in our local machine.

我们在其中一个远程虚拟机（Windows Server 2008）中有一个 MySQL 服务器。直到昨天，在我们本地机器上安装的工作台的帮助下，我们能够连接到 MySQL 服务器。

Yesterday there was a restart to the machine which has the Virtual Machine installed. After that we are unable to connect to MYSQL. Though I can ping and remote connect this particular VM. I can even execute the queries inside the workbench installed in the VM.

昨天重新启动了安装了虚拟机的机器。之后我们无法连接到MYSQL。虽然我可以 ping 和远程连接这个特定的虚拟机。我什至可以在安装在 VM 中的工作台内执行查询。

I am not too good at networking or security related stuffs. Please help me to solve this issue.

我不太擅长网络或安全相关的东西。请帮我解决这个问题。

Error :

错误 ：

Your connection attempt failed for user 'root' from your host to server at ABC:3306: Can't connect to MySQL server on 'ABC' (10060)

您在 ABC:3306 上为用户“root”从主机到服务器的连接尝试失败：无法连接到“ABC”上的 MySQL 服务器 (10060)

回答by Steve

Really this could be a magnitude of possible reasons, hopefully this is a start:

真的，这可能是一系列可能的原因，希望这是一个开始：

Check basic network

检查基本网络

From the MySQL virtual machine open up a command prompt and type IPCONFIG /ALL. This will show you all the IP addresses bound to different network adapters.

从 MySQL 虚拟机打开命令提示符并键入IPCONFIG /ALL. 这将显示绑定到不同网络适配器的所有 IP 地址。

Check that the IP address you're connected to is listed there, the virtual machine might have got a new IP from DHCP rather than having a static IP after its restart.

检查您连接到的 IP 地址是否在那里列出，虚拟机可能从 DHCP 获得了一个新 IP，而不是在重新启动后具有静态 IP。

Hostname vs IP

主机名与 IP

You should check the hostname resolution, from your quoted error it would suggest you are you are connecting to a hostname rather than a server IP. Check your machine can resolve to the hostname using the correct IP address - it could also be worth changing the hostname for the actual IP of the server in the connection string.

您应该检查主机名解析，从您引用的错误中可以看出您正在连接到主机名而不是服务器 IP。检查您的机器是否可以使用正确的 IP 地址解析主机名 - 在连接字符串中更改服务器实际 IP 的主机名也是值得的。

MySQL config file

MySQL配置文件

You've said you're running MySQL on Windows, it was customary to rename the my.cnfto my.ini. The configuration file for older versions of MySQL previous to 4.1.5 was usually stored in either c:\my.inior c:\windows\my.ini. For versions after this the default location is the installation directory usually %PROGRAMDATA%\MySQL\MySQL Server xxx.

你曾说过你是在Windows上运行MySQL，这是习惯重命名my.cnf到my.ini。对于旧版本的MySQL之前的4.1.5的配置文件通常存储在任一c:\my.ini或c:\windows\my.ini。对于此之后的版本，默认位置通常是安装目录%PROGRAMDATA%\MySQL\MySQL Server xxx。

When you have located the configuration file please open it on Notepad (or similar text editor), locate the [mysqld]section and make sure that port=the port you're trying to connect to and bind-address=the IP address you're trying to connect to.

找到配置文件后，请在记事本（或类似的文本编辑器）上打开它，找到该[mysqld]部分并确保port=您尝试连接的端口和您尝试连接bind-address=的 IP 地址。

Server ports

服务器端口

From the MySQL virtual server open a command prompt and type netstat –ano, this will show you a list of processes and what IP's / ports they are listening on. The MySQL server should be listed here and should be listening on the same port and IP as the config file defines.

从 MySQL 虚拟服务器打开命令提示符并键入netstat –ano，这将显示进程列表以及它们正在侦听的 IP/端口。MySQL 服务器应该在此处列出，并且应该在与配置文件定义的相同端口和 IP 上进行侦听。

Windows firewall

视窗防火墙

You should have a firewall rule to allow MySQL, the following will add one on the default port of 3306

你应该有一个防火墙规则允许MySQL，下面会在默认端口3306上添加一个

netsh advfirewall firewall add rule name="MySQL Server" action=allow protocol=TCP dir=in localport=3306

Identify if this is machine specific

确定这是否是机器特定的

You could setup the MySQL Workbench application on another workstation and try to connect to identify if this is a global problem or just one related to your specific workstation.

您可以在另一台工作站上设置 MySQL Workbench 应用程序并尝试连接以确定这是一个全局问题还是仅与您的特定工作站相关的问题。

回答by Volkan Ulukut

mysql administrator of your database should allow remote connection to the mysql server. change this in my.cnf:

您的数据库的 mysql 管理员应该允许远程连接到 mysql 服务器。在 my.cnf 中更改：

bind-address    = 127.0.0.1 # this shoul be your mysql server ip

and comment this:

并对此发表评论：

# skip-networking

回答by Gustavo Rubio

Chances are that your configuration was set up for an IP that has changed. By default, mysql won't let you connect from remote hosts unless you explicitly give permissions for a specific user on a specific schema or a group of schemas, for example if you did something like this:

您的配置可能是为已更改的 IP 设置的。默认情况下，mysql 不会让您从远程主机连接，除非您明确授予特定模式或一组模式上的特定用户的权限，例如，如果您执行以下操作：

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Maybe what you actually did was to set the grant onto your own IP address, that is the address of your local machine, and if your local machine (not the remote server) has changed it's IP address, then mysql will not let you connect unless you have the "1.2.3.4" IP address which obviously you don't have anymore if you have a dynamic IP address (common with DSL/Cable connections)

也许您实际所做的是将授权设置为您自己的 IP 地址，即您本地机器的地址，如果您的本地机器（不是远程服务器）更改了它的 IP 地址，那么 mysql 将不会让您连接，除非您拥有“1.2.3.4”IP 地址，如果您拥有动态 IP 地址（常见于 DSL/Cable 连接），显然您不再拥有该 IP 地址

So connect through SSH or Telnet or whatever you use to your windows server and go to mysql as root and do this:

因此，通过 SSH 或 Telnet 或任何您使用的 Windows 服务器连接，并以 root 身份转到 mysql 并执行以下操作：

SELECT * from information_schema.user_privileges;

That will show you the grants on all users and how they are allowed to connect. If you don't see your local IP Address listed there or a wildcard (which would allow you to connect from any remote machine to the server) then you have to set it up like this:

这将显示所有用户的授权以及他们如何被允许连接。如果您没有在那里看到您的本地 IP 地址或通配符（它允许您从任何远程机器连接到服务器），那么您必须像这样设置它：

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Where USERNAME of course is your user. See that after the on there is a wildcard / dot /wildcard that means you want that user to be able to connect to any schema (database, for mysql) from any user from any network. But I'd recommend that you only do the grant for the user for the specific schema you need to connect to.

其中 USERNAME 当然是您的用户。看到在 on 之后有一个通配符/点/通配符，这意味着您希望该用户能够从任何网络的任何用户连接到任何模式（数据库，对于 mysql）。但我建议您只为需要连接的特定架构的用户授予权限。

Then after that, if you actually had the right information and still can't connect than use a portscanner like nmapor something like that to do a port scan and see if mysql is:

然后，如果您确实拥有正确的信息但仍然无法连接，则使用像nmap 之类的端口扫描器或类似的东西来进行端口扫描并查看 mysql 是否：

1. Open and listening to external network
2. Running on the port that you actually want to connect through

1. 打开并监听外网
2. 在您实际想要连接的端口上运行

If 1 is true, then check 2 because maybe there is a misconfiguration of the port. But if any of these 2 points do work then it sounds definitely not like a network configuration but a user setting or something else.

如果 1 为真，则检查 2，因为可能端口配置错误。但是，如果这 2 点中的任何一个确实有效，那么这听起来绝对不像是网络配置，而是用户设置或其他东西。

回答by XXX

GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';

this command should do the trick for all users @Gustavo Rubio has already given the proper explanation.

这个命令应该对所有用户都有用@Gustavo Rubio 已经给出了正确的解释。

To ensure what ports are open run cmd in the virtual machine and type.

要确保打开了哪些端口，请在虚拟机中运行 cmd 并键入。

netstat -a

TCP    127.0.0.1:3360     Hostname:3360   LISTENING

The my.cnf is located Mysql-install-path\MySQL\MySQL Server xxx make sure you backup original before changing

my.cnf 位于 Mysql-install-path\MySQL\MySQL Server xxx 确保在更改前备份原始文件

Can't connect to [local] MySQL server

无法连接到 [本地] MySQL 服务器

Testing The MySQL Server Installation on Microsoft Windows

在 Microsoft Windows 上测试 MySQL 服务器安装

MySQL Workbench: Manage MySQL on Windows Servers the Windows way

MySQL Workbench：以 Windows 方式在 Windows 服务器上管理 MySQL

回答by Adam Endvy

For the first time you need to test and make sure your connection to mysql is not blocked by the firewall.

第一次您需要测试并确保您与 mysql 的连接没有被防火墙阻止。

To disable the firewall on each host in your cluster, perform the following steps on each host.

要在集群中的每台主机上禁用防火墙，请在每台主机上执行以下步骤。

1. Save the existing iptables rule set.

1. 保存现有的iptables 规则集。

iptables-save > /root/firewall.rules

2. Disable iptables.

2.禁用iptables。

For RHEL, CentOS, Oracle, and Debian:

对于 RHEL、CentOS、Oracle 和 Debian：

chkconfig iptables off

and

和

/etc/init.d/iptables stop

For SLES:

对于 SLES：

chkconfig SuSEfirewall2_setup off

and

和

rcSuSEfirewall2 stop

For Ubuntu:

对于 Ubuntu：

service ufw stop

https://www.cloudera.com/documentation/enterprise/5-7-x/topics/install_cdh_disable_iptables.html

https://www.cloudera.com/documentation/enterprise/5-7-x/topics/install_cdh_disable_iptables.html

回答by Robert Sinclair

Depends on your setup, but if you're using cPanel just go to RemoteMYSQL and enter your host. You can also use a wildcard. Below worked for me when I was getting the error

取决于您的设置，但如果您使用的是 cPanel，只需转到 RemoteMYSQL 并输入您的主机。您也可以使用通配符。当我收到错误时，下面对我有用

"Could not connect to DB server '' as user ''. port : Host '' is not allowed to connect to this MySQL server"

“无法以用户 '' 身份连接到数据库服务器 ''。端口：不允许主机 '' 连接到此 MySQL 服务器”