Logstash收集数据并写入Mysql数据库

ES中的日志后续会被删除，但有些重要数据，比如状态码、客户端IP、客户端浏览器版本等，后期可以会按月或年做数据统计等。因此需要持久保存

1.安装Mysql数据库并修改配置​

apt-get -y install mysql-server
#修改配置
vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
#重启
systemctl restart mysql

2.创建库和表并授权用户登录​

#进入mysql界面后执行如下操作：
create database elk character set utf8 collate utf8_bin;
create user elk@'%' identified by '123456';
grant all privileges on elk.* to elk@'%';
flush privileges;
use elk
#创建表，字段对应需要保存的数据字段
create table elklog (clientip varchar(39),responsetime float(10,3),uri varchar(256),status char(3),time timestamp default current_timestamp );

3.Logstash 配置 mysql-connector-java包

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

#在logstash服务器执行
dpkg -i mysql-connector-j_8.0.32-1ubuntu22.04_all.deb
mkdir -p /usr/share/logstash/vendor/jar/jdbc
cp /usr/share/java/mysql-connector-j-8.0.32.jar /usr/share/logstash/vendor/jar/jdbc

4.更改gem源

Logstash 基于 Ruby 语言实现。Ruby 语言使用国外的gem源, 由于网络原因，从国内访问很慢而且不稳定

#在logstash服务器执行
apt-get -y install ruby
#由于默认源是国外的，需要指定为国内源
gem sources --add https://gems.ruby-china.com/ --remove https://rubygems.org/

5.Logstash安装对应插件

查看已经安装有关jdbc的插件

还需要安装output-jdbc插件

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

查看是否安装成功，如果有logstash-output-jdbc说明安装成功：

/usr/share/logstash/bin/logstash-plugin list | grep jdbc

6.配置Logstash将数据写入数据库​

vim nginx_to_mysql_es.conf
input {
file {
path => "/var/log/nginx/access.log"
type => "nginx-accesslog"
#指定输入格式为json
codec => json
start_position => "beginning"
stat_interval => "3"
  }
}
output {
if [type] == "nginx-accesslog" {
elasticsearch {
hosts => ["192.168.131.11:9200","192.168.131.12:9200","192.168.131.13:9200"]​
index => "nginx-accesslog-%{+YYYY.MM.dd}"
    }
jdbc {
#指定mysql连接驱动
driver_jar_path => "/usr/share/logstash/vendor/jar/jdbc/mysql-connector-j-8.0.32.jar"
connection_string => "jdbc:mysql://192.168.131.14/elk?user=elk&password=123456&useUnicode=true&characterEncoding=UTF8"​
statement => ["INSERT INTO elklog(clientip,responsetime,uri,status) VALUES(?,?,?,?)","clientip","responsetime","uri","status"]​
    }
  }
}

之后重启logstash​

执行：/usr/share/logstash/bin/logstash -f nginx_to_mysql_es.conf

向nginx日志追加内容：head -n 10 access_json.log-20220304 >> /var/log/nginx/access.log

此时查看数据库，发现已经有数据了：