Logstash日志写入Mysql数据库

一 Mysql数据库配置

  1.1.1 mysql建库授权

mysql> create database elk  character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql>  grant all privileges on elk.* to elk@"192.168.10.%" identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush  privileges;

    1.1.2 建表

   创建表名为：elklog

  按照自己所需在日志里面取key创建自定义的表

  time默认值没有定义为CURRENT_TIMESTAMP的状态

二 安装插件

2.1.1 logstash配置mysql-connector-java包

    

   MySQL Connector/J是MySQL官方JDBC驱动程序，JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。

  官方下载地址：https://dev.mysql.com/downloads/connector/

  下载地址：https://dev.mysql.com/downloads/connector/j/

mkdir -p /usr/share/logstash/vendor/jar/jdbc
cd /usr/share/logstash/vendor/jar/jdbc
rz
ls
tar xf mysql-connector-java-8.0.12.tar.gz 
mv mysql-connector-java-8.0.12/mysql-connector-java-8.0.12.jar .
mv mysql-connector-java-8.0.12 mysql-connector-java-8.0.12.tar.gz /tmp
[root@localhost vendor]# pwd
//usr/share/logstash/vendor
[root@localhost vendor]# chown -R logstash.logstash jar

 1.1.2 安装 logstash-output-jdbc插件

    更改gem源：

       国外的gem源由于网络原因，从国内访问太慢而且不稳定，还经常安装不成功，因此之前一段时间很多人都是使用国内淘宝的gem源https://ruby.taobao.org/，现在淘宝的gem源虽然还可以使用已经停止维护更新，其官方介绍推荐使用https://gems.ruby-china.org。

yum install gem
gem sources --add https://gems.ruby-china.org/ --remove https://rubygems.org/ 
[root@localhost ~]# gem sources --add https://gems.ruby-china.org/ --remove https://rubygems.org/ 
source https://gems.ruby-china.org/ already present in the cache
source https://rubygems.org/ not present in cache
[root@localhost ~]#  gem source list
*** CURRENT SOURCES ***

https://gems.ruby-china.org/

    安装插件：

[root@localhost ~]# /usr/share/logstash/bin/logstash-plugin   install  
[root@localhost ~]# /usr/share/logstash/bin/logstash-plugin  list

三 配置logstash Nginx配置文件

[root@localhost ~]# cat /etc/logstash/conf.d/nginx.conf 
input {
      file {
          path => "/opt/vhosts/fatai/logs/access_json.log"
              start_position => "beginning"
          type => "nginx-accesslog"
          codec => json
                  stat_interval => "2"          
      }
      file {
           path => "/var/log/messages" 
               start_position => "beginning" 
           type => "systemlog-test"   
                   stat_interval => "2"          
      }
      file {
           type => "nginx-error" 
           path => [ "/var/log/nginx/error.log" ]
           tags => [ "nginx","error"]
                start_position => "beginning"
                   stat_interval => "2"          
       }
}


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

      }
    if [type] == "systemlog-test" {
      elasticsearch {
           hosts => ["192.168.10.10:9200"]
           index => "logstash-systemlog-test-%{+YYYY.MM.dd}"
      }}
    if [type] == "nginx-error" {
      elasticsearch {
          hosts => ["192.168.10.10:9200"]
          index => "logstash-nginx-error-test-%{+YYYY.MM.dd}"                                                                      
    }}
}

   验证配置文件并重启Logstash

[root@localhost ~]# /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/nginx.conf -t
WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults
Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console
Configuration OK
[root@localhost ~]# systemctl restart logstash.service

四 数据库查看数据