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