ElasticSearch5+logstash的logstash-input-jdbc实现mysql数据同步

最新推荐文章于 2022-08-23 09:36:38 发布

SakuraZ

最新推荐文章于 2022-08-23 09:36:38 发布

阅读量398

点赞数

安装

https://www.cnblogs.com/phpshen/p/6098333.html

在实现的路上遇到了各种坑，再次验证官方文档只能产考不能全信！

ElasticSearch安装就不说了上一篇有说！

安装logstash

官方：https://www.elastic.co/guide/en/logstash/current/installing-logstash.html

1.下载公共密钥
rpm --import https://artifacts.elastic.co/GPG-KEY-elasticsearch
2.添加yum源
vim  /etc/yum.repos.d/logstash.repo
文件中写入
[logstash-5.x]
name=Elastic repository for 5.x packages
baseurl=https://artifacts.elastic.co/packages/5.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md
保存退出

3.使用yum安装
yum install logstash
4.验证是否安装成功

进入 logstash 安装目录
cd /usr/share/logstash
运行
bin/logstash -e 'input { stdin { } } output { stdout {} }'
等待几秒钟出现

The stdin plugin is now waiting for input:

然后输入
hello world
得到类似的结果

2016-11-24T08:01:55.949Z bogon hello world

安装logstash-input-jdbc插件

1.修改ruby仓库镜像

如果没有安装 gem 的话安装gem
yum install gem
替换国内的镜像
gem sources --add https://gems.ruby-china.org/ --remove https://rubygems.org/
验证是否成功
gem sources -l
修改Gemfile的数据源地址
whereis logstash # 查看logstash安装的位置， 我的在 /usr/share/logstash目录
cd /usr/share/logstash
vim Gemfile
修改 source 的值 为： "https://gems.ruby-china.org/"
vim  Gemfile.jruby-1.9.lock # 找到 remote 修改它的值为：
https://gems.ruby-china.org/
或者直接替换源这样你不用改你的 Gemfile 的 source。
gem install bundler


$ bundle config mirror.https://rubygems.org https://gems.ruby-china.org/
 
然后开始安装
bin/logstash-plugin  install logstash-input-jdbc
如果镜像地址没有改错的话应该可以直接安装

或者进入源码地址的release页面logstash-input-jdbc

2.开始同步 mysql 数据

需要建立两个文件一个 .conf后缀的一个 .sql 后缀

一个 mysql 的Java 驱动包： mysql-connector-java-5.1.40-bin.jar

filename.conf 内容：

里面的参数可以参考 logstash-input-jdbc官方参考文档
input {
    stdin {
    }
    jdbc {
    # 数据库地址  端口  数据库名
      jdbc_connection_string => "jdbc:mysql://localhost:3306/shen"
    # 数据库用户名      
    jdbc_user => "root"
    # 数据库密码
      jdbc_password => "rootroot"
    # mysql java驱动地址 
      jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-5.1.40-bin.jar"
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"
      # sql 语句文件
      statement_filepath => "filename.sql"
      schedule => "* * * * *"
      type => "jdbc"
    }
}
output {
 stdout {
        codec => json_lines
    }
　　　elasticsearch {
        hosts  => "localhost:9200"
        index => "contacts"
　　　　　document_type => "contact"
        document_id => "%{id}"
    }
}
filename.sql
select * from a
注意：在你的数据库里要有一个数据库名字和filename.conf 里的对应就可以了表明和 filename.sql 里的对应就可以了在表中有一个id字段是为了和filename.conf 中document_id => "%{id}" 这个参数对应可以执行修改

然后开始执行
bin/logstash -f fielname.conf

配置

https://segmentfault.com/a/1190000011784259

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容量就不断上升

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

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

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

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

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

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

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

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

下载的文件的链接：

logstash5.6.1

https://artifacts.elastic.co/downloads/logstash/logstash-5.6.1.rpm

logstash-input-jdbc

https://codeload.github.com/logstash-plugins/logstash-input-jdbc/tar.gz/v4.3.13

版本是对应好的