FEDERATED Storage Engine

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:

over_view_federated_engine

How Enable the Federated Engine:

Login into MySQL server and check whether Federated engine enabled or not.

show_engines

If its not enabled then enable it same via my.cnf as below
Vi /etc/my.cnf

enable_my.cnf

Verify the engines after bounced/re-start the MySQL.

show_engines_enabled

How to create Federated Engine Table Using Connection

On source server(MySQL 3306 Instance):
The format of the connection string is as follows:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

source_create_Ftable

Federated table structure:

Ftable_in_server

Create a Federated Table using Create server:

Create server syntax:
CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] …)
9_1
Federated table creation using create server option:
9_2
9_3

On destination server:

10_1

The remote table physical structure:
10_2

Try to insert the data in source table:
11_1_edited
Give access on remote server:
11_2
Now try to insert the Data on source table:
12_1_edited
Retrieve the data from source tables(But actually it take from destination table “destination_table”)
12_2_edited

Changing table structure on destination server:
13_1
Table structure on Source:
13_2
Now try to SELECT or INERT on source table.
14_1_edited
Select the data from destination table:
15_1_edited

Security Issue:

Those who are all having basic SELECT access on Federated table they can easily find remote table credentials.

16_1
TO solve the security issue, Create a view from different schema.
17_1
switch to user DB:
17_2_edited

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.

Advertisements