业务诉求

用户存在另外一个不同的主机的 mysql 的某个 schema A中，业务系统 B 中只存储关联的 userId，而业务需要通过用户名称模糊匹配查询。

方案

1. 主张数据冗余，将 A 中 userName 存储到 B 中，但是这样，可能导致数据不一致（用户修改名称），或者用户修改名称后，涉及到修改的表（数据）较多；
2. 主张查询时，先模糊搜索人，传入用户userId（或一组 userId）来精确匹配，但这样要求产品业务设计上的让步；
3. 采用 Oracle 的 dblink 方式，远程访问。mysql 是 federated（联合存储引擎）。

不说前两种解决办法，学习一下 federated。

mysql federated

联邦存储引擎 federated
FEDERATED存储引擎可让您从远程MySQL数据库，而不使用复制或群集技术访问数据。查询本地FEDERATED表会自动从远程（联合）表中提取数据。本地表上不存储任何数据。

环境

本人测试的 mysql（本机，Windows 10，mysql-8.0.22-winx64），模拟业务系统 B 记为 origin，远程访问 linux mysql（5.7.29），模拟用户系统 A，记为 target。

Windows origin 开启 federated

先查看是否开启

执行命令： show engines;

show engines;

开启 federated

通过命令，当前 Windows 10 下的 mysql-8.0.22-winx64，没有开启 federated。<br />需要修改 my.ini，在** [mysqld] **下添加 federated

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\devServer\MySQL
# 设置mysql数据库的数据的存放目录
datadir=D:\devServer\MySQL\Data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
# 支持远程 dblink 访问
federated
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

注意，一定要加在 [mysqld] 下。修改完后，需要重启 mysql 服务。

执行 show engines 命令

show engines

federated 显示为 YES，我们就可以进行远程访问别的 mysql 了。

远程访问步骤

建立对应的数据表结构

CREATE TABLE if not exists `appnamespace` (
  `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `Name` varchar(32) NOT NULL DEFAULT '' COMMENT 'namespace名字，注意，需要全局唯一',
  `AppId` varchar(64) NOT NULL DEFAULT '' COMMENT 'app id',
  `Format` varchar(32) NOT NULL DEFAULT 'properties' COMMENT 'namespace的format类型',
  `IsPublic` bit(1) NOT NULL DEFAULT b'0' COMMENT 'namespace是否为公共',
  `Comment` varchar(64) NOT NULL DEFAULT '' COMMENT '注释',
  `IsDeleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '1: deleted, 0: normal',
  `DataChange_CreatedBy` varchar(64) NOT NULL DEFAULT 'default' COMMENT '创建人邮箱前缀',
  -- `DataChange_CreatedTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  -- `DataChange_LastModifiedBy` varchar(64) DEFAULT '' COMMENT '最后修改人邮箱前缀',
  `DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`Id`),
  KEY `IX_AppId` (`AppId`),
  KEY `Name_AppId` (`Name`,`AppId`),
  KEY `DataChange_LastTime` (`DataChange_LastTime`)
)ENGINE=FEDERATED CONNECTION='mysql://root:Platform#123@192.168.0.213:30001/apolloconfigdb/appnamespace' DEFAULT CHARSET=utf8mb4 COMMENT='测试连接 federated 使用';

创建的关键点在于 engine 要为 federated ，并指定connection，格式：_scheme_://_user_name_[:_password_]@_host_name_[:_port_num_]/_db_name_/_tbl_name_。<br />**需要注意的只是字段必须在远程表中，且类型一致，不要求字段一样多（换言之，可以少），不要求本地表名称与远程表名称一样（换言之，可以改动表名）**；

附录 1 Creating a FEDERATED Table Using CONNECTION

创建的官网说明 Creating a FEDERATED Table Using CONNECTION

Creating a FEDERATED Table Using CONNECTION

To use the first method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. For example:

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 = latin1 CONNECTION = 'mysql://fed_user@remote_host:9306/federated/test_table';

Note
CONNECTION replaces the COMMENT used in some previous versions of MySQL.
The CONNECTION string contains the information required to connect to the remote server containing the table used for physical storage of the data. The connection string specifies the server name, login credentials, port number and database/table information. In the example, the remote table is on the server remotehost, using port 9306. The name and port number should match the host name (or IP address) and port number of the remote MySQL server instance you want to use as your remote table.
The format of the connection string is as follows:
_scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
Where:

• scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point.
• user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT, UPDATE, and so forth) on the remote table.
• password: (Optional) The corresponding password for user_name.
• host_name: The host name or IP address of the remote server.
• port_num: (Optional) The port number for the remote server. The default is 3306.
• db_name: The name of the database holding the remote table.
• tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.

Sample connection strings:
CONNECTION=’mysql://username:password@hostname:port/database/tablename’ CONNECTION=’mysql://username@hostname/database/tablename’ CONNECTION=’mysql://username:password@hostname/database/tablename’

附录 2 Creating a FEDERATED Table Using CREATE SERVER

网址：Creating a FEDERATED Table Using CREATE SERVER

Creating a FEDERATED Table Using CREATE SERVER

