Creating a FEDERATED Table on MySQL

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