MySql FEDERATED storage engine :Data to be accessed from a remote MySQL database on a local server without using replication or cluster technology

MySql FEDERATED storage engine :-

Than enable you to create table into local server Database and allow access data from Remote Server Database without using replication or cluster technology.

Use FEDERATED Tables :-

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

The example uses a MyISAM table, but the table could use any storage engine.
Next, create a FEDERATED table on the local server for accessing the remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)


Check FEDERATED Engine enable or not in database :-

mysql> show engines;
+------------+---------+
| Engine     | Support | 
+------------+---------+
| ndbcluster | NO      | 
| MRG_MYISAM | YES     | 
| BLACKHOLE  | YES     | 
| CSV        | YES     | 
| MEMORY     | YES     | 
| FEDERATED  | NO      | 
| ARCHIVE    | YES     | 
| InnoDB     | YES     | 
| MyISAM     | DEFAULT |

For Enable federated Engines into MYSQL :-

 

Add Below Line into my.ini or my.cnf :-

 

 [mysqld]
federated

 

Then Restart MySQL Server.