logstash使用mysql作为输出源

在开发中我们碰到需要将kafka中的数据持久化到mysql,网上查阅了很多博客发现了一些问题，

后经本人实验跑通流程后，故记录此流程，以便有需求的人参考。

1. 环境  system: centOS 7.2

             logstash: 5.5.2

             mysql:  5.6

 

2.  kafka-to-mysql.conf内容

input {    
kafka {    
  bootstrap_servers=> "127.0.0.1:9092"
  group_id => "logstash-kafka-group"    
  topics => ["nginx_test3"]

 auto_offset_reset => "latest" 
 #reset_beginning => false # boolean (optional)， default: false    
 #consumer_threads => 1  # number (optional)， default: 1    
 #decorate_events => false # boolean (optional)， default: false,//此属性会将当前topic、offset、grouppartition等信息也带到message中    
  }    
}    
filter{
mutate {
        gsub =>[
            "message", "\u0000", ""   #处理nginx端发送的过来的数据logstash读取出来的有自动补全的空格
        ]
    }
json{
 skip_on_invalid_json=> false  #如果数据不符合json规范，跳过json解析，不抛出异常
 source => "message"
 remove_field => ["@timestamp"]
}
mutate {
        convert => ["end", "integer"] #用于计算response
        convert => ["start", "integer"]
}
ruby {code => "event.set('response', event.get('end').to_i - event.get('start').to_i)"
     } #calculate the request's response time

ruby {
      code => "event.set('create_date',event.get('start').to_i/1000)" #calculate creation_time
     }
# date {
#
 #       locale => "zh"
  #      timezone => "Asia/Shanghai" 
   #     match => ["end", "MM/dd/yyyy HH:mm:ss+08:00","UNIX_MS"]
    #    target => "endTime"

    #}

}

output {    
 if[uuid] #if json is success  write to mysql
 {  
jdbc {
driver_jar_path => "/app/mysql/mysql-connector-java-8.0.11.jar"
driver_class => "com.mysql.cj.jdbc.Driver"
connection_string => "jdbc:mysql://127.0.0.1:3306/sdgp?autoReconnect=true"
username => "root"
password => "root"
statement => ["insert into nginx_request_data(auth,return_code,host,uuid,client,referrer,url,args,request_body,method_name,protocol,create_date,start_timestamp,end_timestamp,response_time) values(?,?,?,?,?,?,?,?,?,?,?,from_unixtime(?),?,?,?)","%{[auth]}","%{[return_code]}","%{[host]}","%{[uuid]}","%{[client]}","%{[referrer]}","%{[url]}","%{[args]}","%{[request_body]}","%{[method_name]}","%{[protocol]}","%{[create_date]}","%{[start]}","%{[end]}","%{[response]}" ]
    

}
# stdout {
 #  codec => rubydebug
  # }
}
}