ELK收集mysql用户行为日志

　　安装logstash-input-jdbc 默认logstash6.5.4自带，没有参考上一篇安装步骤：▶▶▶

1 下载mysql驱动jar包

　　下载mysql驱动jar包：https://dev.mysql.com/downloads/connector/j/5.1.html

wget https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-5.1.47.zip

　　#解压将mysql-connector-java-5.1.47.jar拿出来放到/home/logstash/lib/driver.d/，提前创建好存放目录

2 添加配置文件和启动

　　cat jdbc.conf

input {
    stdin {
    }
    jdbc {
      jdbc_connection_string => "jdbc:mysql://10.10.16.227:3306/uc_server_dev"
      jdbc_user => "admin"
      jdbc_password => "didi123"
      jdbc_driver_library => "/home/logstash/lib/driver.d/mysql-connector-java-5.1.47.jar"
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"

      statement => "SELECT 
m.userName as '用户名',
m.areaCode as '区号',
m.mobile as '手机号',
m.email as '邮箱',
m.alias as '别名',
m.realName as '真实姓名',
(case  when m.sexCode=0 then '保密' when m.sexCode=1 then '男' else '女' end) as '性别',
e.company as '公司名称',
e.department as '部门',
e.position as '职位',
e.contry  as '国家',
e.province as '省',
e.city as '市',
r.sysName as '注册系统',
(case  when m.regPTType=0 then 'WEB' else 'APP' end)  as '注册终端',
m.regIP as '注册IP',
m.regTime as '注册时间',
(case  when m.userType=0 then '外部' else '内部' end) as '用户类型',
m.loginTimes as '登陆次数',
m.lastLoginTime as '最后登陆时间',
b.lastActTime as '最后活跃时间',
b.isVisitSJ as '是否访问药渡数据',
b.isVisitZX as '是否访问药渡资讯',
(case  when m.accStatus=-1 then '停用' when m.accStatus=1 then '激活' else '未激活' end) as '激活状态'
from user_main m 
left join user_ext e on m.id=e.userID
left join biz_system_reg r on m.regSysCode=r.sysCode and r.`status`=0
join (
  select a.userID,max(a.reqTime) as lastActTime,
  if(max(if(a.sysCode ='A201801110Y46',1,0))=1,'Y','N') as isVisitZX, 
  if(max(if(a.sysCode ='A20180111CA46',1,0))=1,'Y','N') as isVisitSJ from user_act_info a 
  where 
  a.reqTime>=date_sub(date_sub(curdate(),interval day(curdate()) - 1 day),interval 1 month) -- 上月1号
  and a.sysCode in ('A201801110Y46','A20180111CA46')
  group by a.userID
) b on m.id=b.userID
where m.`status`=0 order by b.lastActTime"
      #statement_filepath => "/opt/uc_server.sql"
      schedule => "* * * * *"
  ###############################
    record_last_run => "true"
        use_column_value => "true"
        tracking_column => "autoid"
    last_run_metadata_path => "/opt/last_id"
        clean_run => "false"
        lowercase_column_names => "false"
  ###############################

    }
}

filter {}

output {
    file {
            path => "/var/log/jdbc.log"
        }
    elasticsearch {
               user =>  "elastic"
            password => "xiange"
            hosts => ["10.10.16.253:9200"]
            index => "jdbc_%{+YYYY.MM.dd}"
            #document_type => "userName"
            document_id => "%{autoid}"
            template_overwrite => true
    }

    stdout {
        codec => json_lines
    }
}

　　启动logstash：

cd /home/logstash
nohup sh bin/logstash -f conf/jdbc.conf &
tail -f nohup.out 

　　同样，创建索引，在发现栏查看数据。