ELK收集MySQL慢日志

最新推荐文章于 2024-05-01 16:21:51 发布
最新推荐文章于 2024-05-01 16:21:51 发布
阅读量4.2k 收藏 14
点赞数 1
分类专栏： java 文章标签： ELK mysql慢日志 Elastic Stack 6.6 ELK6.6 filebeat
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wokuailewozihao/article/details/88102750
版权

ELK6.6.1+FileBeat6.6.1收集mysql慢日志

一、elk简单介绍

1、之前常说的elk，现已被官方整合为Elastic Stack，官网地址（https://www.elastic.co/cn/products），官网给出的架构如下， 其中Beats是一系列轻量级日志收集器，ELK中E指Elasticsearch，L指Logstash，K指Kibana：

其中各个产品的介绍、特性等建议直接查看官网。 

2、常用部署架构

a、单一的架构，logstash作为日志搜集器，从数据源采集数据，并对数据进行过滤，格式化处理，然后交由Elasticsearch存储，kibana对日志进行可视化处理。

b、多节点logstash，这种架构模式适合需要采集日志的客户端不多，且各服务端cpu,内存等资源充足的情况下。因为每个节点都安装Logstash, 非常消耗节点资源。其中，logstash作为日志搜集器，将每一台节点的数据发送到Elasticsearch上进行存储，再由kibana进行可视化分析。

c、Beats是一系列轻量级的日志搜集器，其不占用系统资源，自出现之后，迅速更新了原有的elk架构。Filebeats将收集到的数据发送给Logstash解析过滤，在Filebeats与Logstash传输数据的过程中，为了安全性，可以通过ssl认证来加强安全性。之后将其发送到Elasticsearch存储，并由kibana可视化分析。

二、安装部署，实现mysql慢日志的收集和分析

为了方便，实验环境只用了一台服务器，即使用单一的架构，我的环境：

 

系统centos7
mysql5.6
jdk1.8
elk+filebeat6.6.1
静态ip192.168.8.254

其中elasticsearch和kibana的安装不在赘述，这里的重点主要是filebeat和logstash的一些配置。

1、首先开启Mysql5.6的慢日志。

方法一：登录mysql修改，此方法是临时修改，mysql重启后就会失效
mysql> set global slow_query_log='ON'; 
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log_file='/home/mysql5.6/mysql-slow.log';
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /home/mysql5.6/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.00 sec)

方法二：永久修改

编辑配置文件/etc/my.cnf加入如下内容

[mysqld]

slow_query_log = ON

slow_query_log_file = /home/mysql5.6/mysql-slow.log

long_query_time = 1

修改后运行：systemctl restart mysqld
再次登录mysql验证：
使用下面命令验证
mysql> show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /home/mysql5.6/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

新一点的版本如：MySql5.7/MySql8.0，其中5.7应该和5.6一样，最新版本mysql8.0可以参考其他文章https://blog.csdn.net/phker/article/details/83146676

验证一下，运行一条sql：select sleep(1);

测试成功，查询一条sql产生的日志格式如下：

# Time: 190304 14:12:00
# User@Host: root[root] @  [192.168.8.121]  Id:    16
# Query_time: 1.000221  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use lgadmin;
SET timestamp=1551679920;
SELECT SLEEP(1);
# User@Host: root[root] @  [192.168.8.121]  Id:    16
# Query_time: 0.001146  Lock_time: 0.000115 Rows_sent: 15  Rows_examined: 293
SET timestamp=1551679920;
SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID;
# User@Host: root[root] @  [192.168.8.121]  Id:    16
# Query_time: 0.001123  Lock_time: 0.000139 Rows_sent: 13  Rows_examined: 281
SET timestamp=1551679920;
SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/1.000286*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=39 GROUP BY STATE ORDER BY SEQ;

要特别注意一下日志的格式，不同版本mysql的满日至格式稍有不同，因为filebeat和logstash中都需要对日志作匹配。

可以看出，# Time:这行没啥用，基本上是# User@Host: 。。。# Query_time:。。。为一组记录，也就是说一条慢sql产生了三条日志记录；

2、安装filebeat模块

#安装完毕重启Elasticsearch
cd /home/elasticsearch-6.6.1/bin
./elasticsearch-plugin install ingest-geoip
./elasticsearch-plugin install ingest-user-agent

3、安装配置filebeat6.6.1

//下载filebeat最新版本，文章发布时为6.6.1，切记Elastic Stack的产品版本要保持一致
[root@localhost home]# curl -L -O https://artifacts.elastic.co/downloads/beats/filebeat/filebeat-6.6.1-linux-x86_64.tar.gz
//解压
[root@localhost home]# tar -zvxf filebeat-6.6.1-linux-x86_64
//进入filebeat-6.6.1-linux-x86_64
[root@localhost home]# cd filebeat-6.6.1-linux-x86_64/
[root@localhost filebeat-6.6.1-linux-x86_64]# ls
data        filebeat                filebeat.yml  LICENSE.txt  module     NOTICE.txt
fields.yml  filebeat.reference.yml  kibana        logs         modules.d  README.md
[root@localhost filebeat-6.6.1-linux-x86_64]# vi filebeat.yml

//然后修改filebeat.yml如下，作了一些精简，可直接拷贝：


#需要注意的是收集的日志是文档，filebeat按行读取分割的，所以带来的问题有排除行，合并行等问题，filebeat中使用正则表达式来实现，
#beats系列工具主要是收集数据，对数据的处理能力不强，一般是使用logstash的filter来实现数据过滤
filebeat.inputs:
- type: log
  #为true时才会收集paths中设置的日志，我们这里暂时选择关闭，因为filebeat提供了很多已经存在的插件，例如收集nginx、apache、mysql的日志，在module文件夹中
  enabled: false
  #需要收集的日志地址，多个
  paths:
    #- /home/mysql5.6/mysql-slow.log
    #- c:\programdata\elasticsearch\logs\*
  #使用正则去掉不需要的日志行，例如不需要DBG信息
  #exclude_lines: ['^DBG']
  # 包含行，例如我需要ERR和WARN信息
  #include_lines: ['^ERR', '^WARN']
  #排除文件，例如paths中出现 /*时，表示某文件夹下所有文件，但可能有些文件日志不需要收集
  #exclude_files: ['.gz$']
filebeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false
setup.template.settings:
  index.number_of_shards: 3
setup.kibana:
#选择输出到logstash
output.logstash:
  # Logstash的地址
  hosts: ["192.168.8.254:5044"]
processors:
  - add_host_metadata: ~
  - add_cloud_metadata: ~

修改完成后保存即可，接下来修改自带的mysqlmodule：

[root@localhost filebeat-6.6.1-linux-x86_64]# cd module/mysql/
[root@localhost mysql]# ls
error  module.yml  slowlog
[root@localhost mysql]# cd slowlog/
[root@localhost slowlog]# ls
config  ingest  manifest.yml
[root@localhost slowlog]# vi manifest.yml 

修改 manifest.yml中mysql慢日志所在地址即可：

module_version: "1.0"

var:
  - name: paths
    default:
      #第一步中，设置的mysql慢日志所在地址
      - /home/mysql5.6/mysql-slow.log

ingest_pipeline: ingest/pipeline.json
input: config/slowlog.yml

修改完后保存，接下来修改ingest/pipeline.json

[root@localhost slowlog]# cd ingest/
[root@localhost ingest]# ls
pipeline.json
[root@localhost ingest]#vi pipeline.json

这里的文件内容比较多，只用修改一个地方，将patterns修改为

"patterns":[
       "^# User@Host: %{USER:mysql.slowlog.user}(\\[[^\\]]+\\])? @ %{HOSTNAME:mysql.slowlog.host} \\[(IP:mysql.slowlog.ip)?\\](\\s*Id:\\s* %{NUMBER:mysql.slowlog.id})?\n# Query_time: %{NUMBER:mysql.slowlog.query_time.sec}\\s* Lock_time: %{NUMBER:mysql.slowlog.lock_time.sec}\\s* Rows_sent: %{NUMBER:mysql.slowlog.rows_sent}\\s* Rows_examined: %{NUMBER:mysql.slowlog.rows_examined}\n(SET timestamp=%{NUMBER:mysql.slowlog.timestamp};\n)?%{GREEDYMULTILINE:mysql.slowlog.query}"
        ]

注意：patterns用的是正则表达式，匹配的是日志格式，根据mysql版本不同，慢日志的格式可能不太一样，可能需要进行一些修改
修改保存后接着修改 ../config/slowlog.yml

[root@localhost ingest]# cd ../config/
[root@localhost config]# ls
slowlog.yml
[root@localhost config]#vi slowlog.yml

#在最后一行exclude_lines中加入一个匹配：'^# Time'，就是将上述日志格式中的# Time开头那一行忽略掉
type: log
paths:
{{ range $i, $path := .paths }}
 - {{$path}}
{{ end }}
exclude_files: ['.gz$']
multiline:
  pattern: '^# Time|^# User@Host: '
  negate: true
  match: after
exclude_lines: ['^[\/\w\.]+, Version: .* started with:.*', '^# Time']   # Exclude the header

保存修改后启用我们刚刚修改的mysql module

[root@localhost config]# cd /home/filebeat-6.6.1-linux-x86_64/
[root@localhost filebeat-6.6.1-linux-x86_64]# ./filebeat modules enable mysql
Enabled mysql
//查看已启用的module
[root@localhost filebeat-6.6.1-linux-x86_64]# ./filebeat modules list
Enabled:
mysql

Disabled:
apache2
auditd
elasticsearch
haproxy
icinga
iis
kafka
kibana
logstash
mongodb
nginx
osquery
postgresql
redis
suricata
system
traefik

