當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

ELK之收集日志到mysql数据库

發(fā)布時間：2025/6/17 数据库 26 豆豆

生活随笔收集整理的這篇文章主要介紹了 ELK之收集日志到mysql数据库小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

? 寫入數(shù)據(jù)庫的目的是持久化保存重要數(shù)據(jù),比如狀態(tài)碼、客戶端瀏覽器版本等,用于后期按月做數(shù)據(jù)統(tǒng)計(jì)等.

環(huán)境準(zhǔn)備

linux-elk1:10.0.0.22,Kibana ES Logstash Nginx

linux-elk2:10.0.0.33,MySQL5.7

1.linux-elk2上配置數(shù)據(jù)庫

安裝好數(shù)據(jù)庫后,配置,并授權(quán)

mysql -uroot -p'Root123!@#' create database elk character set utf8 collate utf8_bin; grant all privileges on elk.* to elk@'10.0.0.%' identified by 'Elk123!@#'; flush privileges;# 在linux-elk1上驗(yàn)證是否能登錄elk2上的mysql mysql -u elk -h 10.0.0.33 -p'Elk123!@#'

2.配置JDBC數(shù)據(jù)庫驅(qū)動

/usr/share/logstash/bin/logstash-plugin list | grep jdbc logstash-input-jdbc # 沒有l(wèi)ogstash-output-jdbc # 安裝logstash的數(shù)據(jù)庫驅(qū)動需要先安裝gem源 yum -y install gem gem -v gem source list # 目前是一個國外的源,需要將其換成rubychina的 gem sources --add https://gems.ruby-china.org/ --remove https://rubygems.org/ Error fetching https://gems.ruby-china.org/:bad response Not Found 404 (https://gems.ruby-china.org/specs.4.8.gz) # 替換不成功,是因?yàn)楣倬W(wǎng)換地址了 gem sources --add https://gems.ruby-china.com/ --remove https://rubygems.org/ https://gems.ruby-china.com/ added to sources https://rubygems.org/ removed from sources

RubyChina官網(wǎng)由org換成com

安裝JDBC驅(qū)動

報(bào)錯1:WARNING: SSLSocket#session= is not supported

報(bào)錯2:INFO: I/O exception (java.net.SocketException) caught when processing request to {s}->https://repo.maven.apache.org:443

解決辦法:

vim /usr/share/logstash/Gemfile # source "https://rubygems.org" 將國外的源注釋,換成國內(nèi)的 source "https://gems.ruby-china.com/"

安裝順利的話是這樣的

/usr/share/logstash/bin/logstash-plugin install logstash-output-jdbc Validating logstash-output-jdbc Installing logstash-output-jdbc Installation successful/usr/share/logstash/bin/logstash-plugin list | grep jdbc logstash-input-jdbc logstash-output-jdbc# 下載數(shù)據(jù)庫的JDBC驅(qū)動-https://dev.mysql.com/downloads/connector/j/, # 上傳到服務(wù)器,驅(qū)動的路徑必須嚴(yán)格一致,否則連接數(shù)據(jù)庫會報(bào)錯. tar xf mysql-connector-java-5.1.47.tar.gz cd mysql-connector-java-5.1.47/ mkdir -p /usr/share/logstash/vendor/jar/jdbc cp mysql-connector-java-5.1.47-bin.jar /usr/share/logstash/vendor/jar/jdbc/ chown -R logstash.logstash /usr/share/logstash/vendor/jar/jdbc/

3.創(chuàng)建數(shù)據(jù)表

配置Nginx日志格式

log_format access_log_json '{"host":"$http_x_real_ip","client_ip":"$remote_addr","log_time":"$time_iso8601","request":"$request","status":"$status","body_bytes_sent":"$body_bytes_sent","req_time":"$request_time","AgentVersion":"$http_user_agent"}';access_log /var/log/nginx/access.log access_log_json;nginx -t nginx -s reload

創(chuàng)建數(shù)據(jù)表:在數(shù)據(jù)庫中存儲數(shù)據(jù)的時候,沒有必要存儲日志的所有內(nèi)容,只需存儲我們需要的重要信息即可.

注意:數(shù)據(jù)表中需要創(chuàng)建time字段,time的默認(rèn)值設(shè)置為CURRENT_TIMESTAMP.

use elk; create table nginx_log(host varchar(128),client_ip varchar(128),status int(4),req_time float(8,3),AgentVersion varchar(512), time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

4.配置Logstash將日志寫入數(shù)據(jù)庫

cat /etc/logstash/conf.d/nginx_log.conf input{file{path => "/var/log/nginx/access.log"start_position => "beginning"stat_interval => "2"codec => "json"} }output{elasticsearch {hosts => ["10.0.0.22:9200"]index => "nginx-log-%{+YYYY.MM.dd}" }jdbc{connection_string => "jdbc:mysql://10.0.0.33/elk?user=elk&password=Elk123!@#&useUnicode=true&characterEncoding=UTF8"statement => ["insert into nginx_log(host,client_ip,status,req_time,AgentVersion) VALUES(?,?,?,?,?)", "host","client_ip","status","req_time","AgentVersion"] } } systemctl restart logstash.service

訪問http://10.0.0.22/nginxweb/,可以在數(shù)據(jù)庫看到數(shù)據(jù)已經(jīng)入庫

輸出到es的nginx日志

logstash安裝插件解決報(bào)錯:https://www.jianshu.com/p/4fe495639a9a

ELK收集日志到mysql數(shù)據(jù)庫:http://blog.51cto.com/tryingstuff/2050360

定期刪除es集群10天以上的索引:https://blog.csdn.net/felix_yujing/article/details/78207667

ELK批量刪除索引及集群相關(guān)操作記錄:https://www.cnblogs.com/kevingrace/p/9994178.html

轉(zhuǎn)載于:https://www.cnblogs.com/fawaikuangtu123/p/10360264.html

總結(jié)

以上是生活随笔為你收集整理的ELK之收集日志到mysql数据库的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。