我正在尝试设置 mysql，以便用户“imbnpandmkexby”可以从任何远程 IP 地址或本地连接到数据库“de0rllo43ct314”。

=========== 这些是我采取的步骤:===========

1) 在我的 MySQL 配置中，我注释掉了 bind-address 行，确认 skip-networking 不在文件中，然后重新启动 mysql:

#/etc/mysql/my.cnf:
#bind-address    = 127.0.0.1

2) 我在所需数据库“de0rllo43ct314”上为用户“imbnpandmkexby”添加了远程权限(通过使用“%”):

[ remote ] > mysql -u root -p
[ mysql  ] > GRANT ALL PRIVILEGES ON de0rllo43ct314.* TO 'imbnpandmkexby'@'localhost' IDENTIFIED BY 'passwordhere' WITH GRANT OPTION;
[ mysql  ] > GRANT ALL PRIVILEGES ON de0rllo43ct314.* TO 'imbnpandmkexby'@'127.0.0.1' IDENTIFIED BY 'passwordhere' WITH GRANT OPTION;
[ mysql  ] > GRANT ALL PRIVILEGES ON de0rllo43ct314.* TO 'imbnpandmkexby'@'%' IDENTIFIED BY 'passwordhere' WITH GRANT OPTION;
[ mysql  ] > FLUSH PRIVILEGES;
[ mysql  ] > select * from mysql.user\G

这个输出:

*************************** 6. row ***************************
                  Host: localhost
                  User: imbnpandmkexby
              Password: *0000000000000000000000
...
*************************** 7. row ***************************
                  Host: 127.0.0.1
                  User: imbnpandmkexby
              Password: 
...
*************************** 8. row ***************************
                  Host: %
                  User: imbnpandmkexby
              Password: 
...

3) 此时我可以使用 Sequel Pro 连接 SSH 隧道。用户似乎拥有所有正确的权限。

4) 接下来我打开一个防火墙端口并验证 MySQL 正在监听该端口:

[ remote ] > sudo iptables -I INPUT 10 -p tcp --dport 3306 -j ACCEPT
[ remote ] > sudo iptables -L

Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             state     RELATED,ESTABLISHED
ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp     dpt:4505
ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp     dpt:4506
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:http
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:https
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:http-alt
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:zabbix-agent
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:zabbix-trapper
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp dpt:ssh
ACCEPT     icmp --  anywhere             anywhere             icmp echo-request
LOG        all  --  anywhere             anywhere             limit: avg 5/min burst 5 LOG level debug prefix "iptables denied: "
REJECT     all  --  anywhere             anywhere             reject-with icmp-port-unreachable

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
REJECT     all  --  anywhere             anywhere             reject-with icmp-port-unreachable

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     all  --  anywhere             anywhere    

由于这个盒子是托管在amazon ec2上的，所以我也在它的安全组里开放了3306端口:

5) 我可以远程登录到端口:

Trying 00.00.00.000...
Connected to ec2-00.00.00.000.us-west-2.compute.amazonaws.com.
Escape character is '^]'.

=========== 这就是我被卡住的地方:===========

显示的是 00.00.00.000 而不是实际 IP

当我尝试从我的本地机器连接到数据库时，它不起作用:

[ local ] > mysql -u imbnpandmkexby -h 00.00.00.000 -p
[ local ] > Enter password: 
[ local ] > ERROR 2003 (HY000): Can't connect to MySQL server on '00.00.00.000' (61)

我能够连接到 dreamhost 服务器上的数据库，所以它似乎不是我这边的障碍:

[ local ] > mysql -u dreamhost_user -h mysql.dreamhostdomain.com -p
[ local ] > Enter password: 
[ local ] > Welcome to the MySQL monitor.  Commands end with ; or \g.

我是否缺少一层权限？

最佳答案

好吧，终于明白了!我遇到了两个问题的组合:

1) 我的 SQL 规则是在 iptables 中的 REJECT 规则之后出现的:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             state RELATED,ESTABLISHED
ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp dpt:4505
ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp dpt:4506
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:http
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:https
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:http-alt
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:zabbix-agent
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:zabbix-trapper
ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp dpt:ssh
ACCEPT     icmp --  anywhere             anywhere             icmp echo-request
LOG        all  --  anywhere             anywhere             limit: avg 5/min burst 5 LOG level debug prefix "iptables denied: "
REJECT     all  --  anywhere             anywhere             reject-with icmp-port-unreachable
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:mysql

我所做的是删除最后一条规则，并在索引 10 处重新添加它:

[ remote ] > iptables -vnL --line-numbers ##Prints rules along with line numbers
[ remote ] > iptables -D INPUT 14
[ remote ] > sudo iptables -I INPUT 10 -p tcp --dport 3306 -j ACCEPT

我知道这是朝着正确方向迈出的一步，因为我现在可以通过 telnet(“telnet 00.00.00.000 3306”)连接到盒子

2) 我遇到的第二个问题是我的 MySQL 用户只在“localhost”用户上设置了密码，而不是有权访问“127.0.0.1”或“%”的用户。事实证明，每个用户-主机组合都需要一个密码。现在，当我在 MySQL 控制台中运行“select * from mysql.user\G”时，我得到:

*************************** 6. row ***************************
                  Host: localhost
                  User: imbnpandmkexby
              Password: *0000000000000000000000
...
*************************** 7. row ***************************
                  Host: 127.0.0.1
                  User: imbnpandmkexby
              Password: *0000000000000000000000
...
*************************** 8. row ***************************
                  Host: %
                  User: imbnpandmkexby
              Password: *0000000000000000000000
...

关于mysql - 无法连接到 AWS 上的远程 MySQL 服务器，但 ssh 隧道有效，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/28650062/