微服务轮子项目（14） - 慢查询SQL

1. 前言

在前面一篇博客《微服务轮子项目（13） - 统一日志中心详解（docker安装部署）》，主要讲解了在Docker下部署ELK，以及各个组件的作用（包括filebeat）。

本文基于上一篇博客的环境下，讲解慢查询SQL如何配置。

2. 慢查询配置

2.1 MySQL数据库配置

step1: 修改数据库配置，增加慢sql日志

vim /etc/my.cnf
#是否开启慢查询日志
slow_query_log=ON
#日志存放地址
slow_query_log_file=/var/lib/mysql/mysql-slow.log
#慢查询时间(s)，这里为了测试所以设置比较小
long_query_time=0.1

step2: 重启数据库

service mysqld restart

step3：登录数据库查询配置信息是否生效

show VARIABLES like '%slow%';
show VARIABLES like 'long_query_time';

2.2 Logstash配置

step1：修改10-syslog.conf配置，在filter里增加以下内容

  if [fields][docType] == "mysqlslowlogs" {
    grok {
        match => [
          "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id}\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nuse\s(?<dbname>\w+);\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query_str>[\s\S]*)",
          "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id}\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query_str>[\s\S]*)",
          "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nuse\s(?<dbname>\w+);\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query_str>[\s\S]*)",
          "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query_str>[\s\S]*)"
        ]
    }
​
    date {
      match => ["timestamp_mysql","yyyy-MM-dd HH:mm:ss.SSS","UNIX"]
    }
    date {
      match => ["timestamp_mysql","yyyy-MM-dd HH:mm:ss.SSS","UNIX"]
      target => "timestamp"
    }
    mutate {
      convert => ["query_time", "float"]
      convert => ["lock_time", "float"]
      convert => ["rows_sent", "integer"]
      convert => ["rows_examined", "integer"]
      remove_field => "message"
      remove_field => "timestamp_mysql"
      remove_field => "@version"
    }
  }

step2: 修改30-output.conf配置，增加以下内容

if [fields][docType] == "mysqlslowlogs" {
   elasticsearch {
     hosts => ["localhost"]
     manage_template => false
     index => "mysql-slowlog-%{+YYYY.MM.dd}"
     document_type => "%{[@metadata][type]}"
   }
 }

2.3 Filebeat配置

修改配置文件filebeat.yml，在filebeat.inputs 里改为以下内容:

filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /var/lib/mysql/mysql-slow.log
  fields:
    docType: mysqlslowlogs
  exclude_lines: ['^\# Time']
  multiline:
    pattern: '^\# Time|^\# User'
    negate: true
    match: after