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

标签: | 发表时间:2020-10-07 15:59 | 作者:
出处:https://mp.weixin.qq.com

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。

相关 [mysql logstash 同步] 推荐:

使用logstash同步mysql 多表数据到ElasticSearch实践 - 三度 - 博客园

- -
参考样式即可,具体使用配置参数根据实际情况而定. jdbc_connection_string => "jdbc:mysql://localhost/数据库名". jdbc_driver_library => "mysql-connector-java-5.1.45-bin.jar所在位置". type => "数据库表名1".

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

- -
MYSQL logstash 同步增量数据到ES. 最近一段时间,在使用mysql通过logstash-jdbc同步数据到es,但是总是会有一定程度数据丢失. logstash-jdbc无非是通过sql遍历数据表的所有数据,然后同步到es. 对于表里面的所有字段都需要查出来然后同步到es中去. 数据同步脚本分为全量同步与增量同步.

同步mysql数据到hive

- - ITeye博客
地址为:http://archive.cloudera.com/cdh/3/下载相应版本,如sqoop-1.2.0-CDH3B4.tar.gz. 地址为:http://archive.cloudera.com/cdh/3/,版本可以为hadoop-0.20.2-CDH3B4.tar.gz. 3.解压 sqoop-1.2.0-CDH3B4.tar.gz ,hadoop-0.20.2-CDH3B4.tar.gz 到某目录如/home/hadoop/,解压后的目录为.

MySQL半同步复制(Semisynchronous Replication)

- - IT技术博客大学习
MySQL5.5引入了半同步复制(Semi-synchronous Replication),以下是对于半同步复制的认知和理解:. 半同步启动需要主从两端都需要加载安装各自对应的semi模块,从库端支持半同步功能的数量至少一台;主库端当一个事务成功提交后,并不及时反馈给前端用户,该线程会被临时block,等待由从库端返回确认该条事务也同时成功写入到relay log中的receipt(回执确认),这时主库线程才返回给当前session告知操作完成,半同步复制并不关心在从库一端该事务是否都被执行并被提交完成.

MySQL数据库设置主从同步

- - CSDN博客架构设计推荐文章
MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力. 1、可以作为一种备份机制,相当于热备份. 2、可以用来做读写分离,均衡数据库负载. 1、主从数据库版本一致,建议版本5.5以上. # 日志文件名 log-bin = mysql-bin # 日志格式,建议mixed binlog_format = mixed # 主数据库端ID号 server-id = 1.

Databus for MySQL 同步 · linkedin/databus Wiki · GitHub

- -
A frequently asked question on the Databus open source mailing list is about the possibility of capturing changes in MySQL through Databus. $ (cd bin && ./create_person.sh): The script assumes that MySQL is started on port 33066; please change it appropriately for your setup.

MySQL中的半同步复制

- - 学习日志
MySQL当前存在的三种复制模式有:异步模式、半同步模式和组复制模式. 注意:MySQL复制模式没有“同步复制”这一项的,文章中只是为了读者方便理解半同步复制的概念才介绍了同步复制概念 https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html.

MySQL 数据同步 主主设置(互为主备)

- - CSDN博客推荐文章
MySQL 数据同步 主主设置(互为主备). 两台MySQL主机做为服务器:. 这一步在每一台(主)服务器上创建一个用户,并为之授权,使它们可以互相访问彼此的数据库. 创建一个充许master-2来访问的用户rep,密码为rep. 创建一个充许master-1来访问的用户rep密码为rep. 备注:为了操作方便,我们在两台服务器上,指定的访问权限时,设定的用户名和密码,一摸一样 .

Galera:多主同步MySQL集群原理解析

- - 进击的程序猿
Galera Cluster是基于MySQL/innodb二次开发而成的一个支持“多主同步”的数据库主从集群. 强调主从集群意味着Galera Cluster的每个节点充当一个数据冗余,而没有在节点间做分库分表的水平扩展. Galaer官网中为Galera Cluster洋洋洒洒罗列了10大优势,其实总结下来无非上文用引号注明的两点:.

巧用Percona Toolkit解决MySQL主从不同步问题

- - 极客521 | 极客521
由于各种原因,mysql主从架构经常会出现数据不一致的情况出现,大致归结为如下几类. 2:执行non-deterministic query. 3:回滚掺杂事务表和非事务表的事务. 4:binlog或者relay log数据损坏. 数据不同步给应用带来的危害是致命的,当出现主从数据不一致的情况,常见的应对方法是先把从库下线,然后找个半夜三更的时间把应用停掉,重新执行同步,如果数据库的体积十分庞大,那工作量可想而知,会让人崩溃.