SQLyog客户端无法连接MySQL服务器案例分析

 22:20:43 up  2:39,  1 user,  load average: 0.00, 0.01, 0.05

USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT

root     pts/1    192.168.1.7      21:35    3.00s  0.07s  0.01s w

 

--创建客户端用户

root@localhost 22:23:15[(none)]> create user 'zlm'@'192.168.1.7' identified by 'zlm';   

Query OK, 0 rows affected (0.00 sec) 

 

--用新创建的用户通过SQLyog客户端连接服务器

SQLyog客户端无法连接MySQL服务器案例分析


SQLyog客户端无法连接MySQL服务器案例分析

 

提示无法连接,"Can't connect to MySQL server"

 

--创建服务器上的本地账户

root@localhost 22:34:26[(none)]> create user 'zlm'@'192.168.1.11' identified by 'zlm';

Query OK, 0 rows affected (0.00 sec)

 

root@localhost 22:34:29[(none)]> exit

Bye

 

--测试是否可以连接

[root@mysql ~]# mysql --protocol=tcp -P 3306 -h192.168.1.11 -uzlm -pzlm

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 19

Server version: 5.5.39-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

zlm@192.168.1.11 22:44:52[(none)]> exit

Bye

 

[root@mysql ~]# netstat -nalp | grep "3306" 

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3645/mysqld        

tcp        0      0 192.168.1.11:3306           192.168.1.7:59783           ESTABLISHED 3645/mysqld        

tcp        0      0 192.168.1.11:3306           192.168.1.7:59779           ESTABLISHED 3645/mysqld        

[root@mysql ~]# netstat -nalp|grep "3306"|awk '{print $5}'|awk -F: '{print $1}'|sort |uniq -c|sort -nr

      2 192.168.1.7
      1 0.0.0.0

[root@mysql ~]# 

 

本地用户可以用3306端口连接,说明网络没有问题,3306端口也开启着,其实问题还是出在iptables

刚才用chkconfig iptables off来关闭各终端的iptables需要重启后才生效,此时并未重启过

 

[root@mysql ~]# chkconfig iptables --list

iptables        0:off   1:off   2:on    3:on    4:on    5:on    6:off

[root@mysql ~]# chkconfig iptables off

[root@mysql ~]# chkconfig iptables --list

iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off

SQLyog客户端无法连接MySQL服务器案例分析

 

--不重启直接关闭iptables服务

[root@mysql ~]# /etc/init.d/iptables stop

iptables: Setting chains to policy ACCEPT: filter          [  OK  ]

iptables: Flushing firewall rules:                         [  OK  ]

iptables: Unloading modules:                               [  OK  ]

[root@mysql ~]# /etc/init.d/iptables status

iptables: Firewall is not running.

 

SQLyog客户端无法连接MySQL服务器案例分析

 

--关闭iptables后,再次连接成功

SQLyog客户端无法连接MySQL服务器案例分析

SQLyog客户端无法连接MySQL服务器案例分析

 

SQLyog客户端无法连接MySQL服务器案例分析


 

如果不想关iptables也可以,把-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT添加到/etc/sysconfig/iptables即可

 

--在iptables中添加允许规则(注意不是添加在最后)

[root@mysql ~]# vim /etc/sysconfig/iptables 

# Firewall configuration written by system-config-firewall

# Manual customization of this file is not recommended.

*filter

:INPUT ACCEPT [0:0]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [0:0]

-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

-A INPUT -p icmp -j ACCEPT

-A INPUT -i lo -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT --表示允许3306端口通过防火墙

-A INPUT -j REJECT --reject-with icmp-host-prohibited

-A FORWARD -j REJECT --reject-with icmp-host-prohibited

COMMIT

 

--改完后重启iptables

[root@mysql ~]# /etc/init.d/iptables restart

iptables: Setting chains to policy ACCEPT: filter          [  OK  ]

iptables: Flushing firewall rules:                         [  OK  ]

iptables: Unloading modules:                               [  OK  ]

iptables: Applying firewall rules:                         [  OK  ] 

SQLyog客户端无法连接MySQL服务器案例分析


这次再通过SQLyog客户端连接MySQL服务器,依然成功连接!可见,之前无法连接的问题就是因为3306被防火墙给阻挡了。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/ca7c3f7ecfbde9ddca3d4b5f3b5ad777.html