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
