无法远程连接到 EC2 MySQL 安装

2021-12-21 00:00:00 amazon-ec2 mysql

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

我正在尝试访问在 EC2 实例上运行的已安装 MySQL.大量搜索(这似乎最接近我的问题 Connect tomysql 在 Amazon EC2 上从远程服务器)，我显然错过了一些愚蠢或误解的东西.

EC2 Ubuntu，mysqld 运行良好，本地网络/应用程序服务器运行良好，所有连接.我可以从本地 mysql.我需要从外部访问这台开发机器，因为使用 Iron.io 需要从外部访问数据库.

我有

  • ec2 安全组所有配置允许 0.0.0.0
  • 我可以通过 ssh/http 访问服务器，没问题
  • 配置的mysqld
    • 绑定地址=0.0.0.0(已尝试以下变体)
    • 从 % 授予我的非 root 用户 (myuser) 访问权限例如GRANT ALL ON . to myuser@'%' IDENTIFIED BY 'password';刷新特权；
  • 确保未配置我的 ubuntu 实例 iptables例如我只使用 ec2 安全组来保护实例)

我试过了

  • ssh 进入服务器
  • telnet 到 mysqld 端口 3306 上的公共 ip 以确保其侦听 - 请参阅下面的注释
  • 删除/etc/mysql/my.cnf中的绑定地址
  • 检查它不是 sock v 端口错误:始终通过端口连接(本地检查，例如 mysql -h - localhost -u root -p --port=3306)
  • 绑定地址 0.0.0.0
  • 绑定地址 EC2 本地 IP，例如172.
  • 绑定地址 EC2 外部 IP，例如51.x
  • mysql 用户授予 % 权限
  • mysql 用户授予我个人外部 ip 的权限
  • 创建另一个用户，并且只授予特定数据库的权限将 mydb.* 上的所有权限授予 'myuser2'@'%' IDENTIFIED BY 'password';
  • 重复 %，我的个人外部 ip
  • 将 root@'%' 添加到列表中并尝试使用该用户(基于此建议:无法远程连接到运行在 EC2 实例(非 RDS)上的 MySQL 实例)

(之后总是重启服务器)

没有任何效果，总是测试

$ mysql --host=54.x.x.x --port=3306 --user=myuser -p输入密码:错误 1045 (28000): 用户 'myuser'@'54.x.x.x' 的访问被拒绝(使用密码:YES)

我注意到了

  1. SELECT * from information_schema.user_privileges;将 IS_GRANTABLE 设置为 N 的 myser:但我不认为这是一个限制因素(为什么 GRANT 在 MySQL 中不起作用?)

  2. netstat -a 显示不同的端口 &一个套接字监听！?

    下面 netstat 中的侦听端口不是 3306，但我已经从外部 telnet 到该端口，它显示 mysql 正在侦听

谁能建议我遗漏的任何步骤?

数据库权限摘录，my.cnf如下

# 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

解决方案

我发现的最好的工具是 sqlyog，(也许还有 mysql workbench)，它实际上会在连接失败时告诉您来自的主机名.

我喜欢它的原因是它保持安全，显示出类似用户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

相关文章