SQLyog客户端无法连接MySQL服务器案例分析
SQLyog客户端无法连接MySQL服务器案例分析
--查看客户端ip [root@mysql ~]# w 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客户端连接服务器提示无法连接,"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 --不重启直接关闭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. --关闭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-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服务器,依然成功连接!可见,之前无法连接的问题就是因为3306被防火墙给阻挡了。
CentOS 6.3 安装MySQL与SQLyog连接
Ubuntu 14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二进制安装
本文永久更新链接地址:
评论暂时关闭