logstash-input-jdbc同步mysql数据到elasticsearch

7

前言:

有项目以前使用coreseek来做索引,随着时间推移,技术上面也要更新换代,该项目后端从php5升级到php7,
而这时候问题来了,coreseek的作者已经不再更新,官网也关闭了,因此寻求其他的索引软件替代,而elasticsearch刚好可以很好的满足业务

一、安装

1.首先需要安装java,elasticsearch,logstash,可以参考我另外一篇文章:https://segmentfault.com/a/11...

2.然后logstash-input-jdbc安装

cd /opt/logstash
.bin/plugin install logstash-input-jdbc

二、配置

安装还是比较容易的,主要是配置这里有一些坑,这里需要为logstash准备配置文件jdbc.conf和jdbc.sql

jdbc.conf文件内容

input {
    stdin {
    }
    jdbc {
      // mysql相关jdbc配置
      jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/your_mysql_database"
      jdbc_user => "mysql_user"
      jdbc_password => "mysql_password"
 
      // jdbc连接mysql驱动的文件目录,可去官网下载:https://dev.mysql.com/downloads/connector/j/
      jdbc_driver_library => "/opt/logstash/conf/mysql-connector-java/mysql-connector-java-5.1.44-bin.jar"
      # the name of the driver class for mysql
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"

      // mysql文件, 也可以直接写SQL语句在此处,如下:
      // statement => "SELECT * from Table_test;"
      statement_filepath => "/opt/logstash/conf/jdbc.sql"

      // 这里类似crontab,可以定制定时操作,比如每10分钟执行一次同步(分 时 天 月 年)
      schedule => "*/10 * * * *"
      type => "jdbc"

      // 是否记录上次执行结果, 如果为真,将会把上次执行到的 tracking_column 字段的值记录下来,保存到 last_run_metadata_path 指定的文件中
      record_last_run => "true"

      // 是否需要记录某个column 的值,如果record_last_run为真,可以自定义我们需要 track 的 column 名称,此时该参数就要为 true. 否则默认 track 的是 timestamp 的值.
      use_column_value => "true"

      // 如果 use_column_value 为真,需配置此参数. track 的数据库 column 名,该 column 必须是递增的. 一般是mysql主键
      tracking_column => "autoid"

      last_run_metadata_path => "/opt/logstash/conf/last_id"

      // 是否清除 last_run_metadata_path 的记录,如果为真那么每次都相当于从头开始查询所有的数据库记录
      clean_run => "false"

      //是否将 字段(column) 名称转小写
      lowercase_column_names => "false"
    }
}

// 此处我不做过滤处理,如果需要,也可参考elk安装那篇
filter {}

output {
    // 输出到elasticsearch的配置
    elasticsearch {
        hosts => ["127.0.0.1:9200"]
        index => "jdbc"

        // 将"_id"的值设为mysql的autoid字段
        document_id => "%{autoid}"
        template_overwrite => true
    }

    // 这里输出调试,正式运行时可以注释掉
    stdout {
        codec => json_lines
    }
}

下面是mysql文件jdbc.sql,注意:sql_last_value,后面会说道

SELECT
*

FROM
Table_test

WHERE
autoid > :sql_last_value

三、遇到的问题

相信很多人在安装和实际使用的过程中有这样那样的问题,这里我记录了一些自己遇到的

1.elasticsearch数据重复以及增量同步

在默认配置下,tracking_column这个值是@timestamp,存在elasticsearch就是_id值,是logstash存入elasticsearch的时间,这个值的主要作用类似mysql的主键,是唯一的,但是我们的时间戳其实是一直在变的,所以我们每次使用select语句查询的数据都会存入elasticsearch中,导致数据重复。
解决方法:在要查询的表中,找主键或者自增值的字段,将它设置为_id的值,因为_id值是唯一的,所以,当有重复的_id的时候,数据就不会重复

  // 是否记录上次执行结果, 如果为真,将会把上次执行到的 tracking_column 字段的值记录下来,保存到 last_run_metadata_path 指定的文件中
  record_last_run => "true"

  // 是否需要记录某个column 的值,如果record_last_run为真,可以自定义我们需要 track 的 column 名称,此时该参数就要为 true. 否则默认 track 的是 timestamp 的值.
  use_column_value => "true"

  // 如果 use_column_value 为真,需配置此参数. track 的数据库 column 名,该 column 必须是递增的. 一般是mysql主键
  tracking_column => "autoid"

2.数据同步频繁,影响mysql数据库性能

我们写入jdbc.sql文件的mysql语句是写死的,所以每次查询的数据库有很多是已经不需要去查询的,尤其是每次select * from table;的时候,对mysql数据库造成了非常大的压力

解决:(1)根据业务需求,可以适当修改定时同步时间,我这里对实时性相对要求较高,因此设置了10分钟

  // 这里类似crontab,可以定制定时操作,比如每10分钟执行一次同步(分 时 天 月 年)
  schedule => "*/10 * * * *"

(2)设置mysql查询范围,防止大量的查询拖死数据库

  // 如果 use_column_value 为真,需配置此参数. track 的数据库 column 名,该 column 必须是递增的. 一般是mysql主键
  tracking_column => "autoid"

  // 上次执行数据库的值,该值是上次查询时tracking_column设置的字段最大值
  last_run_metadata_path => "/opt/logstash/conf/last_id"

  // 是否清除 last_run_metadata_path 的记录,如果为真那么每次都相当于从头开始查询所有的数据库记录
  clean_run => "false"

