剧情介绍
作者：马文斌
mysql ocp 认证，postgressql pgca 认证，擅长 mysql、postgresql、dble 等开源数据库相关产品的备份恢复、读写分离、sql 调优、监控运维、高可用架构设计等。目前任职于月亮小屋(中国)有限公司。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
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 实例
5. 引擎设置
- 在配置文件制定开启 federated 存储引擎
配置文件中添加 federated 引擎就可以，两个实例都要添加 vim /etc/my.cnf [mysqld] federated- 查看 federated 是否开启 [ federated 中 support 状态 no 表明引擎未开启]
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | engine | support | comment | transactions | xa | savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | csv | yes | csv storage engine | no | no | no | | mrg_myisam | yes | collection of identical myisam tables | no | no | no | | myisam | yes | myisam storage engine | no | no | no | | blackhole | yes | /dev/null storage engine (anything you write to it disappears) | no | no | no | | performance_schema | yes | performance schema | no | no | no | | memory | yes | hash based, stored in memory, useful for temporary tables | no | no | no | | archive | yes | archive storage engine | no | no | no | | innodb | default | supports transactions, row-level locking, and foreign keys | yes | yes | yes | | federated | yes | federated mysql storage engine | no | no | no | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) federated | yes 说明已经开启了federated 引擎6. 部署
- 在 server-1 上有一个数据库 db1，在 server-2 上有数据库 db2，要在 server-2 的数据库 db2 上建立 server-1 的数据库 db1 上的表 tb1 的数据表链接 remote_tb1，通过普通用户 test 连接。
- 在 server-1 上建立测试的数据库数据库、表
create database db1; use db1; create table tb1( id int primary key not null auto_increment )engine=innodb; 插入数据： insert into tb1 select null; insert into tb1 select null; insert into tb1 select null; 查看数据 mysql> select * from tb1; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)- 在 server-1 上建立测试的账号
create user 'test'@'192.168.%' identified by '123456'; grant select,update,insert,delete on db1.* to 'test'@'192.168.%'; flush privileges;- 在 server-2 上建立远程访问的数据库和表
create database db2; use db2; create table remote_tb1( id int primary key not null auto_increment )engine=federated connection='mysql://test:123456@192.168.234.204:3306/db1/tb1'; create table tb2( id int primary key not null auto_increment, name varchar(20) )engine=innodb; 插入数据： insert into tb2(name) select 'a'; insert into tb2(name) select 'b'; insert into tb2(name) select 'c'; mysql> select * from db2.tb2; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec)- 在 server-2 中查询 server-1 中的 db1.tb1 是否有数据
mysql> select * from db2.remote_tb1; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) 说明是关联上了， 测试下join： select t2.* from db2.remote_tb1 as t1 join db2.tb2 as t2 on t1.id=t2.id where t2.name='c'; mysql> select t2.* from db2.remote_tb1 as t1 join -> db2.tb2 as t2 on t1.id=t2.id -> where t2.name='c'; +----+------+ | id | name | +----+------+ | 3 | c | +----+------+ 1 row in set (0.00 sec) 说明本地表和远程表关联也是可以的。- 测试 server-2 操作下 dml， 观察 binlog 是否会写 gtid 事务
mysql> delete from db2.remote_tb1 where id =3; query ok, 1 row affected (0.00 sec) mysql> select * from db2.remote_tb1; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)- /usr/local/mysql-5.7.26/bin/mysqlbinlog -vvv mysql-bin.000884 |grep -c 10 remote_tb1
set @@session.gtid_next= 'anonymous'/*!*/; # at 40057515 #210415 14:25:53 server id 2342042 end_log_pos 40057586 crc32 0x82abe215 query thread_id=53 exec_time=0 error_code=0 set timestamp=1618467953/*!*/; set @@session.sql_mode=1411383296/*!*/; /*!c utf8 *//*!*/; set @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/; begin /*!*/; # at 40057586 #210415 14:25:53 server id 2342042 end_log_pos 40057638 crc32 0xddbc9dfc table_map: `db2`.`remote_tb1` mapped to number 416 # at 40057638 #210415 14:25:53 server id 2342042 end_log_pos 40057678 crc32 0x5c28d0d0 delete_rows: table id 416 flags: stmt_end_f binlog ' cdx3yboavcmanaaaacy7ywiaakabaaaaaaeaa2rimgakcmvtb3rlx3rimqabawaa/j283q== cdx3yccavcmakaaaae47ywiaakabaaaaaaeaagab//4daaaa0naoxa== '/*!*/; ### delete from `db2`.`remote_tb1` ### where ### @1=3 /* int meta=0 nullable=0 is_null=0 */ # at 40057678 #210415 14:25:53 server id 2342042 end_log_pos 40057750 crc32 0xb37fe7b3 query thread_id=53 exec_time=0 error_code=0 set timestamp=1618467953/*!*/; commit /*!*/; 从 binlog set @@session.gtid_next= 'anonymous'/*!*/; 是可以看出，链接端操作dml是把gtid 事务隐藏了。- server-2 服务器观察 remote_tb2 相关文件
.frm 表定义文件 [ federated链接库本地不产生数据文件 ]
[root@eos_db04 db2]# pwd /mysqldata/3310_data/data/db2 [root@eos_db04 db2]# ll total 128 -rw-r----- 1 mysql mysql 67 apr 15 14:11 db.opt -rw-r----- 1 mysql mysql 8556 apr 15 14:11 remote_tb1.frm -rw-r----- 1 mysql mysql 8586 apr 15 14:18 tb2.frm -rw-r----- 1 mysql mysql 98304 apr 15 14:18 tb2.ibd总结
对比其他的数据同步产品，这种建立 链接 跨 ip 跨库查询会显轻便一些，搭建起来很方便。
