从远程服务器连接到 Amazon EC2 上的 mysql

Connect to mysql on Amazon EC2 from a remote server

提问by Aashish Katta

I want to connect to db on EC2 from my local machine, I am not able to do and have tried everything- I am using this command to connect to EC2:

我想从我的本地机器连接到 EC2 上的数据库，我无法做到并且已经尝试了一切 - 我正在使用此命令连接到 EC2：

mysql -uUSERNAME -hEC2_IP -pPASSWORD

This error is generated

产生这个错误

ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (110)

错误 2003 (HY000)：无法连接到“IP”上的 MySQL 服务器 (110)

I have modified my.cnf with

我已经修改了 my.cnf

skip networking
bind-address            = 0.0.0.0

Still not able to connect to the database

仍然无法连接到数据库

采纳答案by Sandip Dhummad

There could be one of the following reasons:

可能有以下原因之一：

1. You need make an entry in the Amazon Security Group to allow remote access from your machine to Amazon EC2 instance. :- I believe this is done by you as from your question it seems like you already made an entry with 0.0.0.0, which allows everybody to access the machine.
2. MySQL not allowing user to connect from remote machine:- By default MySql creates root user id with admin access. But root id's access is limited to localhost only. This means that root user id with correct password will not work if you try to access MySql from a remote machine. To solve this problem, you need to allow either the root user or some other DB user to access MySQL from remote machine. I would not recommend allowing root user id accessing DB from remote machine. You can use wildcard character % to specify any remote machine.
3. Check if machine's local firewall is not enabled. And if its enabled then make sure that port 3306 is open.

1. 您需要在 Amazon 安全组中创建一个条目以允许从您的机器远程访问 Amazon EC2 实例。:- 我相信这是由您完成的，因为从您的问题来看，您似乎已经使用 0.0.0.0 进行了输入，这允许每个人都可以访问该机器。
2. MySQL 不允许用户从远程机器连接：- 默认情况下，MySql 创建具有管理员访问权限的 root 用户 ID。但是 root id 的访问权限仅限于 localhost。这意味着如果您尝试从远程计算机访问 MySql，则具有正确密码的 root 用户 ID 将不起作用。要解决这个问题，您需要允许 root 用户或其他一些数据库用户从远程机器访问 MySQL。我不建议允许 root 用户 ID 从远程机器访问数据库。您可以使用通配符 % 来指定任何远程机器。
3. 检查机器的本地防火墙是否未启用。如果已启用，请确保端口 3306 已打开。

Please go through following link: How Do I Enable Remote Access To MySQL Database Server?

请通过以下链接：如何启用对 MySQL 数据库服务器的远程访问？

回答by mohamed elbou

as mentioned in the responses above, it could be related to AWS security groups, and other things. but if you created a user and gave it remote access '%' and still getting this error, check your mysql config file, on debian, you can find it here: /etc/mysql/my.cnf and find the line:

正如上面的回复中提到的，它可能与 AWS 安全组和其他事情有关。但是，如果您创建了一个用户并为其提供远程访问“%”，但仍然出现此错误，请检查您的 mysql 配置文件，在 debian 上，您可以在此处找到它：/etc/mysql/my.cnf 并找到以下行：

bind-address            = 127.0.0.1

and change it to:

并将其更改为：

bind-address            = 0.0.0.0

and restart mysql.

并重新启动mysql。

on debian/ubuntu:

在 debian/ubuntu 上：

/etc/init.d/mysql restart

I hope this works for you.

我希望这对你有用。

回答by JerryGoyal

Update:Feb 2017

更新：2017 年 2 月

Here are the COMPLETE STEPSfor remote access of MySQL (deployed on Amazon EC2):-

以下是MySQL 远程访问的完整步骤（部署在 Amazon EC2 上）：-

1. Add MySQL to inbound rules.

1. 将 MySQL 添加到入站规则中。

Go to security group of your ec2 instance -> edit inbound rules -> add new rule -> choose MySQL/Auroraand source to Anywhere.

