无法远程连接到 EC2 MySQL 安装

# user_privileges# 例如SELECT * from information_schema.user_privileges;+--------------------------------+---------------+-------------------------+--------------+|受赠人 |表_目录 |特权_类型 |Q1 |+--------------------------------+---------------+-------------------------+--------------+|'root'@'localhost' |定义 |选择 |是 |...|'我的用户'@'%' |定义 |选择 |否 ||'我的用户'@'%' |定义 |插入 |否 ||'我的用户'@'%' |定义 |更新 |否 |#]$ netstat -a |grep 'mysql'tcp 0 0 *:mysql *:* 听unix 2 [ACC] 流听 61212/var/run/mysqld/mysqld.sock$ telnet 54.x.x.x 3306正在尝试 54.x.x.x...连接到 ec2-54-x-x-x.us-west-2.compute.amazonaws.com.转义字符是^]".[5.5.44-0ubuntu0.14.04.1+c<>d^+W?1*!e{wdp&hZmysql_native_password#我的.cnf[mysqld]## * 基本设置#用户 = mysqlpid-file =/var/run/mysqld/mysqld.pidsocket =/var/run/mysqld/mysqld.sock端口 = 3306基于目录 =/usr数据目录 =/var/lib/mysqltmpdir =/tmplc-messages-dir =/usr/share/mysql#skip-external-locking## 而不是跳过网络默认是现在只监听# localhost 更兼容且不那么安全.#绑定地址=本地主机# 似乎没有用绑定地址 = 0.0.0.0# 不起作用# ec2 外部 ip#绑定地址= 54.x.x.x#ec2内部ip#绑定地址= 172.x.x.x

我喜欢它的原因是它保持安全，显示出类似用户fred123@hdm38.newyork.comcastbusiness.net"的访问被拒绝.请注意，该主机名是我刚刚编造的，但它很重要，无论您输入的是什么.否则是通配符时间，我不会这样做.你可以选择.

这很重要，因为该主机名 (hdm38.newyork.comcastbusiness.net) 是我在 mysql create user 期间使用的主机名，而不是使用 % 之类的通配符

我让上面的连接失败，但我注意到上面的主机名是什么.你会在下面看到它.

快速检查清单

1) 您的远程用户正在通过使用适当的 user,host 条目创建的帐户进行连接(查看 select user,host from mysql.user order by 1,2)

CREATE USER 'santa'@'hdm38.newyork.comcastbusiness.net' IDENTIFIED BY 'mypassword';

使用上面的命令，我们现在有了一个有机会进入的新用户.什么也做不了.无法更改为 db.基本上他们可以做一些事情，比如 select now();

2) 您已经使用 flush 权限(至少是前者)执行了 grants

将 mydb123.* 上的所有权限授予 'santa'@'hdm38.newyork.comcastbusiness.net';

我们使用上述命令的用户现在可以在 mydb123 数据库/模式中执行任何操作.探索手册中的上述 Grant 命令，以微调您创建的用户对最低限度的访问权限.

如果您不熟悉 mysql 安全，请在研究之前不要包含 WITH GRANT OPTION.

有些人会说上面的主机名是第 1 步和第 2 步的%".这意味着圣诞老人可以从任何主机连接.这是你的选择.开始紧绷，到达某个地方并对其进行研究后放松.

3) 您已经修改了 my.cnf 并将 bind-address 从 127.0.0.1 或 localhost 更改为>，赞成 0.0.0.0

如果 bind-address 不是 0.0.0.0，你只是在用 ssh 连接

4) 你已经修改了 my.cnf 并且有一个 rem'd out 行 #skip-networking.即使您必须创建该行只是为了将其删除，也可以这样做.

3/4 更改需要重启 mysql 守护进程

5) 防火墙问题.因此，对于 EC2，您需要您的 AWS 安全组 对实例处于活动状态以包括开放端口 3306

End of my tether, hours of variations...

I am trying to access installed MySQL running on an EC2 instances. Lots of searching (this seemed closest to my issues Connect to mysql on Amazon EC2 from a remote server), and I'm obviously missing something silly or mis-understanding.

EC2 Ubuntu, mysqld running fine, local web/app server working fine all connecting. I can mysql from local. I need external access to this dev machine as using iron.io which needs to reach the database from external.

I have

• ec2 security gruops all configured allowing 0.0.0.0
• I can ssh/http reach the server no problem
• configured mysqld
  • bind address=0.0.0.0 (have tried variations below)
  • granted access for my non-root user (myuser) from % e.g. GRANT ALL ON . to myuser@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
• ensured my ubuntu instance iptables are not configured e.g. i'm only using ec2 security group to protect the instance)

I have tried

