MySQL使用federated 实现Oacle链路效果

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

 

 

 

评论

1 楼 hejianhuacn 2013-03-20  

出现连接拒绝，需要注意的参数：

Check the number of active connections on the remote/destination server.

Looking at the source code (of newer versions) I think it's a good bet that the "Too many connections" message is actually being returned from the remote end -- as opposed to originating on the local server (where the FEDERATED engine is being used).

SHOW STATUS LIKE '%connect%';
Threads_connected is the current count, Max_used_connections is the highest value seen since the last time FLUSH STATUS was issued.

If that max value is the same as (or 1 more than) the value returned by SHOW VARIABLES LIKE 'max_connections'; then at some point that server was maxed out and could have returned this message.

If the remote server shows a lot of idle inbound federated connections, you may find that issuing FLUSH TABLES on the local server will release them.