MYSQL建立dblink

mysql的dblink就是访问目标数据库，通过dblink连接的方式来访问到源数据库的数据。

这里源端为远程服务器，目标端为本地

查看本地mysql的有没有federated 引擎：
mysql> show engines;

+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to
it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+

安装了federated，但没启动。（如果没有安装FEDERATED 引擎        执行install plugin federated soname 'ha_federated.so';）

在mysql目录下的my.ini文件里添加一行

federated

重启mysql服务，然后查看federated 

mysql> show engines;

+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to
it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine
| NO | NO | NO |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+

看到已开启

在本地创建和远程数据库的表的表结构一致的表，以及远程数据库的连接：例如

CREATE TABLE dblink_view (
bank_name VARCHAR(50),
sys_bank_code VARCHAR(12),
PROV_INPUT_NAME VARCHAR(20),
NAME VARCHAR(10)
)ENGINE=FEDERATED CONNECTION='mysql://bankinfo:bankinfo@192.168.3.42:3307/t2_cpv2/view_bt_input_bank_info';

 engine=federated connection = 'mysql://用户:密码@IP地址:端口/库名称/表名称';

完成后就可以本地数据库查到远程库数据：SELECT * FROM dblink_view；