通过触发器实现跨库同步表数据
文章实现了postgresql和mysql两种数据库的跨库同步数据表。
1.postgresql实现跨库同步表数据
postgresql采用postgres_fdw插件实现跨库同步,在编写跨库同步脚本前,需要先准备好postgres_fdw的执行环境。
1.1安装插件、创建远程服务
如已配置好远程服务,可以忽略此步骤
-- 安装 postgres_fdw 插件 CREATE EXTENSION postgres_fdw; -- 创建远程服务 CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw; -- 查看远程服务 select * from pg_foreign_server ; -- 修改远程服务 alter server remote_server options (add hostaddr '11.216.10.215', add port '5432', add dbname 'db_des');
1.2创建需要跨库同步的两个数据表
实现将数据库DATABASE_A中的DEMO_TABLE表数据同步到数据库DATABASE_B中的DEMO_TABLE表
--在数据库DATABASE_A和DATABASE_B中分别创建表-- CREATE TABLE "DEMO_TABLE" ( "id" serial PRIMARY KEY, "priv_prod_id" int4, "shr_type_id" int4, "start_date" date, "end_date" date, "commission" numeric(10,4), "create_by" varchar(10) COLLATE "default", "create_time" timestamp(6), "update_by" varchar(10) COLLATE "default", "update_time" timestamp(6), "rec_stat" varchar(1) COLLATE "default", "version" int2 );
1.3创建远程控制表
远程控制表需要与跨库待同步的数据表结构保持一致,能自动将insert、update、delete命令同步在关联跨库表中执行。
------------创建远程控制表------------------- DROP TABLE IF EXISTS "public"."fdw_DEMO_TABLE"; CREATE FOREIGN TABLE "public"."fdw_DEMO_TABLE" ( "id" int4, "prod_id" int4, "shr_type_id" int4, "start_date" date, "end_date" date, "commission" numeric(10,4), "create_by" varchar(10) COLLATE "pg_catalog"."default", "create_time" timestamp(6), "update_by" varchar(10) COLLATE "pg_catalog"."default", "update_time" timestamp(6), "rec_stat" varchar(1) COLLATE "pg_catalog"."default", "version" int2 ) ----使用已经配置好的数据库外部服务---------- SERVER "pg_fdw_DEMO_TABLE" ----关联外部数据库服务的数据表,表结构需要与远程控制表一致---------- OPTIONS ("table_name" 'demo_table') ;
1.4创建触发器和函数,实时将数据表中的数据同步到远程控制表
--创建同步函数-- -------delete函数--------- CREATE OR REPLACE FUNCTION "public"."fuc_DEMO_TABLE_delete"() RETURNS "pg_catalog"."trigger" AS $BODY$ BEGIN DELETE FROM fdw_DEMO_TABLE WHERE id = OLD.id; RETURN OLD; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; -------insert函数--------- CREATE OR REPLACE FUNCTION "public"."fuc_DEMO_TABLE_insert"() RETURNS "pg_catalog"."trigger" AS $BODY$ BEGIN INSERT INTO fdw_DEMO_TABLE ( id, prod_id, shr_type_id, start_date, end_date, commission, create_by, create_time, update_by, update_time, rec_stat, version ) VALUES(new.id, new.priv_prod_id, new.shr_type_id, new.start_date, new.end_date, new.commission, new.create_by, new.create_time, new.update_by, new.update_time, new.rec_stat, new.version); RETURN new; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; -------update函数--------- CREATE OR REPLACE FUNCTION "public"."fuc_DEMO_TABLE_update"() RETURNS "pg_catalog"."trigger" AS $BODY$ BEGIN UPDATE fdw_DEMO_TABLE SET prod_id = NEW.priv_prod_id , shr_type_id = NEW.shr_type_id, start_date = NEW.start_date, end_date = NEW.end_date, commission = NEW.commission, create_by = NEW.create_by, create_time = NEW.create_time, update_by = NEW.update_by, update_time = NEW.update_time, rec_stat = NEW.rec_stat, version = NEW.version WHERE id = NEW.id; RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; --3.创建触发器-- CREATE TRIGGER "tg_DEMO_TABLE_delete" AFTER DELETE ON "public"."DEMO_TABLE" FOR EACH ROW EXECUTE PROCEDURE "fuc_DEMO_TABLE_delete"(); CREATE TRIGGER "tg_DEMO_TABLE_insert" AFTER INSERT ON "public"."DEMO_TABLE" FOR EACH ROW EXECUTE PROCEDURE "fuc_DEMO_TABLE_insert"(); CREATE TRIGGER "tg_DEMO_TABLE_update" AFTER UPDATE ON "public"."DEMO_TABLE" FOR EACH ROW EXECUTE PROCEDURE "fuc_DEMO_TABLE_update"();
以上就是postgresql跨库同步数据的全部实现
2.MySql实现跨库同步表数据
mysql采用fed的方式实现跨库同步,数据库默认未开启fed功能模块。
2.1开启fed功能
执行 show ENGINES; 语句
Support值为NO,说明未支持该引擎。需修改mysql的配置文件进行设置。
找到mysql的配置文件,在[mysqld]后添加federated即可;添加完成,重启mysql服务再次查询该引擎状态为YES即为支持。
2.2创建需要跨库同步的两个数据表
参照1.2中的执行脚本
2.3创建fed远程控制表
远程控制表需要与跨库待同步的数据表结构保持一致,能自动将insert、update、delete命令同步在关联跨库表中执行。
CREATE TABLE `learn`.`fed_DEMO_TABLE` ( `id` varchar(36), `code` char(8), `version` int(0), `create_time` datetime(0), `user_name` varchar(100), `password` varchar(100), `password_salt` varchar(100), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `idx_DEMO_TABLE_user_name`(`user_name`) USING BTREE ) ENGINE=FEDERATED CONNECTION = 'mysql://root:root@192.168.92.128:3306/shrio/DEMO_TABLE'; ----- CONNECTION 的链接格式为CONNECTION = 'mysql://username:password@ip:port/database/tablename'; -----
2.4创建触发器,实时将数据表中的数据同步到远程控制表
-------insert触发器--------- DELIMITER $ create trigger DEMO_TABLE_insert_trigger after insert on DEMO_TABLE for each row begin INSERT INTO fed_DEMO_TABLE(id,code,version,create_time,user_name,password,password_salt) VALUES(new.id,new.code,new.version,new.create_time,new.user_name,new.password,new.password_salt); END$; DELIMITER ; -------update触发器--------- DELIMITER $ create trigger DEMO_TABLE_update_trigger after update on DEMO_TABLE for each row begin update fed_DEMO_TABLE set id=new.id,code=new.code,version=new.version,create_time=new.create_time,user_name=new.user_name,password=new.password,password_salt=new.password_salt where id=old.id; END$; DELIMITER ; -------delete触发器--------- DELIMITER $ create trigger DEMO_TABLE_delete_trigger after delete on DEMO_TABLE for each row begin delete from fed_DEMO_TABLE where id=old.id; END$; DELIMITER ;
以上是实现mysql跨库同步表的方式。