Using MySQL Federated Storage Engine for Remote Table Access

Background : When a local MySQL instance needs to read data from a remote MySQL table without using data‑synchronization tools, the FEDERATED storage engine can be employed. The engine must be compiled with the --with-federated-storage-engine option.

Federated Engine Architecture : When a client issues a query against a FEDERATED table, the local server builds a SQL statement for each column, sends it to the remote server via the MySQL client API, receives any result set, converts each column to the internal format expected by the engine, and finally returns the data to the client.

Supported and Unsupported Features : The engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE and indexes, but does not support ALTER TABLE, DROP TABLE, or any DDL that changes the table structure. INSERT … ON DUPLICATE KEY UPDATE is accepted but fails on duplicate‑key conflicts. Batch inserts are limited by the maximum packet size, and CONNECTION strings cannot contain the ‘@’ character in passwords.

Environment : Two MySQL 5.7.26 instances are used. ip port remark 192.168.234.204 3306 server-1 192.168.234.204 3310 server-2

Engine Enablement :

# Add federated engine to my.cnf on both instances
vim /etc/my.cnf
[mysqld]
federated

Verify the engine is active:

mysql> show engines;
+--------------------+---------+--------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                      | Transactions | XA   | Savepoints |
+--------------------+---------+--------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                               | NO           | NO   | NO         |
+--------------------+---------+--------------------------------------------------------------+--------------+------+------------+

Deployment Steps :

Create a test database and table on server-1 :

create database db1;
use db1;
create table tb1(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
) ENGINE=INNODB;

insert into tb1 select NULL;
insert into tb1 select NULL;
insert into tb1 select NULL;

select * from tb1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

Create a user for remote access:

create user 'test'@'192.168.%' IDENTIFIED BY '123456';
grant select,update,insert,delete on db1.* to 'test'@'192.168.%';
flush privileges;

Create the remote (FEDERATED) table on server-2 and a local InnoDB table for join tests:

create database db2;
use db2;
create table remote_tb1(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
) ENGINE=FEDERATED
CONNECTION='mysql://test:[email protected]:3306/db1/tb1';

create table tb2(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(20)
) ENGINE=INNODB;

insert into tb2(name) select 'a';
insert into tb2(name) select 'b';
insert into tb2(name) select 'c';

select * from db2.tb2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+

Query the remote table to confirm linkage:

select * from db2.remote_tb1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

Perform a join between the remote and local tables:

select t2.* from db2.remote_tb1 as t1 join db2.tb2 as t2 on t1.id=t2.id where t2.name='c';
+----+------+
| id | name |
+----+------+
|  3 | c    |
+----+------+

Test DML on the remote table and inspect binlog for GTID handling:

delete from db2.remote_tb1 where id = 3;
select * from db2.remote_tb1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+

Extract relevant binlog entries:

mysqlbinlog -vvv mysql-bin.000884 | grep -C 10 remote_tb1
SET @@SESSION.GTID_NEXT='ANONYMOUS';
# ...
### DELETE FROM `db2`.`remote_tb1`
### WHERE @1=3 /* INT */
COMMIT

File system check shows that the FEDERATED table does not create a data file, only a .frm definition:

# pwd
/mysqlData/3310_data/data/db2
# ls -l
-rw-r----- 1 mysql mysql  67 Apr 15 14:11 db.opt
-rw-r----- 1 mysql mysql 8556 Apr 15 14:11 remote_tb1.frm
-rw-r----- 1 mysql mysql 8586 Apr 15 14:18 tb2.frm
-rw-r----- 1 mysql mysql 98304 Apr 15 14:18 tb2.ibd

Conclusion : Compared with dedicated data‑synchronization products, using the FEDERATED engine provides a lightweight way to query across IPs and databases, with simple setup and minimal overhead.