ELK收集MySQL SlowLog（Filebeat .KO. Logstash ）【实操系列】

故事背景

1. 需求

收集腾讯云MySQL实例的慢查询，并展示

2. 问题拆解

1. 调用腾讯云API接口拉取slowlog文件
2. 使用Logstash或Filebeat收集慢查询

3. 难点/处理方法

难点

每次拉取慢查询只能是全量拉取

本人编程能力比较差，仅仅提供粗糙的处理方式

处理方法

腾讯云的API并不会下载慢查询文件，只会打印文件大小，下载地址等信息

# 当前存在这三个文件
slcur：当前最新慢查询日志
sl2elk：最终会被elk采集的慢查询日志（可以认为是跟slcur数据保持一致）。
slold：始终是上一个版本的慢查询日志

# 调用API获取当前慢查询日志文件大小
if slcur_size > sl2elk_size: # 如果当前慢查询大小比sl2elk的大，则说明需要更新（此时并没有真正下载最新的慢查询日志）
   mv slcur slold # 将当前的慢查询移动到存放老版本的文件夹中
   wget slcur # wget重新获取最近的慢查询日志
   num=`cat slold | wc -l` # 从老版本慢查询日志中慢查询的条数
                     # 此时的slcur是比sl2elk完整的，所以要将差异的部分追缴到sl2elk中
   sed -n "$num,$p" slcur >> sl2elk # 追加差异部分日志到sl2elk中

可能有点2，希望得到大佬们的指导，谢谢。

ELK收集MySQL SlowLog

Elasticsearch、Kibana的安装部署不做赘述，重点放在Logstash和Filebeat

方案一：Logstash收集

input {
  file {
    type => "xxxx"
    path => "/PATH/slowlog_20200204.log"
    start_position => beginning
    codec => multiline {
      pattern => "^# User@Host:"
      negate => true
      what => "previous"
    }
  }
}
filter {
  grok {
    match => ["message","(?m)^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IPV4:clientip})?\]\s+Id:\s+%{NUMBER:row_id:int}\n#\s+Query_time:\s+%{NUMBER:query_time:float}\s+Lock_time:\s+%{NUMBER:lock_time:float}\s+Rows_sent:\s+%{NUMBER:rows_sent:int}\s+Rows_examined:\s+%{NUMBER:rows_examined:int}\n\s*(?:use %{DATA:database};\s*\n)?SET\s+timestamp=%{NUMBER:timestamp};\n\s*(?<sql>(?<action>\w+)\b.*;)\s*(?:\n#\s+Time)?.*$"]
  }
  date {
    match => ["timestamp","UNIX","YYYY-MM-dd HH:mm:ss"]
    remove_field => ["timestamp","_index","host","path","row_id","tags","_type"]
  }
}
output {
  stdout {
    codec=> rubydebug{}
  }
  elasticsearch {
    hosts => ['localhost:9200']
    index => "mysql-slowlog"
  }
}

基本就是这样了，注意点就是grok部分写的有点挫。
另外，默认启动占用的资源是1G内存，有点大，可以修改配置：
6.x版本的是在

grep -Ev "^#|^$" jvm.options
-Xms128m
-Xmx128m
-XX:+UseParNewGC
-XX:+UseConcMarkSweepGC
-XX:CMSInitiatingOccupancyFraction=75
-XX:+UseCMSInitiatingOccupancyOnly
-Djava.awt.headless=true
-Dfile.encoding=UTF-8
-Djruby.compile.invokedynamic=true
-Djruby.jit.threshold=0
-XX:+HeapDumpOnOutOfMemoryError
-Djava.security.egd=file:/dev/urandom

7.x版本是在

➜  logstash-7.5.1 grep -Ev "^#|^$" config/jvm.options
-Xms1g
-Xmx1g
-XX:+UseConcMarkSweepGC
-XX:CMSInitiatingOccupancyFraction=75
-XX:+UseCMSInitiatingOccupancyOnly
-Djava.awt.headless=true
-Dfile.encoding=UTF-8
-Djruby.compile.invokedynamic=true
-Djruby.jit.threshold=0
-Djruby.regexp.interruptible=true
-XX:+HeapDumpOnOutOfMemoryError
-Djava.security.egd=file:/dev/urandom
-Dlog4j2.isThreadContextMapInheritable=true

当启动到4-5个的时候，机器内存已经扛不住了。无奈

方案二：Filebeat收集

使用filebeat其实不用我们再去写grok，往往自己写的比较业余，可以直接使用帮我们写好的module，如下，非常全的吧

➜  module ls
apache        aws           cisco         envoyproxy    ibmmq         iptables      logstash      mssql         netflow       panw          redis         system
apache2       azure         coredns       googlecloud   icinga        kafka         misp          mysql         nginx         postgresql    santa         traefik
auditd        cef           elasticsearch haproxy       iis           kibana        mongodb       nats          osquery       rabbitmq      suricata      zeek

我们重点看下mysql相关的，
filebeat比较完善，可以收集errlog和slowlog

tree mysql/
mysql/
├── error
│   ├── config
│   │   └── error.yml
│   ├── ingest
│   │   └── pipeline.json
│   └── manifest.yml
├── module.yml
└── slowlog
    ├── config
    │   └── slowlog.yml
    ├── ingest
    │   ├── pipeline.json
    │   └── _tmp
    │       └── pipeline.json
    └── manifest.yml

7 directories, 8 files

激活mysql模块报错

➜  filebeat-7.5.2-darwin-x86_64 ./filebeat modules enable mysql

其实无所谓的，单机启动多个filebeat配置文件没问题，这个都无所谓

配置文件

 cat  name_1.yml
filebeat.config.modules:
  path: /PATH/mysql_1.yml # 这个是filebeat的模版文件
  reload.enabled: true
  reload.period: 10
output.elasticsearch:
  hosts: ["localhost:9200"]
  index: "mysqlslowlogs"
setup.template.name: "mysqlslowlogs"
setup.template.pattern: "mysqlslowlogs*"
setup.template.enabled: false
setup.template.overwrite: true

修改模块

# cat /PATH/mysql_1.yml
- module: mysql
  error:
    enabled: false

  slowlog:
    enabled: true
    var.paths: ["/PATH/mysqlslowlog.log"]

启动filebeat

/bin/filebeat -c /PATH/name_1.yml --path.data=/PATH/name_1_data -e

以上操作调通之后，就可以放心的部署多filebeat了。当然忘不了我们的supervisorctl

supervisor配置Demo

# cat supervisor_demo.ini
[program:name_1]
command = /bin/nohup /bin/filebeat -c /PATH/name_1.yml --path.data=/PATH/name1_data -e
autostart = true
startsecs = 5
autorestart = true
startretries = 3
user = root
redirect_stderr = true
stdout_logfile_maxbytes = 20MB
stdout_logfile_backups = 20
stdout_logfile = /var/log/supervisor/supervisor-name1.log

现在集群跑了36个，问题不大

# supervisorctl status | wc -l
36

踩坑链接

写的比较粗糙，很多东西都没详细记录，以后再说吧。