//接下来可以测试filebeat的效果了
//PS:可以看出来module中的内容其实可以都配在filebeat.xml当中，不过这样不太好管理

测试filebeat效果：

运行：

[root@localhost filebeat-6.6.1-linux-x86_64]# ./filebeat -e -c filebeat.yml -d "publish"
2019-03-04T14:29:53.589+0800	INFO	instance/beat.go:616	Home path: [/home/filebeat-6.6.1-linux-x86_64] Config path: [/home/filebeat-6.6.1-linux-x86_64] Data path: [/home/filebeat-6.6.1-linux-x86_64/data] Logs path: [/home/filebeat-6.6.1-linux-x86_64/logs]
2019-03-04T14:29:53.590+0800	INFO	instance/beat.go:623	Beat UUID: e07be1a2-2174-46e5-aa0b-f636b56a3d30
2019-03-04T14:29:53.590+0800	INFO	[seccomp]	seccomp/seccomp.go:116	Syscall filter successfully installed
2019-03-04T14:29:53.590+0800	INFO	[beat]	instance/beat.go:936	Beat info	{"system_info": {"beat": {"path": {"config": "/home/filebeat-6.6.1-linux-x86_64", "data": "/home/filebeat-6.6.1-linux-x86_64/data", "home": "/home/filebeat-6.6.1-linux-x86_64", "logs": "/home/filebeat-6.6.1-linux-x86_64/logs"}, "type": "filebeat", "uuid": "e07be1a2-2174-46e5-aa0b-f636b56a3d30"}}}
2019-03-04T14:29:53.590+0800	INFO	[beat]	instance/beat.go:945	Build info	{"system_info": {"build": {"commit": "928f5e3f35fe28c1bd73513ff1cc89406eb212a6", "libbeat": "6.6.1", "time": "2019-02-13T16:12:26.000Z", "version": "6.6.1"}}}
2019-03-04T14:29:53.590+0800	INFO	[beat]	instance/beat.go:948	Go runtime info	{"system_info": {"go": {"os":"linux","arch":"amd64","max_procs":4,"version":"go1.10.8"}}}
2019-03-04T14:29:53.591+0800	INFO	[beat]	instance/beat.go:952	Host info	{"system_info": {"host": {"architecture":"x86_64","boot_time":"2019-02-28T14:39:30+08:00","containerized":true,"name":"localhost.localdomain","ip":["127.0.0.1/8","::1/128","192.168.8.106/24","fe80::872c:6ca:3666:3f88/64","192.168.8.254/22","fe80::aae2:b334:2fd9:6f63/64","192.168.122.1/24"],"kernel_version":"3.10.0-862.14.4.el7.x86_64","mac":["50:9a:4c:77:88:91","50:9a:4c:77:88:92","52:54:00:01:54:80","52:54:00:01:54:80"],"os":{"family":"redhat","platform":"centos","name":"CentOS Linux","version":"7 (Core)","major":7,"minor":5,"patch":1804,"codename":"Core"},"timezone":"CST","timezone_offset_sec":28800,"id":"808d566509d84563b4db09739872d798"}}}
2019-03-04T14:29:53.591+0800	INFO	[beat]	instance/beat.go:981	Process info	{"system_info": {"process": {"capabilities": {"inheritable":null,"permitted":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"effective":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"bounding":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"ambient":null}, "cwd": "/home/filebeat-6.6.1-linux-x86_64", "exe": "/home/filebeat-6.6.1-linux-x86_64/filebeat", "name": "filebeat", "pid": 8650, "ppid": 3937, "seccomp": {"mode":"filter"}, "start_time": "2019-03-04T14:29:52.860+0800"}}}
2019-03-04T14:29:53.591+0800	INFO	instance/beat.go:281	Setup Beat: filebeat; Version: 6.6.1
2019-03-04T14:29:56.592+0800	INFO	add_cloud_metadata/add_cloud_metadata.go:319	add_cloud_metadata: hosting provider type not detected.
2019-03-04T14:29:56.593+0800	DEBUG	[publish]	pipeline/consumer.go:137	start pipeline event consumer
2019-03-04T14:29:56.593+0800	INFO	[publisher]	pipeline/module.go:110	Beat name: localhost.localdomain
2019-03-04T14:29:56.593+0800	INFO	[monitoring]	log/log.go:117	Starting metrics logging every 30s
2019-03-04T14:29:56.593+0800	INFO	instance/beat.go:403	filebeat start running.
2019-03-04T14:29:56.594+0800	INFO	registrar/registrar.go:134	Loading registrar data from /home/filebeat-6.6.1-linux-x86_64/data/registry
2019-03-04T14:29:56.594+0800	INFO	registrar/registrar.go:141	States Loaded from registrar: 12
2019-03-04T14:29:56.594+0800	WARN	beater/filebeat.go:367	Filebeat is unable to load the Ingest Node pipelines for the configured modules because the Elasticsearch output is not configured/enabled. If you have already loaded the Ingest Node pipelines or are using Logstash pipelines, you can ignore this warning.
2019-03-04T14:29:56.594+0800	INFO	crawler/crawler.go:72	Loading Inputs: 1
2019-03-04T14:29:56.597+0800	INFO	log/input.go:138	Configured paths: [/var/log/mysql/error.log* /var/log/mysqld.log*]
2019-03-04T14:29:56.598+0800	INFO	log/input.go:138	Configured paths: [/home/mysql5.6/mysql-slow.log]
2019-03-04T14:29:56.598+0800	INFO	crawler/crawler.go:106	Loading and starting Inputs completed. Enabled inputs: 0
2019-03-04T14:29:56.598+0800	INFO	cfgfile/reload.go:150	Config reloader started
2019-03-04T14:29:56.600+0800	INFO	log/input.go:138	Configured paths: [/var/log/mysql/error.log* /var/log/mysqld.log*]
2019-03-04T14:29:56.600+0800	INFO	log/input.go:138	Configured paths: [/home/mysql5.6/mysql-slow.log]
2019-03-04T14:29:56.600+0800	INFO	input/input.go:114	Starting input of type: log; ID: 4895000611580543503 
2019-03-04T14:29:56.600+0800	INFO	input/input.go:114	Starting input of type: log; ID: 17864741715548327178 
2019-03-04T14:29:56.600+0800	INFO	cfgfile/reload.go:205	Loading of config files completed.
2019-03-04T14:29:56.643+0800	INFO	log/harvester.go:255	Harvester started for file: /home/mysql5.6/mysql-slow.log

logstash还没启动，所以会有一个连接错误，暂时不管，只要有日志打印到屏幕就算成功了，同样运行一个sql：select seleep(1);屏幕打印结果如下：

2019-03-04T14:33:05.834+0800	DEBUG	[publish]	pipeline/processor.go:308	Publish event: {
  "@timestamp": "2019-03-04T06:33:05.834Z",
  "@metadata": {
    "beat": "filebeat",
    "type": "doc",
    "version": "6.6.1",
    "pipeline": "filebeat-6.6.1-mysql-slowlog-pipeline"
  },
  "offset": 25,
  "log": {
    "file": {
      "path": "/home/mysql5.6/mysql-slow.log"
    },
    "flags": [
      "multiline"
    ]
  },
  "event": {
    "dataset": "mysql.slowlog"
  },
  "input": {
    "type": "log"
  },
  "host": {
    "name": "localhost.localdomain",
    "architecture": "x86_64",
    "os": {
      "platform": "centos",
      "version": "7 (Core)",
      "family": "redhat",
      "name": "CentOS Linux",
      "codename": "Core"
    },
    "id": "808d566509d84563b4db09739872d798",
    "containerized": true
  },
  "message": "# User@Host: root[root] @  [192.168.8.121]  Id:    16\n# Query_time: 1.000215  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0\nSET timestamp=1551681183;\nSELECT SLEEP(1);",
  "source": "/home/mysql5.6/mysql-slow.log",
  "fileset": {
    "module": "mysql",
    "name": "slowlog"
  },
  "prospector": {
    "type": "log"
  },
  "beat": {
    "version": "6.6.1",
    "name": "localhost.localdomain",
    "hostname": "localhost.localdomain"
  }
}
2019-03-04T14:33:05.835+0800	DEBUG	[publish]	pipeline/processor.go:308	Publish event: {
  "@timestamp": "2019-03-04T06:33:05.834Z",
  "@metadata": {
    "beat": "filebeat",
    "type": "doc",
    "version": "6.6.1",
    "pipeline": "filebeat-6.6.1-mysql-slowlog-pipeline"
  },
  "beat": {
    "hostname": "localhost.localdomain",
    "version": "6.6.1",
    "name": "localhost.localdomain"
  },
  "host": {
    "architecture": "x86_64",
    "os": {
      "version": "7 (Core)",
      "family": "redhat",
      "name": "CentOS Linux",
      "codename": "Core",
      "platform": "centos"
    },
    "id": "808d566509d84563b4db09739872d798",
    "containerized": true,
    "name": "localhost.localdomain"
  },
  "source": "/home/mysql5.6/mysql-slow.log",
  "message": "# User@Host: root[root] @  [192.168.8.121]  Id:    16\n# Query_time: 0.001150  Lock_time: 0.000097 Rows_sent: 15  Rows_examined: 282\nSET timestamp=1551681183;\nSELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID;",
  "input": {
    "type": "log"
  },
  "fileset": {
    "module": "mysql",
    "name": "slowlog"
  },
  "event": {
    "dataset": "mysql.slowlog"
  },
  "prospector": {
    "type": "log"
  },
  "offset": 197,
  "log": {
    "file": {
      "path": "/home/mysql5.6/mysql-slow.log"
    },
    "flags": [
      "multiline"
    ]
  }
}
2019-03-04T14:33:10.835+0800	DEBUG	[publish]	pipeline/processor.go:308	Publish event: {
  "@timestamp": "2019-03-04T06:33:05.835Z",
  "@metadata": {
    "beat": "filebeat",
    "type": "doc",
    "version": "6.6.1",
    "pipeline": "filebeat-6.6.1-mysql-slowlog-pipeline"
  },
  "offset": 455,
  "log": {
    "file": {
      "path": "/home/mysql5.6/mysql-slow.log"
    },
    "flags": [
      "multiline"
    ]
  },
  "source": "/home/mysql5.6/mysql-slow.log",
  "event": {
    "dataset": "mysql.slowlog"
  },
  "prospector": {
    "type": "log"
  },
  "input": {
    "type": "log"
  },
  "fileset": {
    "module": "mysql",
    "name": "slowlog"
  },
  "message": "# User@Host: root[root] @  [192.168.8.121]  Id:    16\n# Query_time: 0.001018  Lock_time: 0.000137 Rows_sent: 13  Rows_examined: 278\nSET timestamp=1551681183;\nSELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/1.000268*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=46 GROUP BY STATE ORDER BY SEQ;",
  "beat": {
    "name": "localhost.localdomain",
    "hostname": "localhost.localdomain",
    "version": "6.6.1"
  },
  "host": {
    "os": {
      "family": "redhat",
      "name": "CentOS Linux",
      "codename": "Core",
      "platform": "centos",
      "version": "7 (Core)"
    },
    "id": "808d566509d84563b4db09739872d798",
    "containerized": true,
    "name": "localhost.localdomain",
    "architecture": "x86_64"
  }
}

说明filebeat配置启动成功。

 

4、安装配置Logstash6.6.1

//下载最新版本，文章发布时为6.6.1，切记Elastic Stack的产品版本要保持一致

[root@localhost home]# curl -L -O https://artifacts.elastic.co/downloads/logstash/logstash-6.6.1.tar.gz
//解压
[root@localhost home]# tar -zvxf logstash-6.6.1
[root@localhost logstash-6.6.1]# ls
bin     CONTRIBUTORS  Gemfile       lib          logs           logstash-core-plugin-api  NOTICE.TXT  vendor
config  data          Gemfile.lock  LICENSE.txt  logstash-core  modules                   tools       x-pack
[root@localhost logstash-6.6.1]# cd config/
[root@localhost config]# ls
jvm.options        logstash.yml       pipelines.yml
log4j2.properties  logstash-sample.conf  startup.options
//直接新建一个配置
[root@localhost config]# vi logstash-mysql.conf

# Sample Logstash configuration for creating a simple
# Beats -> Logstash -> Elasticsearch pipeline.

input {
  beats {
    port => 5044
  }
}

#过滤和清理filebeats传过来的日志,作用是把日志里的关键信息拆分出来，比如User、Host、Quer_Time、Lock_Time、timestamp等
filter {
  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}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)"
 ] 
  }
  
  date {
        match => ["mysql.slowlog.timestamp", "UNIX", "YYYY-MM-dd HH:mm:ss"]
        target => "@timestamp"
        timezone => "Asia/Chongqing"
  }

  mutate {
        remove_field => ["@version","message","timestamp_mysql"]
  }

}
#输出到es中
output {
    elasticsearch{
        hosts => ["192.168.8.254:9200"]
    #这是es中索引的名称，我这里按天创建索引，也可以按月划分
	index  => "mysql-slow-%{+YYYY.MM.dd}"
    }
}
#直接输出到屏幕，方便验证查看
output { stdout {} }

保存后退出，这里还是，需要特别注意grok match中的message匹配，这里就是把filebeat传过来的message字段进行过滤！！如果mysql版本不是5.6，一定要确认下当前mysql慢日志的格式，以便修改正则表达式

[root@localhost config]# ../bin/logstash -f mysql-stdout.conf
Sending Logstash logs to /home/logstash-6.6.1/logs which is now configured via log4j2.properties
[2019-03-04T15:09:14,077][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2019-03-04T15:09:14,086][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"6.6.1"}
[2019-03-04T15:09:14,530][INFO ][logstash.config.source.local.configpathloader] No config files found in path {:path=>"/home/logstash-6.6.1/mysql-stdout.conf"}
[2019-03-04T15:09:14,535][ERROR][logstash.config.sourceloader] No configuration found in the configured sources.
[2019-03-04T15:09:14,703][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[root@localhost logstash-6.6.1]# cd config/
[root@localhost config]# ../bin/logstash -f mysql-stdout.conf 
Sending Logstash logs to /home/logstash-6.6.1/logs which is now configured via log4j2.properties
[2019-03-04T15:10:28,792][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2019-03-04T15:10:28,802][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"6.6.1"}
[2019-03-04T15:10:33,896][INFO ][logstash.pipeline        ] Starting pipeline {:pipeline_id=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50}
[2019-03-04T15:10:34,195][INFO ][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://192.168.8.254:9200/]}}
[2019-03-04T15:10:34,324][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"http://192.168.8.254:9200/"}
[2019-03-04T15:10:34,357][INFO ][logstash.outputs.elasticsearch] ES Output version determined {:es_version=>6}
[2019-03-04T15:10:34,360][WARN ][logstash.outputs.elasticsearch] Detected a 6.x and above cluster: the `type` event field won't be used to determine the document _type {:es_version=>6}
[2019-03-04T15:10:34,388][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["//192.168.8.254:9200"]}
[2019-03-04T15:10:34,394][INFO ][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2019-03-04T15:10:34,407][INFO ][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>60001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2019-03-04T15:10:34,709][INFO ][logstash.inputs.beats    ] Beats inputs: Starting input listener {:address=>"0.0.0.0:5044"}
[2019-03-04T15:10:34,733][INFO ][logstash.pipeline        ] Pipeline started successfully {:pipeline_id=>"main", :thread=>"#<Thread:0x423010a1 run>"}
[2019-03-04T15:10:34,778][INFO ][logstash.agent           ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
[2019-03-04T15:10:34,789][INFO ][org.logstash.beats.Server] Starting server on port: 5044
[2019-03-04T15:10:34,943][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}

启动成功

接下来还是验证一下，运行一条sql：select sleep(1)；等待片刻看屏幕是否打印出相关数据：

{
              "log" => {
         "file" => {
            "path" => "/home/mysql5.6/mysql-slow.log"
        },
        "flags" => [
            [0] "multiline"
        ]
    },
            "query" => "SELECT SLEEP(1);",
       "@timestamp" => 2019-03-04T07:13:15.891Z,
             "user" => "root",
       "prospector" => {
        "type" => "log"
    },
           "source" => "/home/mysql5.6/mysql-slow.log",
          "fileset" => {
          "name" => "slowlog",
        "module" => "mysql"
    },
           "offset" => 844,
        "rows_sent" => "1",
        "lock_time" => "0.000000",
            "event" => {
        "dataset" => "mysql.slowlog"
    },
         "clientip" => "192.168.8.121",
       "query_time" => "1.000223",
    "rows_examined" => "0",
             "beat" => {
         "version" => "6.6.1",
            "name" => "localhost.localdomain",
        "hostname" => "localhost.localdomain"
    },
            "input" => {
        "type" => "log"
    },
             "host" => {
                   "os" => {
             "version" => "7 (Core)",
              "family" => "redhat",
            "codename" => "Core",
            "platform" => "centos",
                "name" => "CentOS Linux"
        },
        "containerized" => true,
                 "name" => "localhost.localdomain",
         "architecture" => "x86_64",
                   "id" => "808d566509d84563b4db09739872d798"
    },
               "id" => "16",
             "tags" => [
        [0] "beats_input_codec_plain_applied"
    ]
}
{
              "log" => {
         "file" => {
            "path" => "/home/mysql5.6/mysql-slow.log"
        },
        "flags" => [
            [0] "multiline"
        ]
    },
            "query" => "SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID;",
       "@timestamp" => 2019-03-04T07:13:15.891Z,
             "user" => "root",
       "prospector" => {
        "type" => "log"
    },
          "fileset" => {
          "name" => "slowlog",
        "module" => "mysql"
    },
           "source" => "/home/mysql5.6/mysql-slow.log",
           "offset" => 1016,
        "rows_sent" => "15",
        "lock_time" => "0.000097",
            "event" => {
        "dataset" => "mysql.slowlog"
    },
         "clientip" => "192.168.8.121",
       "query_time" => "0.001123",
    "rows_examined" => "279",
             "beat" => {
         "version" => "6.6.1",
            "name" => "localhost.localdomain",
        "hostname" => "localhost.localdomain"
    },
            "input" => {
        "type" => "log"
    },
             "host" => {
                   "os" => {
            "platform" => "centos",
             "version" => "7 (Core)",
            "codename" => "Core",
              "family" => "redhat",
                "name" => "CentOS Linux"
        },
        "containerized" => true,
                 "name" => "localhost.localdomain",
         "architecture" => "x86_64",
                   "id" => "808d566509d84563b4db09739872d798"
    },
               "id" => "16",
             "tags" => [
        [0] "beats_input_codec_plain_applied"
    ]
}
{
              "log" => {
         "file" => {
            "path" => "/home/mysql5.6/mysql-slow.log"
        },
        "flags" => [
            [0] "multiline"
        ]
    },
            "query" => "SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/1.000283*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=53 GROUP BY STATE ORDER BY SEQ;",
       "@timestamp" => 2019-03-04T07:13:15.893Z,
             "user" => "root",
       "prospector" => {
        "type" => "log"
    },
           "source" => "/home/mysql5.6/mysql-slow.log",
          "fileset" => {
          "name" => "slowlog",
        "module" => "mysql"
    },
           "offset" => 1274,
        "rows_sent" => "13",
        "lock_time" => "0.000127",
            "event" => {
        "dataset" => "mysql.slowlog"
    },
         "clientip" => "192.168.8.121",
       "query_time" => "0.000997",
    "rows_examined" => "281",
             "beat" => {
         "version" => "6.6.1",
            "name" => "localhost.localdomain",
        "hostname" => "localhost.localdomain"
    },
            "input" => {
        "type" => "log"
    },
             "host" => {
                   "os" => {
            "platform" => "centos",
             "version" => "7 (Core)",
            "codename" => "Core",
              "family" => "redhat",
                "name" => "CentOS Linux"
        },
        "containerized" => true,
                 "name" => "localhost.localdomain",
         "architecture" => "x86_64",
                   "id" => "808d566509d84563b4db09739872d798"
    },
               "id" => "16",
             "tags" => [
        [0] "beats_input_codec_plain_applied"
    ]
}

验证成功，一共三条记录，每条记录中，user、query、clientid、query_time等都被抽取出来了

 

5、进入kibana再次验证es是否有相关数据

ok，mysql慢日志的关键信息都有了，当日志多了就可以用来分析了 

 

这里顺便说一下，Kibana中的Discover，只接受indexpatterns，例如我们需要分析的索引格式为mysql-slow-*，就在Management中添加一个index pattern即可

回到Discover就可以选择索引了

大功告成～

欧阳陈曦
关注
  • 1
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
ELK收集MySQL SlowLog（Filebeat .KO. Logstash ）【实操系列】
要啥自行车，一把梭~
02-22 1358
文章目录故事背景1. 需求2. 问题拆解3. 难点/处理方法难点处理方法ELK收集MySQL SlowLog方案一：Logstash收集 故事背景 1. 需求 收集腾讯云MySQL实例的慢查询，并展示 2. 问题拆解 调用腾讯云API接口拉取slowlog文件 使用Logstash或Filebeat收集慢查询 3. 难点/处理方法 难点 每次拉取慢查询只能是全量拉取 本人编程能力比较差，仅仅...
ELK日志分析系统-filebeat
10-19
ELK（Elasticsearch, Logstash, Kibana）日志分析系统是一个广泛使用的开源解决方案，用于收集、处理、分析和可视化各种来源的日志数据。Filebeat是这个系统中的一个关键组件，它作为轻量级的日志转发器，负责从...
收集mysql慢查询日志进elk
山间明月江上清风的专栏
02-25 813
1，首先mysql开启慢查询日志 vim /etc/mysql/mysql.conf.d/mysqld.cnf #添加配置，这块目录可以自定义 slow_query_log = ON slow_query_log_file = /var/lib/mysql/slow_query.log long_query_time = 2 #然后重启mysql服务 service mysqld resta...
MySQL慢查询日志slowlog
最新发布
qq_43842093的博客
05-01 1892
慢速查询日志记录的是执行时间超过秒和检查的行数超过的SQL语句，这些语句通常是需要进行优化的。官方参考文档：https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html。
使用ELK收集分析MySQL慢查询日志
abtmh02622的专栏
02-27 461
　　参考文档：https://www.cnblogs.com/bixiaoyu/p/9638505.html 　　MySQL开启慢查询不详述 　　MySQL5.7慢查询日志格式如下 /usr/local/mysql/bin/mysqld, Version: 5.7.22-log (MySQL Community Server (GPL)). started with: Tcp...
ELK采集MySQL慢日志实现
雪辉博客
02-14 3279
使用ELK采集mysql慢日志进行统计分析
elk收集mysql日志 filebead收集mysql慢日志与错误日志
系统运维博客，分享实用的运维技巧、经验和解决方案。致力于帮助读者解决系统运维中的各种问题，提升运维效率。欢迎关注，共同学习进步！
01-05 521
filebeat收集mysql错误日志与慢日志
ELK日志系统部署方案cycwll.doc
10-12
ELK（Elasticsearch, Logstash, Kibana）日志系统是一种流行的开源日志管理架构，用于收集、处理、存储和分析来自不同源的日志数据。它在运维监控、应用监控、网络监控以及错误排查等方面发挥着重要作用，提高了故障...
基于Golang+Kratos+MySQL+Redis+Kafka+elk+Opentracing实现的微服务项目
07-28
6. **ELK Stack**：ELK是Elasticsearch、Logstash和Kibana的组合，提供了一个强大的日志分析和可视化解决方案。Elasticsearch用于存储和搜索日志，Logstash收集、处理和转发日志，Kibana则用于日志的可视化展示和...
docker-for-symfony：用于Symfony 4的Docker堆栈-NGINX PHP7-FPM MySQL ELK Redis RabbitMQ
02-04
**ELK (Elasticsearch, Logstash, Kibana)** 日志栈提供了强大的日志收集、存储和分析功能。Elasticsearch用于存储和搜索日志，Logstash负责收集和预处理来自不同源的日志，而Kibana则提供了可视化界面，帮助开发者...
已上线的日志采集系统，使用flume收集日志.zip
01-04
在这个日志系统中，Kafka扮演了中间件的角色，接收来自Logstash的结构化日志数据，并存储在主题（topics）中，等待消费者（可能是数据分析应用、ELK Stack或其他系统）进行实时处理或离线分析。 **数据库** 虽然...
运维36讲第22课：ELK 构建 MySQL 慢日志收集平台详解
sucaiwa的博客
03-21 742
想必你对 ELK、EFK 都不陌生，它们有一个共同的组件：Elasticsearch（简称ES），它是一个实时的全文搜索和分析引擎，可以提供日志数据的收集、分析、存储 3 大功能。另外一个组件 Kibana 是这套检索系统中的 Web 图形化界面系统，可视化展示在 Elasticsearch 的日志数据和结果。ELF/EFK 工具集中还有 l 和 F 这两个名称的缩写，这两个缩写代表的工具根据不同的架构和使用方式而定。L 通常是 Logstash 组件，它是一个用来搜集、分析、过滤日志的工具。
ELK原理讲解与收集MySQL慢日志实现
weixin_42460087的博客
08-04 1271
ELK 最早是 Elasticsearch（简称ES）、Logstash、Kibana 三款开源软件的简称，三款软件后来被同一公司收购，并加入了Xpark、Beats等组件，改名为Elastic Stack，成为现在最流行的开源日志解决方案，虽然有了新名字但大家依然喜欢叫ELK，现在所说的ELK就指的是基于这些开源软件构建的日志系统。一般大型系统是一个分布式部署的架构，不同的服务模块部署在不同的服务器上，问题出现时，大部分情况需要根据问题暴露的关键信息，定位到具体的服务器和服务模块。...
MySQL 慢查询日志分析(Filebeat+Elasticsearch+DataEase)
XiaoBei_BI的博客
12-30 1151
本仪表板使用开源数据可视化分析工具 DataEase 制作，可以通过上方的搜索框过滤查询语句，也可以过滤日志产生的日期范围。
ELK之收集日志到mysql数据库
weixin_30271335的博客
02-10 1261
写入数据库的目的是持久化保存重要数据,比如状态码、客户端浏览器版本等,用于后期按月做数据统计等. 环境准备 linux-elk1:10.0.0.22,Kibana ES Logstash Nginx linux-elk2:10.0.0.33,MySQL5.7 1.linux-elk2上配置数据库 安装好数据库后,配置,并授权 mysql -uroot -p'Root1...
elk mysql 全文索引_利用ELK收集分析展现MYSQL相关日志-51Testing.PDF
weixin_39925098的博客
01-26 112
利用ELK收集分析展现MYSQL相关日志-51Testing利用ELK收集、分析、展现MYSQL相关日志——任志强背景1、现状日志特点：▲ 海量数据▲ 分散分布▲ 分析、汇总背景2、日志系统一个完整日志系统的特点：▲ 收集－能够采集多种来源的日志数据▲ 传输－能够稳定的把日志数据传输到中央系统▲ 存储－如何存储日志数据▲ 分析－可以支持 UI 分析▲ 警告－能够提供错误报告，监控机制ELK介绍1、...
使用ELK分析Mysql慢查询日志
蜗牛^_^的博客
08-28 1955
目录 1.ELK介绍 2.ELK安装及问题解决 3.架构 4.Mysql慢查询配置 5.具体分析 6.参考资料 1.ELK介绍 1）Elasticsearch Elasticsearch 是基于 JSON 的分布式搜索和分析引擎，专为实现水平扩展、高可靠性和管理便捷性而设计。 Elasticsearch 是一个分布式的 RESTful 风格的搜索和数据分析引擎，能够解决不断涌现出的...
ELK收集分析处理mysql slowlog
qq_37256882的博客
12-28 363
这篇文章没有涉及ELK程序的安装，重点关注于以下几个方面1，通过filebeat收集mysql-slowlog，并将slowlog的多行合并为一行2，logstash通过grok将slowlog处理为json格式3，通过kibana的dashboard展示数据。
kibana大屏可视化的MySQL慢日志数据收集和展示查询
运维打怪晋级之路
09-24 1468
ELK收集和展示Mysql慢日志数据 1、收集需求Mysql慢日志 要求Mysql的慢日志可以有地方查询，便于开发人员分析问题； 对应的慢日志记录如下： # Time: 2021-08-19T05:28:13.275255+08:00 # User@Host: gtmed_wm[gtmed_wm] @ [10.152.160.184] Id: 50278 # Schema: gtmed_wm Last_errno: 0 Killed: 0 # Query_time: 4.434879 Lock.

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值