PXC loves firewalls (and System Admins loves iptables)
Written by Marco Tusa   




Let them stay together. In the last YEARS, I have seen quite often that users, when installing a product such as PXC, instead of spending five minutes to understand what to do just run iptables -F and save. In short, they remove any rules for their firewall.

With this post, I want to show you how easy it can be to do the right thing instead of putting your server at risk. I'll show you how a slightly more complex setup like PXC (compared to MySQL), can be easily achieved without risky shortcuts. iptables is the utility used to manage the chains of rules used by the Linux kernel firewall, which is your basic security tool. Linux comes with a wonderful firewall built into the kernel.

As an administrator, you can configure this firewall with interfaces like ipchains — which we are not going to cover — and iptables, which we shall talk about. iptables is stateful, which means that the firewall can make decisions based on received packets. This means that I can, for instance, DROP a packet if it's coming from bad-guy.com. I can also create a set of rules that either will allow or reject the package, or that will redirect it to another rule. This potentially can create a very complex scenario.



However, for today and for this use case let's keep it simple… Looking at my own server:

iptables -v -L
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination
 250K   29M ACCEPT     all  --  any    any     anywhere             anywhere             state RELATED,ESTABLISHED
    6   404 ACCEPT     icmp --  any    any     anywhere             anywhere
    0     0 ACCEPT     all  --  lo     any     anywhere             anywhere
    9   428 ACCEPT     tcp  --  any    any     anywhere             anywhere             state NEW tcp dpt:ssh
    0     0 ACCEPT     tcp  --  any    any     anywhere             anywhere             state NEW tcp dpt:mysql
    0     0 ACCEPT     tcp  --  any    any     anywhere             anywhere             
  210 13986 REJECT     all  --  any    any     anywhere             anywhere             reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination
    0     0 REJECT     all  --  any    any     anywhere             anywhere             reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT 241K packets, 29M bytes)
 pkts bytes target     prot opt in     out     source               destination

That's not too bad, my server is currently accepting only SSH and packets on port 3306. Please note that I used the -v option to see more information like IN/OUT and that allows me to identify that actually row #3 is related to my loopback device, and as such it's good to have it open. The point is that if I try to run the PXC cluster with these settings it will fail, because the nodes will not be able to see each other. A quite simple example when try to start the second node of the cluster:

2018-05-21T17:56:14.383686Z 0 [Note] WSREP: (3cb4b3a6, 'tcp://') connection to peer 584762e6 with addr tcp:// timed out, no messages seen in PT3S

Starting a new node will fail, given that the connectivity will not be established correctly. In the Percona documentation there is a notes section in which we mention that these ports must be open to have the cluster working correctly.:

  • 3306 For MySQL client connections and State Snapshot Transfer that use the mysqldump method.
  • 4567 For Galera Cluster replication traffic, multicast replication uses both UDP transport and TCP on this port.
  • 4568 For Incremental State Transfer.
  • 4444 For all other State Snapshot Transfer.

Of course, if you don’t know how to do it that could be a problem, but it is quite simple. Just use the following commands to add the needed rules:

iptables -I INPUT 2 --protocol tcp --match tcp --dport 3306 --source --jump ACCEPT
iptables -I INPUT 3 --protocol tcp --match tcp --dport 4567 --source --jump ACCEPT
iptables -I INPUT 4 --protocol tcp --match tcp --dport 4568 --source --jump ACCEPT
iptables -I INPUT 5 --protocol tcp --match tcp --dport 4444 --source --jump ACCEPT
iptables -I INPUT 6 --protocol udp --match udp --dport 4567 --source --jump ACCEPT

Once you have done this check the layout again and you should have something like this:

[root@galera1h1n5 gal571]# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
ACCEPT tcp -- anywhere tcp dpt:mysql
ACCEPT tcp -- anywhere tcp dpt:tram
ACCEPT tcp -- anywhere tcp dpt:bmc-reporting
ACCEPT tcp -- anywhere tcp dpt:krb524
ACCEPT udp -- anywhere udp dpt:tram
ACCEPT icmp -- anywhere anywhere
ACCEPT tcp -- anywhere anywhere tcp dpt:ssh
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
REJECT all -- anywhere anywhere reject-with icmp-port-unreachable

Chain FORWARD (policy ACCEPT)
target prot opt source destination
REJECT all -- anywhere anywhere reject-with icmp-port-unreachable

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Try to start the secondary node, and — tadaaa — the node will connect, will provision itself, and finally will start correctly. All good? Well not really, you still need to perform a final step. We need to make our server accessible also for PMM monitoring agents. You have PMM right? If you don’t take a look here and you will want it. :D Anyhow PMM will not work correctly with the rules I have, and the result will be an empty set of graphs when accessing the server statistics. Luckily, PMM has a very easy way to help you identify the issue:

[root@galera1h1n5 gal571]# pmm-admin check-network
PMM Network Status

Server Address |
Client Address |

