使用mysql_exporter监控mysql

原创

Bob hadoop

修改于 2021-03-09 10:26:47

2.2K0

修改于 2021-03-09 10:26:47

文章被收录于专栏：日常杂记

目的：记录mysql监控搭建全过程

因为监控架构采用的是prometheus+grafana+alter manager架构，所以监控mysql第一时间想到的是使用mysql_exporter。

1、准备

mysql_exporter下载地址为：https://github.com/prometheus/mysqld_exporter

线上服务器操作系统为Center OS 7 对应选择相应版本

解压、更改属主和属组以及执行权限。

[root@bigdata01 monitor]# chown -R root:root mysqld_exporter/
[root@bigdata01 monitor]# chmod -R 755 mysqld_exporter/
[root@bigdata01 monitor]# ll
总用量 0
drwxr-xr-x  3 root root 105 2月  25 16:42 alertmanager
drwxr-xr-x  2 root root 254 3月   7 10:27 clickhousesoft
drwxr-xr-x 10 root root 272 2月  17 02:13 go
drwxr-xr-x  8 root root 166 2月  25 17:04 grafana
drwxr-xr-x  2 root root  58 7月  29 2019 mysqld_exporter
drwxr-xr-x  2 root root  56 6月  16 2020 node_exporter
drwxrwxr-x  3 root root 260 6月  13 2017 prom2click-0.2
drwxr-xr-x  7 root root 201 2月  25 21:12 prometheus
drwxr-xr-x  6 root root 252 2月  25 16:42 prometheus-webhook-dingtalk

配置mysql_exporter启动脚本

vi /usr/lib/systemd/system/mysql_exporter.service

[Unit]
Description=mysqld_exporte
After=network.target
[Service]
Type=simple
User=mysql
Environment=DATA_SOURCE_NAME=exporter:1qaz@WSX(localhost:3306)/
ExecStart=/opt/monitor/mysqld_exporter/mysqld_exporter --web.listen-address=0.0.0.0:9104 
  --config.my-cnf /etc/my.cnf \
  --collect.slave_status \
  --collect.slave_hosts \
  --log.level=error \
  --collect.info_schema.processlist \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.innodb_tablespaces \
  --collect.info_schema.innodb_cmp \
  --collect.info_schema.innodb_cmpmem 
Restart=on-failure
[Install]
WantedBy=multi-user.targe

2、被监控端mysql配置

赋权访问exporter用户通过1qaz@WSX密码本地访问mysql，这样mysql端就配置完毕了。

mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost' IDENTIFIED BY '1qaz#EDC' WITH MAX_USER_CONNECTIONS 3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

启动mysql_exproter,并验证页面

systemctl start mysqld_exporter.service

访问localhost:9104/metrics可以看到mysql的指标

3、配置prometheus

[root@bigdata01 prometheus]# ll
总用量 161744
drwxr-xr-x. 2 root root       38 3月   8 17:44 console_libraries
drwxr-xr-x. 2 root root      173 3月   8 17:44 consoles
drwxr-xr-x. 7 root root      172 3月   8 23:00 data
-rw-r--r--. 1 root root    11357 3月   8 17:44 LICENSE
drwxr-xr-x  5 root root       41 3月   8 18:18 monitor_config
-rw-r--r--. 1 root root     3420 3月   8 17:44 NOTICE
-rwxr-xr-x. 1 root root 87758460 3月   8 17:44 prometheus
-rw-r--r--  1 root root    33526 3月   8 23:00 prometheus.log
-rw-r--r--  1 root root     1896 3月   8 22:15 prometheus.yml
-rwxr-xr-x. 1 root root 77805320 3月   8 17:44 promtool
drwxr-xr-x  2 root root        6 3月   8 18:18 rules

需要在monitor_config添加如下配置

[root@bigdata01 mysql]# cat test_mysql.yml 
- targets: [ "172.16.176.1:9104" ]
  labels:
    group: "mysql"
    mode: "mysql"

需要更改prometheus.yml文件。mysql没有注释的那段。

[root@bigdata01 prometheus]# cat prometheus.yml 
# my global config
global:
  scrape_interval:     15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).
#########################alerting#########################################################
# Alertmanager configuration
alerting:
  alertmanagers:
  - static_configs:
    - targets: ['172.16.176.1:9093']
      # - alertmanager:9093
#########################rules############################################################
# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"

########################prometheus#######################################################
# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: 'prometheus'

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
    - targets: ['127.0.0.1:9090']

######################hosts#############################################################
#  - job_name: 'hosts_b'
#    file_sd_configs:
#    - files: ['/opt/prometheus/monitor_config/host/*.yml']
#      refresh_interval:    5s

#####################mysql##############################################################
  - job_name: 'mysql'
    file_sd_configs:
    - files: ['/opt/monitor/prometheus/monitor_config/mysql/*.yml']
      refresh_interval: 5s

#####################cm###############################################################
#  - job_name: 'cm_monitor'
#    file_sd_configs:
#    - files: ['/opt/prometheus/monitor_config/cm/*.yml']
#      refresh_interval: 5s

4、验证采集

上一步骤更改完配置文件之后需要reload，这需要你的prometheus在启动的时候加上--web.enable-lifecycle启用远程热加载配置文件。

/opt/monitor/prometheus/prometheus --config.file="/opt/monitor/prometheus/prometheus.yml"> /opt/monitor/prometheus/prometheus.log --web.enable-lifecycle 2>&1 &

去页面查看