在开发中我们碰到需要将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
# }
}
}