我系绳的结束，变化小时......

我要访问安装在EC2实例中运行MySQL。很多搜索（这似乎最接近我的问题Connect到MySQL在Amazon EC2上从远程服务器），而我显然缺少一些愚蠢的或错误的理解。

EC2的Ubuntu，mysqld在运行正常，本地Web /应用服务器工作正常，所有的连接。我的mysql本地。我需要外部访问这个开发机的使用iron.io这需要从外部到数据库中。

我

在EC2安全gruops所有配置的允许0.0.0.0 在我的ssh / HTTP访问该服务器没有问题 在配置的mysqld 绑定地址= 0.0.0.0（已尝试下面的变化） 在我的非root的用户％（为myuser）授予访问权限 例如。GRANT ALL ON的 应用于MYUSER @'％'IDENTIFIED BY'密码'; FLUSH PRIVILEGES; 在确保我的Ubuntu实例的iptables没有配置     例如我只使用EC2安全组来保护实例）

我已经试过

SSH到服务器 telnet到mysqld的3306端口上的公网IP​​，以确保它听 - 见下面的注释 在/etc/mysql/my.cnf 删除绑定地址 检查它是不是一个袜子V接口错误：总是通过端口连接（本地检查如MySQL的-h - 本地主机ü根-p --port = 3306） 绑定地址0.0.0.0 绑定地址的EC2本地IP例如172。 绑定地址EC2的外部IP例如51.X 在％ MySQL用户授予权限 在我个人的外部IP MySQL用户授予权限 创建另一个用户，并只授予priviledgs到特定的数据库 GRANT ALL权限的MYDB * TO'myuser2'@'％'IDENTIFIED BY'密码'; 重复％，我个人的外部IP 添加根@％的名单，并试图用户（在此基础上建议：Can't连接到MySQL实例远程正在对EC2实例（不RDS））运行

（总是重启后服务器）

没有什么工作，测试始终

$的MySQL主机= 54.xxx --port = 3306 --user = myuser的-p   输入密码：   ERROR 1045（28000）：拒绝访问用户myuser'@'54.xxx'（使用密码：是）

我已经但是注意到

选择*从information_schema.user_privileges;有myser与IS_GRANTABLE设置为N，但我不认为这是一个限制因素（的为什么GRANT不是工作在MySQL的？）

netstat -a可显示不同的端口和放大器;插座听了！？

在下面的netstat的监听端口是不是3306，但我已经从外部telneted到该端口，它显示了MySQL是听

任何人都可以建议我已经错过了任何一个步骤？

数据库priviledges提取物，下面的my.cnf 的

 ＃user_privileges
＃ 例如。 SELECT * FROM information_schema.user_privileges;
+ -------------------------------- + -------- + ------------------------- + -------------- +
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | Q1 |
+ -------------------------------- + -------- + ------------------------- + -------------- +
| '根'@'localhost'的|高清| SELECT | YES |
...
| myuser的'@'％'|高清| SELECT | NO |
| myuser的'@'％'|高清|插入| NO |
| myuser的'@'％'|高清| UPDATE | NO |


＃
] $ netstat -a可| grep的MySQL的
TCP 0 0 *：MYSQL *：* LISTEN
UNIX 2 [ACC] STREAM聆听61212 /var/run/mysqld/mysqld.sock


$远程登录54.x.x.x 3306
尝试54.x.x.x ...
连接到ec2-54-xxx.us-west-2.compute.amazonaws.com。
转义字符为^]。
[
5.5.44-0ubuntu0.14.04.1 + C＆LT;＆GT; D！^ + W 1 * E \ {WDP＆放大器; hZmysql_native_password


＃的my.cnf
[mysqld的]
＃
＃*基本设置
＃
用户= mysql的
PID文件= /var/run/mysqld/mysqld.pid
插座= /var/run/mysqld/mysqld.sock
端口= 3306
BASEDIR = / USR
DATADIR =的/ var / lib中/ MySQL的
TMPDIR = / tmp目录
LC-消息-DIR =的/ usr /共享/ MySQL的
＃跳外锁
＃
＃跳网络，而不是默认，现在只在听
＃本地主机哪个更相容并且安全性较低。
＃绑定地址=本地主机
＃donts似乎工作
绑定地址= 0.0.0.0
＃不工作
＃EC2的外部IP
＃绑定地址= 54.x.x.x
＃EC2内部IP
＃绑定地址= 172.x.x.x
 

解决方案

我发现了SQLyog的，（也许是MySQL的工作台也是如此），这实际上会告诉你从失败的连接进来的主机名的最好工具。

我喜欢它的原因是，它使安全严密，呈现出类似 拒绝访问用户fred123@hdm38.newyork.comcastbusiness.net。请注意，主机名，我只是做了，但重要的是，无论你正在进入，否则为通配符的时候，这是我不会做。您可以选择。

由于该主机名（ hdm38.newyork.comcastbusiness.net ）是一个MySQL的过程中，我使用创建用户，而不是这一点很重要使用通配符，如％

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

快速清单

1）远程用户连接通过与相应的用户创建一个帐户，主机项（从选择用户，主机看输出从mysql.user ORDER BY 1,2 ）

  CREATE USER'santa'@'hdm38.newyork.comcastbusiness.net'ID​​ENTIFIED BY'输入mypassword';
 

通过上面的命令，我们现在有一个有机会获得一个新用户。不能做任​​何事情。无法更改到一个数据库。基本上，他们可以做的事情一样选择现在（）;

2）已执​​行资助与 flush privileges的（至少前）

  GRANT ALL权限的mydb123 * TO'santa'@'hdm38.newyork.comcastbusiness.net。
 

我们的现在与上面的命令，用户可以做的mydb123数据库/模式什么。探索格兰特以上命令手动微调进入到最低限度，为您创建的用户

如果你是新的MySQL的安全性，不包括 WITH GRANT OPTION ，直到你研究它。

有那些会说上面有该主机名是'％'的第一步和第二步这意味着圣诞老人可以从任何主机进行连接。这是你的选择。开始紧张，放松，一旦你要去哪里和做研究的就可以了。

3）你已经修改的my.cnf 并变更绑定地址远离 127.0.0.1 或本地主机，有利于 0.0.0.0

如果绑定地址是不是 0.0.0.0 ，你只连接使用ssh

4）你已经修改的my.cnf 并有rem'd脱节＃跳过联网。即使你必须创建行只是REM出来，做到这一点。

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

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?)

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