场景示例 - MySQL慢查询日志

优质
小牛编辑
131浏览
2023-12-01

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. }
  12. filter {
  13. # drop sleep events
  14. grok {
  15. match => { "message" => "SELECT SLEEP" }
  16. add_tag => [ "sleep_drop" ]
  17. tag_on_failure => [] # prevent default _grokparsefailure tag on real records
  18. }
  19. if "sleep_drop" in [tags] {
  20. drop {}
  21. }
  22. grok {
  23. 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:.*$" ]
  24. }
  25. date {
  26. match => [ "timestamp", "UNIX" ]
  27. remove_field => [ "timestamp" ]
  28. }
  29. }

运行该配置,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: 24575727nSET 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. }