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!

MySQL reference manual gives us a nice explanation for FEDERATED storage engine:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

I am going to explain how to set up FEDERATED tables on our primary controlpanel server, called cpsrv below. The main database will be hosted on our primary mail server, called mailsrv below. I am not going to explain how to set up MySQL replication in this article.

Prepare Remote Host

On the remote host mailsrv that is going to host database mailsync, we need to set up a separate MySQL user which is going to be used for FEDERATE access:

CREATE USER mailsync@cpsrv IDENTIFIED BY '********';
GRANT SELECT, INSERT, UPDATE, DELETE ON `mailsync`.* TO mailsync@cpsrv;

Prepare Source Host

Enable FEDERATED Storage Engine

By default, MySQL FEDERATED Storage Engine is not enabled in MySQL. To enable FEDERATED, you must start the MySQL server using the --federated option. You can configure this in your my.cnf:

[mysqld]
federated

After restarting MySQL, check if the engine is enabled:

mysql> SHOW ENGINES
+--------------------+---------+
| Engine             | Support |
+--------------------+---------+
| FEDERATED          | YES     |
...

We only need to enable FEDERATED on the server that uses FEDERATED tables, not on the remote host where it connects to. In my case, I had to enable FEDERATED on cpsrv, but not on mailsrv.

Create SERVER

Prepare SERVER (user credentials for mailsync user connection to mailsrv) for FEDERATED Storage Engine with CREATE SERVER statement on cpsrv:

CREATE SERVER mailsync
    FOREIGN DATA WRAPPER mysql
    OPTIONS (HOST 'mailsrv', DATABASE 'mailsync', USER 'mailsync', PASSWORD '********');

You should then test the connection:

cpsrv$ mysql -u mailsync -h mailsrv -p -e 'SELECT USER()'
+----------------+
| USER()         |
+----------------+
| mailsync@cpsrv |
+----------------+

Setup FEDERATED tables

Assuming we already have set up the remote database mailsync on remote mailsrv and have a table schema from that whole database at hand. You should now copy that db schema mailsync-schema.sql to mailsync-schema-federated.sql and simply add ENGINE=FEDERATED CONNECTION='mailsync/<tablename>' at the end of every CREATE TABLE statement, e.g.:

CREATE SCHEMA IF NOT EXISTS `mailsync` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `mailsync` ;

CREATE TABLE IF NOT EXISTS `mailmaps` (...)
ENGINE = FEDERATED
CONNECTION = 'mailsync/mailmaps';

CREATE TABLE IF NOT EXISTS `mailaccounts` (...)
ENGINE = FEDERATED
CONNECTION = 'mailsync/mailaccounts';

The mailsync/ prefix in CONNECTION refers to the connection we have set up with CREATE SERVER (see above). You should use exactly the same table schema on both MySQL servers. And no, FEDERATED cannot detect the remote table schema, so you need to copy the whole CREATE TABLE field definitions. The schema on the source host will the just act as a skeleton to access the remote data.

Remember, no data of that mailsync database is ever stored on our source server cpsrv, so you could reload the whole database schema at any time like this without loosing any data:

cpsrv$ mysql -e 'DROP DATABASE `mailsync`'
cpsrv$ mysql < mailsync-schema-federated.sql

You can now INSERT data into this database and it will actually be inserted directly on the remote host mailsrv, all transparently and magically.

Possible Problems

Communication link failure

Up until MySQL 8.0.23, FEDERATED was working just perfectly without any single issue. Communication between the two hosts was super stable. But since MySQL 8.0.24, wet started to see sporadic errors on the source host like:

Communication link failure: 1160 Got an error writing communication packets
Communication link failure: 1156 Got packets out of order

I was able to track this down to the FEDERATED Storage Engine and the problem seems to be related to the newly introduced connection management in MySQL 8.0.24:

Connection Management Notes

Previously, if a client did not use the connection to the server within the period specified by the wait_timeout system variable and the server closed the connection, the client received no notification of the reason. Typically, the client would see Lost connection to MySQL server during query (CR_SERVER_LOST) or MySQL server has gone away (CR_SERVER_GONE_ERROR).

In such cases, the server now writes the reason to the connection before closing it, and client receives a more informative error message, The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. (ER_CLIENT_INTERACTION_TIMEOUT).

The previous behavior still applies for client connections to older servers and connections to the server by older clients.

I have reported this issue in Percona Community Forum:

• MySQL 8.0.24 / 8.0.25 connection problems with FEDERATED storage engine (Communication link failure)

Until now, the problem still is not resolved and I am still waiting for a response on that forum post. But setting wait_timeout high enough (raising it from default 8h to 24h) as a workaround almost made this issue disappear.

Set this on your remote host (the one your FEDERATED tables connect to):

[mysqld]
interactive_timeout     = 86400
wait_timeout            = 86400

I will keep you posted here, if MySQL fixes this for good or if I find a real solution for it.