This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous list of articles, I demonstrated FEDERATED storage engine to perform cross-server queries between MySQL Servers.
MySQL: Create FEDERATED Table to SELECT data from another server (Part 3/4)
In this post, I am showing to create the Connection Server and use that Server detail with multiple FEDERATED Table of MySQL.
Without a Connection server, we need to write the connection string in every FEDERATED table so when we have a multiple FEDERATED table and which are using same remote connection information, create Connection Server once and use with all tables.
Below is a small demonstration.
First Create a Server:
1 2 3 |
CREATE SERVER FederatedConnectionServer FOREIGN DATA WRAPPER mysql OPTIONS (USER 'UserName',password 'Password', HOST 'localhost', PORT 3306, DATABASE 'TestDB'); |
Now use this server information to create Remote FEDERATED Table:
1 2 3 4 5 6 7 |
CREATE TABLE tbl_RemoteTable ( ID integer ,Name VARCHAR(255) ) ENGINE=FEDERATED CONNECTION='FederatedConnectionServer/tbl_RemoteTable'; |