mysql实现类似于oracle dblink的功能

阳光穿透心脏的1/2处 2022-01-31 10:15 296阅读 0赞

![收藏代码][Image 1]![Image 1][]

1.  federated   
2.  重启mysql服务后，  
3.  mysql> show engines;

federated 
    重启mysql服务后，
    mysql> show engines;

建立连接处理  
  
1 引擎说明  
本地MySQL数据库要访问远程MySQL数据库的表中的数据, 必须通过FEDERATED存储引擎来实现. 有点类似Oracle中的  
数据库链接(DBLINK). 要允许这个存储引擎, 当构建MySQL时使用--with-federated-storage-engine来configure.   
当创建一个FEDERATED表的时候, 服务器在数据库目录创建一个表定义文件. 文件由表的名字开始, 并有一个.frm扩展名.  
无其它文件被创建, 因为实际的数据在一个远程数据库上. 使用FEDERATED表的步骤是非常简单的. 通常, 运行两个服务器,  
要么在同一个主机上, 要么在不同主机上.  
首先, 必须在想要用FEDERATED表访问的远程服务器上有一个表. 假设, 远程的表在DB\_FED数据库中并且被如下定义:   
Sql代码

![收藏代码][Image 1]![Image 1][]

1.  CREATE TABLE test\_table (  
2.      id     int(20) NOT NULL auto\_increment,  
3.      name   varchar(32) NOT NULL default '',  
4.      other  int(20) NOT NULL default '0',  
5.      PRIMARY KEY  (id),  
6.      KEY name (name),  
7.      KEY other\_key (other)  
8.  ) ENGINE=MyISAM;

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),
        KEY name (name),
        KEY other_key (other)
    ) ENGINE=MyISAM;

接着, 在本地服务器上为访问远程表创建一个FEDERATED表:   
Sql代码

![收藏代码][Image 1]![Image 1][]

1.  CREATE TABLE federated\_table (  
2.      id     int(20) NOT NULL auto\_increment,  
3.      name   varchar(32) NOT NULL default '',  
4.      other  int(20) NOT NULL default '0',  
5.      PRIMARY KEY  (id),  
6.      KEY name (name),  
7.      KEY other\_key (other)  
8.  ) ENGINE=FEDERATED CONNECTION='mysql://username:password@remote\_host:3306/db\_name/table\_name';

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),
        KEY name (name),
        KEY other_key (other)
    ) ENGINE=FEDERATED CONNECTION='mysql://username:password@remote_host:3306/db_name/table_name';

除了ENGINE选项应该是FEDERATED, 并且CONNECTION表选项是给FEDERATED指明如何连接到远程服务器上的连接字符串之外,  
这个表的结构必须完全与远程表的结构相同.   
远程主机信息指明本地服务器要连接到的远程服务器, 数据库和表信息指明哪一个远程表要被作为数据文件来用. 在这个例子中.  
远程服务器被指定来作为远程主机在9306端口上运行, 所以要启动服务器, 让它监听9306端口.   
在CONNECTION选项中的连接字符串的一般形式如下(密码和端口号是可选的):   
mysql://user\_name\[:password\]@host\_name\[:port\_num\]/db\_name/tbl\_name  
这里有一些连接字符串的例子:   
CONNECTION='mysql://username:password@hostname:port/database/tablename'  
CONNECTION='mysql://username@hostname/database/tablename'  
CONNECTION='mysql://username:password@hostname/database/tablename'  
因为任何被用的密码作为纯文本被存在连接字符串中, 它可以被任何使对FEDERATED表使用SHOW CREATE TABLE或 SHOW TABLE STATUS的用户, 或者在INFORMATION\_SCHEMA数据库中查询TABLES表的用户看见.   
FEDERATED支持及不支持的如下:   
·远程服务器必须是一个MySQL服务器. FEDERATED对其它数据库引擎的支持可能会在将来被添加.   
·FEDERATED表指向的远程表在通过FEDERATED表访问它之前必须存在.   
·一个FEDERATED表指向另一个FEDERATED表是可能的, 但是必须小心不要创建一个循环.   
·没有对事务的支持.   
·如果远程表已经改变, 对FEDERATED引擎而言是没有办法知道的. 这个的原因是因为这个表必须象数据文件一样工作,  
除了数据库其它任何都不会被写入. 如果有任何对远程数据库的改变, 本地表中数据的完整性可能会被破坏.   
·FEDERATED存储引擎支持SELECT,INSERT,UPDATE,DELETE和索引. 它不支持ALTER TABLE,DROP TABLE或任何  
其它的数据定义语言语句. 当前的实现不使用预先准备好的语句.   
·执行使用SELECT,INSERT,UPDATE和DELETE, 但不用HANDLER.   
·FEDERATED表不能对查询缓存不起作用.   
这些限制中的一些在FEDERATED处理机的将来版本可能被消除.   
  
