Mysql慢查询之filebeat 和 logrotate的设置

0.097字数 1249阅读 224

概述

服务器

服务器hostname                     类型

mysqlAm                                 mysql集群A主库

mysqlAs                                  mysql集群A从库库

mysqlBm                                 mysql集群B主库

mysqlBs                                  mysql集群B从库库

es1                                          elasticseatch Master

es2                                           elasticseatch



目标

使用filebeat 导入到es 收集所有集群的mysql slow log 和errorlog



安装

以下在mysql服务器上进行 本例为:

mysqlAm, mysqlAs, mysqlBm, mysqlBs

下载filebeat

这里下载:   https://www.elastic.co/products/beats/filebeat

我们这里使用filebeat-6.5.4-x86_64.rpm

安装filebeat

# rpm -ivh filebeat-6.5.4-x86_64.rpm

开启filebeat的mysql模块

filebeat modules enable mysql

filebeat modules list

配置filebeat

vi /etc/filebeat/filebeat.yml

#============================= Filebeat modules ===============================

filebeat.config.modules:

# Configure what output to use when sending the data collected by the beat.

#索引名称

setup.template.name: "mysql-slow"

setup.template.pattern: "mysql-slow*"

template.overwrite: true

#-------------------------- Elasticsearch output ------------------------------

output.elasticsearch:

  # Array of hosts to connect to.

  hosts: ["es1:9200","es2:9200"]

  index: "mysql-slow-%{+yyyy.MM.dd}"

  timeout: 180

  # Optional protocol and basic auth credentials.

  #如果开启认证请输入密码

  #protocol: "http"

  #username: "elastic"

  #password: "changeme"

#================================ Procesors =====================================

# Configure processors to enhance or manipulate events generated by the beat.

processors:

  - add_host_metadata: ~

  - add_cloud_metadata: ~

#================================ Logging =====================================

# Sets log level. The default log level is info.

# Available log levels are: error, warning, info, debug

logging.level: info

初始化index template

filebeat setup -e

配是mysql module 信息

vi /etc/filebeat/modules.d/mysql.yml 

- module: mysql

  # Error logs

  error:

    enabled: true

    # Set custom paths for the log files. If left empty,

    # Filebeat will choose the paths depending on your OS.

    #输入errorlog的日志位置

    var.paths: ["/mysqldata/mysql3306/log/mysql-error.log*"]

  # Slow logs

  slowlog:

    enabled: true

    # Set custom paths for the log files. If left empty,

    # Filebeat will choose the paths depending on your OS.

    #slow的日志位置

    var.paths: ["/mysqldata/mysql3306/log/mysql-slow.log*"]

改写filebeat mysql module的处理逻辑使用percona版本(可忽略)



可以看到 在/usr/share/filebeat/module/ 下为filebeat各个module的处理逻辑

在slowlog module下有两个处理程序 error和slwolog


我们拿slowlog为例 可以看到config 和 ingest两个文件夹

config定义了日志的爬取规则

ingest定义了日志存入es的预处理规则


由于percona版本的slow log 和mysql版本的slowlog不一样

我们来看下percona版本的


可以看到多出了很多信息

我们使用kibana的dev tool 之grok



最后的的ingest为

more /usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json

{

  "description": "Pipeline for parsing MySQL slow logs.",

  "processors": [{

    "grok": {

      "field": "message",

      "patterns":[

"^# User@Host: %{USER:mysql.slowlog.user}\\[(%{WORD:mysql.slowlog.db})?\\] @%{ANY}\\[(%{IP:mysql.slowlog.ip})?\\]%{SPACE}*Id: (%{NUMBER:mysql.slowlog.id})?\n# Schema: (%{USER:mysql.slowlog.schema})?%{SPACE}Last_errno%{ANY}\n# Query_time

: %{NUMBER:mysql.slowlog.query_time.sec}%{SPACE} Lock_time: %{NUMBER:mysql.slowlog.lock_time.sec}%{SPACE} Rows_sent: %{NUMBER:mysql.slowlog.rows_sent}%{SPACE} Rows_examined: %{NUMBER:mysql.slowlog.rows_examined}%{ANY}\n(SET timestamp=%{

NUMBER:mysql.slowlog.timestamp};\n)?(?<mysql.slowlog.query>(?<mysql.slowlog.action>\\w+)%{SPACE}%{ANY})"

        ],

      "pattern_definitions" : {

        "GREEDYMULTILINE" : "(.|\n)*",

        "ANY": "([\\w|\\W]*)",

        "SPACE": "([\\s]*)"

      },

      "ignore_missing": true

    }

  }, {

    "remove":{

      "field": "message"

    }

  }, {

    "date": {

      "field": "mysql.slowlog.timestamp",

      "target_field": "@timestamp",

      "formats": ["UNIX"],

      "ignore_failure": true

    }

  },{

    "gsub": {

      "field": "mysql.slowlog.query",

      "pattern": "\n# Time: .*",

      "replacement": "",

      "ignore_failure": true

  }

  }],

  "on_failure" : [{

    "set" : {

      "field" : "error.w11",

      "value" : "{{ _ingest.on_failure_message }}"

    }

  }]

}

最后的config为

添加了胡烈'^# Time:.*', '^ Time:.*'的信息

more /usr/share/filebeat/module/mysql/slowlog/config/slowlog.yml

type: log

paths:

{{ range $i, $path := .paths }}

- {{$path}}

{{ end }}

exclude_files: ['.gz$']

multiline:

  pattern: '^# User@Host: '

  negate: true

  match: after

exclude_lines: ['^[\/\w\.]+, Version: .* started with:.*', '^# Time:.*', '^ Time:.*' ]  # Exclude the header and time

更新pipeline

filebeat setup --modules mysql --pipelines

试启动filebeat

filebeat -e

配置logrotate

vi /etc/logrotate.d/mysql

/mysqldata/mysql3306/log/mysql-error.log {

        # create 600 mysql mysql

        daily

        rotate 3

        missingok

        delaycompress

        postrotate

            mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save;'

        endscript

}

/mysqldata/mysql3306/log/mysql-slow.log {

        daily

        rotate 30

        missingok

        delaycompress

    postrotate

        mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save;'

    endscript

}

不使用copytruncate

一是因为filebeat记录的是inode copytruncate会改变inode

二是因为copytruncate 会堵塞mysql

原因请查看

https://www.percona.com/blog/2013/04/18/rotating-mysql-slow-logs-safely/

开启

logrotate /etc/logrotate.d/mysql

强制切割日志

logrotate -f /etc/logrotate.d/mysql

查看filebeat的记录

more /var/lib/filebeat/registry

后端启动filebeat

systemctl start filebeat

systemctl status filebeat

kibana查看


trouble shoot

如果数据全是:


说明/usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json

中的patterns 不对请用grok测试

推荐阅读更多精彩内容