The FEDERATED Storage Engine

The FEDERATED Storage Engine
1,Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.
FEDERATED存储引擎的表会自动从远端表抽数据，本地并不保持数据

2,The FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the --federated option.
FEDERATED存储引擎默认不开启，需要在MySQL server启动时用参数--federated才能开启

3,When you create a FEDERATED table, the table definition is the same, but the physical storage of the data is handled on a remote server.
当你创建FEDERATED表，表的定义是相同的，但数据的物理存储是放在远端的服务器上


A FEDERATED table consists of two elements:
1，A remote server with a database table, which in turn consists of the table definition (stored in the .frm file) and the associated table.including MyISAM or InnoDB.
2，A local server with a database table, where the table definition matches that of the corresponding table on the remote server. The table definition is stored within the .frm file.

When executing queries and statements on a FEDERATED table on the local server, the operations that
would normally insert, update or delete information from a local data file are instead sent to the remote
server for execution, where they update the data file on the remote server or return matching rows from the
remote server.


How to Create FEDERATED Tables
1，远程服务器创建表
2，本地FEDERATED创建表，并添加connection信息用于本地与远程表之间的链接

与远程表链接方法：
1，在建表时创建connection
2, 用之前创建好的server

一，Creating a FEDERATED Table Using CONNECTION
一，用CONNECTION创建FEDERATED表
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';

The format of the connection string is as follows:
connection字符串格式如下：
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

二，Creating a FEDERATED Table Using CREATE SERVER
The format of the CREATE SERVER statement is:
创建SERVER语句的格式如下：
CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

The server_name is used in the connection string when creating a new FEDERATED table.
server_name被用connection字符串，当创建新的FEDERATED表时

For example, to create a server connection identical to the CONNECTION string:
例如，创建一个 server connection 相对于CONNECTION string
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

You would use the following statement:
你用如下语句：
CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');

To create a FEDERATED table that uses this connection, you still use the CONNECTION keyword, but specify the name you used in the CREATE SERVER statement.
为了创建FEDERATED表用connection，你依然要用CONNECTION关键字，并指定创建的SERVER，如下
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=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';