2.3.6 Federate 远程访问数据库
1）允许本地访问远程mysql数据库中表的数据
2）本地只存結构，不存数据
3）类似于 oracle dblink
4）Federate存储引擎默认不开启，如果需要开启需要在my.cnf加入：
[mysqld]
federatedshow engines
5）mysql 中的 Federate目前不支持异构数据库，但 mariadb 中的 FederateX 支持
注意事项：
1）本地表的结构必须与远程的完全一样。
2）远程数据目前只能是 mysql
3）不支持事务
4）不支持表结构的修改
语法：
在建表语句尾部加入：
engine=federated connection= 'MYSQL://用户名:密码@IP:端口/数据库名/表名"
案例学习如何使用 Federate
1.查看存储引擎
show engines
2.配置启动 federated
my.cnf 加入以下参数后重启：
[mysqld]
federated
mysql root@localhost:(none)> show engines
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
create database itpuxdb1
create database itpuxdb2
create database itpuxdb3
grant all privileges on itpuxdb2.* to lewen@'%' identified by 'lewen'
grant all privileges on itpuxdb3.* to lewen@'%' identified by 'lewen'
flush privileges 在 itpuxdb2 上面
use itpuxdb2;
create table itpuxt2(id int not null auto_increment primary key,name char(20) not null) engine myisam;在 itpuxdb3 上面
use itpuxdb3
create table itpuxt3(id int not null auto_increment primary key,name char(20) not null) engine myisam;在 itpuxdb1 上面
use itpuxdb1
CREATE TABLE itpuxt12 ( id INT NOT NULL auto_increment PRIMARY KEY, NAME CHAR ( 20 ) NOT NULL ) ENGINE federated CONNECTION = 'mysql://root:root@10.0.0.33:33057/itpuxdb2/itpuxt2';
CREATE TABLE itpuxt13 ( id INT NOT NULL auto_increment PRIMARY KEY, NAME CHAR ( 20 ) NOT NULL ) ENGINE federated CONNECTION = 'mysql://root:root@10.0.0.33:33057/itpuxdb3/itpuxt3';
软连接
insert into itpuxt12(name) values('乐乐2')
insert into itpuxt13(name) values('乐乐3')
mysql root@localhost:itpuxdb1> select itpuxt12.*,itpuxt13.* from itpuxt12,itpuxt13 where itpuxt12.id=itpuxt13.id
+----+-------+----+-------+
| id | NAME | id | NAME |
+----+-------+----+-------+
| 1 | 乐乐2 | 1 | 乐乐3 |
+----+-------+----+-------+
insert into itpuxt12(name) values('乐乐22')
insert into itpuxt13(name) values('乐乐33')
insert into itpuxt12(name) values('乐乐222')
insert into itpuxt13(name) values('乐乐333')
mysql root@localhost:itpuxdb1> select itpuxt12.*,itpuxt13.* from itpuxt12,itpuxt13 where itpuxt12.id=itpuxt13.id
+----+---------+----+---------+
| id | NAME | id | NAME |
+----+---------+----+---------+
| 1 | 乐乐2 | 1 | 乐乐3 |
| 2 | 乐乐22 | 2 | 乐乐33 |
| 3 | 乐乐222 | 3 | 乐乐333 |
+----+---------+----+---------+
mysql root@localhost:(none)> SELECT * FROM itpuxdb2.itpuxt2 a,itpuxdb3.itpuxt3 b where a.id=b.id
+----+---------+----+---------+
| id | name | id | name |
+----+---------+----+---------+
| 1 | 乐乐2 | 1 | 乐乐3 |
| 2 | 乐乐22 | 2 | 乐乐33 |
| 3 | 乐乐222 | 3 | 乐乐333 |
+----+---------+----+---------+
