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