EFK环境收集MySQL慢日志-工具盒子

本文通过EFK（Elasticsearch、Fluentd、Kibana）来收集MySQL慢日志，当前系统环境基于Centos7，MySQL基于wlnmp一键包安装。本文由吴昊博客全网首发。

注意文中软件版本，版本不同可能会影响最终结果，本文未在新版中进行测试。

1、安装Fluentd

方法一：使用官方推荐的安装方式
curl -L https://toolbelt.treasuredata.com/sh/install-redhat-td-agent3.sh | sh

|---|--------------------------------------------------------------------------------| | 1 | curl -L https://toolbelt.treasuredata.com/sh/install-redhat-td-agent3.sh | sh |

方法二：直接下载rpm包安装
wget https://s3.amazonaws.com/packages.treasuredata.com/3/redhat/7/x86_64/td-agent-3.8.1-0.el7.x86_64.rpm yum install td-agent-3.8.1-0.el7.x86_64.rpm

|-----|-------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 | wget https://s3.amazonaws.com/packages.treasuredata.com/3/redhat/7/x86_64/td-agent-3.8.1-0.el7.x86_64.rpm yum install td-agent-3.8.1-0.el7.x86_64.rpm |

2、修改fluentd启动用户
sed -i s#User=td-agent#User=root# /usr/lib/systemd/system/td-agent.service sed -i s#Group=td-agent#Group=root# /usr/lib/systemd/system/td-agent.service

|-----|---------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 | sed -i s#User=td-agent#User=root# /usr/lib/systemd/system/td-agent.service sed -i s#Group=td-agent#Group=root# /usr/lib/systemd/system/td-agent.service |

systemctl daemon-reload

|---|-------------------------| | 1 | systemctl daemon-reload |

3、安装fluent一些插件

安装mysql慢查询插件
td-agent-gem install fluent-plugin-mysqlslowquery

|---|---------------------------------------------------| | 1 | td-agent-gem install fluent-plugin-mysqlslowquery |

Fetching: myslog-0.1.1.gem (100%)
Successfully installed myslog-0.1.1
Fetching: fluent-plugin-mysqlslowquery-0.0.9.gem (100%)
Successfully installed fluent-plugin-mysqlslowquery-0.0.9
Parsing documentation for myslog-0.1.1
Installing ri documentation for myslog-0.1.1
Parsing documentation for fluent-plugin-mysqlslowquery-0.0.9
Installing ri documentation for fluent-plugin-mysqlslowquery-0.0.9
Done installing documentation for myslog, fluent-plugin-mysqlslowquery after 0 seconds
2 gems installed

安装elasticsearch插件
td-agent-gem install fluent-plugin-elasticsearch

|---|--------------------------------------------------| | 1 | td-agent-gem install fluent-plugin-elasticsearch |

Fetching: faraday-em_http-1.0.0.gem (100%)
Successfully installed faraday-em_http-1.0.0
Fetching: faraday-em_synchrony-1.0.0.gem (100%)
Successfully installed faraday-em_synchrony-1.0.0
Fetching: faraday-excon-1.1.0.gem (100%)
Successfully installed faraday-excon-1.1.0
Fetching: faraday-httpclient-1.0.1.gem (100%)
Successfully installed faraday-httpclient-1.0.1
Fetching: faraday-multipart-1.0.4.gem (100%)
Successfully installed faraday-multipart-1.0.4
Fetching: faraday-net_http-1.0.1.gem (100%)
Successfully installed faraday-net_http-1.0.1
Fetching: faraday-net_http_persistent-1.2.0.gem (100%)
Successfully installed faraday-net_http_persistent-1.2.0
Fetching: faraday-patron-1.0.0.gem (100%)
Successfully installed faraday-patron-1.0.0
Fetching: faraday-rack-1.0.0.gem (100%)
Successfully installed faraday-rack-1.0.0
Fetching: faraday-retry-1.0.3.gem (100%)
Successfully installed faraday-retry-1.0.3
Fetching: ruby2_keywords-0.0.5.gem (100%)
Successfully installed ruby2_keywords-0.0.5
Fetching: faraday-1.10.2.gem (100%)
Successfully installed faraday-1.10.2
Fetching: fluent-plugin-elasticsearch-5.2.4.gem (100%)
Successfully installed fluent-plugin-elasticsearch-5.2.4
Parsing documentation for faraday-em_http-1.0.0
Installing ri documentation for faraday-em_http-1.0.0
Parsing documentation for faraday-em_synchrony-1.0.0
Installing ri documentation for faraday-em_synchrony-1.0.0
Parsing documentation for faraday-excon-1.1.0
Installing ri documentation for faraday-excon-1.1.0
Parsing documentation for faraday-httpclient-1.0.1
Installing ri documentation for faraday-httpclient-1.0.1
Parsing documentation for faraday-multipart-1.0.4
Installing ri documentation for faraday-multipart-1.0.4
Parsing documentation for faraday-net_http-1.0.1
Installing ri documentation for faraday-net_http-1.0.1
Parsing documentation for faraday-net_http_persistent-1.2.0
Installing ri documentation for faraday-net_http_persistent-1.2.0
Parsing documentation for faraday-patron-1.0.0
Installing ri documentation for faraday-patron-1.0.0
Parsing documentation for faraday-rack-1.0.0
Installing ri documentation for faraday-rack-1.0.0
Parsing documentation for faraday-retry-1.0.3
Installing ri documentation for faraday-retry-1.0.3
Parsing documentation for ruby2_keywords-0.0.5
Installing ri documentation for ruby2_keywords-0.0.5
Parsing documentation for faraday-1.10.2
Installing ri documentation for faraday-1.10.2
Parsing documentation for fluent-plugin-elasticsearch-5.2.4
Installing ri documentation for fluent-plugin-elasticsearch-5.2.4
Done installing documentation for faraday-em_http, faraday-em_synchrony, faraday-excon, faraday-httpclient, faraday-multipart, faraday-net_http, faraday-net_http_persistent, faraday-patron, faraday-rack, faraday-retry, ruby2_keywords, faraday, fluent-plugin-elasticsearch after 1 seconds
13 gems installed

