logstash jdbc同步mysql多表数据到elasticsearch

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/dh798417147/article/details/78543245

一、环境准备

  1. elasticsearch5.5.3
  2. logstash5.5.3
  3. mysql5.6.37
  4. mysql表中必须有时间戳字段

二、测试条件

  1. 同步两张表数据到索引
    • 以表名作为es索引名称
    • 以表的注解id作为es的object id

三、书写logstash配置文件,并命名为dbToEs.conf

input {
   jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.36.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://192.168.1.151:3306/mydb"
    jdbc_user => "root"
    jdbc_password => "root@hiekn"
    schedule => "* * * * *"
    statement => "SELECT * from tb_user where lastModifyTime > :sql_last_value"
    add_field => { "[@metadata][type]" => "mydb" } 
  }
  jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.36.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://192.168.1.151:3306/mydb2"
    jdbc_user => "root"
    jdbc_password => "root@hiekn"
    schedule => "* * * * *"
    statement => "SELECT * from tb_user where lastModifyTime > :sql_last_value"
    add_field => { "[@metadata][type]" => "mydb2" } 
  }
}

filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
    mutate  {
        remove_field => ["@timestamp","@version"]
    }
}

output {
    if [@metadata][type] == "mydb" {
       elasticsearch {
            hosts => [ "192.168.1.157:9200" ]
            #manage_template => false
            index => "mydb"
            document_id => "%{id}"
            document_type => "mydb_data"
        }
    } else if [@metadata][type] == "mydb2"{
        elasticsearch {
            hosts => [ "192.168.1.157:9200" ]
            index => "mydb2"
            document_id => "%{id}"
            document_type => "mydb2_data"
        }
    }
}

四、运行logstash

  1. 将配置文件放在bin目录下
  2. 执行./logstash -f ./dbToEs.conf

五、说明

  1. 第一次执行时间戳是以1970-01-01 00:00:00为起点
  2. logstash每间隔1分钟执行一次sql
展开阅读全文

没有更多推荐了,返回首页