Prometheus监控MySQL5.7
1. MySQL配置
MySQL安装配置参考我的博客其他⽂章（安装配置参考：，主从配置参考：），这⾥只讲述如何利⽤Prometheus监控MySQL5.7数据库。
1.1 新增监控帐号
在MySQL数据库中，新增Prometheus监控帐号，⽤于各类性能数据查询；
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'::1' IDENTIFIED BY 'exporter@123' WITH MAX_USER_CONNECTIONS 3;
mysql> flush privileges;
1.2 修改MySQL配置⽂件增加授权
配置myf
cat >> /usr/local/mysqld_exporter/.myf << EOF
[client]
host=localhost
port=3306
user=exporter
password=exporter@123
EOF
2. 安装监控客户端
2.1 监控客户端安装
mysql需要安装documentation在需要监控的MySQL数据库服务器上安装mysqld_exporter：
cd /usr/local/
wget github/prometheus/mysqld_exporter/releases/download/v0.12.0/mysqld_exporter-0.12.0.
# 下载不了的⾃⼰想办法哈，当然也可以留⾔给我。
tar xzf mysqld_exporter-0.12.1.
ln -sv mysqld_exporter-0.12.1.linux-amd64 mysqld_exporter
chown -R prometheus:prometheus /usr/local/mysqld_exporter/mysqld_exporter
chmod 755 /usr/local/mysqld_exporter/mysqld_exporter
2.2 配置mysqld_exporter为系统服务
cat >> /usr/lib/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=mysqld_exporter
Documentation=prometheus.io/
After=network.target
[Service]
Type=simple
User=prometheus
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --web.listen-address=0.0.0.0:9104 ---cnf=/usr/local/mysqld_exporter/.myf \
--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
EOF
下⾯是搜集主从信息的，没有配置主从可以不加这两个选项，否则会报错：msg="Error scraping for collect.slave_hosts: Error 1227: Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation" source=":171"
--collect.slave_status \
--collect.slave_hosts \
2.3 设置开机启动并启动mysqld_exporter
chown -R prometheus:prometheus /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
3. 监控指标查看
3.1 metrics监控内容
访问：可以看到mysql的指标
# HELP go_gc_duration_seconds A summary of the GC invocation durations.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 1.0977e-05
go_gc_duration_seconds{quantile="0.25"} 1.1386e-05
go_gc_duration_seconds{quantile="0.5"} 1.1929e-05
go_gc_duration_seconds{quantile="0.75"} 1.665e-05
go_gc_duration_seconds{quantile="1"} 3.3897e-05
go_gc_duration_seconds_sum 0.000189596
go_gc_duration_seconds_count 12
# HELP go_goroutines Number of goroutines that currently exist.
# TYPE go_goroutines gauge
go_goroutines 8
...此处省略若⼲⾏
# HELP mysql_up Whether the MySQL server is up.
# TYPE mysql_up gauge
mysql_up 1
# HELP mysql_version_info MySQL version and distribution.
.
..此处省略若⼲⾏
3.2 确认监控指标正常
# curl localhost:9104/metrics | grep mysql_up
% Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed
100 233k 100 233k 0 0 4322k 0 --:--:-- --:--:-- --:--:-- 4410k
# HELP mysql_up Whether the MySQL server is up.
# TYPE mysql_up gauge
mysql_up 1 #说明mysql启动，并且能正常监控到了
4. 在Prometheus中配置MySQL监控在Prometheus-Server中配置MySQL数据采集
修改prometheus配置⽂件：
在 scrape_configs下⾯添加如下配置
# vim /usr/local/l
scrape_configs:
- job_name: 'mysql_db'
static_configs:
- targets: ['10.88.8.120:9104']
labels:
instance: wikidb-10.88.8.120
group: mysql_db
- job_name: 'mysql-host'
static_configs:
- targets: ['10.88.8.120:9100']
labels:
instance: wikidb-10.88.8.120
在Prometheus界⾯可以看到mysql相关指标
5. 在Grafana中添加MySQL监控图表打开Grafana监控界⾯，新增MySQL监控图表
在弹出界⾯中输⼊：7362
然后点击Load，在新界⾯中，选择数据源，然后导⼊即可。
之后就能够在grafana中看到采集到的MySQL数据库的相关参数，以及操作系统参数（附上⼀些监控图供⼤家观赏 :) 。