MYSQL logstash 同步数据到es的几种方案对比以及每种方案数据丢失

2020-10-02 21:30 写PHP的老王

MYSQL logstash 同步增量数据到ES

最近一段时间,在使用mysql通过logstash-jdbc同步数据到es,但是总是会有一定程度数据丢失。logstash-jdbc无非是通过sql遍历数据表的所有数据,然后同步到es。

对于表里面的所有字段都需要查出来然后同步到es中去。本地测试数据表结构如下:

CREATE TABLE `user` (
  `id` bigint(20NOT NULL AUTO_INCREMENT,
  `username` varchar(32CHARACTER SET utf8 NOT NULL,
  `upnum` decimal(20,0NOT NULL,
  `last_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `last_update_time` (`last_update_time`)
ENGINE=InnoDB AUTO_INCREMENT=25094590 DEFAULT CHARSET=latin1;

数据同步脚本分为全量同步与增量同步。

全量同步sql如下:

select id,username,upnum,last_update_time from user where id>{$MIN_ID} and id<{$MAX_ID}

通过shell的方式在每次执行之前替换脚本里面的最大最小ID,每批查询10万ID数据。

增量同步sql:

select id,username,upnum,last_update_time from user where last_update_time>=:last_sql_value

增量同步,每次记录last_update_time最后时间,然后每次查询查询上一次时间之后的数据。最终结果,增量丢数据!!!

方案1

每次查询last_update_time 时间大于等于上一次数据时间的数据,分页查询。

select * from (select id,username,upnum,last_update_time from user where last_update_time>=:last_sql_val) as a limit 10000 offset 10000

这种方案会涉及到几个问题:

1)  相同last_update_time的数据导致数据最终查询出来的顺序有可能不确定。导致有可能在查第一页的时候,数据id=100001的数据在第二页,但是当分页到第二页的时候,id=100001的数据又在第一页了,从而导致这条数据不会被同步。

2)last_update_time 使用的是ON UPDATE CURRENT_TIMESTAMP,时间是更新语句执行的时间。但是只有当更新语句事务提交的时候才会被查询到。这种情况,当一条数据在2020-09-30 00:00:00执行update语句,但是因为事务提交时间比较长,到2020-09-30 00:00:03的时候才提交。这个时候在2020-09-30 00:00:03的时候,同步sql有可能已经是翻页翻到时间为2020-09-30 00:00:02的数据了,因此导致数据丢失。

3)主从同步导致数据分页查询不到。一般都用es了,都会有主从同步,而es数据同步也是在从库中读取数据同步的。因此主从同步的这个时间延时也会导致数据查询不正确。

方案2

不分页查询,避免分页造成的数据丢失。不分页查询的话,需要将数据查询的时间区间减少。调整logstatsh调度频率,每5秒钟执行一次,每次查询数据10万条。

select id,username,upnum,last_update_time from user where last_update_time>=:last_sql_val limit 10000

这个方案的问题:

1) 查询条件是大于等于。因此,当在last_update_time 临界时间2020-09-30 00:00:00有10万条数据(比如添加字段初始化数据),就会让整个数据更新脚本原地踏步
2) 主从同步,已经事务提交延迟的问题依然存在

方案3

使用两个脚本同步数据,新增一个5分钟延迟的同步脚本,减少数据丢失的概率

select id,username,upnum,last_update_time from user where last_update_time>:last_sql_val limit 10000
select id,username,upnum,last_update_time from user where last_update_time>:last_sql_val and last_update_time<DATE_SUB(NOW(),INTERVAL 5 MINUTElimit 30000

这个方案的问题:
1) 不会出现原地踏步的情况,但是在同时间的数据可能会有数据丢失
2) 可以减少因为主从同步,事务提交延迟的数据丢失,但是没办法确保100%不丢失

方案4

使用binlog的方式同步到es。但是对于现有的历史数据,还是需要通过遍历数据表的方式进行同步。

结论

以上内容是目前在工作中mysql同步数据到es的几个方案。按目前网络中大部分文章,都是通过logstash进行数据同步。

但是请注意,logstash的方案是有可能造成成数据丢失的。而这种数据丢失对于insert操作而言,还是可以确定是否有没有丢数据。毕竟对比一下两边的数据量就可以了。

但是,对于update操作呢,怎么确定是否全部同步过去了呢。假如您有一个几千万用户单据信息,而且还是关于钱的,怎么样确定每次更新都正确无误的同步到了es呢?

基于上面的分析,最靠谱的办法,还是通过binlog的方式同步es。


本文章转载自公众号:laowang_php

首页 - PHP 相关的更多文章: