logstash查询mysql写入es logstash到es

转载

索姆拉 2024-01-31 00:33:02

文章标签 logstash查询mysql写入es elasticsearch elk logstash bc 文章分类 MySQL 数据库

一. 版本和下载

我这边选择的版本是es：7.7.0，所以kibana和logstash也是对应的版本：7.7.0
下载地址：https://www.elastic.co/cn/downloads/past-releases，点进这个地址可以选择对应的产品和版本就可以进行下载
本文按照windows电脑进行示例

二. 安装

安装es：把下载的es压缩包解压，放到自己的目录即可，然后进入es目录的bin目录下，双击：elasticsearch.bat，就可以启动es了，验证是否启动成功可以在浏览器访问：http://localhost:9200/，如果看到正常的一串json格式的es基本信息，就说明es启动成功了
安装kibana：把下载的kibana压缩包解压，放到自己的目录即可，然后进入kibana目录的bin目录下，双击kibana.bat，就可以启动kibana了，验证是否启动成功可以在浏览器访问：http://localhost:5601/app/kibana#/home。如果进入到kibana页面就说明成功了
安装logstash：把下载的logstash压缩包解压，放到自己的目录即可，logstash用来同步数据，需要做各种配置，这里后面再讲

三.配置logstash进行数据同步

创建一个文件夹：mysql(自定义数据同步配置的文件夹)，这个文件夹名称可以自己定义
然后在这个文件夹中创建两个文件：jdbc.conf(数据源和输出目标的配置)和testDataSql.sql(从mysql查询数据的sql)，这两个文件的名字也可以自定义，
然后再创建一个文件：test_last_value.txt(这个文件夹用来处理增量数据同步时记录最新数据值的)
然后再下载数据库对应的数据库驱动，比如我本地mysql是5.7版本，我使用的是mysql-connector-java-5.1.38.jar，这个可以在java项目的pom文件中找到你们使用的驱动版本，然后在maven仓库里面找到对应版本的jar

编辑第1步中创建的jdbc.conf文件：

input {
    stdin {
    }
    jdbc {
      # mysql相关jdbc配置
      jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/test_database?characterEncoding=UTF-8&useSSL=false&autoReconnect=true"
      jdbc_user => "root"
      jdbc_password => "123456"
 
      # jdbc连接mysql驱动的文件
      jdbc_driver_library => "D:/soft/logstash-7.7.0/bin/mysql/mysql-connector-java-5.1.38.jar"
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "10000"
 
      # mysql文件, 也可以直接写SQL语句在此处，如下：
      statement_filepath => "D:/soft/logstash-7.7.0/bin/mysql/testDataSql.sql"
 
      # 这里类似crontab,可以定制定时操作，比如每10分钟执行一次同步(分 时 天 月 年)
      schedule => "* * * * * *"
      # 是否将字段名转换为小写，默认true（如果有数据序列化、反序列化需求，建议改为false）
      lowercase_column_names => false
      # 处理中文乱码问题
      codec => plain { charset => "UTF-8" }
      # 使用其它字段追踪，而不是用时间
      use_column_value => true
      # 追踪的字段  这个字段只有在上面的lowercase_column_names配置为false的时候才会区分大小写  因为不配置为false的话默认是true  查询结果字段默认会变成全小写；   我这里使用的是更新时间
      tracking_column => "updateDate"
      # 这个就是追踪字段的类型，只有数值和时间两个类型(numeric和timestamp，默认numeric) 这个值会记录到last_run_metadata_path 配置的文件中 如果配置是numeric 那么默认值为0 如果配置为timestamp 那么默认值为1970年
      tracking_column_type => "timestamp"
      # 记录上一次运行记录
      record_last_run => true
      # 上一个sql_last_value值的存放文件路径, 必须要在文件中指定字段的初始值  这个就是增量数据同步的关键
      last_run_metadata_path => "D:/soft/logstash-7.7.0/bin/mysql/test_last_value.txt"
      # 是否清除 last_run_metadata_path 的记录,如果为true那么每次都相当于从头开始查询所有的数据库记录
      clean_run => false
      # 这个type可以用来做多个输入源和多个输出源区分  这里举例所以只有一个
      type => "_doc"
    }
}
 
# 此处我不做过滤处理
filter {}
 
output {
    #输出到elasticsearch的配置
    elasticsearch {
        hosts => ["127.0.0.1:9200"]
        index => "test_index"
        #将"_id"的值设为mysql的autoid字段
        document_id => "%{id}"
        template_overwrite => true
    }
 
    #这里输出调试，正式运行时可以注释掉
    stdout {
        codec => json_lines
    }
}

编辑第一步中创建的testDataSql.sql

SELECT  id as id, create_date as createDate, update_date as updateDate FROM `test_table` where update_date>date_add(:sql_last_value,INTERVAL 8 HOUR)

注意这条sql，这是我的一个举例，我的字段都带有别名，并且切换了驼峰式，这是为了是es中存储的字段名字和java代码中存储一样，方便后续从es中查询数据的时候可以直接序列化为java对象(我不知道有没有别的简便方法，反正这样没问题)
其次就是这个sql的where条件后面带了一个条件：update_date>date_add(:sql_last_value,INTERVAL 8 HOUR)，这个就是增量数据的查询条件，一条数据怎么才算增量数据就是由这个条件确定的，而其中的：:sql_last_value就是第2步中配置项中的last_run_metadata_path里配置的文件路径对应的文件中的值，也就是每次同步数据后最新一条的updateDate值，那么下次同步数据的时候，sql根据这个条件就不会查到非增量数据，同时第2步中配置项中的tracking_column配置的值就是updateDate，实际使用的时候可以根据场景更换这个字段
还有其中date_add函数是处理时区问题，给时间+8小时
编辑第一步中的创建的test_last_value.txt

1970-00-00 00:00:00

通过上面的步骤，我们应该已经知道这个文件就是用来记录增量数据最新值的，那么在jdbc.conf配置文件的注释中也写到了，这个文件的内容需要根据jdbc.conf配置项中的tracking_column_type决定，这个配置项只有数值和时间两个类型(numeric和timestamp，默认numeric) 这个值会记录到last_run_metadata_path 配置的文件中，如果配置是numeric 那么默认值为0，如果配置为timestamp 那么默认值为1970年
所以如果你是根据数值类型记录最新值，那么最开始的时候你可以在这个文件中写：0(或者你自己定义的最新值)
但是如果你是根据日期类型记录最新值，那么最开始的时候你可以在这个文件中写：1970-00-00 00:00:00(这个是简单写，他会自动改)，又或者你可以根据你自己的情况去选择一个时间，只要跟你的追踪字段匹配就行

四. 执行脚本进行同步

经过上面的配置全部完成后，就可以准备开始同步数据了，windows电脑进入cmd，然后进入logstash目录的bin目录下，执行脚本：logstash.bat -f mysql\jdbc.conf，-f 后面的这个mysql\jdbc.conf就是我们创建的配置文件。然后执行后看输出日志，期间不要乱按键盘，如果出现报错，那么根据报错信息和配置文件的理解进行修改，一般就不会有错误了，如果还是遇到各种奇怪的错误，那么90%都是配置文件的问题，请认真检查配置文件TvT

logstash查询mysql写入es logstash到es_elk