使用 MySQL Workbench 通过 EC2 实例连接到 Amazon RDS 实例

Connecting to Amazon RDS instance through EC2 instance using MySQL Workbench

提问by iamyojimbo

In AWS I have a VPC set up with a Bastion Host. The bastion host is a single EC2 instance with a public address trough which you can SSH to any other server on the VPC.

在 AWS 中，我设置了一个带有堡垒主机的 VPC。堡垒主机是具有公共地址槽的单个 EC2 实例，您可以通过 SSH 连接到 VPC 上的任何其他服务器。

I have created an RDS MySQL instance within the VPC and I would like to connect to it using MySQL workbench. I have followed the steps detailed here, however in "Step 6: Setting up remote SSH Configuration", it asks me to "Provide the Public DNS of the Amazon EC2 instance" (i.e. the bastion host).

我在 VPC 中创建了一个 RDS MySQL 实例，我想使用 MySQL 工作台连接到它。我已按照此处详述的步骤操作，但是在“第 6 步：设置远程 SSH 配置”中，它要求我“提供 Amazon EC2 实例的公共 DNS”（即堡垒主机）。

MySQL workbench then does checks for certain MySQL resources on that server. However, this is not correct in my opinion as I have provided the bastion host's address, which does not have MySQL installed. As a result, the last two checks for "Check location of start/stop commands" and "Check MySQL configuration file" then fail.

然后 MySQL 工作台会检查该服务器上的某些 MySQL 资源。但是，我认为这是不正确的，因为我提供了堡垒主机的地址，而该地址没有安装 MySQL。结果，最后两次检查“检查启动/停止命令的位置”和“检查 MySQL 配置文件”失败。

I have then tried using the endpoint address of the RDS MySQL instance but with no success (as it is in the private subnet so is not publicly addressable).

然后我尝试使用 RDS MySQL 实例的端点地址，但没有成功（因为它在私有子网中，所以不能公开寻址）。

It seems that many people have this up and running, but what am I doing wrong here?

似乎很多人都已经启动并运行了，但是我在这里做错了什么？

回答by AndrewSmiley

I've been struggling with something similar for weeks now. Just figured it out a few minutes ago.

几个星期以来，我一直在为类似的事情苦苦挣扎。几分钟前才弄明白。

1. In mysql workbench, create a new server instance.
2. For the remote host address, enter your endpoint address (i.e. xxxxxx.us-east-1.rds.amazonaws.com)
3. For connection method, select "Standard TCP/IP over SSH"
4. The SSH Hostname is the public DNS of your EC2 instance
5. I specified ec2-user (I believe it varies by EC2 Instance type) as the username and then specified the downloaded key file corresponding to the key pair the instance was using.
6. The mysql hostname is the endpoint of the RDS instance.
7. The username is the username for the RDS instance (i.e. ebroot)

1. 在 mysql 工作台中，创建一个新的服务器实例。
2. 对于远程主机地址，输入您的端点地址（即 xxxxxx.us-east-1.rds.amazonaws.com）
3. 连接方式选择“Standard TCP/IP over SSH”
4. SSH 主机名是您的 EC2 实例的公共 DNS
5. 我将 ec2-user（我相信它因 EC2 实例类型而异）指定为用户名，然后指定与实例使用的密钥对对应的下载密钥文件。
6. mysql 主机名是 RDS 实例的端点。
7. 用户名是RDS实例的用户名（即ebroot）

Using that I was able to connect. However, I did not utilize VPC in my setup. Hope this helps. Good luck!

使用它我能够连接。但是，我没有在我的设置中使用 VPC。希望这可以帮助。祝你好运！

回答by john

Since you are using a VPC, this is how it should be configured in order to accept connections from your sub-net only:

由于您使用的是 VPC，因此应配置它以仅接受来自您的子网的连接：

1. select VPC Security Group used by your db instance

2. add a new rule to allow all ips from your private sub-net on port 3306

   ex: Rule INBOUND 3306 (MYSQL) 172.33.11.0/24

3. use mysql workbench tcp/ssh and it will work (follow AndrewSmiley answer).

1. 选择您的数据库实例使用的 VPC 安全组

2. 添加新规则以允许端口 3306 上的私有子网中的所有 ip

   例如：规则入站 3306 (MYSQL) 172.33.11.0/24

3. 使用 mysql workbench tcp/ssh 它将起作用（按照 AndrewSmiley 的回答）。

回答by user3067703

I recommend using SSH tunneling:

我建议使用 SSH 隧道：

