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

MYSQL logstash 同步增量数据到ES

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

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

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 NOT NULL,
  `upnum` decimal(20,0) NOT 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 MINUTE) limit 30000

这个方案的问题:

1) 不会出现原地踏步的情况，但是在同时间的数据可能会有数据丢失

2) 可以减少因为主从同步，事务提交延迟的数据丢失，但是没办法确保100%不丢失

方案4

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

结论

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

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

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

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