Prometheus系列--使用mysqld_exporter监控MySQL

一、概述

Prometheus监控MySQL，是使用mysqld_exporter插件来监控，在监控的时候，需要在mysql中创建一个MySQL用户，mysqld_exporter使用此用户获取MySQL运行状态数据。

二、在MySQL中增加mysqld_exporter专用账户

# 因为exporter是安装在MySQL主机上，所以创建用户的时候只创建localhost地址能访问的用户
mysql>  GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost' IDENTIFIED BY 'Test123456!' WITH MAX_USER_CONNECTIONS 3;
​
mysql> flush privileges;
​

三、安装mysqld_exporter

下载mysqld_exporter需要到github.com中下载，可能会比较慢，所以可以用github加速服务下载，具体百度吧。

cd /usr/local/src/
wget 
https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.12.1.linux-amd64.tar.gz  -C /usr/local/prometheus/
ln -s /usr/local/prometheus/mysqld_exporter-0.12.1.linux-amd64/mysqld_exporter /usr/local/prometheus/mysqld_exporter
​

四、创建mysqld_exporter的systemctl启动文件并启动

不管用systemctl 还是supervisor管理启动，都需要创建一个prometheus的用户，并且将exporter可执行文件授权prometheus

# 先创建一个prometheus的用户
useradd prometheus -s /sbin/nologin -M
chown -R prometheus.prometheus /usr/local/prometheus

1、使用systemctl形式

# 先创建一个prometheus的用户
useradd prometheus -s /sbin/nologin -M
​
# vim /usr/lib/systemd/system/mysqld_exporter.service
[Unit]
Description=mysqld_exporter
After=network.target
​
[Service]
Type=simple
User=prometheus
Environment=DATA_SOURCE_NAME=exporter:Test123456!@(localhost:3306)/
ExecStart=/usr/local/prometheus/mysqld_exporter --web.listen-address=0.0.0.0:9104 --config.my-cnf=/usr/local/prometheus/mysqld_exporter-0.12.1.linux-amd64/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.target

启动

chown -R root:root /usr/lib/systemd/system/mysqld_exporter.service
chmod 644 /usr/lib/systemd/system/mysqld_exporter.service
systemctl daemon-reload
systemctl enable mysqld_exporter.service
systemctl start mysqld_exporter.service

2、使用supervisor形式

# vim /etc/supervisor.d/mysqld_exporter.ini
[program:mysqld_exporter]
​
directory=/usr/local/prometheus
​
DATA_SOURCE_NAME=exporter:Test123456!@(localhost:3306)/
​
command=/usr/local/prometheus/mysqld_exporter --web.listen-address=0.0.0.0:9104 --config.my-cnf=/usr/local/prometheus/mysqld_exporter-0.12.1.linux-amd64/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
​
user=mysql
​
autostart=true
​
autorestart=true
​
startsecs = 2
​
redirect_stderr=true
​
stdout_logfile=/var/log/mysqld_exporter.log
​

启动

supervisorctl update
supervisorctl start mysqld_exporter

3、检查是否启动

# ps检查 
ps -ef |grep mysqld_exporter
# netstat/ss 检查
netstat/ss -luntp |grep 9104

五、查看mysqld_exporter监控到的指标

~]# curl localhost:9104/metrics
# HELP go_gc_duration_seconds A summary of the GC invocation durations.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 0
go_gc_duration_seconds{quantile="0.25"} 0
go_gc_duration_seconds{quantile="0.5"} 0
####此处省略若干，因为获取到的metrics实在太多，影响阅读，有兴趣自己curl命令查看下完整的。
mysql_info_schema_threads_seconds{state="replication master"} 0
mysql_info_schema_threads_seconds{state="rolling back"} 0
mysql_info_schema_threads_seconds{state="searching rows for update"} 0
mysql_info_schema_threads_seconds{state="sending data"} 0
mysql_info_schema_threads_seconds{state="sorting for group"} 0
mysql_info_schema_threads_seconds{state="sorting for order"} 0
mysql_info_schema_threads_seconds{state="sorting index"} 0
mysql_info_schema_threads_seconds{state="sorting result"} 0
mysql_info_schema_threads_seconds{state="statistics"} 0
mysql_info_schema_threads_seconds{state="updating"} 0
mysql_info_schema_threads_seconds{state="waiting for lock"} 0
mysql_info_schema_threads_seconds{state="waiting for table flush"} 0
mysql_info_schema_threads_seconds{state="waiting for tables"} 0
mysql_info_schema_threads_seconds{state="waiting on cond"} 0
mysql_info_schema_threads_seconds{state="writing to net"} 0
# HELP mysql_up Whether the MySQL server is up.
# TYPE mysql_up gauge
mysql_up 1
####此处省略若干，因为获取到的metrics实在太多，影响阅读，有兴趣自己curl命令查看下完整的。
​
# TYPE promhttp_metric_handler_requests_total counter
promhttp_metric_handler_requests_total{code="200"} 0
promhttp_metric_handler_requests_total{code="500"} 0
promhttp_metric_handler_requests_total{code="503"} 0

六、在prometheus的server端添加msyqld_exporter的相关配置

# vim /usr/local/prometheus/prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['xxx.xxx.xxx.xxx:9104']

7、重新加载prometheus的server端配置

curl -XPOST http://prometheus.xxxxxxxxx.com/-/reload

8、编写告警规则和告警

告警规则模板：https://github.com/prometheus/mysqld_exporter/blob/master/mysqld-mixin/rules/rules.yaml

告警模板：https://github.com/prometheus/mysqld_exporter/blob/master/mysqld-mixin/alerts/galera.yaml

告警模板：https://github.com/prometheus/mysqld_exporter/blob/master/mysqld-mixin/alerts/general.yaml

其中文件，可以自行修改，修改后放于自己服务器上。

9、配置grafana的Dashboard

grafana模板：https://grafana.com/grafana/dashboards/7362

grafana模板：github提供的json文件：https://github.com/prometheus/mysqld_exporter/blob/master/mysqld-mixin/dashboards/mysql-overview.json

有的地方需要自行修改，请根据需要自行修改，当然也有更多更适合自己的grafana，请自行查找吧。