• ssh into the server
• telnet to the public ip on the mysqld port 3306 to make sure its listening - see notes below
• remove bind address in /etc/mysql/my.cnf
• checking it is not a sock v port error: always connecting via port (locally checked e.g. mysql -h - localhost -u root -p --port=3306)
• bind address 0.0.0.0
• bind address EC2 local ip e.g. 172.
• bind address EC2 external ip e.g. 51.x
• mysql user grant privileges on %
• mysql user grant privileges on my personal external ip
• create another user, and only grant priviledgs to a specific database GRANT ALL PRIVILEGES ON mydb.* TO 'myuser2'@'%' IDENTIFIED BY 'password';
• repeated for %, my personal external ip
• adding root@'%' to the list and tried that user (based on this suggestion: Can't Connect to MySQL instance Remotely that is running on EC2 Instance (Not RDS))

(Always restarting server after)

Nothing works, the test always

$ mysql --host=54.x.x.x --port=3306 --user=myuser -p Enter password: ERROR 1045 (28000): Access denied for user 'myuser'@'54.x.x.x' (using password: YES)

I have noticed however that

1. SELECT * from information_schema.user_privileges; has myser with IS_GRANTABLE set to N: but I don't think this is a limiting factor (Why is GRANT not working in MySQL?)

2. netstat -a shows a different port & a socket listening!?

   The listening port in the netstat below is not 3306 BUT i've telneted externally to that port and it shows mysql IS listening

Can anyone suggest any step I've missed?

Extracts of db priviledges, my.cnf below

# user_privileges
# e.g. SELECT * from information_schema.user_privileges;
+--------------------------------+---------------+-------------------------+--------------+
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE          |  q1 |
+--------------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'             | def           | SELECT                  | YES          |
...
| 'myuser'@'%'                 | def           | SELECT                  | NO           |
| 'myuser'@'%'                 | def           | INSERT                  | NO           |
| 'myuser'@'%'                 | def           | UPDATE                  | NO           |


# 
]$ netstat -a | grep 'mysql'
tcp        0      0 *:mysql                 *:*                     LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     61212    /var/run/mysqld/mysqld.sock


$ telnet 54.x.x.x 3306
Trying 54.x.x.x...
Connected to ec2-54-x-x-x.us-west-2.compute.amazonaws.com.
Escape character is '^]'.
[
5.5.44-0ubuntu0.14.04.1+c<>d^+W?1*!e{wdp&hZmysql_native_password


# my.cnf
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = localhost
# donts seem to work
bind-address            = 0.0.0.0
# does not work
# ec2 external ip
#bind-address            = 54.x.x.x
# ec2 internal ip
#bind-address            = 172.x.x.x

the best tool i found was sqlyog, (perhaps mysql workbench too), which would actually tell the hostname you are coming in from on the failed connect.

The reason I like it is that it keeps security tight, showing something like Access denied for user "fred123@hdm38.newyork.comcastbusiness.net". Note, that hostname, I just made up, but it is important, whatever yours is coming in. Otherwise it is wildcard time, something I won't do. You might choose to.

that is important because that hostname (hdm38.newyork.comcastbusiness.net) is the one i use during mysql create user as opposed to using wildcards like %

I let that connect above fail, but I note what the hostname is above. You will see it below.

A quick checklist

1) your remote user is connecting through an account that was created with appropriate user,host entries (look at output from select user,host from mysql.user order by 1,2)

CREATE USER 'santa'@'hdm38.newyork.comcastbusiness.net' IDENTIFIED BY 'mypassword';

With that above command we now have a new user that has a chance to get in. Cannot do anything. Cannot change to a db. Basically they can just do things like select now();

2) you have performed grants with flush privileges (at least the former)

GRANT ALL PRIVILEGES ON mydb123.* TO 'santa'@'hdm38.newyork.comcastbusiness.net';

Our user with the above command now can do anything in the mydb123 database/schema. Explore that above Grant command in the manual for fine-tuning access to the bare minimum for the users you create.

If you are new to mysql security, do not include the WITH GRANT OPTION until you research it.

There are those that would say have that hostname above to be '%' for steps 1 and 2. That means santa can connect from any host. The choice is yours. Start tight, loosen up once you are getting somewhere and have done the research on it.

3) you have modified my.cnf and changed bind-address away from 127.0.0.1 or localhost, in favor of 0.0.0.0

If bind-address is not 0.0.0.0, you are only connecting with ssh

4) you have modified my.cnf and have a rem'd out line #skip-networking. Even if you have to create the line just to rem it out, do it.

3/4 changes require mysql daemon restart

5) firewall issues. So for EC2, you need your AWS Security Group that is active for the instance to include the opening up of port 3306

这篇关于无法远程连接到 EC2 MySQL 安装的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！