Mysql 远程连接的问题 2003 - Can't connect to MySQL server on '39.106.187.107' (60 "Operation timed out")

向右看齐 2022-05-28 00:40 58阅读 0赞

一、在上一篇文章中，[使用ECS和mysql搭建mysql服务器][ECS_mysql_mysql]搭建好了mysql服务器，现在需要远程连接  
报错

2003 - Can't connect to MySQL server on '39.106.187.107' (60 "Operation timed out")

1、通过google在stackoverflow.com找到了问题[这里写链接内容][Link 1]

2、这个方案貌似可行来试一下  
![这里写图片描述][70]

3、说我没有安装nc

[root@iz2ze2llim71y07x3numlbz ~]# nc -l -p 3306
    -bash: nc: 未找到命令
    [root@iz2ze2llim71y07x3numlbz ~]# nc
    -bash: nc: 未找到命令

4、安装一个nc，使用yum安装方便快捷

[root@iz2ze2llim71y07x3numlbz ~]# yum install nc
    已加载插件：fastestmirror
    Loading mirror speeds from cached hostfile
    正在解决依赖关系
    --> 正在检查事务
    ---> 软件包 nmap-ncat.x86_64.2.6.40-7.el7 将被 安装 --> 解决依赖关系完成 依赖关系解决
    
    ================================================================================
     Package            架构            版本                    源             大小
    ================================================================================
    正在安装:
     nmap-ncat x86_64 2:6.40-7.el7 base 201 k
    
    事务概要 ================================================================================
    安装  1 软件包
    
    总下载量：201 k
    安装大小：414 k
    Is this ok [y/d/N]: y
    Downloading packages:
    nmap-ncat-6.40-7.el7.x86_64.rpm | 201 kB 00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction 正在安装 : 2:nmap-ncat-6.40-7.el7.x86_64                               1/1 
     验证中 : 2:nmap-ncat-6.40-7.el7.x86_64 1/1 
    
    已安装:
     nmap-ncat.x86_64 2:6.40-7.el7 
    
    完毕！

5、使用nc

[root@iz2ze2llim71y07x3numlbz ~]# nc -l -p 3306
    Ncat: bind to :::3306: Address already in use. QUITTING.
    [root@iz2ze2llim71y07x3numlbz ~]#

6、使用nc，访问ip+端口

[root@iz2ze2llim71y07x3numlbz ~]# nc 59.110.218.184 3306
    Ncat: Connection timed out.
    [root@iz2ze2llim71y07x3numlbz ~]#

If this is not working, this is not a mysql server configuration issue but a network issue, and you must check your router firewall rules.  
Otherwise, your problem comes from mysql server settings. Check your mysql configuration file for bind-address, and remove them to make mysqld accept clients from any IP address.

7、可能是防火墙的问题  
进入到/etc/sysconfig，看centos7没有iptables。

8、停止并屏蔽firewalld服务

[root@iz2ze2llim71y07x3numlbz sysconfig]# systemctl stop firewalld
    [root@iz2ze2llim71y07x3numlbz sysconfig]# systemctl mask firewalld
    Created symlink from /etc/systemd/system/firewalld.service to /dev/null.
    [root@iz2ze2llim71y07x3numlbz sysconfig]#

9、安装iptables-services软件包

[root@iz2ze2llim71y07x3numlbz sysconfig]# yum install iptables-services
    已加载插件：fastestmirror
    Loading mirror speeds from cached hostfile
    正在解决依赖关系
    --> 正在检查事务
    ---> 软件包 iptables-services.x86_64.0.1.4.21-18.3.el7_4 将被 安装
    --> 正在处理依赖关系 iptables = 1.4.21-18.3.el7_4，它被软件包 iptables-services-1.4.21-18.3.el7_4.x86_64 需要

10、在引导时启用iptables服务

[root@iz2ze2llim71y07x3numlbz sysconfig]# systemctl enable iptables
    Created symlink from /etc/systemd/system/basic.target.wants/iptables.service to /usr/lib/systemd/system/iptables.service.

11、启动iptables服务

[root@iz2ze2llim71y07x3numlbz ~]# systemctl start iptables
    [root@iz2ze2llim71y07x3numlbz ~]#

12、保存防火墙规则

[root@iz2ze2llim71y07x3numlbz ~]# service iptables save
    iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]
    [root@iz2ze2llim71y07x3numlbz ~]#

另外：管理iptables服务

systemctl [stop|start|restart] iptables

13、编辑防火墙规则，vi /etc/sysconfig/iptables

# Generated by iptables-save v1.4.21 on Mon Mar 26 17:50:59 2018
    *filter
    :INPUT ACCEPT [0:0]
    :FORWARD ACCEPT [0:0]
    :OUTPUT ACCEPT [19:3404]
    -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT
    # Completed on Mon Mar 26 17:50:59 2018

14、重启防火墙，systemctl restart iptables

[ECS_mysql_mysql]: https://blog.csdn.net/wd2014610/article/details/79700932
[Link 1]: https://stackoverflow.com/questions/29370829/remote-access-mysql-connection-error
[70]: /images/20220528/7b23acb0080649c9938af919e129982f.png