What is wrong with this iptable for Mysql Replication

Question

I'm not quite used to IPTables and I'm trying to run an iptables script to allow only ssh connection from all and connection to mysql server only from specified IPs.

I made a bash script for this, which is lister under, but when I run this, my master-master replication stops working.

For information, here's my network structure :

db1 : 1.1.1.1
db2 : 2.2.2.2
web1 : 3.3.3.3
web2 : 4.4.4.4

Here's my script :

#!/bin/sh
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Ce script crée les règles IPTABLES pour les accès serveurs
# Voir http://askubuntu.com/a/119398/123916
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# My system IP/set ip address of server
SERVER_IP="1.1.1.1"
ALLOWED_IP="2.2.2.2,3.3.3.3,4.4.4.4"

# Flushing all rules
iptables -F
iptables -X

# Setting default filter policy
iptables -P INPUT DROP
iptables -P OUTPUT DROP
iptables -P FORWARD DROP

# Allow unlimited traffic on loopback
iptables -A INPUT -i lo -j ACCEPT
iptables -A OUTPUT -o lo -j ACCEPT

# Allow incoming ssh only
iptables -A INPUT -p tcp -s 0/0 -d $SERVER_IP --sport 513:65535 --dport 22 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP -d 0/0 --sport 22 --dport 513:65535 -m state --state ESTABLISHED -j ACCEPT

# Allow MySQL incoming from other servers :
iptables -A INPUT -p tcp -s $ALLOWED_IP --sport 1024:65535 -d $SERVER_IP --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP --sport 3306 -d $ALLOWED_IP --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

# make sure nothing comes or goes out of this box
iptables -A INPUT -j DROP
iptables -A OUTPUT -j DROP

Shouldn't master-master replication need to allow outgoing connection _to_ the other master's port 3306? Your rules allow only incoming connection to port 3306, but blocks outgoing connection to port 3306 (the very last line). — pepoluan, Jan 29 '14 at 08:19
@Cyril N. I think i can see where your problem is, but i need to know where runs your iptables script ? at db1 side or db2 side ? or both ? — krisFR, Jan 30 '14 at 12:48
@Cyril N. ok so db2 side script should be a bit different from the one at db1 side (at least `$SERVER_IP` variable, i guess). Please provide scripts you have for db1 **and** db2, or tell me if the script you have already posted is the one at db1 or db2 side. — krisFR, Jan 30 '14 at 13:17
why not use a maintained system like CSF - http://configserver.com/cp/csf.html has a web admin interface to make it easier to do — anthonysomerset, Jan 30 '14 at 20:17

krisFR · Answer 1 · 2014-01-30T20:06:12.840

I am surprised that you use exact same script for db1 and db2 ! Sure it cannot work.

Seems you have messed up with source/destination hosts and source/destination ports, and which server initiates the connection to the other. Also, variables you have defined cannot be the same depending on where you are (db1 or db2).

I've tested this in Lab and it worked :

DB1 side :

#!/bin/sh
# ---------------
# DB1 side script
# ---------------

# My system IP/set ip address of server
SERVER_IP="1.1.1.1"
ALLOWED_IP="2.2.2.2,3.3.3.3,4.4.4.4"

# Flushing all rules
iptables -F
iptables -X

# Setting default filter policy
iptables -P INPUT DROP
iptables -P OUTPUT DROP
iptables -P FORWARD DROP

# Allow unlimited traffic on loopback
iptables -A INPUT -i lo -j ACCEPT
iptables -A OUTPUT -o lo -j ACCEPT

# Allow incoming ssh only
iptables -A INPUT -p tcp -s 0/0 -d $SERVER_IP --dport 22 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP -d 0/0 --sport 22 -m state --state ESTABLISHED -j ACCEPT

# Allow replication from DB1 to DB2
iptables -A INPUT -p tcp -s $ALLOWED_IP --sport 1024:65535 -d $SERVER_IP --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP --sport 3306 -d $ALLOWED_IP --dport 1024:65535 -m state --state NEW,ESTABLISHED -j ACCEPT

# Allow replication from DB2 to DB1
iptables -A INPUT -p tcp -s $ALLOWED_IP --sport 3306 -d $SERVER_IP --dport 1024:65535 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP --sport 1024:65535 -d $ALLOWED_IP --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

# make sure nothing comes or goes out of this box
iptables -A INPUT -j DROP
iptables -A OUTPUT -j DROP

DB2 side :

#!/bin/sh
# ---------------
# DB2 side script
# ---------------

# My system IP/set ip address of server
SERVER_IP="2.2.2.2"
ALLOWED_IP="1.1.1.1,3.3.3.3,4.4.4.4"

# Flushing all rules
iptables -F
iptables -X

# Setting default filter policy
iptables -P INPUT DROP
iptables -P OUTPUT DROP
iptables -P FORWARD DROP

# Allow unlimited traffic on loopback
iptables -A INPUT -i lo -j ACCEPT
iptables -A OUTPUT -o lo -j ACCEPT

# Allow incoming ssh only
iptables -A INPUT -p tcp -s 0/0 -d $SERVER_IP --dport 22 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP -d 0/0 --sport 22 -m state --state ESTABLISHED -j ACCEPT

# Allow replication from DB1 to DB2
iptables -A INPUT -p tcp -s $ALLOWED_IP --sport 3306 -d $SERVER_IP --dport 1024:65535 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP --sport 1024:65535 -d $ALLOWED_IP --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

# Allow replication from DB2 to DB1
iptables -A INPUT -p tcp -s $ALLOWED_IP --sport 1024:65535 -d $SERVER_IP --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s $SERVER_IP --sport 3306 -d $ALLOWED_IP --dport 1024:65535 -m state --state NEW,ESTABLISHED -j ACCEPT

# make sure nothing comes or goes out of this box
iptables -A INPUT -j DROP
iptables -A OUTPUT -j DROP

why specifying source ports for remote connections, it can be safely ommited because the packets will match based on the destination ports — anthonysomerset, Jan 30 '14 at 20:15
@anthonysomerset I agree with you, i just wanted to stay closest from the original OP's script. — krisFR, Jan 30 '14 at 20:19

What is wrong with this iptable for Mysql Replication

1 Answers1