一文吃透企业级elk技术栈：11. zabbix报警实现

最新推荐文章于 2024-08-27 09:32:39 发布

hh真是个慢性子

最新推荐文章于 2024-08-27 09:32:39 发布

阅读量474

点赞数 7

分类专栏： elk 文章标签： elk zabbix adb

本文链接：https://blog.csdn.net/weixin_45385457/article/details/134647648

版权

elk 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

十一、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

hh真是个慢性子

关注

7
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
一文吃透企业级elk技术栈：11. zabbix报警实现

es的每台机器的默认最大分片数位1000，如果集群有3台机器，那就有3000当满了之后就无法创建索引，所以需要设置更大的参数。mysql多次登录失败告警 // 存在问题日志信息无法打印到zabbix。
复制链接

扫一扫

专栏目录