MySQL慢查询日志

MySQL 有多种日志可以记录，常见的有 error log、slow log、general log、bin log 等。其中 slow log 作为性能监控和优化的入手点，最为首要。本节即讨论如何用 logstash 处理 slow log。至于 general log，格式处理基本类似，不过由于 general 量级比 slow 大得多，推荐采用 packetbeat 协议解析的方式更高效的完成这项工作。相关内容阅读本书稍后章节。

MySQL slow log 的 logstash 处理配置示例如下：

  1. input {
  2.   file {
  3.     type => "mysql-slow"
  4.     path => "/var/log/mysql/mysql-slow.log"
  5.     codec => multiline {
  6.       pattern => "^# User@Host:"
  7.       negate => true
  8.       what => "previous"
  9.     }
  10.   }
  11. }
  13. filter {
  14.   # drop sleep events
  15.   grok {
  16.     match => { "message" => "SELECT SLEEP" }
  17.     add_tag => [ "sleep_drop" ]
  18.     tag_on_failure => [] # prevent default _grokparsefailure tag on real records
  19.   }
  20.   if "sleep_drop" in [tags] {
  21.     drop {}
  22.   }
  23.   grok {
  24.     match => [ "message", "(?m)^# User@Host: %{USER:user}\[[^\]]+\] @ (?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s*# 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}\s*(?:use %{DATA:database};\s*)?SET timestamp=%{NUMBER:timestamp};\s*(?<query>(?<action>\w+)\s+.*)\n# Time:.*$" ]
  25.   }
  26.   date {
  27.     match => [ "timestamp", "UNIX" ]
  28.     remove_field => [ "timestamp" ]
  29.   }
  30. }

运行该配置，logstash 即可将多行的 MySQL slow log 处理成如下事件：

  1. {
  2.        "@timestamp" => "2014-03-04T19:59:06.000Z",
  3.           "message" => "# User@Host: logstash[logstash] @ localhost [127.0.0.1]\n# Query_time: 5.310431  Lock_time: 0.029219 Rows_sent: 1  Rows_examined: 24575727\nSET timestamp=1393963146;\nselect count(*) from node join variable order by rand();\n# Time: 140304 19:59:14",
  4.          "@version" => "1",
  5.              "tags" => [
  6.         [0] "multiline"
  7.     ],
  8.              "type" => "mysql-slow",
  9.              "host" => "raochenlindeMacBook-Air.local",
  10.              "path" => "/var/log/mysql/mysql-slow.log",
  11.              "user" => "logstash",
  12.        "clienthost" => "localhost",
  13.          "clientip" => "127.0.0.1",
  14.        "query_time" => 5.310431,
  15.         "lock_time" => 0.029219,
  16.         "rows_sent" => 1,
  17.     "rows_examined" => 24575727,
  18.             "query" => "select count(*) from node join variable order by rand();",
  19.            "action" => "select"
  20. }