6.prometheus监控mysql多实例

一、背景介绍

公司采用prometheus作为监控平台，经过资料查找，mysqld_exporter和mysql是[1:1]，既每个mysql实例都需要起一个exporter进程来采集mysql监控数据，不过最近看github文档关注到mysqld_exporter已经开始支持[1:n],即单个mysqld_exporter可以收集多个mysql实例，不过具体使用过程中会发现一些问题！下面来详解一下。

二、mysql准备

2.1 mysql集群

ip	端口	版本
47.103.89.xxx	3306	8.0.30
47.103.89.xxx	3307	8.0.27
47.100.70.xxx	3306	8.0.28
47.100.70.xxx	3307	8.0.28

2.2 mysql数据库创建账户

create user readonly@'%' identified by '密码';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'readonly'@'%';

三、安装mysqld_exporter

3.1 配置golang环境

cd /opt
wget https://golang.google.cn/dl/go1.19.linux-amd64.tar.gz
tar xf go1.19.linux-amd64.tar.gz
mv go /usr/local/

配置环境变量

cat /etc/profile
export GOPATH=/home/go
export GOROOT=/usr/local/go
export PATH=$PATH:$GOROOT/bin

. /etc/profile

3.2 下载mysqld_exporter

git clone https://github.com/prometheus/mysqld_exporter.git
cd  mysqld_exporter-main
go build
mkdir /opt/mysqld_exporter
mv mysqld_exporter /opt/mysqld_exporter/

3.2 配置systemd服务

cat /usr/lib/systemd/system/mysqld_exporter.service
[Unit]
Description=Prometheus MySQL daemon
After=network.target

[Service]
User=root
Group=root
Type=simple
Restart=always
ExecStart=/opt/mysqld_exporter/mysqld_exporter \
    --collect.global_status \
    --collect.auto_increment.columns \
    --collect.info_schema.processlist \
    --collect.binlog_size \
    --collect.info_schema.tablestats \
    --collect.global_variables \
    --collect.info_schema.innodb_metrics \
    --collect.info_schema.query_response_time \
    --collect.info_schema.userstats \
    --collect.info_schema.tables \
    --collect.perf_schema.tablelocks \
    --collect.perf_schema.file_events \
    --collect.perf_schema.eventswaits \
    --collect.perf_schema.indexiowaits \
    --collect.perf_schema.tableiowaits \
    --collect.slave_status \
    --web.listen-address=0.0.0.0:9104

[Install]
WantedBy=multi-user.target

三、 配置my.cnf

3.1 准备my.cnf

[root@pre-106 ~]# cat .my.cnf 
[client]
user=readonly
password=xxxxx
[client.servers]
user=readonly
password=xxxx

3.2 验证

# curl 'http://localhost:9104/probe?target=106.14.46.xxx:3307' 2>/dev/null|grep mysql_version_info\{
mysql_version_info{innodb_version="8.0.28",version="8.0.28",version_comment="MySQL Community Server - GPL"} 1

# curl 'http://localhost:9104/probe?target=47.103.89.xxx:3306' 2>/dev/null|grep mysql_version_info\{
mysql_version_info{innodb_version="8.0.30",version="8.0.30",version_comment="MySQL Community Server - GPL"} 1

# curl 'http://localhost:9104/probe?target=47.103.89.xxx:3307' 2>/dev/null|grep mysql_version_info\{
mysql_version_info{innodb_version="8.0.27",version="8.0.27",version_comment="MySQL Community Server - GPL"} 1

四、prometheus集成mysqld_exporter

4.1 更改配置文件

  - job_name: mysql # To get metrics about the mysql exporter’s targets
    params:
      # Not required. Will match value to child in config file. Default value is `client`.
      auth_module: [client.servers]
    static_configs:
    - targets:
      - 47.103.89.210:3306
      labels:
        project: faone-mysql-test
        server: faone-mysql-test-3306
        
    - targets:
      - 47.103.89.210:3307
      labels:
        project: faone-mysql-test
        server: faone-mysql-test-3307
        
    - targets:
      - 106.14.46.145:3306
      labels:
        project: faone-mysql-pre
        server: faone-mysql-pre-3306
        
    - targets:
      - 106.14.46.145:3307
      labels:
        project: faone-mysql-pre
        server: faone-mysql-pre-3307
 
    relabel_configs:
      - source_labels: [__address__]
        target_label: __param_target
      - source_labels: [__param_target]
        target_label: instance
      - target_label: __address__
        # The mysqld_exporter host:port
        replacement: 172.16.40.152:9104

4.2 验证