使用node_exporter方式采集MySQL慢SQL信息在grafana面板显示

【背景说明】

因为要集成监控数据，需要把MySQL的慢SQL日志信息显示在grafana，其他方法抓取慢日志然后显示到面板需要安装挺多插件，是简单的脚本采集数据，这里使用node_exporter方式实现。

说明：看实际情况，方便的采集MySQL慢日志的话可以直接使用PMM来监控就行

【步骤】

前提：安装好prometheus跟grafana跟MySQL

1,在MySQL主机上面安装好node_exporter

node_exporter配置指定需要扫描的文件路径参数 --collector.textfile.directory=，这里指定的是/root/soft/prometheus_exporter/node_exporter-1.3.1/slow/slowlog目录：

cat  /etc/systemd/system/node_exporter.service
[Unit]
Description=node_exporter
Documentation=https://prometheus_exporter.io/
After=network.target
[Service]
ExecStart=/root/soft/prometheus_exporter/node_exporter-1.3.1/node_exporter --collector.textfile.directory=/root/soft/prometheus_exporter/node_exporter-1.3.1/slow/slowlog
Restart=on-failure
[Install]
WantedBy=multi-user.target

2,创建生成prometheus能识别的格式日志SHELL脚本

定义临时慢日志文件名称logfile变量，定义Prometheus读取prometheus_directory目录变量，然后采集各个指标

cd /root/soft/prometheus_exporter/node_exporter-1.3.1/slow
vim  mysql_slow.sh
#!/bin/bash

# 输入日志文件路径
logfile="mysql01_slow.log"

# 指标名称
metric_name="mysql_slow_query_duration_seconds"

# 指定Prometheus读取目录
prometheus_directory="/root/soft/prometheus_exporter/node_exporter-1.3.1/slow"

cd ${prometheus_directory}
rm -rf mysqlslow.prom
echo "# HELP mysql_slow_query_text">>mysqlslow.prom
echo "# TYPE mysql_slow_query_text gauge">>mysqlslow.prom

# 逐行读取日志文件并处理每个日志条目
while IFS= read -r line || [[ -n "$line" ]]; do
    if [[ $line == "# Time:"* ]]; then
        # 如果是新的日志条目的开头，初始化指标值
        query_time=""
        lock_time=""
        rows_sent=""
        rows_examined=""
        query=""
        timestamp=""
        vdate=$(echo "$line" |awk '{print $3}')
        username=""
    elif [[ $line == "# User@Host:"* ]]; then
        #定义用户信息字段
        username=$(echo "$line" |awk '{print $3}')
    elif [[ $line == "SET timestamp="* ]]; then
        # 在日志条目末尾，生成Prometheus格式数据并输出到文件
        timestamp=$(echo "$line" | awk '{print $2}')
    elif [[ $line == "# Query_time:"* ]]; then
        # 提取指标值
        query_time=$(echo "$line" | awk '{print $3}')
        lock_time=$(echo "$line" | awk '{print $5}')
        rows_sent=$(echo "$line" | awk '{print $7}')
        rows_examined=$(echo "$line" | awk '{print $9}')
    elif [[ $line != "# "* ]]; then
        # 提取SQL查询语句
        #query=$line
        query+=" $line"
        printf "mysql_slow_query_text{query_time=\"%s\",lock_time=\"%s\",rows_sent=\"%s\",rows_examined=\"%s\",query=\"%s\",username=\"%s\",vdate=\"%s\"} %s\n" \
        "$query_time" "$lock_time" "$rows_sent" "$rows_examined" "$query" "$username" "$vdate"  "$query_time" >> mysqlslow.prom
    fi
done < "${prometheus_directory}/$logfile"

#拷贝文件到Prometheus读取目录
mv   mysqlslow.prom  "${prometheus_directory}/slowlog"

执行生成prometheus能识别的格式日志SHELL脚本，能正常采集数据格式如下即可：

有指标头信息跟具体采集的mysql_slow_query_text指标值信息，格式一定要准确