在sql语句这里设置select * from WHERE autoid > :sql_last_value;
注意:如果你的语句比较复杂,autoid > :sql_last_value一定要写在WHERE后面,然后接AND即可

3.elasticsearch存储容量不断上升

稍微观察下就会发现,即使没有新的数据写入到elasticsearch里面,但只要logstash定时每次运行,elasticsearch容量就不断上升

clipboard.png

过一段时间看,占用空间增大,其实elasticsearch数据是一样的

clipboard.png

原因:在elasticsearch/nodes/0/indices/jdbc/{0,1,2,3,4}/下有个translog,这个是elasticsearch的事务日志,类似mysql的binlog。elasticsearch为了数据安全,接收到数据后,先将数据写入内存和translog,然后再建立索引写入到磁盘,这样即使突然断电,重启后,还可以通过translog恢复,不过这里由于我们每次查询都有很多重复的数据,而这些重复的数据又没有写入到elasticsearch的索引中,所以就囤积了下来

clipboard.png

解决:查询官网说会定期refresh,会自动清理掉老的日志,因此可不做处理

4.增量同步和mysql范围查询导致mysql数据库有修改时无法同步到以前的数据

增量同步解决了,mysql每次都小范围查询,解决了数据库压力的问题,不过却导致无法同步老数据的修改问题

解决:可根据业务状态来做,如果你数据库是修改频繁类型,那只能做全量更新了,但是高频率大范围扫描数据库来做的索引还不如不做索引了(因为建立索引也是有成本的),我们做索引主要是针对一些数据量大,不常修改,很消耗数据库性能的情况。我这里是数据修改较少,而且修改也一般是近期数据,因为同步时,我在mysql范围上面稍微调整一下

如:autoid > (:sql_last_value-100000),每次扫描上次扫描范围往之前再多10W行,这样扫描的数据量相对较小,也照顾到了可能会修改的数据类型

但是范围扫描还存在一个问题,就是过往的数据写入了elasticsearch之后,如果有修改,而又不在范围扫描以内,那么elasticsearch就不会同步到。因此,我们还可以定期做一次全量或者更大范围的同步,只需要修改范围值即可。具体的值当然可以根据业务来定


如果觉得我的文章对你有用,请随意赞赏

你可能感兴趣的

13 条评论
apple · 2017-11-12

logstash感觉用起来不够灵活,脚本没办法动态传参,只能通过:sql_last_value要么以时间戳,要么以自增列来勉强实现动态效果。而且使用enable_paging时,其是直接将原sql语句作为子查询,拼接offset和limit来实现。当查询出的结果集较大时存在深度分页瓶颈,数据的抽取效率会越来越慢。现在基本都快弃用logstash了

回复

0

我目前的业务上面还没有遇到瓶颈,量相对较小。大量极你有什么更好的方案吗

青菜 作者 · 2017-11-13
0

@青云 我目前也正在面临大数据量的同步。初步的想法是使用阿里开源的datax以及kafka connect来进行数据的全量同步,以及基于时间戳的增量同步。如果实时性要求较高的话,最好使用canal来进行增量订阅与消费。当然了如果你们公司已经有大数据平台,事先已经将数据同步到hbase层了,可以直接使用官网提供的elastic-hadoop组件直接将数据从hbase或hive层捞过来。你有更好的方案的话可以一起分享下。

apple · 2017-11-28
maokeyang · 2017-11-13

更新和删除有什么好的解决方案吗 ?还有两张表数据查询 es有啥什么好的处理方式 博主

回复

0

这个可以自动更新啊,但是logstash暂时没有比较好的删除方案

青菜 作者 · 2017-11-14
0

@maokeyang 在业务上删除,一般都使用软删除。硬删除风险太高。利用logstash将删除标记同步到ES中,在ES集群中添加定时删除任务就好了。

apple · 2017-11-28
双枪小黄人 · 2018-08-10
select * from table  WHERE autoid > :sql_last_value;

这个好像只能新增同步
不能更新同步

select * from table  

这样新增和修改都能同步,但是查询全表,
请问这个有什么办法解决还是我哪里写错了

回复

0

select * from table WHERE autoid > (:sql_last_value - 10000); 你可以这样来更新最近的条数

青菜 作者 · 2018-08-14
0

看来没啥更新和删除好的解决方案啊

双枪小黄人 · 2018-08-14
常伴在风 · 8月14日

楼主,现在有张表数据3000W+ 实时性比较高, 用logstash全量同步太耗时了, 怎么解决呢

回复

0

你不用总是做全量同步啊,如果你数据新增频繁,SQL可以缩小范围;如果你数据有很多修改,又对实时性要求很高,还不如从数据库着手,比如搜索使用单独的从库,或者表太大拆分也行

青菜 作者 · 8月14日
一笑而过 · 9月12日

更新同步可以采用更新时间字段作为tracking_column,sql以tracking_column字段排序查询,不需要全量导入也可以的

回复

0

这是个好方法,学习了

青菜 作者 · 9月16日
载入中...