从本地机器连接到 AWS 上的 MySQL

Connect to MySQL on AWS from local machine

提问by Jeffrey Hunter

I am trying to set up a dev environment on my local machine that accesses a MySQL DB on AWS, but I keep getting a "Can't connect" message.

我正在尝试在访问 AWS 上的 MySQL 数据库的本地计算机上设置开发环境，但我不断收到“无法连接”消息。

mysql_connect('xxx.xxx.xxx.xxx:3306', 'USERNAME', 'PASSWORD');

I also commented out the bind-address in the my.cnf file, and granted permissions to the IP address that is connecting.

我还注释掉了 my.cnf 文件中的绑定地址，并为正在连接的 IP 地址授予了权限。

Anyone ever successfully get this working?

有没有人成功地让这个工作？

采纳答案by Stephan B

I suppose this is firewalled by Amazon, try using a SSH tunnel:

我想这是由亚马逊防火墙，尝试使用 SSH 隧道：

http://blogs.oracle.com/divyen/entry/connecting_mysql_server_on_amazon

http://blogs.oracle.com/divyen/entry/connecting_mysql_server_on_amazon

Note: Do not open MySQL to the public internet, not even when using IP filtering. SSH tunnels are way more secure. Best part of it: The tunnel could be accessible with localhost:3306 on your machine, no need to change the config : )

注意：不要将 MySQL 开放到公共互联网，即使使用 IP 过滤也不行。SSH 隧道更安全。最好的部分：可以在您的机器上使用 localhost:3306 访问隧道，无需更改配置:)

回答by mikemay

My experience in Aug-2013 was as follows for an RDS instance created through Elastic Beanstalk.

对于通过 Elastic Beanstalk 创建的 RDS 实例，我在 2013 年 8 月的经验如下。

0) Assuming the RDS instance has already been created
1) Log in to the management console: https://console.aws.amazon.com/console/home
2) Select Services->VPC
3) Select Security Groups (on the left hand side)
4) Select the group whose description says "Security Group for RDS DB..."
5) In the Security Group Selected panel at the bottom of the page, choose "Inbound"
6) Select MySQL as the rule.
7) Type the ip address of my local machine e.g. 145.23.32.15/32
8) Click Add Rule and Apply Rule Changes

0）假设已经创建了RDS实例
1）登录管理控制台：https://console.aws.amazon.com/console/home
2）选择Services->VPC
3）选择Security Groups（在左边手边)
4) 选择描述为“RDS DB 的安全组...”的组
5) 在页面底部的“已选择的安全组”面板中，选择“入站”
6) 选择 MySQL 作为规则。
7) 输入我本地机器的 IP 地址，例如 145.23.32.15/32
8) 单击添加规则并应用规则更改

After doing this I could connect to the database using mysql from my local machine.

完成此操作后，我可以使用本地计算机上的 mysql 连接到数据库。

a) From management console select Services->RDS
b) Click on DB Instances (I have only one) and select "Go to Details Page" for the required instance
c) Obtain Host and Port from the endpoint
d) From a terminal session do soemthing like: mysql --host blah.blah.blah.us-west-2.rds.amazonaws.com --port 3306 -u my-user-name -p

a) 从管理控制台选择服务-> RDS
b) 单击数据库实例（我只有一个）并为所需实例选择“转到详细信息页面”
c) 从端点获取主机和端口
d) 从终端会话执行诸如此类：mysql --host blah.blah.blah.us-west-2.rds.amazonaws.com --port 3306 -u my-user-name -p

回答by Cbox

If you are using MySql on AWS via an RDS instance you must add the IP address you want to connect from to the "DB Security Groups". To do this go to your AWS Managment Console and select RDS.
1. Select "DB Security Groups" on the left panel
2. Select "default"
3. Select "CIDR/IP" from the select box and enter your workstations public IP address. Example:
23.234.192.123/32 (dont forget the /32 for a single ip)
4. Click "Add"
5. Wait a few minutes for it to go into effect and then connect your MySql client.

This only applies for RDS instances, if you are using MySql installed on an EC2 instance then the instructions are the same as accessing MySql from any remote machine.

如果您通过 RDS 实例在 AWS 上使用 MySql，则必须将要连接的 IP 地址添加到“数据库安全组”。为此，请转到您的 AWS 管理控制台并选择 RDS。
1. 在左侧面板上选择“DB Security Groups”
2. 选择“default”
3. 从选择框中选择“CIDR/IP”并输入您工作站的公共 IP 地址。示例：
23.234.192.123/32（不要忘记单个ip的/32）
4.单击“添加”
5.等待几分钟使其生效，然后连接您的MySql客户端。

这仅适用于 RDS 实例，如果您使用安装在 EC2 实例上的 MySql，则说明与从任何远程机器访问 MySql 相同。

回答by chrisco

I have been using MySQL Workbench http://www.mysql.com/products/workbench/with RDS and it works great. Very easy to create and save a new database service instance. Click "New Server Instance" under "Server Administration" and follow the prompts. You will need to enter the information provided in the AWS RDS webpage for that instance (for example, it's endpoint).

