federated引擎实现mysql跨服务器表连接

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

📢作者 小小明-代码实体

📢博客主页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中正确填入目标数据库的用户名密码即可。

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

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

第二个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。

测试跨MySQL服务器查询

最终我们测试一下

select
	left(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表。

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6