一文吃透企业级elk技术栈：11. zabbix报警实现
·
十一、zabbix报警实现
mysql多次登录失败告警 // 存在问题 日志信息无法打印到zabbix
[root@elk-02 ~]# cat /etc/logstash/conf.d/mysql.conf
input {
kafka {
bootstrap_servers => "10.10.8.10:9092,10.10.8.11:9092,10.10.8.12:9092"
topics => ["elktest"]
group_id => "elkgroup"
codec => "json"
}
}
filter {
if [type] == "mysql-slow-logs" {
grok {
# 有ID有use
match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id:int}\n# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}\nuse\s(?<dbname>\w+);\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]
# 有ID无use
match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id:int}\n# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]
# 无ID有use
match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\n# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}\nuse\s(?<dbname>\w+);\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]
# 无ID无use
match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\n# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]
# mariadb慢日志获取
match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\n# Thread_id:%{NUMBER:thread_id:int}\s+Schema: %{DATA:schema}\s+QC_hit: %{DATA:qc_hit}\n# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]
}
date {
match => ["timestamp_mysql","UNIX"]
target => "@timestamp"
}
mutate {
remove_field => ["@version","message","timestamp_mysql"]
}
}
else if [type] == "mysql-err-logs" {
grok {
# mysql 5.7 err
match => [ "message", "(?m)^%{NOTSPACE:date} %{NUMBER:bytes} \[%{GREEDYDATA:log_level}\] %{GREEDYDATA:messages}" ]
# mysql 5.6 err
match => [ "message", "(?<timestamp>\d+ \d+:\d+:\d+) \[%{LOGLEVEL:log_level}\] %{GREEDYDATA:messages}\s*$" ]
# mariadb err1
match => [ "message", "(?<timestamp>\d+ \d+:\d+:\d+) \[%{DATA:log_level}\] %{GREEDYDATA:messages}\s*$" ]
# mariadb err2
match => [ "message", "(?<timestamp>\d+ \d+:\d+:\d+) %{GREEDYDATA:messages}\s*$" ]
}
mutate {
add_field => ["[zabbix_host]","10.10.8.152"]
add_field => ["[zabbix_key]","mysql_passwd_verification_failed"]
}
mutate {
add_field => ["count","%{[agent][name]}_%{messages}"]
}
mutate {
remove_field => [ "@version" ]
}
}
}
output {
if [type] == "mysql-slow-logs" {
# 推送到es
elasticsearch {
hosts => ["http://10.10.8.10:9200","http://10.10.8.11:9200","http://10.10.8.12:9200"]
user => "elastic"
password => "123456"
index => "mysql-slow-%{+YYYY.MM.dd}"
}
}
else if [type] == "mysql-err-logs" {
elasticsearch {
hosts => ["http://10.10.8.10:9200","http://10.10.8.11:9200","http://10.10.8.12:9200"]
user => "elastic"
password => "123456"
index => "mysql-err-%{+YYYY.MM.dd}"
}
if [count] =~ /(Access denied)/ {
zabbix {
zabbix_host => "[zabbix_host]"
zabbix_key => "[zabbix_key]"
zabbix_server_host => "10.10.8.166"
zabbix_server_port => "10051"
zabbix_value => "count"
}
}
}
}
客户端配置
centos 7安装
systemctl status filebeat
cd /root && \
wget https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-7.9.2-x86_64.rpm && \
rpm -ivh filebeat-7.9.2-x86_64.rpm && \
systemctl enable filebeat.service && \
systemctl start filebeat.service && \
mkdir /etc/filebeat/conf.d
centos 6安装
systemctl status filebeat
cd /root && \
wget https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-7.9.2-x86_64.rpm && \
rpm -ivh filebeat-7.9.2-x86_64.rpm && \
service filebeat start && \
chkconfig --add filebeat && \
mkdir /etc/filebeat/conf.d
配置文件配置
cat >/etc/filebeat/filebeat.yml<<'EOF'
filebeat.config.inputs:
enabled: true
# 配置多配置文件路径
path: /etc/filebeat/conf.d/*.yml
processors:
- drop_fields:
fields: ["source","input","beat","prospector","offset"]
# 区分日志来自哪台主机 // 自行修改当前主机IP
name: 103.29.16.83
output:
# 输出到kafka
kafka:
hosts: ["10.10.8.164:9092", "10.10.8.165:9092", "10.10.8.166:9092"]
topic: elktest
logging.level: info
logging.to_files: true
logging.files:
path: /var/log/filebeat
name: filebeat
keepfiles: 7
permissions: 0644
#output.file:
# path: "/tmp/filebeat"
# filename: filebeat
EOF
mysql日志配置
netstat -lntp|grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 166237/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 1853/mysqld
tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 181145/mysqld
tcp 0 0 0.0.0.0:3310 0.0.0.0:* LISTEN 261336/mysqld
tcp6 0 0 :::3311 :::* LISTEN 85491/mysqld
tcp 0 0 0.0.0.0:3316 0.0.0.0:* LISTEN 127655/mysqld
tcp 0 0 0.0.0.0:3326 0.0.0.0:* LISTEN 27277/mysqld
cat >/etc/filebeat/conf.d/mysql.yml<<'EOF'
# mysql 慢日志推送
- type: log
tail_files: true
backoff: "1s"
paths:
# 多实例推送 // 自行修改慢日志路径
- /data/mysql3309/log/mysql-slow.log
- /data/mysql3310/log/mysql-slow.log
- /data/mysql3316/log/mysql-slow.log
- /data/mysql3326/log/slow.log
tags: ["mysql-slow-logs"]
# 排除列
exclude_lines: ['^\# Time']
fields:
# 配置logstash区分
type: "mysql-slow-logs"
fields_under_root: true
multiline:
# 多行匹配
pattern: '^\# Time|^\# User'
negate: true
match: after
# mysql 错误日志推送
- type: log
tail_files: true
backoff: "1s"
paths:
# 多实例推送 // 自行修改错误日志路径
- /var/log/mysqld.log
- /data/mysql3307/log/mysql-error.log
- /data/mysql3309/log/mysql-error.log
- /data/mysql3310/log/mysql-error.log
- /data/mysql3311/error.log
- /data/mysql3316/log/mysql-error.log
- /data/mysql3326/log/mysql-error.log
tags: ["mysql-err-logs"]
fields:
# 配置logstash区分
type: "mysql-err-logs"
fields_under_root: true
EOF
系统日志
cat >/etc/filebeat/conf.d/system.yml<<'EOF'
# 系统messages日志推送
- type: log
tail_files: true
backoff: "1s"
paths:
- /var/log/messages
tags: ["system-messages-logs"]
fields:
# 配置logstash区分
type: "system-messages-logs"
fields_under_root: true
# 系统secure日志推送
- type: log
tail_files: true
backoff: "1s"
paths:
- /var/log/secure
tags: ["system-secure-logs"]
fields:
# 配置logstash区分
type: "system-secure-logs"
fields_under_root: true
EOF
重启
systemctl restart filebeat.service
防火墙规则
telnet 10.10.8.164 9092
ip a
问题解决
1. “error”=>{“type”=>“validation_exception”, “reason”=>“Validation Failed: 1: this action would add [2] shards, but this cluster currently has [999]/[1000] maximum normal shards open;”}
PUT _cluster/settings
{
"persistent": {
"cluster": {
"max_shards_per_node":20000
}
}
}
es的每台机器的默认最大分片数位1000，如果集群有3台机器，那就有3000当满了之后就无法创建索引，所以需要设置更大的参数
vim /etc/elasticsearch/elasticsearch.yml
cluster.max_shards_per_node: 20000
更多推荐
7
0- 0
已为社区贡献5条内容
所有评论(0)