What are Federated tables in Mysql?

Question

How can I insert data into one table from two other tables where i have three tables namely users, role and userrole.
Now I want to insert the data into userrole table from users table and role table with a single statement.

Answer 1

A federated database system is a type of meta-database management system (DBMS), which transparently maps multiple autonomous database systems into a single federated database.

A Federated Table is a table which points to a table in another MySQL database instance (mostly on another server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links.

Federated Table Creation : It has two elements :

Remote server : Data is physically stored in this server
Local Server : No data is stored here,simply you can fire the query and you will be able to see your required output.

Example :

Suppose you have a table on the Remote Server (say Master Table : “student_test”)
Data Base Name is : STUDENT_DB that you want to access by using a Federated table

CREATE TABLE student_test(

sid     INT(20) NOT NULL AUTO_INCREMENT,

sname   VARCHAR(32) NOT NULL DEFAULT ,

PRIMARY KEY  (id),

)ENGINE=MyISAM

DEFAULT CHARSET=latin1;

1. Next,You have to create a table on the local database server(say Federated Table :”Student_federated)” DataBase Name is STUDENT_FEDERATED to access the data on the Master Table

   CREATE TABLE Student_federated(

   sid INT(20) NOT NULL AUTO_INCREMENT,

   sname VARCHAR(32) NOT NULL DEFAULT ,

   PRIMARY KEY (id),

   )ENGINE=FEDERATED

   DEFAULT CHARSET=latin1;

   CONNECTION=’scheme://username:password@hostname:port/DBname/TableName;

For the above scenario , The connection String should be :

CONNECTION=’mysql://root:root@192.168.2.9:3306/STUDENT_DB /Student_test;