[root@mysqld ~]# mysql -uroot -h 192.168.1.35 -pEnter password: ERROR 1130 (HY000): Host '192.168.1.66' is not allowed to connect to this MySQL server
下表可见3306端口没打开:
[root@v01-svn-test-server online]# service iptables statusTable: filterChain INPUT (policy ACCEPT)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 2 ACCEPT all -- 127.0.0.1 127.0.0.1 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:80 Chain FORWARD (policy ACCEPT)num target prot opt source destination Chain OUTPUT (policy ACCEPT)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:22 state ESTABLISHED 2 ACCEPT all -- 127.0.0.1 0.0.0.0/0 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:80 state ESTABLISHED
[root@v01-svn-test-server online]# iptables -A INPUT -p tcp -s 192.168.1.66 --dport 3306 -j ACCEPT[root@v01-svn-test-server online]# service iptables statusTable: filterChain INPUT (policy ACCEPT)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 2 ACCEPT all -- 127.0.0.1 127.0.0.1 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:80 4 ACCEPT tcp -- 192.168.1.66 0.0.0.0/0 tcp dpt:3306 Chain FORWARD (policy ACCEPT)num target prot opt source destination Chain OUTPUT (policy ACCEPT)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:22 state ESTABLISHED 2 ACCEPT all -- 127.0.0.1 0.0.0.0/0 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:80 state ESTABLISHED
建个远程账户：
mysql> select user,host,password from user;+------+-----------+-------------------------------------------+| user | host | password |+------+-----------+-------------------------------------------+| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |+------+-----------+-------------------------------------------+1 row in set (0.06 sec)
mysql> grant select on *.* to "select_user"@"%" identified by "123";Query OK, 0 rows affected (0.10 sec)
mysql> select user,host,password from user;+-------------+-----------+-------------------------------------------+| user | host | password |+-------------+-----------+-------------------------------------------+| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || select_user | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |+-------------+-----------+-------------------------------------------+2 rows in set (0.00 sec)
成功连入远程连入mysql服务器:
[root@mysqld ~]# mysql -uselect_user -h192.168.1.35 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 13Server version: 5.5.40-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.mysql>
关掉3306端口，再次测试:
[root@v01-svn-test-server online]# iptables -D INPUT -p tcp -s 192.168.1.66 --dport 3306 -j ACCEPT[root@v01-svn-test-server online]# service iptables statusTable: filterChain INPUT (policy ACCEPT)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 2 ACCEPT all -- 127.0.0.1 127.0.0.1 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:80 Chain FORWARD (policy ACCEPT)num target prot opt source destination Chain OUTPUT (policy ACCEPT)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:22 state ESTABLISHED 2 ACCEPT all -- 127.0.0.1 0.0.0.0/0 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:80 state ESTABLISHED
[root@v01-svn-test-server online]# iptables -P INPUT DROP[root@v01-svn-test-server online]# iptables -P OUTPUT DROP[root@v01-svn-test-server online]# iptables -P FORWARD DROP[root@v01-svn-test-server online]# service iptables statusTable: filterChain INPUT (policy DROP)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 2 ACCEPT all -- 127.0.0.1 127.0.0.1 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:80 Chain FORWARD (policy DROP)num target prot opt source destination Chain OUTPUT (policy DROP)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:22 state ESTABLISHED 2 ACCEPT all -- 127.0.0.1 0.0.0.0/0 3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:80 state ESTABLISHED
[root@mysqld ~]# mysql -uselect_user -h192.168.1.35 -pEnter password: #卡主无法链接
重新开启3306端口:
[root@v01-svn-test-server online]# service iptables statusTable: filterChain INPUT (policy DROP)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306 2 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 3 ACCEPT all -- 127.0.0.1 127.0.0.1 4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:80 Chain FORWARD (policy DROP)num target prot opt source destination Chain OUTPUT (policy DROP)num target prot opt source destination 1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:3306 2 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:22 state ESTABLISHED 3 ACCEPT all -- 127.0.0.1 0.0.0.0/0 4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:80 state ESTABLISHED [root@v01-svn-test-server online]# cat /etc/sysconfig/ipip6tables ip6tables.old iptables-config iptables.save ip6tables-config iptables iptables.old [root@v01-svn-test-server online]# cat /etc/sysconfig/iptables# Generated by iptables-save v1.4.7 on Wed Jun 1 22:15:41 2016*filter:INPUT DROP [24:3081]:FORWARD DROP [0:0]:OUTPUT DROP [0:0]-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT-A OUTPUT -p tcp -m tcp --sport 3306 -j ACCEPT-A INPUT -p tcp -m tcp --dport 22 -j ACCEPT -A INPUT -s 127.0.0.1/32 -d 127.0.0.1/32 -j ACCEPT -A INPUT -p tcp -m tcp --dport 80 -j ACCEPT -A OUTPUT -p tcp -m tcp --sport 22 -m state --state ESTABLISHED -j ACCEPT -A OUTPUT -s 127.0.0.1/32 -j ACCEPT -A OUTPUT -p tcp -m tcp --sport 80 -m state --state ESTABLISHED -j ACCEPT COMMIT# Completed on Wed Jun 1 22:15:41 2016