MySQL 无法从 EC2 实例连接到 RDS 实例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16837806/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't connect to RDS instance from EC2 instance
提问by Mark Shust at M.academy
From EC2 instance i-78a8df00, I'm trying to connect to RDS instance mysql.************.us-east-1.rds.amazonaws.com. They are both in the U.S. East region. I added the security group of EC2 instance (sg-********) to the RDS security group, but that didn't help. It appears to be a firewall/DNS issue as it is timing out when running this command:
从 EC2 实例 i-78a8df00，我试图连接到 RDS 实例 mysql.************.us-east-1.rds.amazonaws.com。他们都在美国东部地区。我将 EC2 实例的安全组 (sg-********) 添加到 RDS 安全组，但这没有帮助。这似乎是防火墙/DNS 问题，因为它在运行此命令时超时：
ubuntu@ip-10-195-189-237:~$ mysql -h mysql.************.us-east-1.rds.amazonaws.com
ERROR 2003 (HY000): Can't connect to MySQL server on 'mysql.************.us-east-1.rds.amazonaws.com' (110)
错误 2003 (HY000)：无法连接到“mysql.************.us-east-1.rds.amazonaws.com”上的 MySQL 服务器 (110)
I can connect to RDS instance fine from my local machine using the same line as above. I tried various forum solutions but those don't help.
我可以使用与上面相同的线路从我的本地机器连接到 RDS 实例。我尝试了各种论坛解决方案，但这些都无济于事。
采纳答案by Mark Shust at M.academy
Looks like sometime between the last posting and this posting, Amazon fixed the DNS routing issue, because everything works fine now for multi-AZ rds...
看起来在上次发布和本次发布之间的某个时间，亚马逊修复了 DNS 路由问题，因为现在对于多可用区 rds 一切正常...
回答by sapenov
I had similar problem, when I spun a new EC2 instance, but didn't change setting in RDS security group of inbound IP address allowed to connect to port 3306 of my RDS instance.
我遇到了类似的问题，当我启动一个新的 EC2 实例时，但没有更改允许连接到我的 RDS 实例的端口 3306 的入站 IP 地址的 RDS 安全组中的设置。
The confusing bit was an option in RDS dashboard, called Security Groups. You don't need it to solve the problem.
令人困惑的一点是 RDS 仪表板中的一个选项，称为安全组。你不需要它来解决问题。
What you really need is go to list of RDS instances, click on the instance you are trying to connect, then click on middle tab, locate Security and Networksection and click on the name of security group.
您真正需要的是转到 RDS 实例列表，单击您尝试连接的实例，然后单击中间选项卡，找到安全和网络部分，然后单击安全组的名称。
This should open a new browser tab or window with details of security group. Locate several tabs in bottom part, select Inboundtab and click Editbutton.
这应该打开一个新的浏览器选项卡或窗口，其中包含安全组的详细信息。在底部找到几个选项卡，选择入站选项卡，然后单击编辑按钮。
Change value to the IP address of your EC2 instance or IPv4 CIDR blocks, e.g.
将值更改为 EC2 实例的 IP 地址或 IPv4 CIDR 块，例如
174.33.0.0/16
174.33.0.0/16
To get this value, you can either ssh into your instance and run ifconfigor run EC2 Manager in browser and locate value of Private IPsin your instance details.
要获得此值，您可以通过 ssh 进入您的实例并运行ifconfig或在浏览器中运行 EC2 Manager 并在您的实例详细信息中找到私有 IP 的值。
回答by ives
Additional information for people who might run into similar issues trying to connect to RDS or RedShift:
为在尝试连接到 RDS 或 RedShift 时可能遇到类似问题的人提供的附加信息：
1) Check security groups
1) 检查安全组
Verify the security group for the RDS instance allows access from the security group your source server belongs to (or its IP added directly if external to AWS). The security group you should be looking at is the one specified in the RDS instance attributes from the RDS console UI (named "security group").
验证 RDS 实例的安全组允许从您的源服务器所属的安全组（或如果在 AWS 外部直接添加的 IP）进行访问。您应该查看的安全组是 RDS 控制台 UI（名为“安全组”）的 RDS 实例属性中指定的安全组。
NOTE: Database security groups might be different from AWS EC2 security groups. If your RDS instance is in classic/public EC2, you should check in the "database security group" section of the RDS UI. For VPC users, the security group will be an normal VPC security group (the name sg-xxx will be listed in the RDS instance's attributes).
注意：数据库安全组可能与 AWS EC2 安全组不同。如果您的RDS实例在经典/公共EC2中，您应该检查RDS UI的“数据库安全组”部分。对于 VPC 用户，安全组为普通 VPC 安全组（名称 sg-xxx 将列在 RDS 实例的属性中）。
2) Confirm DNS isn't an issue.
2) 确认 DNS 不是问题。
Amazon uses split DNS, so a DNS lookup external to AWS will return the public IP while a lookup internal to AWS will return a private IP. If you suspect it is a DNS issue, have you confirmed different IPs are returned from different availability zones? If different AZs get different IPs, you will need to contact AWS support.
Amazon 使用拆分 DNS，因此 AWS 外部的 DNS 查找将返回公共 IP，而 AWS 内部的查找将返回私有 IP。如果您怀疑是 DNS 问题，您是否确认从不同的可用区域返回了不同的 IP？如果不同的 AZ 获得不同的 IP，您将需要联系 AWS 支持。
3) Confirm network connectivity by establishing a socket connection.
3) 通过建立套接字连接来确认网络连通性。
Tools like tracepath and traceroute likely won't help since RDS currently drops ICMP traffic.
tracepath 和 traceroute 等工具可能无济于事，因为 RDS 当前会丢弃 ICMP 流量。
Test port connectivity by trying to establish a socket connection to the RDS instance on port 3306 (mysql, or 5432 for postgres). Start by finding the IP of the RDS instance and using either telnet or nc:
通过尝试在端口 3306（mysql 或 postgres 的 5432）上建立到 RDS 实例的套接字连接来测试端口连接。首先找到 RDS 实例的 IP 并使用 telnet 或 nc：
telnet x.x.x.x 3306
nc -vz x.x.x.x 3306
a)If your connection attempt isn't successful and immediately fails, the port is likely blocked or the remote host isn't running a service on that port. you may need to engage AWS support to troubleshoot further. If connecting from outside of AWS, try to connect from another instance inside AWS first (as your firewall might be blocking those connections).
a)如果您的连接尝试不成功并立即失败，则该端口可能被阻止或远程主机未在该端口上运行服务。您可能需要联系 AWS 支持以进一步排除故障。如果从 AWS 外部连接，请先尝试从 AWS 内部的另一个实例连接（因为您的防火墙可能会阻止这些连接）。
b)If your connection isn't successful and you get a timeout, packets are probably being dropped/ignored by a firewall or packets are returning on a different network path. You can confirm this by running netstat -an | grep SYN(from a different CLI window/session while running and waiting for the telnet/nc command to timeout). Connections in the SYN state mean that you've sent a connection request, but haven't received anything back (SYN_ACK or reject/block). Usually this means a firewall or security group is ignoring or dropping packets.
b)如果您的连接不成功并且出现超时，则数据包可能被防火墙丢弃/忽略，或者数据包在不同的网络路径上返回。您可以通过运行netstat -an | grep SYN（从不同的 CLI 窗口/会话运行并等待 telnet/nc 命令超时）来确认这一点。处于 SYN 状态的连接意味着您已发送连接请求，但尚未收到任何回复（SYN_ACK 或拒绝/阻止）。通常这意味着防火墙或安全组忽略或丢弃数据包。
Check to make sure you're not using iptables or a NAT gateway between your host and the RDS instance. If you're in a VPC, also make sure you allow egress/outbound traffic from the source host.
检查以确保您没有在主机和 RDS 实例之间使用 iptables 或 NAT 网关。如果您在 VPC 中，还要确保允许来自源主机的出站/出站流量。
c)If your socket connection test was successful, but you can't connect with a mysql client (CLI, workbench, app, etc.), take a look at the output of netstat to see what state the connection is in (replace x.x.x.x with the actual IP address of the RDS instance):
c)如果你的socket连接测试成功，但是无法连接mysql客户端（CLI、workbench、app等），查看netstat的输出看看连接处于什么状态（替换xxxx RDS实例的实际IP地址）：
netstat -an | grep x.x.x.x
netstat -an | grep x.x.x.x
If you were getting a connection established when using telnet or NC, but you see the 'SYN' state when using a mysql client, you might be running into an MTU issue.
如果您在使用 telnet 或 NC 时建立了连接，但在使用 mysql 客户端时看到“SYN”状态，则您可能遇到了 MTU 问题。
RDS, at the time this is written, may not support ICMP packets used for PMTUD (https://en.wikipedia.org/wiki/Path_MTU_Discovery#Problems_with_PMTUD). This can be a problem if you're trying to access RDS or RedShift that's in a VPC from a classic ec2 instance via ClassicLink. Try lowering the MTU with the following, then testing again:
在撰写本文时，RDS 可能不支持用于 PMTUD 的 ICMP 数据包 ( https://en.wikipedia.org/wiki/Path_MTU_Discovery#Problems_with_PMTUD)。如果您尝试通过 ClassicLink 从经典 ec2 实例访问 VPC 中的 RDS 或 RedShift，这可能会出现问题。尝试使用以下方法降低 MTU，然后再次测试：
sudo ip link show
# take note of the current MTU (likely 1500 or 9001)
sudo ip link set dev eth0 mtu 1400
If the lower MTU worked, be sure to follow up with AWS customer support for help and mention that you are seeing an MTU issue while trying to connect to your RDS instance. This can happen if TCP packets are wrapped with encapsulation for tunneling, resulting in a lower usable MTU for packet data / payload. Lowering the MTU on the source server allows the wrapped packets to still fit under the limit.
如果较低的 MTU 有效，请务必联系 AWS 客户支持寻求帮助并提及您在尝试连接到 RDS 实例时遇到 MTU 问题。如果 TCP 数据包使用隧道封装进行包装，则可能会发生这种情况，从而导致数据包数据/有效负载的可用 MTU 较低。降低源服务器上的 MTU 允许包装的数据包仍然适合限制。
If it didn't work, set your MTU back to it's default and engage AWS support for further troubleshooting.
如果它不起作用，请将您的 MTU 设置回默认值并联系 AWS 支持以进行进一步的故障排除。
回答by lase
While Mark's problem seemed to have something to do with multi-AZ routing & EC2 classic, I ran into this exact same problem today.
虽然 Mark 的问题似乎与多可用区路由和 EC2 经典有关，但我今天遇到了完全相同的问题。
To fix it, I modified the Security Group that was created automatically with my RDS instance by adding the two private IP addresses from my EC2 instance.
为了修复它，我通过添加来自我的 EC2 实例的两个私有 IP 地址修改了使用我的 RDS 实例自动创建的安全组。
This was a fairly obvious problem, but I'm new to AWS in general, so hopefully this is useful for others like myself.
这是一个相当明显的问题，但总的来说我是 AWS 的新手，所以希望这对像我这样的其他人有用。
回答by Mark Shust at M.academy
Apparently, multi-AZ screws everything up. Since the default multi-AZ config placed my database in region us-east-1d, and my EC2 instance was in region us-east-1a, the DNS was not routing correctly. I re-created the RDS instance as non-multi-AZ, and made it live in us-east-1a, and all is happy.
显然，多可用区搞砸了一切。由于默认的多可用区配置将我的数据库放置在区域 us-east-1d 中，而我的 EC2 实例位于区域 us-east-1a 中，因此 DNS 无法正确路由。我将 RDS 实例重新创建为非多可用区，并将其部署在 us-east-1a 中，一切都很顺利。
If there are any super geniuses out there in regards to DNS routing on AWS with RDS, ELB, and multi-AZ capabilities, it would be pretty awesome to know how to do this, since this isn't documented anywhere in Amazon Web Service's documentation.
如果在具有 RDS、ELB 和多可用区功能的 AWS 上的 DNS 路由方面有任何超级天才，知道如何做到这一点会非常棒，因为这在 Amazon Web Service 的文档中没有任何记录.
回答by Aleksy Goroszko
I had the similar problem today when my EC2 instance suddenly lost access to RDS instance and Wordpress stopped working. The security groups were correct and I could even connect to MySQL from console on EC2 instance but not from PHP. For some reason restarting EC2 server helped me.
我今天遇到了类似的问题，当时我的 EC2 实例突然无法访问 RDS 实例并且 Wordpress 停止工作。安全组是正确的，我什至可以从 EC2 实例上的控制台连接到 MySQL，但不能从 PHP 连接。出于某种原因，重新启动 EC2 服务器帮助了我。
回答by lair60
According to the Amazon doc, we should use:
根据亚马逊文档，我们应该使用：
PROMPT> mysql -h <endpoint> -P 3306 -u <mymasteruser> -p
where endpointand mymasteruser(username) are from your RDS instance.
其中端点和mymasteruser（用户名）来自您的 RDS 实例。
I solved that problem using IP public adress directly(of the endpoint) instead of the endpoint(****.us-east-1.rds.amazonaws.com).You can get the ip public address using "ping" command(ping ****.us-east-1.rds.amazonaws.com)
我直接使用 IP 公共地址（端点的）而不是端点（****.us-east-1.rds.amazonaws.com）解决了这个问题。您可以使用“ping”命令获取 ip 公共地址（ ping ****.us-east-1.rds.amazonaws.com)