* System Time
NTP Server (0.pool.ntp.org) | 2018-05-24 08:05:37 -0400 EDT
PMM Server | 2018-05-24 12:05:34 +0000 GMT
PMM Client | 2018-05-24 08:05:37 -0400 EDT
PMM Server Time Drift | OK
PMM Client Time Drift | OK
PMM Client to PMM Server Time Drift | OK

* Connection: Client --> Server
-------------------- -------
-------------------- -------
Consul API OK
Prometheus API OK
Query Analytics API OK

Connection duration | 1.051724ms
Request duration | 311.924µs
Full round trip | 1.363648ms

* Connection: Client <-- Server
-------------- ------------ -------------------- ------- ---------- ---------
-------------- ------------ -------------------- ------- ---------- ---------
linux:metrics galera1h1n5 DOWN NO NO
mysql:metrics gal571 DOWN NO NO

When an endpoint is down it may indicate that the corresponding service is stopped (run 'pmm-admin list' to verify).
If it's running, check out the logs /var/log/pmm-*.log

When all endpoints are down but 'pmm-admin list' shows they are up and no errors in the logs,
check the firewall settings whether this system allows incoming connections from server to address:port in question.

Also you can check the endpoint status by the URL:

What you want more? You have all the information to debug and build your new rules. I just need to open the ports 42000 42002 on my firewall:

iptables -I INPUT 7 --protocol tcp --match tcp --dport 42000 --source --jump ACCEPT
iptables -I INPUT 8 --protocol tcp --match tcp --dport 42002 --source --jump ACCEPT

Please note that we are handling the connectivity for PMM using a different range of IPs/subnet. This because it is best practice to have PXC nodes communicate to a dedicated network/subnet (physical and logical). Run the test again:

* Connection: Client <-- Server
-------------- ------------ -------------------- ------- ---------- ---------
-------------- ------------ -------------------- ------- ---------- ---------
linux:metrics galera1h1n5 OK YES YES
mysql:metrics gal571 OK YES YES

Done … I just repeat this on all my nodes and I will have set my firewall to handle the PXC related security. Now that all my settings are working well I can save my firewall’s rules:

iptables-save > /etc/sysconfig/iptables

For Ubuntu you may need some additional steps as for (https://help.ubuntu.com/community/IptablesHowTo#Using_iptables-save.2Frestore_to_test_rules) There are some nice tools to help you even more, if you are very lazy, like UFW and the graphical one, GUFW. Developed to ease iptables firewall configuration, ufw provides a user friendly way to create an IPv4 or IPv6 host-based firewall. By default UFW is disabled in Ubuntu. Given that ultimately they use iptables, and their use is widely covered in other resources such as the official Ubuntu documentation, I won't cover these here.


Please don't make the mistake of flushing/ignoring your firewall, when to make this right is just a matter of 5 commands. It's easy enough to be done by everyone and it's good enough to stop the basic security attacks. Happy MySQL (and PXC) to everyone.

Last Updated on Wednesday, 18 July 2018 16:25
No orange pants this year
Written by Marco Tusa   

Well here we go another MySQL conference / Percona Live.

Another huge and important event/milestone for the MySQL community. 

But this time I will not be there.

First time in many years I had to decline, drop my speech and say... "No I am so sorry, this time I cannot come".


Was not an easy choice, not only because I am always excited to meet old colleagues, but also because PLSC is a great moment for brainstorming and to identify what could be good to push-on or to investigate better.

Especially this year where we have so many different interesting topics and so many different technologies as well. 

And of course the MySQL 8 GA will make a huge wave, but that was expected. To be honest what I am more interested to see is... what the real adoption of it will means. In Percona, we are working to be able to have it's adoption to happen as smoother as possible.


So I will really miss to be there, my hope is to be able to see the videos and get at least the presentations slides, to review the contents offline... but I will miss all the interactions and Q/A.

Anyhow I went through the schedule, and this is the list of speeches I would like to go listen and raise questions, knowing me ... not fix in the stone at all but you know is a start:


day 1

Make Your Database Dream of Electric Sheep: Designing for Autonomous Operation

MySQL at Twitter: No More Forkin' - Migrating to MySQL Community version

Containerizing Databases at New Relic: What We Learned

Tuning PostgreSQL for High-Write Workloads

Consistent Reads Using ProxySQL and GTID

Benchmark Noise Reduction: How to Configure Your Machines for Stable Results

Cassandra on RocksDB

ClickHouse in Real Life: Case Studies and Best Practices


day 2

Migrating to Vitess at (Slack) Scale

Stateful applications on Mesosphere DC/OS

Aurora PostgreSQL Deep Dive

Data Management in Kubernetes Using Kanister

A Seat At the Blockchain and Cryptocurrency Table for NoSQL Database Technologies


I wish to all of you to enjoy the conference, remember ask ask ask, the more you will interact with the speaker during the sessions, the better the presentation will be for all.

Hope to see you all in PL Europe in Germany and in PL 2019 

Have fun!!! And learn! 

Last Updated on Thursday, 19 July 2018 11:41
More Articles...

Page 1 of 28

Who's Online

We have 37 guests online