Mysql慢查询之filebeat 和 logrotate的设置

概述

服务器

服务器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测试

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容