2 使用示例  
MySQL实例A(主机192.168.41.196)下, 建立对远程MySQL实例B(主机192.168.41.197)的表db\_fed.tab\_test访问.  
  
1) MySQL实例B建立测试表  
Sql代码

![收藏代码][Image 1]![Image 1][]

1.  mysql> create database db\_fed;  
2.  mysql> use db\_fed  
3.  mysql> CREATE TABLE tab\_test (  
4.      id     int(20) NOT NULL auto\_increment,  
5.      name   varchar(32) NOT NULL default '',  
6.      PRIMARY KEY (id)  
7.  ) ENGINE=MyISAM;

mysql> create database db_fed;
    mysql> use db_fed
    mysql> CREATE TABLE tab_test (
        id     int(20) NOT NULL auto_increment,
        name   varchar(32) NOT NULL default '',
        PRIMARY KEY (id)
    ) ENGINE=MyISAM;

Sql代码

![收藏代码][Image 1]![Image 1][]

1.  mysql> insert into tab\_test set name = 'test1';  
2.  mysql> insert into tab\_test set name = '测试1';  
3.  mysql> select \* from tab\_test;  
4.  \+----+-------+  
5.  | id | name  |  
6.  \+----+-------+  
7.  |  1 | test1 |  
8.  |  3 | 测试1 |  
9.  \+----+-------+

mysql> insert into tab_test set name = 'test1';
    mysql> insert into tab_test set name = '测试1';
    mysql> select * from tab_test;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | test1 |
    |  3 | 测试1 |
    +----+-------+

\--建立一个用户专门用于远程federated引擎访问, 这个用户的权限决定了远程federated表的操作权限:  
mysql> grant select on db\_fed.\* to u\_fed@192.168.41.196 identified by 'iamwangnc';  
  
2) MySQL实例A建立FEDERATED引擎表  
\--确认FEDERATED引擎已启用:  
mysql> show engines;  
...  
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |  
...  
  
\--如果未启用, 先确保configure MySQL时加了--with-federated-storage-engine选项, 另外还确保启动mysqld时加了--federated参数,  
或者在my.cnf里\[mysqld\]段后面加了federated参数, 如下, 然后重启mysqld:  
\--------------------------------  
\[mysqld\]  
federated  
\--------------------------------  
  
\--在某个库下建立FEDERATED引擎表:  
mysql> create database db\_test;  
mysql> use db\_test  
mysql> CREATE TABLE tab\_test\_fed (  
    id     int(20) NOT NULL auto\_increment,  
    name   varchar(32) NOT NULL default '',  
    PRIMARY KEY (id)  
) ENGINE=FEDERATED connection = 'mysql://u\_fed:iamwangnc@192.168.41.197:3306/db\_fed/tab\_test';  
  
\--测试是否可以访问:  
mysql> select \* from tab\_test\_fed;  
\+----+-------+  
| id | name  |  
\+----+-------+  
|  1 | test1 |  
|  3 | 测试1 |  
\+----+-------+  
  
\--测试是否可以修改:  
mysql> delete from tab\_test\_fed;  
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1142: DROP command denied to user 'u\_fed'@'comp196'  
for table 'tab\_test'' from FEDERATED

[Image 1]: