Software Technology Tips

Now a days we need to handle/correlate bulk of data those are located in different remote database servers. For handle this type of situtation, MySQL 5.0 and up provide us an option "FEDERATED STORAGE ENGINE". This storage engine will give us facility for link more than one database tables those are exist in  same or different server and handle most of the database operation in local federated table, which is a reflection of the remote database table.

When we set an table engine as FEDERATED,  it will act like an pointer to the actual database table stored in different location. For that FEDERATED table MySQL only create an .frm extension file in the MySQL data folder for the current database. This is due to the reason that FEDERATED table is not keeping the actual data, it just keep the data definition.

If we will create a table in database with MyISAM or Innodb storage engine  it will generally creates three file with the name of its table name along with extension .frm, .MYD and .MYI in the MySQL data folder.
 
File ExtensionPurpose
.frm table definition
.MYD table data
.MYI table indices
 
Configuration:
You can check whether "FEDERATED" option is enabled or not by running a command "SHOW ENGINES" in Query Browser. It will show list of storage engines with its detail information's.
 
Engine Support Comment Transactions XA Savepoints
MyISAM DEFAULT Default engine as of MySQL 3.23 with great perform... NO NO NO
CSV YES CSV storage engine NO NO NO
MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO
BLACKHOLE YES /dev/null storage engine (anything you write to it... NO NO NO
FEDERATED NO Federated MySQL storage engine NULL NULL NULL
InnoDB YES Supports transactions, row-level locking, and fore... YES YES YES
ARCHIVE YES Archive storage engine NO NO NO
MEMORY YES Hash based, stored in memory, useful for temporary... NO NO NO
 
If FEDERATED engine with its support is "NO" then it specify that your currently running MySQL server is not yet enabled FEDERATED option. For enable the FEDERATED storage engine you need to do the below mentioned change in  my.ini"(WINDOW) or "my.cnf"(LINUX) file.
 
# Disable Federated by default
# skip-federated
federated
 
and after save the above change need to restart MySQL Server.
 
Then again run the "SHOW ENGINES" command. Now it will show:
 
Engine Support Comment Transactions XA Savepoints
MyISAM DEFAULT Default engine as of MySQL 3.23 with great perform... NO NO NO
CSV YES CSV storage engine NO NO NO
MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO
BLACKHOLE YES /dev/null storage engine (anything you write to it... NO NO NO
FEDERATED YES Federated MySQL storage engine NO NO NO
InnoDB YES Supports transactions, row-level locking, and fore... YES YES YES
ARCHIVE YES Archive storage engine NO NO NO
MEMORY YES Hash based, stored in memory, useful for temporary... NO NO NO
 
 
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:mysqlpass@192.168.1.222: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:


Related Tags:

Database, MySQL 5.0

Author: Madhusmita Pradhan

Database

Let us Connect!

iso 9001 QA25 Red Herring STPI D&B Fastest Growing SME 2013 Award zinnov Nasscom

copyright (c) Mindfire Solutions 2007-2014. Login