Syntax:
CREATE TABLE table_name ( 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) ) ENGINE=FEDERATED
DEFAULT CHARSET=charset_name
CONNECTION=’scheme://user_name[:password]
@host_name[:port_num]/db_name/tbl_name’;
Sample connection strings:
CONNECTION=’mysql://username:password@hostname:port/database/tablename’ CONNECTION=’mysql://username@hostname/database/tablename’
CONNECTION=’mysql://username:password@hostname/database/tablename’
Example:
Suppose we need to access a table “audit_log”, which is located in a different database server with in the database “users”. For this we need to create a table (e.g. “audit_log_federated”) in local database server with FEDERATED storage engine in below mentioned way:
Actual table “audit_log” definition in “users” database on the “192.168.1.222:3306” server(Remote database server):
CREATE TABLE `users`.`audit_log` ( `audit_log_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `activity` varchar(200) NOT NULL, `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`audit_trail_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=utf8;
Federated table “audit_log_federated” definition in “users” database on the local database server.
CREATE TABLE `users`.`audit_log_federated` ( `audit_log_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `activity` varchar(200) NOT NULL, `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`audit_trail_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=utf8
CONNECTION=
‘mysql://mysqluser:[email protected]:3306/users/audit_log’;
Now you are all set to do SELECT, INSERT, UPDATE and DELETE operation on local FEDERATED table, which will effect the remote data.
Reference:
http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html