8、Elasticsearch7.6.1收集mysql慢查询日志及监控

领域专家: 大数据技术领域

2023-07-03 09:36:49

Elasticsearch 系列文章

文章目录

Elasticsearch 系列文章
一、开启mysql慢查询
二、添加mysql日志
1、filebeat.yml
2、mysql.yml
3、验证
三、添加mysql指标
1、metricbeat.yml
2、mysql.yml

本文简单的介绍了elasticsearch通过mysql的日志监控其慢查询示例。
本文依赖es环境和mysql环境好用
本文分为3个部分，即开启mysql慢查询日志、将mysql日志存储es和通过日志分析结果。

一、开启mysql慢查询

--1、修改配置文件
vim /etc/my.cnf

[mysqld]
....
slow_query_log=on
slow_query_log_file=/var/log/slow-mysql-query.log
long_query_time=10
log_queries_not_using_indexes = 0

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
....

-- 参数说明如下：
-- slow_query_log：慢查询开启状态
-- slow_query_log_file：慢查询日志存放的位置（一般设置为 MySQL 的数据存放目录）
-- long_query_time：查询超过多少秒才记录,该值的单位是秒。如果不设置 long_query_time 选项，默认时间为 10 秒。

-- 或者在mysql命令行执行

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;
SET GLOBAL log_queries_not_using_indexes = 0;

-- 2、日志文件创建与授权
-- 注意 MySQL不会主动创建日志文件，所以需要手动创建对应文件。
chown -R mysql:mysql /var/log/slow-mysql-query.log
chown -R mysql:mysql /var/log/mysqld.log

-- 3、重启MySQL
service mysqlid restart

-- 4、执行命令
set global log_queries_not_using_indexes = 0;

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

-- 5、日志样子
# Time: 2023-02-28T06:46:53.080522Z
# User@Host: skip-grants user[root] @  [192.168.3.105]  Id:    64
# Query_time: 20.861834  Lock_time: 0.000348 Rows_sent: 12606948  Rows_examined: 12606948
use test;
SET timestamp=1677566813;
select * from dx_user where user_name like '%alan%';

# Time: 2023-02-28T07:08:16.970130Z
# User@Host: skip-grants user[root] @  [192.168.3.105]  Id:    64
# Query_time: 13.592648  Lock_time: 0.000063 Rows_sent: 12606948  Rows_examined: 12606948
SET timestamp=1677568096;
select * from dx_user;

二、添加mysql日志

在这里插入图片描述

1、filebeat.yml

###################### Filebeat Configuration Example #########################

#=========================== Filebeat inputs =============================

filebeat.inputs:

- type: log

  enabled: false

  paths:
    - /var/log/*.log


#============================= Filebeat modules ===============================

filebeat.config.modules:

  path: ${path.config}/modules.d/*.yml

  reload.enabled: false


#==================== Elasticsearch template setting ==========================

setup.template.settings:
  index.number_of_shards: 1

#================================ General =====================================


#============================== Dashboards =====================================

#============================== Kibana =====================================

setup.kibana:

  host: "server1:5601"

#============================= Elastic Cloud ==================================

#================================ Outputs =====================================



#-------------------------- Elasticsearch output ------------------------------
output.elasticsearch:

  hosts: ["server1:9200" ,"server2:9200" ,"server3:9200"]

#----------------------------- Logstash output --------------------------------


#================================ Processors =====================================

# Configure processors to enhance or manipulate events generated by the beat.

processors:
  - add_host_metadata: ~
  - add_cloud_metadata: ~
  - add_docker_metadata: ~
  - add_kubernetes_metadata: ~

#================================ Logging =====================================

#============================== X-Pack Monitoring ===============================

#================================= Migration ==================================

2、mysql.yml

- module: mysql
  # Error logs
  error:
    enabled: true

    # Set custom paths for the log files. If left empty,
    # Filebeat will choose the paths depending on your OS.
    var.paths: ["/var/log/mysqld.log"]

  # Slow logs
  slowlog:
    enabled: true

    # Set custom paths for the log files. If left empty,
    # Filebeat will choose the paths depending on your OS.
    var.paths: ["/var/log/slow-mysql-query.log"]

3、验证

在这里插入图片描述

三、添加mysql指标

在这里插入图片描述

1、metricbeat.yml

---
metricbeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false
setup.template.settings:
  index.number_of_shards: 1
  index.codec: best_compression
setup.kibana:
  host: server1:5601
output.elasticsearch:
  hosts:
    - server1:9200
    - server2:9200
    - server3:9200
processors:
  - add_host_metadata: null
  - add_cloud_metadata: null
  - add_docker_metadata: null
  - add_kubernetes_metadata: null

2、mysql.yml

- module: mysql
  metricsets:
    - status
  #  - galera_status
  period: 10s

  # Host DSN should be defined as "user:pass@tcp(127.0.0.1:3306)/"
  # or "unix(/var/lib/mysql/mysql.sock)/",
  # or another DSN format supported by <https://github.com/Go-SQL-Driver/MySQL/>.
  # The username and password can either be set in the DSN or using the username
  # and password config options. Those specified in the DSN take precedence.
  hosts: ["root:rootroot@tcp(192.168.10.44:3306)/"]

  # Username of hosts. Empty by default.
  username: root

  # Password of hosts. Empty by default.
  password: 123456