Eric Stewart: Running Off At The Mouth

So it’s not uncommon (as far as I know) for iptables to be configured to allow all outgoing connections, and to allow the return traffic by using a rule (in CentOS/RHEL /etc/sysconfig/iptables format) like:

-A INPUT -m state ESTABLISHED,RELATED -j ACCEPT

The rule simply states that return traffic for connections a given system initiates be allowed through iptables protection (what the layman or for-lack-of-a-better-term people would call the “local firewall”).  There’s some connection state tracking involved with this, and being aware of that is important when dealing with iptables on a MySQL slave.

See, when a slave makes the connection to the master, it uses (on its side) a (supposedly) randomized port number as the “source” port, and connects to the master’s MySQL port (3306 by default).  Thing is, you need to be aware that for return traffic, the master’s “source” port is 3306 and the slave’s “destination” port is that randomized port number.

So, if you come across a situation where you need to change the iptables configuration on the slave, and edit the /etc/sysconfig/iptables, then use

/etc/init.d/iptables restart

to enable the new ruleset, you’re going to have a bad time if you haven’t specifically addressed the master/slave connection.  See, the thing is, that restart command will wipe the connection tracking information, and you won’t have any real clue that the situation has gone south right away.  MySQL on the slave will simply state that it’s waiting for data from the master, but the master’s packets will not make it through the reset iptables.  In our case, the hint was the various Nagios checks for our master/slave setup started reporting that the checksums between the master’s version of the table and the slave’s version of the table were not equal.  While this can happen mid-update, for it to hang around for five or six minutes is unusual – the Nagios rechecks should eventually see equal checksums for the tables.

Restarting MySQL on the slave will fix the issue in a brute force manner – a new slave connection will be established and the slave will then “catch up” to the master.  However, planning ahead in your iptables configuration is probably the better option by including a rule such as:

-A INPUT -m tcp -p tcp -s (master-ip) --sport 3306 -j ACCEPT

Essentially: “I don’t care if it’s an established connection – if the master sends me a packet with a source port of 3306, accept it to any destination port.”

Any long-term connection a client makes to a server may need this kind of a rule in place to avoid potential issues if you find yourself having to edit iptables rules, particularly if the client initiates the connection but then falls into a “waiting for response” state and doesn’t send any kind of check status packet to the server from time to time (one would theorize that if the client does send some kind of “hello” check to the server, iptables on the client would see it as a “newly established” connection, and return traffic would be allowed to flow).  For us, it was half of a “master-master” MySQL configuration that I encountered this issue on.