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]$
刷新产生日志，看能否写入数据库