1. Create putty session to the bastion host
2. Under Connection --> SSH --> Tunnels, specify the Source port: 3306, Destination: yourRDSendpointname:3306
3. Don't forget to click add!
4. Connect to the bastion host with those settings
5. Add a new connection in MySQL workbench and point it to your localhost port 3306 (assuming you aren't running anything on 3306 on your local client machine)
6. Put in your username and password for your RDS instance

1. 创建到堡垒主机的腻子会话
2. 在连接 --> SSH --> 隧道下，指定源端口：3306，目标：yourRDSendpointname:3306
3. 不要忘记点击添加！
4. 使用这些设置连接到堡垒主机
5. 在 MySQL 工作台中添加一个新连接并将其指向您的本地主机端口 3306（假设您没有在本地客户端机器上的 3306 上运行任何内容）
6. 输入您的 RDS 实例的用户名和密码

回答by Skin

If you want to truly use the VPC connectivity of AWS and not allow public IPs do the following.

如果您想真正使用 AWS 的 VPC 连接并且不允许使用公共 IP，请执行以下操作。

If you have one security group that both your EC2 and RDS are assigned to then add an inbound rule for mysql 3306 on TCP but in the source field do not put IP or subnet but the actual security group ID. ie sg-9829f3d2.

如果您有一个 EC2 和 RDS 都分配到的安全组，则在 TCP 上为 mysql 3306 添加入站规则，但在源字段中不要输入 IP 或子网，而是输入实际的安全组 ID。即 sg-9829f3d2。

I personally have two security groups on VPC.

我个人在 VPC 上有两个安全组。

The first, security group 1 is in use by the EC2 instance and only allows the ports required for the EC2, ie 80 and 22.

第一个，安全组 1 正在被 EC2 实例使用，并且只允许 EC2 所需的端口，即 80 和 22。

The second, security group 2 is in use by just the RDS instance(s) and has one rule for allowing mysql (3302) and the source field is set to the id of security group 1.

第二个，安全组 2 仅由 RDS 实例使用，并且有一个允许 mysql (3302) 的规则，源字段设置为安全组 1 的 id。

All the Mysql workbench SSH tunnelling works with the two security groups as well.

所有 Mysql 工作台 SSH 隧道也适用于两个安全组。

回答by Yasir

You can create an SSH tunnel into your Bastion host (EC2 instance) to forward ports from your local machine to the remote RDS instance.

您可以在堡垒主机（EC2 实例）中创建 SSH 隧道，将端口从本地计算机转发到远程 RDS 实例。

on mac/ linux this is the command (for windows follow instructions in the link below):

在 mac/linux 上，这是命令（对于 Windows，请按照以下链接中的说明进行操作）：

ssh -L 3306:myinstance.123456789012.us-east-1.rds.amazonaws.com:3306  your_c2_ip

then you can connect with workbench using the following settings:

然后您可以使用以下设置连接工作台：

• connection method: standard TCP

• hostname: localhost

• port 3306

• 连接方式：标准TCP

• 主机名：本地主机

• 3306端口

This post explains it the method in more detail; https://userify.com/blog/howto-connect-mysql-ec2-ssh-tunnel-rds/

这篇文章更详细地解释了该方法； https://userify.com/blog/howto-connect-mysql-ec2-ssh-tunnel-rds/

回答by CodeShadow

This is for UBUNTU Mysql workbench

这是用于 UBUNTU Mysql 工作台

You must add an inbound rule to the security group linked with the RDS to accept requests on port 3306 from the bastion/jump/any instance(machine). The machine should have a public IP associated with it.

您必须在与 RDS 链接的安全组中添加入站规则，以接受来自堡垒/跳转/任何实例（机器）的端口 3306 上的请求。这台机器应该有一个与之关联的公共 IP。

Do confirm on your machineif you can connect with the RDS or not first, before trying to setup the SSH tunnel through MYSQL workbench.

在尝试通过 MYSQL 工作台设置 SSH 隧道之前，请先在您的机器上确认您是否可以连接到 RDS。

To test connectivity run:

要测试连接性，请运行：

mysql -u{username} -p{password} -h ***-db-***.cmmaberpdqoc.***.rds.amazonaws.com -P 3306

Replace {username}, {password} and host with your credentials.

用您的凭据替换 {username}、{password} 和 host。

Follow the picture and you should be able to connect.

按照图片，您应该能够连接。

回答by Basil Abbas

This is what solved the issue for me. On the RDS dashboard have a look at the security group for your instance. Click on this and it would take you to the security group page.

这就是为我解决问题的原因。在 RDS 仪表板上查看您的实例的安全组。单击此按钮，它会将您带到安全组页面。

Although it would say "All traffic" for both Inbound and Outbound click edit and ensure the source says My IP. I would not recommend using all IP's as this would open it to any one on the internet. If you do not have a static IP then ensure that you refresh this field once the connection stops working.

虽然它会为入站和出站点击编辑显示“所有流量”，并确保来源显示我的 IP。我不建议使用所有 IP，因为这会向 Internet 上的任何人打开它。如果您没有静态 IP，请确保在连接停止工作后刷新此字段。

After this I was able to connect to the AWS RDS T2 instances.

在此之后，我能够连接到 AWS RDS T2 实例。