增加和修改同步更新(删除同步更新在后面)
思路:表中需要存在字段 update_time, 通过update_time更新时间字段来判断哪些数据是添加或者更新的,并设置update_time时间类型为下图:
然后在input中执行sql将需要同步的数据查询出来,通过output将数据插入另一张表
数据库脚本—建立主表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
数据库脚本—建立从表
CREATE TABLE `user_cong` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
logstash 配置
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8"
jdbc_user => "root"
jdbc_password => "123456"
jdbc_driver_library => "E:\mysql-connector-java-5.1.48.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
statement => "select *,date_add(update_time,interval 8 hour) update_time_add from user where update_time > (select date_add(:sql_last_value, interval 8 hour) update_time)"
tracking_column_type => "timestamp"
schedule => "* * * * *"
type => "user"
}
}
output {
jdbc {
connection_string => "jdbc:mysql://localhost/testcong?useUnicode=true&characterEncoding=utf8"
username => "root"
password => "123456"
driver_class => "com.mysql.jdbc.Driver"
statement => ["insert into user(id,username,password,update_time)
values(?,?,?,?)
on duplicate key update
id = values(id),
username = values(username),
password = values(password),
update_time = values(update_time)",
"id","username","password","update_time_add"]
}
stdout {
codec => json_lines
}
}
jdbc_connection_string—数据库连接地址
jdbc_user—数据库用户名
jdbc_password—数据库密码
jdbc_driver_library—数据库驱动包的位置
jdbc_paging_enabled — 是否分页
jdbc_page_size — 每页多少条数据
statement — 需要执行的sql语句
tracking_column_type — 字段类型
schedule — 同步时间,从左至右分代表,分、时、天、月、年,全为默认含义为每分钟都更新(eg:15分钟更新一次:schedule => "15 * * * *")
type — 类型,用来分别输入与输出对应关系
那么问题来了 为什么要执行select ,date_add(update_time,interval 8 hour) update_time_add from user where update_time > (select date_add(:sql_last_value, interval 8 hour) update_time) 而不是直接select * from user 那
因为logstash的时区比mysql的时区相差了8个小时,所以在查询的时候在where条件中增加了8个小时,同时如果同步的数据中存在时间,那么也需要增加8个小时,不使用原来的时间,使用增加了8个小时的字段 update_time_add 保证时间正确同步过去
值得注意的是,在 input jdbc中指定了驱动包的路径,在output jdbc中并没有指明驱动包的路径,因为logstash 中的output如果没有指定驱动包的路径,驱动包的路径会默认为 /vendor/jar/jdbc/,楼主这里面已经存在了,如果不存在,需要建立目录并把驱动包添加进去
然后就是输出了,在output中执行的sql语句代表添加或者更新,如果数据不存在就添加,存在就更新,判断条件为唯一约束列
运行logstash 就可以实现同步新增和修改了
如果是同步多张表,写多组input和output就可以了
删除更新
既然是同步更新,那自然少不了删除,logstash中无法直接同步删除,想要实现同步删除,需要实现两步设计。
- 在主库中建立中间表 the_same_del ,表结构如下:
object_id — 需要删除数据的主键
table_name — 需要删除数据的表
update_time — 数据更新时间
脚本代码如下:
CREATE TABLE `the_same_del` (
`id` varchar(255) NOT NULL,
`object_id` varchar(255) DEFAULT NULL,
`table_name` varchar(255) DEFAULT NULL,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 在 user 表中创建触发器,删除数据的同时,将删除数据的表名,id插入到中间表,代码如下:
CREATE TRIGGER `user_del` AFTER DELETE ON `user` FOR EACH ROW BEGIN
INSERT INTO the_same_del
SET id=uuid(),object_id=old.id,table_name='user';
END;
这样logstash读取the_same_del表,就可以知道需要删除的表数据为哪张表的哪条数据,然后使用 output jdbc 就可以实现同步删除。
logstash 配置:
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8"
jdbc_user => "root"
jdbc_password => "123456"
jdbc_driver_library => "E:\mysql-connector-java-5.1.48.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
statement => "select *,date_add(update_time,interval 8 hour) update_time_add from user where update_time > (select date_add(:sql_last_value, interval 8 hour) update_time)"
tracking_column_type => "timestamp"
schedule => "* * * * *"
type => "user"
}
jdbc {
jdbc_connection_string => "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8"
jdbc_user => "root"
jdbc_password => "123456"
jdbc_driver_library => "E:\mysql-connector-java-5.1.48.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
statement => "select * from the_same_del where update_time > (select date_add(:sql_last_value, interval 8 hour) update_time)"
tracking_column_type => "timestamp"
schedule => "* * * * *"
type => "user_del"
}
}
output {
if ([type] == "user"){
jdbc {
connection_string => "jdbc:mysql://localhost/testcong?useUnicode=true&characterEncoding=utf8"
username => "root"
password => "123456"
driver_class => "com.mysql.jdbc.Driver"
statement => ["insert into user(id,username,password,update_time)
values(?,?,?,?)
on duplicate key update
id = values(id),
username = values(username),
password = values(password),
update_time = values(update_time)",
"id","username","password","update_time_add"]
}
}
if ([type] == "user_del"){
if ([table_name] == "user"){
jdbc {
connection_string => "jdbc:mysql://localhost/testcong?useUnicode=true&characterEncoding=utf8"
username => "root"
password => "123456"
driver_class => "com.mysql.jdbc.Driver"
statement => ["delete from user where id = ?","[object_id]"]
}
}
# 删除维护的中间表中的数据
jdbc {
connection_string => "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8"
username => "root"
password => "123456"
driver_class => "com.mysql.jdbc.Driver"
statement => ["delete from the_same_del where id = ?","[id]"]
}
}
stdout {
codec => json_lines
}
}
通过 type 判断输入输出对应关系,同时中间表数据随着 user 表数据的删除会一直增加,所以需要维护 the_same_del 中间表