What is Federated Engine
Why we need Federated Engine
Overview of Federated Engine
How Enable the Federated Engine
How to create Federated Engine Table
Testing Federated Engine
Disadvantage of Federated Engine
What is Federated Engine:
It is one of the MySQL database storage engine.
We can access or store the data in remote end without replication or cluster technique.
Why we need Federated Engine:
We can easily access/store the data from multiple source.
End user not aware source information.
No need to bother about local storage space.
Overview of Federated Engine:
How Enable the Federated Engine:
Login into MySQL server and check whether Federated engine enabled or not.
If its not enabled then enable it same via my.cnf as below
Verify the engines after bounced/re-start the MySQL.
How to create Federated Engine Table Using Connection
On source server(MySQL 3306 Instance):
The format of the connection string is as follows:
Federated table structure:
Create a Federated Table using Create server:
Create server syntax:
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] …)
Federated table creation using create server option:
On destination server:
The remote table physical structure:
Try to insert the data in source table:
Give access on remote server:
Now try to insert the Data on source table:
Retrieve the data from source tables(But actually it take from destination table “destination_table”)
Changing table structure on destination server:
Table structure on Source:
Now try to SELECT or INERT on source table.
Select the data from destination table:
Those who are all having basic SELECT access on Federated table they can easily find remote table credentials.
TO solve the security issue, Create a view from different schema.
switch to user DB:
Notes and Tips about Federated Storage engine
If its slave server ensure the federated table able to use the user/password combination that is defined in the CONNECTION string.
The remote server must be a MySQL server.
The remote table must exist before you try to access the table through the FEDERATED table.