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