#mysql

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!

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.

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.

How to set up a dev environment on OS X Yosemite (10.10) with Homebrew, PHP, MySQL, and Apache.