我一直在将 MySQL Workbench http://www.mysql.com/products/workbench/与 RDS 一起使用，并且效果很好。非常容易创建和保存新的数据库服务实例。单击“服务器管理”下的“新建服务器实例”并按照提示进行操作。您需要输入 AWS RDS 网页中为该实例提供的信息（例如，它的终端节点）。

NOTE: In order for you to actually connect, you MUST add your IP address in the "DB Security Groups." The link is in the left-hand column, which is titled "Navigation." I use the "CIDR/IP" option (the other is EC2 Security Group). Make sure to include a "/##" after the IP, such as the "/32" they use in the example (you will see it on the page). In a few seconds, the IP address should be authorized.

注意：为了让您真正连接，您必须在“数据库安全组”中添加您的 IP 地址。该链接位于标题为“导航”的左侧栏中。我使用“CIDR/IP”选项（另一个是 EC2 安全组）。确保在 IP 后包含“/##”，例如他们在示例中使用的“/32”（您将在页面上看到它）。几秒钟后，IP 地址应该被授权。

After that, go back to MySQL Workbench and complete the New Server Instance creation process.

之后，返回 MySQL Workbench 并完成 New Server Instance 创建过程。

To use the connection, your code might look something like this (that excerpts of my Java code):

要使用连接，您的代码可能如下所示（我的 Java 代码的摘录）：

String url = "jdbc:mysql://yourdatabasename.foo.us-east-1.rds.amazonaws.com:3306/";
String userName = "your_user_name";
String password = "your_password";
String dbName = "your_db_name";
String driver = "com.mysql.jdbc.Driver";
Connection connection = DriverManager.getConnection(url + dbName, userName, password);

回答by user3078359

I am on a Windows 7 machine, and had to make the following 3 changes to be able to connect to AWS RDB.

我在 Windows 7 机器上，必须进行以下 3 项更改才能连接到 AWS RDB。

1. VPC Security Group update in AWS Console (similar to what mikemay has above)

   • From https://console.aws.amazon.com, click on Services (top left) and choose VPC.
   • Next select Security Groups
   • Click on the Security Group which has the description "Security Group for RDS DB..."
   • On the "Inbound" tab, choose "MYSQL" in the Create a New Rule dropdown.
   • Add your IP address in CIDR format and click on Add Rule.
   • Click on Apply Rule Changes.

2. my.cnf update in local MySQL configuration

   • Change "bind-address = 127.0.0.1" to "bind-address = 0.0.0.0"
   • Comment out "skip-networking"

3. Turn OFF Windows Firewall

   • Go to Control Panel/System and Security/Windows Firewall and turn OFF Windows Firewall.

1. AWS 控制台中的 VPC 安全组更新（类似于上面的 mikemay）

   • 在https://console.aws.amazon.com 中，单击服务（左上角）并选择 VPC。
   • 接下来选择安全组
   • 单击描述为“RDS DB 的安全组...”的安全组
   • 在“入站”选项卡上，在“创建新规则”下拉列表中选择“MYSQL”。
   • 以 CIDR 格式添加您的 IP 地址，然后单击添加规则。
   • 单击应用规则更改。

2. 本地 MySQL 配置中的 my.cnf 更新

   • 将“绑定地址 = 127.0.0.1”更改为“绑定地址 = 0.0.0.0”
   • 注释掉“skip-networking”

3. 关闭 Windows 防火墙

   • 转到控制面板/系统和安全/Windows 防火墙并关闭 Windows 防火墙。

After these changes, I am able to connect through both

在这些更改之后，我可以通过两者进行连接

• MySQL WorkBench using Database->Connect to Database

• Command Prompt with

  mysql.exe -h <AWS DB Endpoint> -U <UserName> -P <Port Number, likely 3306> -p
  

• MySQL WorkBench 使用 Database->Connect to Database

• 命令提示符

  mysql.exe -h <AWS DB Endpoint> -U <UserName> -P <Port Number, likely 3306> -p
  

回答by vaquar khan

When we create RDS need to configured the firewall to accept mySQL connections from local or other instance, as such the packet is being dropped at the firewall level, to resolve this you need to:

当我们创建 RDS 时需要配置防火墙以接受来自本地或其他实例的 mySQL 连接，因此数据包在防火墙级别被丢弃，要解决此问题，您需要：

• Login into your AWS console Go to RDS

• 登录到您的 AWS 控制台转到 RDS

Note down the security group of your mySQL server (in my case awseb-e)

记下 mySQL 服务器的安全组（在我的例子中是 awseb-e）

• click security groups
• click your group in the center menu awseb
• click inbound tab

• 单击安全组
• 在中央菜单中单击您的组 awseb
• 单击入站选项卡

Select mySQL from the list, add the details of your client server and save the rule

从列表中选择 mySQL，添加客户端服务器的详细信息并保存规则

NOTE : once you choose my IP ,AWS select your IP , if you need other ip use

注意：一旦你选择了我的 IP，AWS 选择你的 IP，如果你需要其他 ip 使用

https://www.whatismyip.com/my-ip-information/

https://www.whatismyip.com/my-ip-information/

and add your IP4 IP

并添加您的 IP4 IP