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!
MySQL reference manual gives us a nice explanation for
FEDERATED storage engine:
FEDERATEDstorage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local
FEDERATEDtable 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
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
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
cpsrv, but not on
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 | +----------------+
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-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';
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.
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
Connection Management Notes
Previously, if a client did not use the connection to the server within the period specified by the
wait_timeoutsystem 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 (
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. (
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
FEDERATEDstorage 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.
Author: Philip Iezzi (Pipo)
Owner of Onlime GmbH - providing quality webhosting with love. All into system engineering, Linux sysadmin, security, full stack web development, mountain biking, slacklining, dancing & feeling connected to nature.