ELK(8)：ELK-logstash收集日志写入数据库

ELK(8)：ELK-logstash收集日志写入数据库

在使用ELK对日志进行收集的时候，如果需要对数据进行存档，可以考虑使用数据库的方式

其实我不建议，日志真的太多了，数据库扛不住的

 

安装logstash的数据库插件

安装logstash的数据库插件需要先安装gem源:

 

sudo yum install gem -y

sudo gem –v

#替换gem源

gem source list

sudo gem sources --add https://gems.ruby-china.com/ --remove https://rubygems.org/

#查看当前已经安装的插件：

/usr/share/logstash/bin/logstash-plugin list

 

 

#安装

sudo  /usr/share/logstash/bin/logstash-plugin install logstash-output-jdbc

 

[admin@pe-jira gems]$ sudo  /usr/share/logstash/bin/logstash-plugin install logstash-output-jdbc

Validating logstash-output-jdbc

Installing logstash-output-jdbc

Installation successful

#查看安装

[admin@pe-jira gems]$ sudo  /usr/share/logstash/bin/logstash-plugin list|grep jdbc

logstash-filter-jdbc_static

logstash-filter-jdbc_streaming

logstash-input-jdbc

logstash-output-jdbc

 

 

 

安装数据库的JDBC驱动

https://dev.mysql.com/downloads/connector/j/ 上传到服务器。驱动的路径必须严格一致，否则连接数据库会报错。

我一般都是这里下载

https://mvnrepository.com/artifact/mysql/mysql-connector-java

 

 

sudo mkdir -p /usr/share/logstash/vendor/jar/jdbc

cd /usr/share/logstash/vendor/jar/jdbc

sudo wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.16/mysql-connector-java-8.0.16.jar

ll

sudo chown -R logstash: /usr/share/logstash/vendor/jar/

 

 

配置MySQL权限

create database elk character set utf8 collate utf8_bin;

grant all privileges on elk.* to elk@"%" identified by '123456';

flush privileges;

 

 

 

样例-存储Nginx访问日志

建表

我们在数据库中存储数据的时候，没有必要存储日志的所有内容，只需存储我们需要的重要信息即可，可以根据自身的需求进行取舍。

 

create table kibana_log(host varchar(128),client_ip varchar(128),url varchar(512),status int(4),responsetime float(8,3),http_user_agent varchar(512),time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
#注：time的默认值设置为CURRENT_TIMESTAMP

 

mysql> desc kibana_log;+-----------------+--------------+------+-----+-------------------+-------+

| Field           | Type         | Null | Key | Default           | Extra |

+-----------------+--------------+------+-----+-------------------+-------+

| host            | varchar(128) | YES  |     | NULL              |       |

| client_ip       | varchar(128) | YES  |     | NULL              |       |

| url             | varchar(512) | YES  |     | NULL              |       |

| status          | int(4)       | YES  |     | NULL              |       |

| responsetime    | float(8,3)   | YES  |     | NULL              |       |

| http_user_agent | varchar(512) | YES  |     | NULL              |       |

| time            | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |

+-----------------+--------------+------+-----+-------------------+-------+

7 rows in set (0.00 sec)

 

mysql>

 

 

配置logstash.conf文件

#[admin@pe-jira conf.d]$ cat kibana.conf
input {
    file {
        type => "pe-jira-kibana"
        path => "/home/admin/webserver/logs/kibana.log"
        start_position => "beginning"
        stat_interval => "2"
    }
}

filter{
         json {
                source => "message"
                skip_on_invalid_json => true
                }
        }
output {
  if [type] == "pe-jira-kibana" {
    elasticsearch {
      hosts => ["10.6.76.27:9200"]
      index => "logstash-pe-jira-nginx-kibana-%{+YYYY.MM.dd}"
      }
   jdbc {
    connection_string => "jdbc:mysql://10.6.76.28/elk?user=elk&password=123456&useUnicode=true&characterEncoding=UTF8"
    statement => ["insert into kibana_log(host,client_ip,url,status,responsetime,http_user_agent) VALUES(?,?,?,?,?,?)","host","clientip","url","status","responsetime","http_user_agent"] }
  }

}
  #注意表和日志字段一一对应

 

 

测试文件,查看是否正确:

[admin@pe-jira conf.d]$ sudo /usr/share/logstash/bin/logstash -f  kibana.conf -t

Thread.exclusive is deprecated, use Thread::Mutex

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

[WARN ] 2019-07-15 15:45:18.839 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified

Configuration OK

[INFO ] 2019-07-15 15:45:28.447 [LogStash::Runner] runner - Using config.test_and_exit mode. Config Validation Result: OK. Exiting Logstash

[admin@pe-jira conf.d]$

 

 

刷新产生日志，看能否写入数据库

 

 

转载于:https://www.cnblogs.com/wangxu01/articles/11192891.html