mysql Federated 引擎最佳实战
**说明:**本文转载地址https://mp.weixin.qq.com/s/Fpas2HcWgcUWFdBrvf6BiQ 如有侵权请联系本博主删除,谢谢。
1. 背景
本地 MySQL 数据库要访问远程 MySQL 数据库的表中的数据,又不想用数据同步方式去实现,所以就考虑 FEDERATED 存储引擎来实现。
要允许这个存储引擎,当构建 MySQL 时使用 --with-federated-storage-engine 来 configure。
2. Federated 引擎架构:
当客户端发出引用 FEDERATED 表的 SQL 语句时,本地服务器(执行 SQL 语句的地方)和远程服务器(实际存储数据的地方)之间的信息流如下:
存储引擎将遍历 FEDERATED 表具有的每一列,并构造一个引用远程表的适当 SQL 语句。
该语句使用 MySQL 客户端 API 发送到远程服务器。
远程服务器处理该语句,本地服务器检索该语句产生的任何结果(受影响的行数或结果集)。
如果该语句产生结果集,则每一列都将转换为 FEDERATED 引擎期望的内部存储引擎格式,并可用于将结果显示给发出原始语句的客户端。
架构图如下:
3. 相关特性
支持的语法:
在内部,实现使用 SELECT, INSERT, UPDATE,和 DELETE,但不会 HANDLER。
该 FEDERATED 存储引擎支持 SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE,和索引。除之外,它不支持 ALTER TABLE、DROP TABLE 或任何直接影响表结构的数据定义语言语句。当前实现不使用准备好的语句。
FEDERATED 接受 INSERT … ON DUPLICATE KEY UPDATE 语句,但是如果发生重复键冲突,该语句将失败并显示错误。
不支持的语法:
FEDERATED 执行批量插入处理,以便将多行批量发送到远程表,从而提高了性能。另外,如果远程表是事务性的,则它可以使远程存储引擎在发生错误时正确执行语句回滚。此功能具有以下限制:
1) 插入的大小不能超过服务器之间的最大数据包大小。如果插入超过此大小,它将被分成多个数据包,并可能发生回滚问题。
2) 不会进行批量插入处理 INSERT … ON DUPLICATE KEY UPDATE。
如果远程表已经改变,对 FEDERATED 引擎而言是没有办法知道的。这样做的原因是,该表必须像数据文件一样工作,除了数据库系统以外,其他任何文件都不能写入。如果远程数据库发生任何更改,则可能会破坏本地表中数据的完整性。
使用 CONNECTION 字符串时,不能在密码中使用 “ @” 字符。您可以通过使用 CREATE SERVER 语句创建服务器连接来解决此限制。
在 insert_id 和 timestamp 选项都不会传播到数据提供者。
DROP TABLE 针对 FEDERATED 表发出的任何语句仅删除本地表,而不删除远程表。
FEDERATED 表不适用于查询缓存。
FEDERATED 表不支持用户定义的分区。
4. 环境
两个 mysql-5.7.26 实例
ip 端口 备注
192.168.234.204 3306 server-1
192.168.234.204 3310 server-2
5. 引擎设置
在配置文件制定开启 Federated 存储引擎
配置文件中添加 federated 引擎就可以,两个实例都要添加
查看 Federated 是否开启 [ FEDERATED 中 Support 状态 NO 表明引擎未开启]
6. 部署
在 server-1 上有一个数据库 db1,在 server-2 上有数据库 db2,要在 server-2 的数据库 db2 上建立 server-1 的数据库 db1 上的表 tb1 的数据表链接 remote_tb1,通过普通用户 test 连接。
在 server-1 上建立测试的数据库数据库、表
在 server-1 上建立测试的账号
在 server-2 上建立远程访问的数据库和表
插入数据:
在 server-2 中查询 server-1 中的 db1.tb1 是否有数据
说明是关联上了,
测试下join:
说明本地表和远程表关联也是可以的。
测试 server-2 操作下 DML, 观察 binlog 是否会写 GTID 事务
/usr/local/mysql-5.7.26/bin/mysqlbinlog -vvv mysql-bin.000884 |grep -C 10 remote_tb1
server-2 服务器观察 remote_tb2 相关文件
.frm 表定义文件 [ Federated链接库本地不产生数据文件 ]
总结
对比其他的数据同步产品,这种建立 链接 跨 ip 跨库查询会显轻便一些,搭建起来很方便