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串連
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通用二進位安裝
本文永久更新連結地址: