This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, we have seen a basic theory and configuration of FEDERATED table. In this post, I am going to create a FEDERATED table which is used to SELECT data from another MySQL Server.
How to create FEDERATED Tables?
Assume that we have two running servers, either both on the same host or different hosts.
For example:
Server – A108 (Remote MySQL Server)
Server – B108 (Local MySQL Server)
We need to execute the SELECT command from Server B108 and fetching data from Server A108.
Here, Server A108 is a remote server and Server B108 is a local server.
Now create a sample table in Server A108.
|
1 2 3 4 5 6 |
CREATE TABLE tbl_RemoteTable ( ID integer ,Name VARCHAR(255) ) ENGINE=InnoDB; |
Now, create a FEDERATED table in Server B108 to SELECT data from Server A108.
You have to specify FEDERATED engine with remote CONNECTION information.
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_RemoteTable ( ID integer ,Name VARCHAR(255) ) ENGINE=FEDERATED CONNECTION='mysql://root:root@localhost:3306/B108/tbl_RemoteTable'; |
As you can see, I created FEDERATED table into Local Server B108 using the connection information of Remote Server A108.
You can access this table like any other table, and if any data insert or delete in remote Table, it automatically updates here because this is a virtual table.
Below are different format of connection strings:
|
1 2 3 |
CONNECTION='mysql://username:password@hostname:port/database/tablename' CONNECTION='mysql://username@hostname/database/tablename' CONNECTION='mysql://username:password@hostname/database/tablename' |
