使用Kettle工具进行增量数据同步
使用Kettle工具进行增量数据同步
使用Kettle工具进行增量数据同步 - 简书 (jianshu.com)
增量同步的方式有很多种,我使用的是: 快照表 + 触发器
需求:
当主库库表发生增删改时,从库库表与主库库表数据保持一致。
环境:
1、Mysql
2、kettle 7.1
思路:
1、在主库中,将需要同步的库表新建快照表,表结构一致。
#创建同步临时表(insert)
DROP TABLE IF EXISTS brch_temp_insert;
CREATE TABLE brch_temp_insert like brch;
#创建同步临时表(delete)
DROP TABLE IF EXISTS brch_temp_delete;
CREATE TABLE brch_temp_delete like brch;
#创建同步临时表(update)
DROP TABLE IF EXISTS brch_temp_update;
CREATE TABLE brch_temp_update like brch;
2、在主库中,分别新增库表的增、删、改的触发器。
#创建同步表触发器(insert)
DROP TRIGGER IF EXISTS tri_insert_brch;
CREATE TRIGGER tri_insert_brch
AFTER INSERT ON brch
FOR EACH ROW
BEGIN
insert into brch_temp_insert
(
ROW_ID,
BRCH_ID,
BRCH_NAME
)
values(
new.ROW_ID,
new.BRCH_ID,
new.BRCH_NAME
);
END;
#创建同步表触发器(delete)
DROP TRIGGER IF EXISTS tri_delete_brch;
CREATE TRIGGER tri_delete_brch
AFTER delete ON brch
FOR EACH ROW
BEGIN
insert into brch_temp_delete(
ROW_ID
)
values(
old.ROW_ID
);
END;
#创建同步表触发器(update)
DROP TRIGGER IF EXISTS tri_update_brch;
CREATE TRIGGER tri_update_brch
AFTER update ON brch
FOR EACH ROW
BEGIN
insert into brch_temp_update(
ROW_ID,
BRCH_ID,
BRCH_NAME
)
values(
new.ROW_ID,
new.BRCH_ID,
new.BRCH_NAME
);
END;
2、新建一个转换,该转换只针对一张表的增删改。
![](http://upload-images.jianshu.io/upload_images/11576306-aee771d96b89a4b1.png?imageMogr2/auto-orient/strip|imageView2/2/w/761/format/webp)
image
3、新建‘表输入’控件,查询主库的快照表
![](http://upload-images.jianshu.io/upload_images/11576306-30ebaa63d3af9d32.png?imageMogr2/auto-orient/strip|imageView2/2/w/774/format/webp)
image
![](http://upload-images.jianshu.io/upload_images/11576306-e3c8bafcdf201ef6.png?imageMogr2/auto-orient/strip|imageView2/2/w/768/format/webp)
image
![](http://upload-images.jianshu.io/upload_images/11576306-8fdb24635bb7e44a.png?imageMogr2/auto-orient/strip|imageView2/2/w/757/format/webp)
image
4、新建‘插入/更新’控件,插入数据到从库的库表,查询的关键字要求唯一。
![](http://upload-images.jianshu.io/upload_images/11576306-b5714694327c0e36.png?imageMogr2/auto-orient/strip|imageView2/2/w/1177/format/webp)
image
![](http://upload-images.jianshu.io/upload_images/11576306-57b5eb79437803d4.png?imageMogr2/auto-orient/strip|imageView2/2/w/1131/format/webp)
image
![](http://upload-images.jianshu.io/upload_images/11576306-22e25a9d3816f93a.png?imageMogr2/auto-orient/strip|imageView2/2/w/1153/format/webp)
image
5、新建‘删除’控件,将主库的快照表中的数据删除。
![](http://upload-images.jianshu.io/upload_images/11576306-69fe193501800377.png?imageMogr2/auto-orient/strip|imageView2/2/w/878/format/webp)
image
![](http://upload-images.jianshu.io/upload_images/11576306-b31c686c6c0cee5a.png?imageMogr2/auto-orient/strip|imageView2/2/w/887/format/webp)
image
![](http://upload-images.jianshu.io/upload_images/11576306-f5d8b4cb8e8168aa.png?imageMogr2/auto-orient/strip|imageView2/2/w/849/format/webp)
image
注意:主库的库表,要新增针对增、删、改的三张表快照,三张表的步骤同上面的1 - 5 一致。
6、新建作业控件
分别将三个转换引用进来。
![](http://upload-images.jianshu.io/upload_images/11576306-46f1eb4abb8ca98f.png?imageMogr2/auto-orient/strip|imageView2/2/w/677/format/webp)
image
7、配置发送邮件服务
![](http://upload-images.jianshu.io/upload_images/11576306-20a635f075883b7d.png?imageMogr2/auto-orient/strip|imageView2/2/w/918/format/webp)
image
![](http://upload-images.jianshu.io/upload_images/11576306-73d85c95bbafadc3.png?imageMogr2/auto-orient/strip|imageView2/2/w/415/format/webp)
image
8、完成
作者:叫我老村长
链接:https://www.jianshu.com/p/d0e34f966596
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。