转自: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进行编码