MySQL FEDERATED 存储引擎介绍及使用

发表于 本文字数： 973 阅读时长 ≈ 4 分钟 分类于 标签：

项目上遇到了一个需求，需要在 MySQL（不同实例）进行跨库 join 操作。我们都知道如果是同一实例，那么 MySQL 跨库查询肯定是没有问题的，但是不同实例的跨库查询之前一直没有做过。不过以前还在用 Oracle 的时候，Oracle 有个功能叫做 DATABASE LINK（参见 CREATE DATABASE LINK），是将远程数据库映射到本地，这样就可以在一个实例中访问其他数据库了。在谷歌搜索 MySQL 跨库查询的时候，发现 MySQL 也有类似的东西，叫做 FEDERATED 存储引擎。所以我们就可以使用 FEDERATED 将需要 join 的远端表映射到本地数据库，这样在做查询的时候使用上就跟本地表一样了。

官方文档：The FEDERATED Storage Engine

FEDERATED 表介绍

FEDERATED 表在使用上与普通表没有区别，不过 FEDERATED 表只会在本地存储表结构定义而不会直接存储数据，所以在每次查询时数据均需要从远端获取。
FEDERATED 表支持 DML 语句，DDL 语句只支持 DROP TABLE，效果为删除本地的 FEDERATED 表。

官方架构如下：

准备工作

在使用 FEDERATED 表前，需要先看一下是否满足条件：

  • 创建 FEDERATED 表的 MySQL 实例需要支持 FEDERATED 引擎，如果是安装的二进制包则没有问题，如果是从源码编译，需要在编译时加上 -DWITH_FEDERATED_STORAGE_ENGINE 参数
  • 创建 FEDERATED 表的 MySQL 实例需要打开 FEDERATED 引擎支持，MySQL 默认没有打开，需要在启动时追加 --federated 命令，或是在配置文件的 [mysqld] 小节添加 federated 并重启服务器。可以在 MySQL 中执行 SHOW ENGINES 语句来查询支持状态
  • FEDERATED 表支持的远端数据库只能为 MySQL，同时支持 MyISAM 和 InnoDB 存储引擎
  • 经测试，5.7 版本的数据库连接 8.0 版本会报 SSL 错误，所以建议使用 8.0 版本

创建 FEDERATED 表

创建 FEDERATED 表有两种方式，但是大同小异，区别只是远端数据库连接是存储在表定义中还是存储到单独的位置。因为 FEDERATED 是表维度，所以为了复用远端连接，建议采用 CREATE SERVER 的方式创建 FEDERATED表。

使用 CREATE SERVER 的方式创建 FEDERATED 表

首先我们需要先创建远端数据库连接，创建远端连接的格式为：

1
2
3
4
CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

例如：

1
2
3
CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', PASSWORD 'password', HOST 'remote_host', PORT 9306, DATABASE 'federated');

我们需要记住输入的 server_name 即 fedlink，然后我们需要拿到要创建的源表的表结构，可以使用 SHOW CREATE TABLE table_name 来打印，然后将 ENGINE 改为 FEDERATED，添加 CONNECTION='fedlink/table_name'，执行后即在本地生成了 FEDERATED 表。例如：

1
2
3
4
5
6
7
8
9
10
11
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),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='fedlink/test_table';

这里创建的表名即 CREATE TABLE 后面的表名可以与源表不一样，只需在 CONNECTION 中指定源表名即可。

使用 CONNECTION 的方式创建 FEDERATED 表

如果没有 CREATE SERVER 权限，也可以直接使用 CONNECTION 的方式创建 FEDERATED 表，只需将上面建表语句中 CONNECTION 的值改为完整连接即可，例如：

1
2
3
4
5
6
7
8
9
10
11
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),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

需要注意的是：

  • 采用这种方式创建的 FEDERATED 表，可以通过 SHOW CREATE TABLE 看到原始的连接信息
  • 如果密码中包含 @，由于 @ 需要作为 url 的分界符，所以这种情况下只能使用 CREATE SERVER 的方式创建 FEDERATED 表