Federated引擎实现两个mysql数据库的表级数据同步

weixin_40169387

已于 2023-01-12 15:45:19 修改

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： java 文章标签：数据库 mysql sql

于 2023-01-11 11:48:53 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_40169387/article/details/128641617

java 专栏收录该内容

8 篇文章

订阅专栏

前言：对 Federated引擎的解释

通过FEDERATED引擎创建的表只是在本地有表定义文件，数据文件则存在于远程数据库中。
通过这个引擎可以实现类似Oracle下DBLINK的远程数据访问功能。
就是说，这种建表方式只会在数据库A中创建一个表B的表结构文件，表的索引、数据等文件还是在机器B上的数据库B中，相当于只是在数据库A中创建了表B的一个快捷方式。

1.准备阶段：开启Federated引擎

1.找到mysql的配置文件my.ini
在这里插入图片描述
2.在my.ini文件追加字段：federated
3.重启mysql服务，或者重启电脑

4.执行SHOW ENGINES; 确认打开FEDERATED引擎
在这里插入图片描述

2.实战阶段：实现两个数据库的表级数据同步

1.创建数据库db1，创建表tb1，插入数据

//建数据库db1
CREATE DATABASE db1;

//创建表tb1
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;

在sqlyog里操作后，实现效果：
在这里插入图片描述

2.创建数据库db2

//建数据库db2
CREATE DATABASE db2;

//重点：创建映射表remote_tb1
use db2;
create table remote_tb1(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)ENGINE=FEDERATED
CONNECTION='mysql://root:123456@localhost:3306/db1/tb1';

//创建表tb2
create table tb2(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)ENGINE=INNODB;

//插入数据
insert into tb2 select NULL;
insert into tb2 select NULL;
insert into tb2 select NULL;

在sqlyog里操作后，实现效果：
在这里插入图片描述
3.测试对db2数据库里的表remote_tb1数据进行增删改查，db1里表tb1的数据是否会同步修改

delete from db2.remote_tb1 where id =3;

实验得知两边的表都会删除id=3的记录
在这里插入图片描述

4.tb2表上右键，创建触发器

创建触发器的语句：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `db2`.`tb2_insert_trigger` AFTER INSERT
    ON `db2`.`tb2`
    FOR EACH ROW BEGIN
	IF NOT EXISTS(SELECT * FROM remote_tb1 WHERE id=new.id) 
	THEN
		INSERT INTO remote_tb1(id) VALUES( new.id);
	END IF;
    END$$

DELIMITER ;

F9运行之后，生成触发器，名称是之前自己设的，如下图
在这里插入图片描述

3.最终效果

在tb2表中增加一条记录，会通过触发器在remote_tb1表增加这条记录，再通过关联映射更改db1中tb1中的表记录。

4.FEDRATE引擎的特点

本地虚拟表与远程实体表之间是 TCP 长连接，并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销。
本虚拟表表与远程实体表之间的网络连接断开后，当对虚拟表发起查询时，它会尝试重新连接远程实体表，所以我们不用担心网络连接断开造成的永久中断问题。
如果无时间未对本地虚拟表作任何操作，虚拟表与实体表之间的连接将在远程主机的 wait_timeout 秒后自动断开，当对虚拟表发起查询时，连接又会重新建立。

5.注意事项

1、FEDERATED 表可能会被复制到其他的slave数据库,需要确保slave服务器也能够连接上远程服务器。
2、远程服务器必须是MySQL数据库
3、在访问FEDERATED表中定义的远程数据库的表前,远程数据库中必须存在这张表。
4、FEDERATED 表不支持通常意义的索引,服务器从远程库获取所有的行然后在本地进行过滤,不管是否加了where条件或limit限制。
　　—查询可能造成性能下降和网络负载,因为查询返回的数据必须存放在内存中,所以容易造成使用系统的swap分区或挂起。
5、FEDERATED表不支持字段的前缀索引
6、FEDERATED表不支持ALTER TABLE语句或者任何DDL语句
7、FEDERATED表不支持事务
8、本地FEDERATED表无法知道远程库中表结构的改变
9、任何drop语句都只是对本地库的操作,不对远程库有影响
10、对本地虚拟表的结构修改，并不会修改远程表的结构 
11、truncate命令，会清除远程表数据

6.目前使用FEDRATE引擎最大的缺点

1.limit语句查询过慢

 select count(*), select * from limit M, N  等语句执行效率非常低，数据量较大时存在很严重的问题
 但按主键或索引列查询，则很快。

   以下查询就非常慢（假设 id 为主索引） 
   select * from test_db.xxl_job_log limit 1;
   而以下查询就很快： 
   select * from test_db.xxl_job_log where id=1;

2.虚拟表和实体表字段不一致

如果虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。
但是当给虚拟表建立索引后，性能恢复正常。

3.like语句查询过慢

类似 where name like "str%" limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢。
原因：federated引擎会将所有满足条件的记录读取到本地，再进行 limit 处理。