转自:http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
在MySQL中使用 federated 表,创建一个远程库链路
1、mysql>show engines;
+------------+---------+
| Engine | Support |
+------------+---------+
| ndbcluster | NO |
| MRG_MYISAM | YES |
| BLACKHOLE | YES |
| CSV | YES |
| MEMORY | YES |
| FEDERATED | NO |
| ARCHIVE | YES |
| InnoDB | YES |
| MyISAM | DEFAULT |
可以看到 未启用federated引擎
2、启用federated引擎
在my.cnf -> mysqld 添加:federated
重启数据库
3、建远程表
假设远程库表为:
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=utf8;
本地库建表:
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=utf8
CONNECTION='mysql://fed_user@remote_host:3306/federated/test_table';
connection 格式:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
如果password、user_name中有特殊字符,需要使用URL encoding进行编码