📢作者： 小小明-代码实体

📢博客主页：https://blog.csdn.net/as604049322

📢欢迎点赞 👍 收藏 ⭐留言 📝 欢迎讨论！

📢本文链接：https://xxmdmst.blog.csdn.net/article/details/128615950

需求背景

公司给了多张表在不同的数据库中，一般情况下我们已经无法对这多张表进行表连接查询，而是借助其他编程语言处理。但是如果我们本地有一个开启了federated引擎的MySQL数据库，就可以轻松关联其他MySQL服务器上的表。

federated引擎可将本地数据表映射至远程 MySQL 数据表，Federated引擎是基于表级别的，只能将本地数据表定义为 Federated 引擎并映射至远程实体表，无法实现基于库级别的整体映射。

开启federated引擎

对于公司给的多个数据库，我们自己往往没有开启federated引擎的权限，但我们可以在自己本地数据库关联其他MySQL数据库。

默认情况下federated引擎未开启：

show engines;

修改my.ini的[mysqld]配置项，增加如下选项：

federated=1

重启MySQL服务后再次查询引擎列表：

可以看到已经成功启动。

关联其他数据库的表

第一个MySQL数据库db1中存在Employee表，内容如下：

| employee_id | department_id |
|-------------|---------------|
| 1           | 1             |
| 2           | 2             |
| 3           | 2             |

可以获取出表创建语句：

show create table employee;

Table     Create Table
--------  -------------------------------------------------------------
employee  CREATE TABLE `employee` (`employee_id` int(11) DEFAULT NULL,`department_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

对于db1中的Employee表，我们可以直接关联：

create table `employee` (`employee_id` int(11) default null,`department_id` int(11) default null
) engine=federated connection='mysql://root:密码@db1的ip或域名:3306/test/employee';

connection中正确填入目标数据库的用户名密码即可。

注意：本地链接表字段只能比远程表的少，不能多，尽量与远程表保持一致。

相关文档：

• https://dev.mysql.com/doc/refman/8.0/en/federated-create-connection.html
• https://dev.mysql.com/doc/refman/8.0/en/federated-usagenotes.html

密码存在特殊字符如何关联

第二个MySQL数据库db2中存在salary表，内容如下：

| id | employee_id | amount | pay_date   |
|----|-------------|--------|------------|
| 1  | 1           | 9000   | 2017-03-31 |
| 2  | 2           | 6000   | 2017-03-31 |
| 3  | 3           | 10000  | 2017-03-31 |
| 4  | 1           | 7000   | 2017-02-28 |
| 5  | 2           | 6000   | 2017-02-28 |
| 6  | 3           | 8000   | 2017-02-28 |

获取表创建语句：

show create table salary;

Table   Create Table
------  ---------------------------------------------------------------
salary  CREATE TABLE `salary` (`id` int(11) DEFAULT NULL,`employee_id` int(11) DEFAULT NULL,`amount` int(11) DEFAULT NULL,`pay_date` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

不过该数据库的密码为Test123@qq，涉及特殊字符，无法使用连接字符串关联表。但是我们可以先创建相应的server。

相关文档：

• https://dev.mysql.com/doc/refman/8.0/en/create-server.html
• https://dev.mysql.com/doc/refman/8.0/en/federated-create-server.html

我们首先创建对应服务器的server：

create server rds_aliyuncs
foreign data wrapper mysql
options(user 'test456',host 'rm-xxx.mysql.rds.aliyuncs.com',
database 'test',port 3306,password 'Test123@qq');

创建后可以查看所有已创建的server：

select * from mysql.servers;

然后就可以基于上述已创建的rds_aliyuncs服务关联表：

create table `salary` (`id` int(11) default null,`employee_id` int(11) default null,`amount` int(11) default null,`pay_date` date default null
) engine=federated connection='rds_aliyuncs/salary';

测试跨MySQL服务器查询

最终我们测试一下：

selectleft(pay_date,7) pay_month,department_id,avg(amount) amount
from salary a join employee b
using(employee_id)
group by left(pay_date,7),department_id
with rollup;

pay_month  department_id  amount     
---------  -------------  -----------
2017-02                1  7000.0000  
2017-02                2  7000.0000  
2017-02           (NULL)  7000.0000  
2017-03                1  9000.0000  
2017-03                2  8000.0000  
2017-03           (NULL)  8333.3333  
(NULL)            (NULL)  7666.6667  

最终顺利的实现了跨服务器查询。

而且在本地数据库中修改federated引擎关联的远程数据库的数据都会顺利同步原始数据库。

缺点在于无法同步表结构，只能同步表数据，若源表结构发生变化，应该重新创建对应结构的federated表。