The FEDERATED Storage Engine
The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.
mysql> show engines;
| FEDERATED | NO | Federated MySQL storage engine
vi /etc/mysql/my.cnf
under [mysqld]
federated
mysql> show engines;
| FEDERATED | YES | Federated MySQL storage engine
/etc/init.d/mysql restart
SAMPLE 1
CREATE TABLE destination_databasename.`table_name_1_dtl` (
Provider_ID VARCHAR(100) NOT NULL,
Provider_Name VARCHAR(250) NOT NULL,
Provider_Type VARCHAR(100) NOT NULL,
Address VARCHAR(255) DEFAULT NULL,
District_Name VARCHAR(100) NOT NULL,
Provider_Phone_Number VARCHAR(50) NOT NULL,
Specialty_Name VARCHAR(100) NOT NULL,
Availability_of_Pharmacy_or_ChemistShop_within_Hospital TINYINT(1) DEFAULT NULL,
Laboratory TINYINT(1) DEFAULT NULL,
Empanelment_Status VARCHAR(50) DEFAULT NULL,
Empanelment_Valid_Upto DATE DEFAULT NULL,
De_Empaneled_Reason VARCHAR(250) DEFAULT NULL,
Contact_Person VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (Provider_ID)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='mysql://root:ussgnovbl5r@localhost:3306/source_databasename/table_1_name_dtl';
SAMPLE 2
CREATE TABLE destination_databasename.`table_name_2_dtl` (
Package_ID VARCHAR(100) CHARACTER SET latin1 NOT NULL,
Dept_ID VARCHAR(100) CHARACTER SET latin1 DEFAULT NULL,
DeptSI_No VARCHAR(200) CHARACTER SET latin1 DEFAULT NULL,
Package_Type VARCHAR(200) CHARACTER SET latin1 NOT NULL,
Category VARCHAR(200) DEFAULT NULL,
Specialty VARCHAR(200) CHARACTER SET latin1 DEFAULT NULL,
Sub_Specialty VARCHAR(200) CHARACTER SET latin1 DEFAULT NULL,
Package_Name VARCHAR(255) CHARACTER SET latin1 NOT NULL,
Length_of_Stay VARCHAR(50) DEFAULT NULL,
Rate_Card_A DECIMAL(65,2) NOT NULL,
Rate_Card_B DECIMAL(65,2) DEFAULT NULL,
Rate_Card_C DECIMAL(65,2) DEFAULT NULL,
PRIMARY KEY (Package_ID),
KEY Package_Type (Package_Type),
KEY Package_Name (Package_Name),
KEY Rate_Card_A (Rate_Card_A,Rate_Card_B,Rate_Card_C)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='mysql://root:ussgnovbl5r@localhost:3306/source_databasename/table_2_name_dtl';
SAMPLE 3
CREATE TABLE destination_databasename.`table_name_3_dtl` (
Preauth_Status VARCHAR(100) DEFAULT NULL,
Total BIGINT(21) NOT NULL DEFAULT '0',
Approved_Amount DECIMAL(65,2) DEFAULT NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='mysql://root:ussgnovbl5r@localhost:3306/source_databasename/table_2_name_dtl';
Ref:- http://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html