MySQL FEDERATED Storage Engine and Replication
Back in Nov 2020, it got time to rethink the MySQL replication infrastructure for Airpane Controlpanel, our customer dashboard at Onlime GmbH. The whole application runs on a separate server, but an extract of mail account information (mail account credentials, mail mappings/forwardings) needs to get replicated to our 3 mail servers (mailsrv
, mx1
, mx2
). At that time, I was using MySQL MASTER-SLAVE replication for a single database in a 4-node setup (1 master + 3 slaves).
For security reasons, I no longer wanted any mail server to have access to the binlog of the server that hosted our controlpanel (even though I already had that limited to a single database with mailserver related data only). I also wanted to reduce complexity a bit, just using MySQL replication for the 3 mail servers and propagating mailsrv
to master.
The mailserver data is extracted from our controlpanel database by MySQL triggers (mainly AFTER INSERT
, AFTER UPDATE
, and BEFORE DELETE
triggers) into a separate database mailsync
. How to get whole mailsync
data stored on the remote server mailsrv
without using MySQL replication? I didn't want to care about this on application level.
That's where MySQL FEDERATED Storage Engine comes into play!
Recursively fetch dependent rows with mysqldump
How to fetch a row from a MySQL database recursively, going through all foreign key (FK) constraints and fetch all dependent rows as well? That's the question that bothered me during the last 20+ years as MySQL administrator. Isn't there a standard tool like some extended mysqldump
that comes with that power? Short answer: No, there is no such tool. I gave up searching. It's just too complex to write a general-purpose tool that works for any kind of database schema.
Fail2ban persistent banning
If you are using Fail2ban, there is no standard recommended way to persistently ban IPs. Some people recommend to do this outside of Fail2ban, using e.g. iptables-persistent, which is actually super easy to install and configure. But let's say, we don't want to install any extras and want to accomplish the same with Fail2ban, as we already have fail2ban on every single host (which is a must!).
Block email forwarding spam with Rspamd
At Onlime GmbH we have a mail infrastructure that consists of 3 mail servers: mx1
acts as primary MX server and provides SMTP as outgoing mail gateway for our customers. mx2
acts as secondary MX and fallback incoming mailserver. Incoming email from mx2
is forwarded to mx1
which does spam/antivirus filtering with Rspamd. Finally, the 3rd mailserver which is simply called mail
acts as IMAP server and outgoing mailserver.
We allow customers to set up email forwardings on their domains. A forwarding address could have another customer email address as destination, but could also directly or indirectly (through another forwarding) point to an external email address. Spam filtering is done on mx1
, but Rspamd actually just flags the email as ham/spam with a spam score and adds the X-Spamd-Result
header with all symbols. The actual action is done on the final mailserver mail
in the recipients mailbox via Sieve rule. The reason for this is that we want to let the customer define his own spam score (going from "minimal" to "radical" which maps to a spam score treshold) and what should happen with an email that got classified as spam (store it in Spam
box or discard it directly).
MySQL MyISAM to InnoDB Conversion
Back in November 2020, I managed to convert all legacy MyISAM tables to InnoDB on all Onlime GmbH database servers and customer webservers. MyISAM as legacy storage engine was quite okay-ish on MySQL 5.7 but started to perform really bad on MySQL 8.0. There was simply no reason to keep on using it and honestly, for the last 10 years I did never understand why people still held onto it. I had to find out that a lot of my customers just never heard of any storage engine types, and they didn't even know of any differences between MyISAM and InnoDB.
Automated Bayesian Spam/Ham Training with Rspamd
At Onlime GmbH we have migrated the mail infrastructure in Dec 2019 from good old Spamassassin to Rspamd which greatly improved spam filtering. Rspamd offers a nice way of Bayesian learning in Rspamd statistical module. You can feed emails through rspamc learn_spam
or rspamc learn_ham
for manual spam/ham training to improve Bayes hit rate.
In the past, we have only internally used Bayesian training. Wouldn't it be nice to let all customers help us improve the Bayes filter / hit rate? Without even asking them to do so?
Process hiding in LXC using hidepid capabilities of procfs
Back in 2013, I wrote about Linux process hiding using hidepid capabilities of procfs. On shared webhosting servers at Onlime GmbH, I have used the hidepid=2
mount option for procfs (/proc
filesystem) for improved security. Like this, a regular system user (which could potentially be an evil customer that has gained SSH access and tries to spy on other's processes) does only see his own processes, all other processes are hidden.
This is great and super simple to enable, as it is part of the official Linux kernel for quite a while now. But things start to get a little trickier when we try to set up hidepid
procfs mount option inside an LXC container. Enabling the mount option on the host system will not do! Inside an LXC container, a regular system user is still able to see all processes. Before LXC 2.1 (released in Sept 2017), this was also quite doable, as we just had to create a new AppArmor profile on the host system to allow the LXC container to set the /proc
mount options. But since LXC 2.1 it got super tricky. I will present both solutions below, in case you have struggled with this hard one in newer LXC versions.
Page 3 of 5 • 29 Posts