If you are creating a number of FEDERATED tables on the same server, or if you want to simplify the process of creating FEDERATED tables, you can use the CREATE SERVER statement to define the server connection parameters, just as you would with the CONNECTION string.
The format of the CREATE SERVER statement is:
CREATE SERVER server_name FOREIGN DATA WRAPPER wrapper_name OPTIONS (option [, option] …)
The server_name is used in the connection string when creating a new FEDERATED table.
For example, to create a server connection identical to the CONNECTION string:
CONNECTION=’mysql://fed_user@remote_host:9306/federated/test_table’;
You would use the following statement:

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

To create a FEDERATED table that uses this connection, you still use the CONNECTION keyword, but specify the name you used in the CREATE SERVER statement.

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 = latin1 CONNECTION = 'fedlink/test_table';

The connection name in this example contains the name of the connection (fedlink) and the name of the table (testtable) to link to, separated by a slash. If you specify only the connection name without a table name, the table name of the local table is used instead.
For more information on CREATE SERVER, see Section 13.1.17, “CREATE SERVER Statement”.
The CREATE SERVER statement accepts the same arguments as the CONNECTION string. The CREATE SERVER statement updates the rows in the mysql.servers table. See the following table for information on the correspondence between parameters in a connection string, options in the CREATE SERVER statement, and the columns in the mysql.servers table. For reference, the format of the CONNECTION string is as follows:
_scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

附录 3 FEDERATED Storage Engine Notes and Tips

FEDERATED Storage Engine Notes and Tips

联合存储引擎注意事项和提示

使用FEDERATED存储引擎时应注意以下几点 ：

• FEDERATED表可以复制到其他副本，但您必须确保副本服务器能够使用CONNECTION字符串（或mysql.servers表中的行）中 定义的用户/密码组合连接到远程服务器。

以下各项表示 FEDERATED存储引擎支持和不支持的功能：

• 远程服务器必须是 MySQL 服务器。
• 在尝试通过 表访问表之前 ，FEDERATED表指向的远程表必须存在FEDERATED。
• 一个FEDERATED表可能指向另一个表，但您必须小心不要创建循环。
• 一个FEDERATED表不支持通常意义上的索引；因为对表数据的访问是远程处理的，所以实际上是远程表使用了索引。这意味着，对于不能使用任何索引并因此需要全表扫描的查询，服务器从远程表中获取所有行并在本地过滤它们。无论使用任何WHERE或LIMIT与此SELECT声明一起使用，都会发生这种情况 ；这些子句在本地应用于返回的行。因此，未能使用索引的查询会导致性能不佳和网络过载。此外，由于返回的行必须存储在内存中，因此这样的查询也会导致本地服务器交换，甚至挂起。
• 创建FEDERATED表时应小心， 因为MyISAM可能不支持来自等效表或其他表的索引定义。例如，创建FEDERATED带有索引前缀的 表对于VARCHAR、 TEXT或 BLOB列会失败。以下定义MyISAM是有效的：

CREATE TABLE T1(A VARCHAR(100),UNIQUE KEY(A(30))) ENGINE=MYISAM;

• 此示例中的键前缀与FEDERATED引擎不兼容， 等效语句失败：

CREATE TABLE T1(A VARCHAR(100),UNIQUE KEY(A(30))) ENGINE=FEDERATED CONNECTION=’MYSQL://127.0.0.1:3306/TEST/T1’;

• 如果可能，您应该在远程服务器和本地服务器上创建表时尝试将列和索引定义分开，以避免这些索引问题。
• 在内部，实现使用 SELECT， INSERT， UPDATE，和 DELETE，但不会 HANDLER。
• 该FEDERATED存储引擎支持 SELECT， INSERT， UPDATE， DELETE， TRUNCATE TABLE，和索引。它不支持ALTER TABLE，或任何直接影响表结构的数据定义语言语句，除了 DROP TABLE. 当前的实现不使用准备好的语句。
• FEDERATED接受 INSERT … ON DUPLICATE KEY UPDATE语句，但如果发生重复键冲突，语句将失败并显示错误。
• 不支持事务。
• FEDERATED执行批量插入处理，以便将多行批量发送到远程表，从而提高性能。此外，如果远程表是事务性的，它使远程存储引擎能够在发生错误时正确执行语句回滚。此功能具有以下限制：
  • 插入的大小不能超过服务器之间的最大数据包大小。如果插入超过这个大小，就会被分成多个数据包，就会出现回滚问题。
  • 不会发生批量插入处理 INSERT … ON DUPLICATE KEY UPDATE。
• 有没有办法让FEDERATED引擎知道，如果远程表已经改变。这样做的原因是这个表必须像一个数据文件一样工作，除了数据库系统之外，它永远不会被写入。如果远程数据库发生任何更改，本地表中数据的完整性可能会受到破坏。
• 使用CONNECTION字符串时，不能在密码中使用“@”字符。您可以通过使用该CREATE SERVER语句创建服务器连接来绕过此限制。
• 在insert_id和 timestamp选项都不会传播到数据提供者。
• DROP TABLE针对FEDERATED表发出的 任何语句仅删除本地表，而不删除远程表。
• FEDERATED 表不适用于查询缓存。
• FEDERATED表 不支持用户定义的分区 。

小结

远程访问，需要开启 federated，可以使用 connection 方式建表（表较少的时候），也可以使用 create server 后，在使用 connection（适用于远程访问较多表的时候）。
访问指定的用户需要具备对应的权限，一般根据业务只需要 select 即可。
federated engine 是不支持 transaction 的。