转到您的 ec2 实例的安全组 -> 编辑入站规则 -> 添加新规则 -> 选择MySQL/Aurora和来源到Anywhere.

2. Add bind-address = 0.0.0.0 to my.cnf

2.在my.cnf中添加bind-address = 0.0.0.0

In instance console:

在实例控制台中：

sudo vi /etc/mysql/my.cnf

this will open vi editor.
in my.cnf file, after [mysqld]add new line and write this:

这将打开 vi 编辑器。
在 my.cnf 文件中，[mysqld]添加新行并写入后：

bind-address = 0.0.0.0

Save file by entering :wq(enter)

通过输入:wq(enter)保存文件

now restart MySQL:

现在重启 MySQL：

sudo /etc/init.d/mysqld restart

3. Create a remote user and grant privileges.

3. 创建远程用户并授予权限。

login to MySQL:

登录 MySQL：

mysql -u root -p mysql(enter password after this)

mysql -u root -p mysql（在此之后输入密码）

Now write following commands:

现在编写以下命令：

CREATE USER 'jerry'@'localhost' IDENTIFIED BY 'jerrypassword';

CREATE USER 'jerry'@'%' IDENTIFIED BY 'jerrypassword';

GRANT ALL PRIVILEGES ON *.* to jerry@localhost IDENTIFIED BY 'jerrypassword' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* to jerry@'%' IDENTIFIED BY 'jerrypassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

EXIT;

After this, MySQL dB can be remotely accessed by entering public dns/ip of your instance as MySQL Host Address, username as jerry and password as jerrypassword. (Port is set to default at 3306)

之后，可以通过输入您实例的公共 dns/ip 作为 MySQL 主机地址，用户名作为 jerry 和密码作为 jerrypassword 远程访问 MySQL dB。（端口设置为默认为 3306）

回答by Tom Mac

It could be that you have not configured the Amazon Security Groupassigned to your EC2 Instance to accept incoming requests on port 3306 (default port for MySQL).

可能是您尚未将分配给 EC2 实例的Amazon 安全组配置为接受端口 3306（MySQL 的默认端口）上的传入请求。

If this is the case then you can easily open up the port for the security group in a few button clicks:

如果是这种情况，那么您只需单击几下按钮即可轻松打开安全组的端口：

1) Log into you AWS Console and go to 'EC2'

1) 登录您的 AWS 控制台并转到“EC2”

2) On the left hand menu under 'Network & Security' go to 'Security Groups'

2) 在“网络和安全”下的左侧菜单中，转到“安全组”

3) Check the Security Group in question

3）检查有问题的安全组

4) Click on 'Inbound tab'

4) 点击“入站标签”

5) Choose 'MYSQL' from drop down list and click 'Add Rule'

5) 从下拉列表中选择“MYSQL”，然后单击“添加规则”

Might not be the reason but worth a go...

可能不是原因，但值得一试......

回答by Bob Diego

A helpful step in tracking down this problem is to identify which bind-address MySQL is actually set to. You can do this with netstat:

跟踪此问题的一个有用步骤是确定 MySQL 实际设置的绑定地址。您可以使用 netstat 执行此操作：

netstat -nat |grep :3306

This helped me zero in on my problem, because there are multiple mysql config files, and I had edited the wrong one. Netstat showed mysql was still using the wrong config:

这帮助我解决了我的问题，因为有多个 mysql 配置文件，而我编辑了错误的一个。Netstat 显示 mysql 仍在使用错误的配置：

ubuntu@myhost:~$  netstat -nat |grep :3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

So I grepped the config directories for any other files which might be overriding my setting and found:

因此，我为可能覆盖我的设置的任何其他文件查找了配置目录，发现：

ubuntu@myhost:~$  sudo grep -R bind /etc/mysql
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address         = 127.0.0.1
/etc/mysql/mysql.cnf:bind-address = 0.0.0.0
/etc/mysql/my.cnf:bind-address = 0.0.0.0

D'oh! This showed me the setting I had adjusted was the wrong config file, so I edited the RIGHT file this time, confirmed it with netstat, and was in business.

