Configuring the FEDERATED Storage Engine in MySQL: Connect and Query Remote Tables - ITFROMZERO - Share tobe shared! The FEDERATED Storage Engine in MySQL lets you directly query remote tables without building an ETL pipeline. This guide covers step-by-step setup, credential security, and critical performance pitfalls to avoid.

Table of Contents

The Problem: Joining Data Across Separated Databases

When decomposing a monolith into microservices, database splitting often introduces a major headache: how do you fetch user details to display alongside orders when users and orders reside on two physically separate servers?

Many teams opt for a quick fix: query the list of user_id values from the Order DB, stuff them into an array, and fire a subsequent WHERE user_id IN (...) query against the User DB. For a few dozen records, this works fine. But once the list hits 20,000 IDs, the backend exhausts its RAM, the payload balloons, and network latency spikes from 15ms to over 1,200ms.

Three Common Approaches for Cross-Server Data Access

Depending on your use case, there are three main approaches to link distributed data:

Application-level Join: The backend maintains connection pools to both databases, pulls data into memory, and joins it manually in application code.
CDC / ETL Synchronization: Use Debezium, Kafka, or MySQL Replication to push data from the source table to the destination server.
FEDERATED Storage Engine: Create a proxy table directly on the local server that points to the actual table on the remote server. The local MySQL instance acts as a client, relaying queries across the network under the hood.

Engineering Trade-Offs Across Solutions

There is no silver bullet. Each approach involves distinct trade-offs:

1. Application-Level Join

Pros: Quick to implement. Requires zero database configuration changes.
Cons: Highly memory-intensive on the backend. Highly prone to Out-of-Memory (OOM) errors when the IN (...) array exceeds the process memory limits.

2. CDC / ETL Synchronization

Pros: Extremely fast read performance because data is already stored locally on disk.
Cons: Consumes double the disk storage. Suffers from replication lag (from hundreds of milliseconds to several seconds). A simple column rename on the remote server can easily break the sync pipeline.

3. FEDERATED Storage Engine

Pros:
- Always accurate in real time with zero sync lag.
- The local server consumes no disk space for data and indexes—storing only table metadata.
- Native query syntax: you can seamlessly JOIN a FEDERATED table with local InnoDB tables.
Cons: Performance is directly constrained by network round-trip time (RTT). It does not support distributed transactions (XA), direct ALTER TABLE statements, or robust optimization for complex queries.

When Should You Use FEDERATED?

FEDERATED is not designed for high-throughput, core OLTP workloads. You should only consider this engine in scenarios such as:

Admin dashboards or internal tools that need quick user lookups without the overhead of building an expensive ETL pipeline.
Referencing slow-changing lookup tables (provinces/cities, postal codes, product categories) centralized on a master database.
Running migration scripts or data reconciliation between legacy and modern systems.

Step-by-Step FEDERATED Configuration Guide

Our lab setup consists of two internal servers:

Remote Server: IP 192.168.1.100, port 3306, hosting database auth_db and the source table users.
Local Server: IP 192.168.1.50, hosting database order_db, needing direct read access to the remote users table.

Step 1: Enable the FEDERATED Engine on the Local Server

Most MySQL installations have this engine disabled by default. First, check its status on the local server:

SHOW ENGINES;

If the Support column shows NO hoặc DISABLED, open your MySQL configuration file (typically located at /etc/mysql/mysql.conf.d/mysqld.cnf) and add the following line under the [mysqld] block:

[mysqld]
federated

Restart the MySQL daemon to apply the configuration:

sudo systemctl restart mysql

Run SHOW ENGINES; again. Once the value switches to YES, the engine is ready to use.

Step 2: Set Up Account and Table on the Remote Server

Switch to the Remote Server (192.168.1.100). Create the schema and grant minimal read permissions to the Local Server IP:

-- Run on Remote Server
CREATE DATABASE IF NOT EXISTS auth_db;
USE auth_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Grant SELECT privileges strictly to the Local Server IP
CREATE USER 'fed_reader'@'192.168.1.50' IDENTIFIED BY 'StrongPass#2026';
GRANT SELECT ON auth_db.users TO 'fed_reader'@'192.168.1.50';
FLUSH PRIVILEGES;

Step 3: Create the Linked Table on the Local Server

The most straightforward method is to specify the connection string directly in the CONNECTION parameter:

-- Run on Local Server
USE order_db;

CREATE TABLE federated_users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP
)
ENGINE=FEDERATED
CONNECTION='mysql://fed_reader:StrongPass#[email protected]:3306/auth_db/users';

The URL syntax follows the pattern: mysql://user:password@host:port/database/table.

Step 4: Secure Credentials Using `CREATE SERVER`

Exposing plaintext passwords inside a CREATE TABLE statement is a major security risk because anyone executing SHOW CREATE TABLE can see them. Instead, you should encapsulate the connection details using CREATE SERVER:

-- Create the server connection object
CREATE SERVER remote_auth_srv
FOREIGN DATA WRAPPER mysql
OPTIONS (
    HOST '192.168.1.100',
    DATABASE 'auth_db',
    USER 'fed_reader',
    PASSWORD 'StrongPass#2026',
    PORT 3306
);

-- Create the table using the newly defined server name
CREATE TABLE federated_users_secure (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP
)
ENGINE=FEDERATED
CONNECTION='remote_auth_srv/users';

Step 5: Test JOIN Query Performance

At this point, the federated_users_secure table behaves just like a local table in order_db:

-- Fetch a single record by primary key
SELECT id, username, email FROM federated_users_secure WHERE id = 10;

-- Join a local InnoDB table with the remote FEDERATED table
SELECT 
    o.id AS order_id,
    o.total_amount,
    u.username,
    u.email
FROM orders o
JOIN federated_users_secure u ON o.user_id = u.id
WHERE o.status = 'PAID'
LIMIT 20;

Performance Pitfalls and Production Best Practices

When running FEDERATED in production environments, beware of three critical pitfalls:

The Full Table Scan Trap: FEDERATED can only push simple, indexed WHERE clauses down to the remote server. If you filter on non-indexed columns (such as LIKE '%keyword%'), the local server will silently pull all hundreds of thousands of rows across the network before filtering them locally. This can instantly saturate a 1Gbps LAN link.
Schema Mismatches Crash Queries: Column definitions between local and remote must match. If the remote table alters a column from VARCHAR(50) to VARCHAR(150) or renames a field without a corresponding update on the local table, all related queries will fail immediately.
Keep Network Latency in Check: Only link FEDERATED tables across servers in the same local network or cloud VPC (with round-trip ping under 1–2ms). Never point FEDERATED across the public Internet between distant data centers, as network jitter and latency will cause database connection pools to hang.