--查看客户端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通用二进制安装
本文永久更新链接地址：