## 1\. ELK部署 * 相关信息请查看[统一日志中心详解](https://www.kancloud.cn/zlt2000/microservices-platform/919434) ## 2\. Mysql数据库配置 ### 2.1. 修改数据库配置，增加慢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 ~~~ ### 2.2. 重启数据库 ~~~ service mysqld restart ~~~ ### 2.3. 登录数据库查询配置信息是否生效 ~~~ show VARIABLES like '%slow%'; ​ show VARIABLES like 'long_query_time'; ~~~ ## 3\. Logstash配置 ### 3.1. 修改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" } } ~~~ ### 3.2. 修改30-output.conf配置，增加以下内容 ~~~ if [fields][docType] == "mysqlslowlogs" { elasticsearch { hosts => ["localhost"] manage_template => false index => "mysql-slowlog-%{+YYYY.MM.dd}" document_type => "%{[@metadata][type]}" } } ~~~ ## 4\. 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 ~~~