Case study of SQLyog client unable to connect to MySQL Server

-- View the Client ip Address [root @ mysql ~] # W22: 20: 43up2: 39,1 user, loadaverage: 0.00, 0.01, 0.05 USERTTYFROM

-- View the Client ip Address [root @ mysql ~] # W 22:20:43 up, 1 user, load average: 0.00, 0.01, 0.05 USER TTY FROM

-- View the Client ip Address

[Root @ mysql ~] # W

22:20:43 up, 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 3.00 s 0.07 s 0.01 s w

-- Create a client user

Root @ localhost 22:23:15 [(none)]> create user 'zlm '@ '192. 168.1.7' identified by 'zlm ';

Query OK, 0 rows affected (0.00 sec)

-- Use the newly created user to connect to the server through the SQLyog Client

Unable to connect, "Can't connect to MySQL server"

-- Create a local account on the 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

-- Test whether connection is allowed

[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,201 4, 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-NCLP | 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-NCLP | 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 ~] #

The local user can use port 3306 to connect, indicating that there is no problem with the network and port 3306 is enabled. In fact, the problem is still caused by iptables.

When you use chkconfig iptables off to disable iptables of various terminals, it takes effect after restart. At this time, it has not been restarted.

[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

-- Disable the iptables service without restarting.

[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.

-- After iptables is disabled, the connection is successful again.

If you do not want to disable iptables, add-a input-m state -- state NEW-m tcp-p tcp -- dport 3306-j ACCEPT to/etc/sysconfig/iptables.

-- Add allow rules to iptables (note that the rules are not added at the end)

[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 -- indicates that port 3306 is allowed to pass the Firewall

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

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

COMMIT

-- Restart iptables after modification

[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]

This time, I connected to the MySQL server through the SQLyog client. The connection is still successful! It can be seen that the problem of connection failure is that 3306 is blocked by the firewall.

Install MySQL and SQLyog in CentOS 6.3

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation