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

Elasticsearch 系列文章

1、介绍lucene的功能以及建立索引、搜索单词、搜索词语和搜索句子四个示例实现
2、Elasticsearch7.6.1基本介绍、2种部署方式及验证、head插件安装、分词器安装及验证
3、Elasticsearch7.6.1信息搜索示例（索引操作、数据操作-添加、删除、导入等、数据搜索及分页）
4、Elasticsearch7.6.1 Java api操作ES（CRUD、两种分页方式、高亮显示）和Elasticsearch SQL详细示例
5、Elasticsearch7.6.1 filebeat介绍及收集kafka日志到es示例
6、Elasticsearch7.6.1、logstash、kibana介绍及综合示例（ELK、grok插件）
7、Elasticsearch7.6.1收集nginx日志及监测指标示例
8、Elasticsearch7.6.1收集mysql慢查询日志及监控
9、Elasticsearch7.6.1 ES与HDFS相互转存数据-ES-Hadoop

---

---

本文简单的介绍了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

以上，简单的介绍了elasticsearch通过mysql的日志监控其慢查询示例。