本文通过EFK（Elasticsearch、Fluentd、Kibana）来收集MySQL慢日志，当前系统环境基于Centos7，MySQL基于wlnmp一键包安装。本文由吴昊博客全网首发。
注意文中软件版本，版本不同可能会影响最终结果，本文未在新版中进行测试。
1、安装Fluentd
方法一：使用官方推荐的安装方式
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
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
systemctl daemon-reload
3、安装fluent一些插件
安装mysql慢查询插件
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
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
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>
5、安装jdk
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
vi /etc/elasticsearch/elasticsearch.yml
#设置监听端口为9200，network.hos按需配置
http.port: 9200
network.host: 0.0.0.0
启动服务
/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
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
8、安装MySQL
我这里MySQL通过wlnmp一键包进行安装的，如果你在前面添加的wlnmp源，那么此处可以不执行
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
注：如果第一次启动报错，可以再试一次
10、造一条慢日志数据
注：MySQL默认密码为空，见官方文档https://www.wlnmp.com/install
mysql -uroot -p
mysql> select sleep(5);
11、访问kibana
你可能不会马上看到一下页面，大概有1~5分钟的延迟
此时MySQL慢日志的数据已经对接进来了。
注：最可能遇到的情况就是在kibana中刷不出来信息，检查配置文件是否配置正常，版本是否与本文一致，如果全部配置正常的情况下，1~5分钟内就会刷新出数据。