4、配置fluentd

如果你是按照本文方式安装的fluentd，那么默认配置文件在/etc/td-agent/td-agent.conf
cp /etc/td-agent/td-agent.conf /etc/td-agent/td-agent.conf.bak

|---|----------------------------------------------------------------| | 1 | cp /etc/td-agent/td-agent.conf /etc/td-agent/td-agent.conf.bak |

vi /etc/td-agent/td-agent.conf

|---|--------------------------------| | 1 | vi /etc/td-agent/td-agent.conf |

将配置文件中原有内容替换成以下配置，/data/mysql/mysql_slow_query.log为mysql慢日志文件路径，es的IP地址按照实际情况进行配置。如果你的es有配置认证，可以增加user fluent和password mysecret两个参数。
<source> @type mysql_slow_query path /data/mysql/mysql_slow_query.log path_key file_path tag mysqld.slowlog pos_file /var/log/td-agent/mysql-slow.log.pos <parse> @type none </parse> </source> <match mysqld.slowlog> @type elasticsearch host IP port 9200 logstash_format true logstash_prefix mysql_slow.${tag} </match>

|----------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <source> @type mysql_slow_query path /data/mysql/mysql_slow_query.log path_key file_path tag mysqld.slowlog pos_file /var/log/td-agent/mysql-slow.log.pos <parse> @type none </parse> </source> <match mysqld.slowlog> @type elasticsearch host IP port 9200 logstash_format true logstash_prefix mysql_slow.${tag} </match> |

5、安装jdk
rpm -ivh https://mirrors.wlnmp.com/centos/wlnmp-release-centos.noarch.rpm yum install jdk1.8

|-----|----------------------------------------------------------------------------------------------| | 1 2 | rpm -ivh https://mirrors.wlnmp.com/centos/wlnmp-release-centos.noarch.rpm yum install jdk1.8 |

6、安装Elasticsearch
cd /usr/local/src wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.2.3.rpm yum install elasticsearch-6.2.3.rpm

|-------|-----------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 | cd /usr/local/src wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.2.3.rpm yum install elasticsearch-6.2.3.rpm |

vi /etc/elasticsearch/elasticsearch.yml #设置监听端口为9200，network.hos按需配置 http.port: 9200 network.host: 0.0.0.0

|---------|------------------------------------------------------------------------------------------------------------| | 1 2 3 4 | vi /etc/elasticsearch/elasticsearch.yml #设置监听端口为9200，network.hos按需配置 http.port: 9200 network.host: 0.0.0.0 |

启动服务
/etc/init.d/elasticsearch start

|---|---------------------------------| | 1 | /etc/init.d/elasticsearch start |

7、安装Kibana
cd /usr/local/src wget https://artifacts.elastic.co/downloads/kibana/kibana-6.2.3-x86_64.rpm yum install kibana-6.2.3-x86_64.rpm

|-------|----------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 | cd /usr/local/src wget https://artifacts.elastic.co/downloads/kibana/kibana-6.2.3-x86_64.rpm yum install kibana-6.2.3-x86_64.rpm |

vi /etc/kibana/kibana.yml #默认监听端口5601 server.port: 5601 #监听IP地址，这里改成0.0.0.0，即监听所有IP server.host: "0.0.0.0" #elasticsearch的地址，如elasticsearch与kibana安装在不同服务器上，需要手动指定地址，按需配置 elasticsearch.url: "http://IP:9200"

|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 | vi /etc/kibana/kibana.yml #默认监听端口5601 server.port: 5601 #监听IP地址，这里改成0.0.0.0，即监听所有IP server.host: "0.0.0.0" #elasticsearch的地址，如elasticsearch与kibana安装在不同服务器上，需要手动指定地址，按需配置 elasticsearch.url: "http://IP:9200" |

启动服务
/etc/init.d/kibana start

|---|--------------------------| | 1 | /etc/init.d/kibana start |

8、安装MySQL

我这里MySQL通过wlnmp一键包进行安装的，如果你在前面添加的wlnmp源，那么此处可以不执行
rpm -ivh https://mirrors.wlnmp.com/centos/wlnmp-release-centos.noarch.rpm yum install wmysql57

|-----|------------------------------------------------------------------------------------------------| | 1 2 | rpm -ivh https://mirrors.wlnmp.com/centos/wlnmp-release-centos.noarch.rpm yum install wmysql57 |

配置/etc/my.conf启动慢日志slow_query_log=on，并重启MySQL服务/etc/init.d/mysql restart

9、启动Fluentd
systemctl daemon-reload /etc/init.d/td-agent start

|-----|----------------------------------------------------| | 1 2 | systemctl daemon-reload /etc/init.d/td-agent start |

注：如果第一次启动报错，可以再试一次

10、造一条慢日志数据

注：MySQL默认密码为空，见官方文档https://www.wlnmp.com/install
mysql -uroot -p mysql> select sleep(5);

|-----|------------------------------------------| | 1 2 | mysql -uroot -p mysql> select sleep(5); |

11、访问kibana