logstash jdbc同步mysql多表数据到elasticsearch

一、环境准备

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