iptables mysql 3306_iptables 开启3306端口

[root@mysqld ~]# mysql -uroot -h 192.168.1.35 -p

Enter 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 status

Table: filter

Chain 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 status

Table: filter

Chain 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 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13

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

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 status

Table: filter

Chain 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 status

Table: filter

Chain 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 -p

Enter password:

#卡主无法链接

重新开启3306端口:

[root@v01-svn-test-server online]# service iptables status

Table: filter

Chain 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/ip

ip6tables 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