How to implement data integration using federated servers in mysql?

Question

How to do data integration between two servers using MYSQL federated servers?

Answer 1

For these several steps, we need to follow. Let us assume there are two servers s1 & s2 and we are having the public IP and private IP both and we are taking Linux servers only.

private IP of s1 is ps1 and for s2 is ps2 public IP of s1 is pbs1 for s2 is pbs2

and two databases are dbs1 and dbs2

If the servers are not on the same LAN then we must use public IP and if in the same LAN then we can use any one of them. But, private IP usage is more secure.

Step 1: We need to enable the federated engine in MYSQL by using this in the file my.cnf

"#bind-address=127.0.0.1 federated"

Step 2:

We need to give permission to MySQL database user of server s1 so that it can access the MySQL database of server s2 and also

We need to give permission to MySQL database user of server s2 so that it can access the MySQL database of server s1

Step 3: We need to create federated servers in server s1 and server s2 by using the following script

in server s2 we will create the federated server for s1 so that we can update the MySQL database of s2 from server s1.

similarly, in server s1 we will create the federated server for s2 so that we can update the MySQL database of s1 from server s2.

assuming two servers are feds1 and feds2

to create feds1 in server s2 we have to run the following script

CREATE SERVER feds1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'dbuser', HOST 'ps1/pbs1', PORT 3306, DATABASE 'dbs1');

to create feds2 in server s1 we have to run the following script

CREATE SERVER feds2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'dbuser', HOST 'ps2/pbs2', PORT 3306, DATABASE 'dbs2');

Step 4:

We have to create federated tables in both the servers.

Assuming that we have to update table fedtab1 of the server s1 from the server s2 and update table fedtab2 of the server s2 from the server s1

Before creating federated tables we have to keep in mind that the main table should be of InnoDB engine otherwise we will not be able to create federated tables.

So before creating federated table fedtab1 in the server s2 table should exist in the server s1 and with the InnoDB engine and same applies for the fedtab2 of server s2.

then we have to create federated tables using the following script

//fedtab1_f federated table in server s2 CREATE TABLE fedtab1_f ( All/selective columns of fedtab1 table in server s1 ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='feds1/fedtab1';

//fedtab2_f federated table in server s1 CREATE TABLE fedtab2_f ( All/selective columns of fedtab2 table in server s2 ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='feds2/fedtab2';

Step 5:

We have to implement data integration logic. It can be using triggers or PHP scripts.