Prometheus 监控 mysql
MySQL Server Exporter
用于Prometheus收集MySQL 服务器的指标的 export
支持版本：
- MySQL >= 5.6.
- MariaDB >= 10.1
开始
下载地址：
添加一个用户授权
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'pron'@'localhost' IDENTIFIED BY 'Prometh.123' WITH MAX_USER_CONNECTIONS 3;
建议设置对用户的最大连接数限制，以免在高峰期导致业务挂掉。
运行
使用环境变量启动
export DATA_SOURCE_NAME='user:password@(hostname:3306)/'
# 通过socket连接
export DATA_SOURCE_NAME=pron:Prometh.123@unix(/mysql_pxc/mysql-smy.sock)/
nohup ./mysqld_exporter &
使用配置文件 ~/.my.cnf 启动
cat <<EOF> ~/.my.cnf
[client]
user=pron
password=Prometh.123
EOF
# 启动
nohup ./mysqld_exporter --config.my-cnf=~/.my.cnf &
curl localhost:9104/metrics 即可访问到数据
......
mysql_global_status_buffer_pool_page_changes_total{operation="flushed"} 1.270503e+07
mysql_global_status_buffer_pool_page_changes_total{operation="lru_flushed"} 0
mysql_global_status_buffer_pool_page_changes_total{operation="made_not_young"} 0
mysql_global_status_buffer_pool_page_changes_total{operation="made_young"} 6436
# HELP mysql_global_status_buffer_pool_pages Innodb buffer pool pages by state.
# TYPE mysql_global_status_buffer_pool_pages gauge
mysql_global_status_buffer_pool_pages{state="data"} 562310
mysql_global_status_buffer_pool_pages{state="free"} 2.563656e+06
mysql_global_status_buffer_pool_pages{state="misc"} 19754
mysql_global_status_buffer_pool_pages{state="old"} 207408
# HELP mysql_global_status_bytes_received Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_bytes_received untyped
mysql_global_status_bytes_received 1.18030182493e+11
# HELP mysql_global_status_bytes_sent Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_bytes_sent untyped
mysql_global_status_bytes_sent 1.410236025522e+12
# HELP mysql_global_status_commands_total Total number of executed MySQL commands.
# TYPE mysql_global_status_commands_total counter
mysql_global_status_commands_total{command="admin_commands"} 2.25534e+06
......
添加到 Prometheus
- job_name: 'mysqld'
static_configs:
- targets:
- 192.168.1.2:9104
重启Prometheus即可
grafana 推荐使用模板
https://grafana.com/grafana/dashboards/7362
告警规则
mysql.yaml
groups:
- name: GaleraAlerts
rules:
- alert: MySQLGaleraNotReady
expr: mysql_global_status_wsrep_ready != 1
for: 5m
labels:
severity: warning
annotations:
description: '{{$labels.job}} on {{$labels.instance}} is not ready.'
summary: Galera cluster node not ready
- alert: MySQLGaleraOutOfSync
expr: (mysql_global_status_wsrep_local_state != 4 and mysql_global_variables_wsrep_desync
== 0)
for: 5m
labels:
severity: warning
annotations:
description: '{{$labels.job}} on {{$labels.instance}} is not in sync ({{$value}}
!= 4).'
summary: Galera cluster node out of sync
- alert: MySQLGaleraDonorFallingBehind
expr: (mysql_global_status_wsrep_local_state == 2 and mysql_global_status_wsrep_local_recv_queue
> 100)
for: 5m
labels:
severity: warning
annotations:
description: '{{$labels.job}} on {{$labels.instance}} is a donor (hotbackup)
and is falling behind (queue size {{$value}}).'
summary: xtradb cluster donor node falling behind
- alert: MySQLReplicationNotRunning
expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running
== 0
for: 2m
labels:
severity: critical
annotations:
description: Slave replication (IO or SQL) has been down for more than 2 minutes.
summary: Slave replication is not running
- alert: MySQLReplicationLag
expr: (mysql_slave_lag_seconds > 30) and on(instance) (predict_linear(mysql_slave_lag_seconds[5m],
60 * 2) > 0)
for: 1m
labels:
severity: critical
annotations:
description: The mysql slave replication has fallen behind and is not recovering
summary: MySQL slave replication is lagging
- alert: MySQLReplicationLag
expr: (mysql_heartbeat_lag_seconds > 30) and on(instance) (predict_linear(mysql_heartbeat_lag_seconds[5m],
60 * 2) > 0)
for: 1m
labels:
severity: critical
annotations:
description: The mysql slave replication has fallen behind and is not recovering
summary: MySQL slave replication is lagging
- alert: MySQLInnoDBLogWaits
expr: rate(mysql_global_status_innodb_log_waits[15m]) > 10
labels:
severity: warning
annotations:
description: The innodb logs are waiting for disk at a rate of {{$value}} /
second
summary: MySQL innodb log writes stalling
个人看法
个人觉得如果只是监控MySQL的话，用pmm2比较好，因为pmm2还有慢sql监控，送上几张图片