哦！这告诉我我调整的设置是错误的配置文件，所以我这次编辑了正确的文件，用netstat确认，并开始营业。

回答by KC Kern

For some configurations of ubuntu, the bind-address needs be changed in this file:

对于ubuntu的一些配置，需要修改这个文件中的bind-address：

/etc/mysql/mysql.conf.d/mysqld.cnf 

回答by New Alexandria

Though this question seems to be answered, another common issue that you can get is the DB user has been mis-configured. This is a mysql administration and permissions issue:

虽然这个问题似乎得到了回答，但您可能会遇到的另一个常见问题是数据库用户配置错误。这是一个 mysql 管理和权限问题：

1. EC2_DB launched with IP 10.55.142.100
2. EC2_web launched with IP 10.55.142.144
3. EC2_DB and EC2_WEBare in the same security group with access across your DB port (3306)
4. EC2_DB has a mysql DB that can be reached locally by the DB root user ('root'@'localhost')
5. EC2_DB mysql DB has a remote user 'my_user'@'%' IDENTIFIED BY PASSWORD 'password'
6. A bash call to mysql from EC2_WEB fails: mysql -umy_user -p -h ip-10-55-142-100.ec2.internalas does host references to the explicit IP, public DNS, etc.

1. 使用 IP 启动的 EC2_DB 10.55.142.100
2. EC2_web 使用 IP 启动 10.55.142.144
3. EC2_DB 和 EC2_WEB 位于同一个安全组中，可以跨数据库端口访问 (3306)
4. EC2_DB 有一个 mysql 数据库，可以由 DB 根用户 ( 'root'@'localhost')在本地访问
5. EC2_DB mysql DB 有远程用户 'my_user'@'%' IDENTIFIED BY PASSWORD 'password'
6. 从 EC2_WEB 对 mysql 的 bash 调用失败：mysql -umy_user -p -h ip-10-55-142-100.ec2.internal主机对显式 IP、公共 DNS 等的引用也是如此。

---

---

Step 6 fails because the mysql DB has the wrong user permisions. It needs this:

步骤 6 失败，因为 mysql 数据库具有错误的用户权限。它需要这个：

GRANT ALL PRIVILEGES ON *.* TO 'my_user'@'ip-10-55-142-144.ec2.internal' IDENTIFIED BY PASSWORD 'password'

I would like to think that %would work for any remote server, but I did not find this to be the case.

我认为这%适用于任何远程服务器，但我发现情况并非如此。

Please let me know if this helps you.

如果这对您有帮助，请告诉我。

回答by Edwin Wachs

Change /etc/mysql/my.cnf:

改变/etc/mysql/my.cnf：

bind-address            = 127.0.0.1 to 0.0.0.0

Create user with '%' hostand restart:

使用 '%' 创建用户host并重新启动：

/etc/init.d/mysql restart

Works for me! :)

为我工作！:)

回答by RC_02

Log into AWS Management Console. Navigate to RDS then select the db instance and go to "security Groups". Adding CIDR/IP under the security group fixed the problem.

登录 AWS 管理控制台。导航到 RDS，然后选择数据库实例并转到“安全组”。在安全组下添加 CIDR/IP 解决了问题。

回答by dhalsumit

Solution to this is three steps:

解决这个问题分三步：

1. Within MySQL my.ini/ my.cnf file change the bind-address to accept connection from all hosts (0.0.0.0).

2. Within aws console - ec2 - specific security group open your mysql port (default is 3306) to accept connections from all hosts (0.0.0.0).

3. Within windows firewall add the mysql port (default is 3306) to exceptions.

1. 在 MySQL my.ini/my.cnf 文件中，更改绑定地址以接受来自所有主机 (0.0.0.0) 的连接。

2. 在 aws 控制台 - ec2 - 特定安全组中打开您的 mysql 端口（默认为 3306）以接受来自所有主机（0.0.0.0）的连接。

3. 在 Windows 防火墙中，将 mysql 端口（默认为 3306）添加到例外中。

And this will start accepting remote connections.

这将开始接受远程连接。