cat slowlog/mysqlslow.prom 
# HELP mysql_slow_query_text
# TYPE mysql_slow_query_text gauge
mysql_slow_query_text{query_time="0.002526",lock_time="0.000147",rows_sent="513",rows_examined="513",query=" SELECT /*replication-manager*/ UPPER(Variable_name) AS variable_name, UPPER(Variable_Value) AS value FROM performance_schema.global_variables;",username="dbadmin[dbadmin]",vdate="2023-08-13T11:19:05.213554+08:00"} 0.002526
mysql_slow_query_text{query_time="0.002886",lock_time="0.000121",rows_sent="513",rows_examined="513",query=" SELECT /*replication-manager*/ UPPER(Variable_name) AS variable_name, UPPER(Variable_Value) AS value FROM performance_schema.global_variables;",username="dbadmin[dbadmin]",vdate="2023-08-13T11:19:07.365838+08:00"} 0.002886
mysql_slow_query_text{query_time="0.002561",lock_time="0.000176",rows_sent="513",rows_examined="513",query=" SELECT /*replication-manager*/ UPPER(Variable_name) AS variable_name, UPPER(Variable_Value) AS value FROM performance_schema.global_variables;",username="dbadmin[dbadmin]",vdate="2023-08-13T11:19:09.477784+08:00"} 0.002561

3,查看是否能正常采集到数据到curl

重启一下node_exporter,服务状态没有报错即可，访问一下采集信息，采集正常即可

systemctl  restart node_exporter
systemctl  status node_exporter
http://xxx.xxx.xxx.xxx:9100/metrics

3,创建增量采集日志SHELL的脚本

cd /root/soft/prometheus_exporter/node_exporter-1.3.1/slow

注意先定义一下自己实际的变量，定义60秒自动扫描MySQL慢日志interval，MySQL本身的慢日志信息slowlog_path，定义存处理后的慢日志文件路径v_node_exporter_slow_path

vim cut_mysql_slowlog.sh
#!/bin/bash

# 定时运行的频率（以秒为单位）
interval=60

# 慢查询日志文件路径
slowlog_path=/db/mysql/mysql01_slow.log
v_node_exporter_slow_path=/root/soft/prometheus_exporter/node_exporter-1.3.1/slow
v_lastpos_files="${v_node_exporter_slow_path}/last_pos"
v_mysql_slow_log=mysql01_slow.log

# 检查文件是否存在
if [ -e "$v_lastpos_files" ]; then
  # 读取文件内容并将其赋给变量
  last_pos=$(cat "$v_lastpos_files")
else
  # 如果文件不存在，则创建文件并赋空值给变量
  echo $(wc -c < "$slowlog_path") > "$v_lastpos_files"
  last_pos=$(cat "$v_lastpos_files")
fi

# 循环运行
while true; do
  # 获取当前日志文件大小
  current_file_size=$(wc -c < "$slowlog_path")
  
  # 如果文件有更新
  if [ "$current_file_size" -gt "$last_pos" ]; then
    # 从上一次位置开始提取增量数据
    tail -c $((current_file_size-last_pos)) "$slowlog_path"  > ${v_node_exporter_slow_path}/mysql01_slow.log
    sh ${v_node_exporter_slow_path}/mysql_slow.sh
    # 更新上一次位置为当前文件大小
    last_pos=$current_file_size
    echo "$last_pos" > ${v_node_exporter_slow_path}/last_pos
  else
    echo ""> ${v_node_exporter_slow_path}/mysql01_slow.log
    sh ${v_node_exporter_slow_path}/mysql_slow.sh
  fi
  
  # 等待一段时间后再次检查
  sleep "$interval"
done

执行增量采集MySQL的SHELL脚本,观察v_node_exporter_slow_path路径下的mysql01_slow.log文件内容是增量的，则采集正常，同时查看node_exporter状态跟slowlog/mysqlslow.prom信息是最新的即可。

nohup sh cut_mysql_slowlog.sh &

【面板显示】

配置好监控配置文件，这里有node_exporter跟mysqld-exporter信息，mysqld-exporter自行安装，主要将监控面板放到MySQL里面去

  - job_name: linux_187
    static_configs:
      - targets: ['xxx.xxx.xxx.xxx:9100']
        labels:
          instance: xxx.xxx.xxx.xxx
  - job_name: mysql_187
    static_configs:
      - targets: ['xxx.xxx.xxx.xxx:9104']
        labels:
          instance: xxx.xxx.xxx.xxx

 导入两个面板后，能查看到具体的监控信息，然后再MySQL面板新建一个面板，面板视图为table，添加监控的指标

 根据实际情况过滤数据或者修改字段的名称及排版样式

 最终效果

【说明】

当前是使用shell脚本60秒采集一下数据的，面板数据会延迟60秒，时间字段数据是从慢日志文件中提取，时间信息精确。