elasticSearch之后端更新logstash配置说明

1、修改要索引的表结构数据
ALTER TABLE `table_name`
ADD COLUMN  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

2、在logstash安装目录/usr/share/logstash/bin下新增配置文件jdbc_client.conf
jdbc_client.conf 内容

input {
    stdin {
    }
    jdbc {
      # mysql jdbc connection string to our backup databse
      jdbc_connection_string => "jdbc:mysql://192.168.1.238:3306/agentmanage?characterEncoding=UTF-8&useSSL=false"
      # the user we wish to excute our statement as
      jdbc_user => "chinaoa-read"
      jdbc_password => "st5RVmsGbbBPfgIj"
      # the path to our downloaded jdbc driver
      jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-5.1.46.jar"
      # the name of the driver class for mysql
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      codec=>plain{charset=>"GBK"}
      jdbc_paging_enabled => "true"
      jdbc_page_size => "10000"
      jdbc_default_timezone => "UTC"
      use_column_value => true
      tracking_column => "mtime"
      record_last_run => true
      last_run_metadata_path => "/usr/share/logstash/bin/station_parameter_client.txt"
      statement_filepath => "/usr/share/logstash/bin/jdbc_client.sql"
      schedule => "* * * * *"
      type => "m_client"
    }
     jdbc {
      # mysql jdbc connection string to our backup databse
      jdbc_connection_string => "jdbc:mysql://192.168.1.238:3306/agentmanage2?characterEncoding=UTF-8&useSSL=false"
      # the user we wish to excute our statement as
      jdbc_user => "chinaoa-read"
      jdbc_password => "st5RVmsGbbBPfgIj"
      # the path to our downloaded jdbc driver
      jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-5.1.46.jar"
      # the name of the driver class for mysql
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      codec=>plain{charset=>"GBK"}
      jdbc_paging_enabled => "true"
      jdbc_page_size => "10000"
      jdbc_default_timezone => "UTC"
      use_column_value => true
      tracking_column => "mtime"
      record_last_run => true
      last_run_metadata_path => "/usr/share/logstash/bin/station_parameter_client2.txt"
      statement_filepath => "/usr/share/logstash/bin/jdbc_client.sql"
      schedule => "* * * * *"
      type => "m_client2"
    }
}

filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
 
output {
    if[type] == "m_client" {
        elasticsearch {
        hosts => "192.168.1.238:9200"
            index => "agentmanage"
            document_type => "m_client"
            document_id => "%{itemid}"
           
        }
    }
    if[type] == "m_client2" {
        elasticsearch {
        hosts => "192.168.1.238:9200"
            index => "agentmanage2"
            document_type => "m_client"
            document_id => "%{itemid}"
           
        }
    }
    stdout {
        codec => json_lines
    }
}

2、按照配置新增/usr/share/logstash/bin/jdbc_client.sql文件，首次跟进自增id全量更新，之后改成增量更新
内容：
select itemid,mcompanyid,follow_uid,follow_uid_nickname,add_uid,UNIX_TIMESTAMP(follow_lasttime) as follow_lasttime,areaid,source,follow_lastlevel,agent_level,follow_laststatus,name,hangye,fund,product_level,product,shop_address,shop_size,shop,opentime,UNIX_TIMESTAMP(addtime) as addtime,UNIX_TIMESTAMP(update_time) as update_time,UNIX_TIMESTAMP(mtime) as mtime from oa_m_client where  mtime >= FROM_UNIXTIME(:sql_last_value)

3、启动 /usr/share/logstash/bin/logstash -f /usr/share/logstash/bin/jdbc_client.conf

4、设置后台运行shell脚本 auto_ela.sh
#!/bin/bash
runuser -l elasticsearch -c '/usr/local/elasticsearch/bin/elasticsearch -d'
runuser -l logstash -c '/usr/share/logstash/bin/logstash -f /usr/share/logstash/bin/jdbc_client.conf &'


参考网址：https://www.cnblogs.com/can-H/articles/7798400.html
报错处理：
1、rpm包启动报错提示配置文件不存在，需要建立配置文件软连接：ln -s /etc/logstash ./config
2、提示文件找不到有可能是文件权限不对，用chown logstash:logstash /etc/logstash处理

目录说明：
    日志目录：/var/log/logstash
    配置目录：/etc/logstash