## 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 ~~~