ELK-日志写入数据库

ps：写入数据库的目的是用于持久化保存重要数据，比如状态码、客户端IP、客户端浏览器版本等等，用于后期按月做数据统计等。

一 安装数据库

[root@linux-node4 ~]# yum install mariadb mariadb-server
[root@linux-node4 ~]# systemctl start mariadb
[root@linux-node4 ~]# systemctl enable mariadb
[root@linux-node4 ~]# mysql

MariaDB [(none)]> create database elk  character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> grant all privileges on elk.* to elk@"%" identified by '123456';
Query OK, 0 rows affected (0.05 sec)

[root@linux-node4 ~]# mysql -uelk -p123456 -h10.0.0.18 -测试
[root@linux-node3 logs]# yum install mysql –y —安装mysql命令
[root@linux-node3 logs]# mysql -uelk -p123456 -h10.0.0.18 --测试

oot@linux-node3 logs]# mkdir -pv /usr/share/logstash/vendor/jar/jdbc
[root@linux-node3 logs]# cd /usr/share/logstash/vendor/jar/jdbc
-rw-r–r-- 1 root root 4297296 Jun 3 2018 mysql-connector-java-5.1.42.zip

[root@linux-node3 jdbc]# unzip mysql-connector-java-5.1.42.zip
[root@linux-node3 jdbc]# ll
total 4200
drwxr-xr-x 4 root root 151 Apr 17 2017 mysql-connector-java-5.1.42
-rw-r–r-- 1 root root 4297296 Jun 3 2018 mysql-connector-java-5.1.42.zip

[root@linux-node3 jdbc]# mv mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar .
[root@linux-node3 jdbc]# mv mysql-connector-java-5.1.42.zip mysql-connector-java-5.1.42 /tmp
[root@linux-node3 jdbc]# cd …/…/
[root@linux-node3 vendor]# ll 这里是第三方的库
total 0
drwxr-xr-x 3 logstash logstash 19 Aug 19 16:26 bundle
drwxr-xr-x 3 root root 18 Aug 20 16:50 jar
drwxr-xr-x 6 logstash logstash 87 Aug 19 16:26 jruby
[root@linux-node3 vendor]# chown logstash.logstash ./* -R

[root@linux-node4 ~]# yum install gem
[root@linux-node4 ~]#gem sources --add https://gems.ruby-china.com/ --remove https://rubygems.org/
$ gem update --system # 这里请翻墙一下
$ gem -v
2.6.3
[root@linux-node4 ~]#/usr/share/logstash/bin/logstash-plugin list
[root@linux-node4 ~]#/usr/share/logstash/bin/logstash-plugin install logstash-output-jdbc
Validating logstash-output-jdbc
Installing logstash-output-jdbc
WARNING: can not set Session#timeout=(0) no session context
Installation successful

[root@linux-node4 ~]# /usr/share/logstash/bin/logstash-plugin list | grep jdbc
logstash-input-jdbc
logstash-output-jdbc

二 用工具连接数据库，建表

添加到配置文件里
[root@linux-node3 ~]# vim /etc/logstash/conf.d/nginx.conf

input{
  file {
    path => "/var/log/nginx/access.log"
    type => "nginx-access-log-17"
    start_position => "beginning"
    stat_interval => "2"
    codec => "json"
  }
  file {
    path => "/var/log/messages"
    type => "system-log-17"
    start_position => "beginning"
    stat_interval => "2"
  }
}

output{
  if [type] == "nginx-access-log-17" {
    elasticsearch {
    hosts => ["10.0.0.17:9200"]
    index => "logstash-nginx-accesslog-18-%{+YYYY.MM.dd}"
    }
     jdbc {
   connection_string => "jdbc:mysql://10.0.0.18/elk?user=elk&password=123456&useUnicode=true&characterEncoding=UTF8"
   statement => ["INSERT INTO elklog(host,url,clientip,responsetime,upstreamtime) VALUES(?,?,?,?,?)", "host","clientip","url","responsetime","upstreamtime"]}
  }
  if [type] == "system-log-17"{
    elasticsearch {
      hosts => ["10.0.0.18:9200"]
      index => "logstash-system-log-17-%{+YYYY.MM.dd}"
    }     
  }      
}