本文通过EFK（Elasticsearch、Fluentd、Kibana）来收集MySQL慢日志，当前系统环境基于Centos7，MySQL基于wlnmp一键包安装。本文由吴昊博客全网首发。
注意文中软件版本，版本不同可能会影响最终结果，本文未在新版中进行测试。
1、安装Fluentd
方法一：使用官方推荐的安装方式
|
1 |
curl -L https://toolbelt.treasuredata.com/sh/install-redhat-td-agent3.sh | sh |
方法二：直接下载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启动用户
|
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 |
|
1 |
systemctl daemon-reload |
3、安装fluent一些插件
安装mysql慢查询插件
|
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插件
|
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
|
1 |
cp /etc/td-agent/td-agent.conf /etc/td-agent/td-agent.conf.bak |
|
1 |
vi /etc/td-agent/td-agent.conf |
将配置文件中原有内容替换成以下配置，/data/mysql/mysql_slow_query.log为mysql慢日志文件路径，es的IP地址按照实际情况进行配置。如果你的es有配置认证，可以增加user fluent和password mysecret两个参数。
|
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
|
1 2 |
rpm -ivh https://mirrors.wlnmp.com/centos/wlnmp-release-centos.noarch.rpm yum install jdk1.8 |
6、安装Elasticsearch
|
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 |
|
1 2 3 4 |
vi /etc/elasticsearch/elasticsearch.yml #设置监听端口为9200，network.hos按需配置 http.port: 9200 network.host: 0.0.0.0 |
启动服务
|
1 |
/etc/init.d/elasticsearch start |
7、安装Kibana
|
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 |
|
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" |
启动服务
|
1 |
/etc/init.d/kibana start |
8、安装MySQL
我这里MySQL通过wlnmp一键包进行安装的，如果你在前面添加的wlnmp源，那么此处可以不执行
|
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
|
1 2 |
systemctl daemon-reload /etc/init.d/td-agent start |
注：如果第一次启动报错，可以再试一次
10、造一条慢日志数据
注：MySQL默认密码为空，见官方文档https://www.wlnmp.com/install
|
1 2 |
mysql -uroot -p mysql> select sleep(5); |
11、访问kibana
你可能不会马上看到一下页面，大概有1~5分钟的延迟
此时MySQL慢日志的数据已经对接进来了。
注：最可能遇到的情况就是在kibana中刷不出来信息，检查配置文件是否配置正常，版本是否与本文一致，如果全部配置正常的情况下，1~5分钟内就会刷新出数据。
原文链接：EFK环境收集MySQL慢日志，转载请注明来源！
