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

--查看客户端ip[root@mysql ~]# w 22:20:43 up 2:39, 1 user, load average: 0.00, 0.01, 0.05USER TTY FROM LOGIN@ IDLE JCPU PCPU WHATroot 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客户端连接服务器
提示无法连接，"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）]> exitBye --测试是否可以连接[root@mysql ~]# mysql --protocol=tcp -P 3306 -h192.168.1.11 -uzlm -pzlmWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 19Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners. Type "help;" or "h" for help. Type "c" to clear the current input statement. zlm@192.168.1.11 22:44:52[（none）]> exitBye [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 --listiptables 0:off 1:off 2:on 3:on 4:on 5:on 6:off[root@mysql ~]# chkconfig iptables off[root@mysql ~]# chkconfig iptables --listiptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off --不重启直接关闭iptables服务[root@mysql ~]# /etc/init.d/iptables stopiptables: Setting chains to policy ACCEPT: filter [ OK ]iptables: Flushing firewall rules: [ OK ]iptables: Unloading modules: [ OK ][root@mysql ~]# /etc/init.d/iptables statusiptables: Firewall is not running. --关闭iptables后，再次连接成功
如果不想关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-prohibitedCOMMIT --改完后重启iptables[root@mysql ~]# /etc/init.d/iptables restartiptables: Setting chains to policy ACCEPT: filter [ OK ]iptables: Flushing firewall rules: [ OK ]iptables: Unloading modules: [ OK ]iptables: Applying firewall rules: [ OK ]
这次再通过SQLyog客户端连接MySQL服务器，依然成功连接！可见，之前无法连接的问题就是因为3306被防火墙给阻挡了。CentOS 6.3 安装MySQL与SQLyog连接 http://www.linuxidc.com/Linux/2012-11/74401.htmUbuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm本文永久更新链接地址