【Prometheus】MySQL主从搭建，以及如何通过prometheus监控MySQL运行状态

✨✨ 欢迎大家来到景天科技苑✨✨

🎈🎈 养成好习惯，先赞后看哦~🎈🎈

🏆 作者简介：景天科技苑
🏆《头衔》：大厂架构师，华为云开发者社区专家博主，阿里云开发者社区专家博主，CSDN全栈领域优质创作者，掘金优秀博主，51CTO博客专家等。
🏆《博客》：Python全栈，前后端开发，小程序开发，人工智能，js逆向，App逆向，网络系统安全，数据分析，Django，fastapi，flask等框架，云原生k8s，Prometheus监控，linux，shell脚本等实操经验，网站搭建，数据库等分享。

所属的专栏：Prometheus监控系统零基础到进阶
景天的主页：景天科技苑

prometheus监控mysql主从

要使用Prometheus 监控 MySQL，则需要 MySQL 服务器上部署对应的mysqld_exporter。
如果需要监控多个MySQL实例，则需要在每个MySQL实例上都部署一个mysqld_exporter，
因为每个 mysqld_exporter 只能连接并抓取一个MySQL 的指标数据。

1、安装并配置mysql_master

1）安装MySQL

主从数据可都安装 jingtian03设置为主节点，jingtian04设置为从节点

yum install mysql mysql-server -y

这种方式安装的MySQL默认密码为空
直接mysql就可以进去

2）配置MySQL，并启动

搭建过程
主服务器（Master）
启用二进制日志 log-bin；
设置一个全局唯一的 server_id；
提前准备好一个有复制权限（replication slave，replication client）的用户。

从服务器（Slave）
启动中继日志 relay-log；
设置一个全局唯一的 server_id；
使用主服务器提供的有复制权限的用户连接至 Master；
启动复制线程。
主服务器相关配置（基于bin-log的主从同步）

主节点配置文件:
主节点打开log-bin

vim /etc/my.cnf.d/mysql-server.cnf

末尾添加:

log-bin=master-a-bin
server-id=1

重启mysql

查看主节点的server id

show global variables like '%server%';

登录主库查看主库的状态

mysql> show master status;

mysql8.4查看主库状态

show binary log status;

在主库创建一个专门用来复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限

新增一个用户为‘copy’

create user 'copy'@'%' identified with mysql_native_password by 'Jingtian31357!';

为该用户授权

grant REPLICATION SLAVE on *.* to 'copy'@'%' with grant option;
grant REPLICATION CLIENT on *.* to 'copy'@'%' with grant option;

刷新权限

flush privileges;

填坑 Plugin ‘mysql_native_password’ is not loaded
mysql8.4，部分场景出现以下错误提示：
Plugin ‘mysql_native_password’ is not loaded

原因是：mysql_native_password 插件(模式)在新版本中被弃用了，新模式为 caching_sha2_password，需要启用一下旧模式。

解决步骤：
1.连接到数据库后，用命令 SHOW PLUGINS 查看插件列表
可以看到插件【mysql_native_password】的状态为【DISABLED】

我们的需要将其状态改为【ACTIVE】，修改步骤在后面。

2.Windows找到配置文件 my.ini 进行修改
配置文件位置举例：C:\ProgramData\MySQL\MySQL Server 8.4\my.ini
linux系统在/etc/my.cnf

在my.cnf最后一行添加：

mysql_native_password=ON

然后就可以创建用户密码了

从数据库验证远程连接主库是否正常，建议一定要检查一下

从库用在主库新增的用户远程登录
[root@node1 ~ ]#mysql -ucopy -h10.10.0.32 -pJingtian31357!

2、从服务器相关配置

在从服务器MySQL配置文件/etc/my.cnf.d/mysql-server.cnf下新增

relay-log=relay-log
server-id=2
log-slave-updates=ON
read-only=ON

改完MySQL配置文件后重启服务

service mysqld restart

在从库上建立主从连接关系，即从库指定主库的日志信息和链接信息

登录mysql
mysql -uroot -p

连接主服务器

change master to master_host='10.10.0.32', master_port=3306, master_user='copy', master_password='Jinghao31357!', master_log_file='master-a-bin.000003', master_log_pos=157;

/MASTER_LOG_FILE对应主库通过show master status查询的file，MASTER_LOG_POS对应主库的Position/

启动slave
start slave;

查看slave的状态，在从库查询
show slave status\G (注意没有分号)

我们主要关注 IO线程和SQL线程是否正常，还有Seconds_Behind_Master 落后主库多少秒

至此，主从数据库搭建完成，然后开始着手监控工作

mysql8.4使用这个命令创建主从

查看主库查看当前位置

SHOW BINARY LOG STATUS;

连接主服务器，启动复制

change replication source to source_host='10.10.0.32', source_port=3306, source_user='copy', source_password='Jingtian31357!', source_log_file='master-a-bin.000011', source_log_pos=537;

启动slave同步进程：
mysql>start replica;
4.查看slave状态：
mysql> show replica status \G

3、监控主从数据库

1）主库和从库分别创建一个 mysql_exporter 专属的监控账户

create user 'exporter'@'localhost' identified by 'Jingtian.net' WITH MAX_USER_CONNECTIONS 3;grant process,replication client,select on *.* to 'exporter'@'localhost';

刷新权限

flush privileges;

查看所有用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

2）安装并配置mysqld_exporter

1. 访问mysqld_exporter的github地址， https://github.com/prometheus/mysqld_exporter
   下载mysqld-exporter
   

进入release，下载最新版本

2. 解压mysqld_exporter

[root@jingtian03 mysqlexport ]#tar xf mysqld_exporter-0.16.0.linux-amd64.tar.gz  -C /etc/
[root@jingtian03 mysqlexport ]#ln -s /etc/mysqld_exporter-0.16.0.linux-amd64/ /etc/mysqld_exporter

看下mysqld_exporter可以搜集哪些数据

3. 新版的mysqld_exporter进行过重大变更。不再支持 DATA_SOURCE_NAME 环境变量方式注入MySQL用户和密码信息。
   但可以使用如下两种方式：
   1、使用 --config.my-cnf=“my.cnf” 指定MySQL用户和密码的保存文件路径，来连接MySQL服务
   2、使用 --mysqld.address 地址、 --mysqld.username 用户名、最后使用MYSQLD_EXPORTER_PASSWORD 环境变量定义传递密码。
   我们使用第二种，测一下

[root@jingtian03 mysqld_exporter ]#export MYSQLD_EXPORTER_PASSWORD=Jingtian.net
[root@jingtian03 mysqld_exporter ]#/etc/mysqld_exporter/mysqld_exporter --mysqld.address=localhost:3306 --mysqld.username=exporter

可以看到运行成功，默认监听的端口是9104端口

我们访问该主机的9104端口的metrics就可以看到指标数据了
http://10.10.0.32:9104/metrics

4. 编写mysqld_exporter启动文件
   mysqld_exporter默认有一些未启动的，我们将它启动。带no的都是默认未启动的
   

[root@jingtian03 mysqld_exporter ]#cat /usr/lib/systemd/system/mysqld_exporter.service 
[Unit]
Description=mysqld_exporter
Documentation=https://github.com/prometheus/mysqld_exporter
After=network.target
[Service]
Environment='MYSQLD_EXPORTER_PASSWORD=Jingtian.net'
ExecStart=/etc/mysqld_exporter/mysqld_exporter \--mysqld.address=localhost:3306 \--mysqld.username=exporter \--web.listen-address=":9104" \--web.telemetry-path="/metrics" \--collect.info_schema.processlist \--collect.info_schema.innodb_tablespaces \--collect.info_schema.innodb_metrics \--collect.info_schema.query_response_time \--collect.info_schema.userstats \--collect.info_schema.tables \--collect.global_status \--collect.global_variables \--collect.slave_status \--collect.binlog_size \--collect.engine_innodb_status
ExecReload=/bin/kill -HUP $MAINPID
TimeoutStopSec=20s
Restart=always
[Install]
WantedBy=multi-user.target

5. 启动mysqld_exporter，并检查服务

ystemctl daemon-reload
systemctl enable mysqld_exporter.service --now

6. 测试mysqld_exporter能否获取到对应的指标
   

从库也把mysqld_exporter起来

4、配置prometheus

1）修改prometheus配置

一主一从，我们将他们俩放到同一个job_name中，如果我们像之前那样，将他俩写到同一个targets的列表中，则无法得知谁是主，谁是从。因此我们可以使用功能不同的targets 配置不同的标签label

vim /etc/prometheus/prometheus.yml- job_name: "mysqld_exporter"static_configs:- targets: ["jingtian03:9104"]labels:service: databaserole: master- targets: ["jingtian04:9104"]labels:service: databaserole: slave

2）重新加载prometheus配置文件

curl -X POST http://localhost:9090/-/reload

3）检查Prometheus的Status->Targets页面，验证 mysqld_exporter 是否已经成功纳入监控中

查看指标

5、MySQL常用指标与示例

对于MySQL数据库服务，通常会采用Google的四个黄金指标进行如下维度的监控：
延迟：MYSQL查询的响应时间。
流量：每秒查询数QPS、每秒事物数TPS。
错误：错误的查询数、连接失败次数。
饱和度：连接数饱和度、连接池饱和度。

1）MySQL健康状态指标

案例1：检查MySQL是否存活
mysql_up == 1

案例2：检查MySQL是否出现过重启，只需要判断启动时间是否小于1分钟即可。
mysql_global_status_uptime < 60

2）MySQL线程连接数相关

案例1：查询MySQL最近5分钟，当前打开的所有线程数，占总连接的比率。
计算公式：（ max_over_time(5分钟打开的线程) / 最大连接）* 100

max_over_time(mysql_global_status_threads_connected[5m]) / mysql_global_variables_max_connections * 100

案例2：查询MySQL的最近5分钟平均活跃的线程数，占总连接的比率。
计算公式：（ avg_over_time( 活跃线程数) /最大连接 * 100 ） 有可能MySQL
有大量的连接，但不是每个连接它都是活跃的。假设：配置的最大连接是
100，目前80个连接意味着会有80个线程，但活跃的就20个线程，因此打开的线程数超过了80%，但活跃线程才20%

avg_over_time(mysql_global_status_threads_running[5m]) / mysql_global_variables_max_connections * 100

3）MySQL InnoDB内存相关指标

案例：计算缓冲池使用率：计算公式：（ 内存缓冲池数据大小 / InnoDB 内存缓冲池的总大小 * 100 ）

mysql_global_status_innodb_buffer_pool_bytes_data / mysql_global_variables_innodb_buffer_pool_size * 100

4）MySQL的QPS、QTS相关指标

案例1：查询MySQL最近5分钟的，每秒的查询数（QPS）

irate(mysql_global_status_queries[5m])

案例2：查询MYSQL最近5分钟的，平均的每秒事物数（TPS）。计算公式：（ TPS = ( Commits + Rollbacks ) / Time ）

mysql_global_status_commands_total{command=“commit”} 每个成功执行的commit都表示一个事务已经完成。
mysql_global_status_commands_total{command=“rollback”} rollback 发生的情况通常表明事务没有成功完成。

rate(mysql_global_status_commands_total{command=~"commit|rollback"}[5m])

5）MySQL文件描述符相关指标

案例：计算MySQL打开的文件数，占比最大文件数的比率。计算公式：（ 打开的文件数 / 配置最大的数量 * 100 ）

mysql_global_status_open_files / mysql_global_variables_open_files_limit *100

6）MySQL主从复制相关指标

MySQL8.4的指标
从数据库IO线程
mysql_slave_status_replica_io_running

从数据库sql线程
mysql_slave_status_replica_sql_running

从库落后于主库秒数
mysql_slave_status_seconds_behind_source

从服务器上人为配置的SQL延迟秒数，通常用于故障转移或其他特定的复制策略。

8.4设置从库延迟

change replication source to source_delay=30;

查看延迟生效

show replica status\G

案例1：确保MySQL从服务器的IO线程和SQL线程都是正常的。

mysql_slave_status_slave_io_running ==0 or and mysql_slave_status_slave_sql_running == 0

#mysql8.4版本指标

mysql_slave_status_replica_io_running == 0 or mysql_slave_status_replica_sql_running ==0 

案例2：从服务落后主服务器时间不能超过5s，
计算公式：（ 实际落后时间=Seconds_Behind_Master - SQL_DELAY）
mysql_slave_status_seconds_behind_master - mysql_slave_status_sql_delay > 5
#mysql8.4版本指标
mysql_slave_status_seconds_behind_source - mysql_slave_status_sql_delay > 5

6、MySQL告警规则文件

1）编写MySQL告警规则文件

8.0.22之前版本

groups:
- name: mysql告警规则rules:- alert: MySQL主库实例宕机expr: mysql_up{role="master"} == 0for: 0mlabels:severity: criticalannotations:summary: "MySQL实例宕机, 实例: {{ $labels.instance }}"description: "服务:{{ $labels.service }} 角色: {{ $labels.role }} 已经宕机。"- alert: MySQL从库实例宕机expr: mysql_up{role="slave"} == 0for: 0mlabels:severity: criticalannotations:summary: "MySQL实例宕机, 实例: {{ $labels.instance }}"description: "服务:{{ $labels.service }} 角色: {{ $labels.role }} 已经宕机。"- alert: MySQL实例重启expr: sum(mysql_global_status_uptime) by (instance,job,service,role)< 60for: 0mlabels:severity: warningannotations:summary: "MySQL实例重启, 实例 {{ $labels.instance }}"description: "服务:{{ $labels.service }} 角色: {{ $labels.role }} 运行时间小于60s。当前值 {{ $value }}s"- alert: MySQL连接数使用率超过80%expr: max_over_time(mysql_global_status_threads_connected[5m]) / mysql_global_variables_max_connections * 100 > 80for: 1mlabels:severity: warningannotations:summary: "MySQL连接数过高, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的连接数在过去5分钟内超过了最大连接数的80%, 当前值 {{ $value }}%。"- alert: MySQL活跃线程数高expr: avg_over_time(mysql_global_status_threads_running[5m]) / mysql_global_variables_max_connections * 100 > 60for: 1mlabels:severity: warningannotations:summary: "MySQL活跃线程数过高, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的活跃线程数在过去5分钟内持续超过了最大连接数的60%, 当前值 {{ $value }}%。"- alert: MySQL查询率(QPS)过高expr: irate(mysql_global_status_queries[5m]) > 1000for: 1mlabels:severity: warningannotations:summary: "MySQL查询率(QPS)超标, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的查询率(QPS)在过去5分钟内超过1000, 当前值 {{ $value }}。"- alert: MySQL事务率(TPS)过高expr: sum(rate(mysql_global_status_commands_total{command=~"(commit|rollback)"}[5m])) without (command) > 100for: 1mlabels:severity: warningannotations:summary: "MySQL事务率(TPS)超标, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的事务率(TPS)在过去5分钟内超过100, 当前值 {{ $value }}。"- alert: MySQL文件描述符使用率过高expr: mysql_global_status_open_files / mysql_global_variables_open_files_limit * 100 > 80for: 5mlabels:severity: warningannotations:summary: "MySQL文件描述符使用率过高, 实例 {{ $labels.instance }},服务:{{ $labels.service }} ⻆⾊: {{ $labels.role }}"description: "该实例MySQL的文件描述符使用率超过80%，当前值 {{ $value }}%可能需要增加文件描述符限制。"- alert: Mysql从库IO线程未运行expr: mysql_slave_status_slave_io_running == 0for: 1mlabels:severity: warningannotations:summary: "MySQL从库IO线程已停止, 实例 {{ $labels.instance }}"description: "该MySQL实例IO线程已停止，当前值 {{ $value }}"- alert: Mysql从库SQL线程未运行expr: mysql_slave_status_slave_sql_running == 0for: 1mlabels:severity: warningannotations:summary: "MySQL从库SQL线程已停止, 实例 {{ $labels.instance }}"description: "该MySQL实例SQL线程已停止，当前值 {{ $value }}"- alert: Mysql从库复制延迟过高expr: mysql_slave_status_seconds_behind_master - mysql_slave_status_sql_delay > 5for: 1mlabels:severity: warningannotations:summary: "MySQL从库复制延迟过高, 实例 {{ $labels.instance }}"description: "该实例MySQL的复制延迟超过5s，当前值 {{ $value }}s"

MySQL8.0.22之后版本

groups:
- name: mysql告警规则rules:- alert: MySQL主库实例宕机expr: mysql_up{role="master"} == 0for: 0mlabels:severity: criticalannotations:summary: "MySQL实例宕机, 实例: {{ $labels.instance }}"description: "服务:{{ $labels.service }} 角色: {{ $labels.role }} 已经宕机。"- alert: MySQL从库实例宕机expr: mysql_up{role="slave"} == 0for: 0mlabels:severity: criticalannotations:summary: "MySQL实例宕机, 实例: {{ $labels.instance }}"description: "服务:{{ $labels.service }} 角色: {{ $labels.role }} 已经宕机。"- alert: MySQL实例重启expr: sum(mysql_global_status_uptime) by (instance,job,service,role)< 60for: 0mlabels:severity: warningannotations:summary: "MySQL实例重启, 实例 {{ $labels.instance }}"description: "服务:{{ $labels.service }} 角色: {{ $labels.role }} 运行时间小于60s。当前值 {{ $value }}s"- alert: MySQL连接数使用率超过80%expr: max_over_time(mysql_global_status_threads_connected[5m]) / mysql_global_variables_max_connections * 100 > 80for: 1mlabels:severity: warningannotations:summary: "MySQL连接数过高, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的连接数在过去5分钟内超过了最大连接数的80%, 当前值 {{ $value }}%。"- alert: MySQL活跃线程数高expr: avg_over_time(mysql_global_status_threads_running[5m]) / mysql_global_variables_max_connections * 100 > 60for: 1mlabels:severity: warningannotations:summary: "MySQL活跃线程数过高, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的活跃线程数在过去5分钟内持续超过了最大连接数的60%, 当前值 {{ $value }}%。"- alert: MySQL查询率(QPS)过高expr: irate(mysql_global_status_queries[5m]) > 1000for: 1mlabels:severity: warningannotations:summary: "MySQL查询率(QPS)超标, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的查询率(QPS)在过去5分钟内超过1000, 当前值 {{ $value }}。"- alert: MySQL事务率(TPS)过高expr: sum(rate(mysql_global_status_commands_total{command=~"(commit|rollback)"}[5m])) without (command) > 100for: 1mlabels:severity: warningannotations:summary: "MySQL事务率(TPS)超标, 实例 {{ $labels.instance }},服务:{{ $labels.service }} 角色: {{ $labels.role }}"description: "该实例MySQL的事务率(TPS)在过去5分钟内超过100, 当前值 {{ $value }}。"- alert: MySQL文件描述符使用率过高expr: mysql_global_status_open_files / mysql_global_variables_open_files_limit * 100 > 80for: 5mlabels:severity: warningannotations:summary: "MySQL文件描述符使用率过高, 实例 {{ $labels.instance }},服务:{{ $labels.service }} ⻆⾊: {{ $labels.role }}"description: "该实例MySQL的文件描述符使用率超过80%，当前值 {{ $value }}%可能需要增加文件描述符限制。"- alert: Mysql从库IO线程未运行expr: mysql_slave_status_replica_io_running == 0for: 1mlabels:severity: warningannotations:summary: "MySQL从库IO线程已停止, 实例 {{ $labels.instance }}"description: "该MySQL实例IO线程已停止，当前值 {{ $value }}"- alert: Mysql从库SQL线程未运行expr: mysql_slave_status_replica_sql_running == 0for: 1mlabels:severity: warningannotations:summary: "MySQL从库SQL线程已停止, 实例 {{ $labels.instance }}"description: "该MySQL实例SQL线程已停止，当前值 {{ $value }}"- alert: Mysql从库复制延迟过高expr: mysql_slave_status_seconds_behind_source - mysql_slave_status_sql_delay > 5for: 1mlabels:severity: warningannotations:summary: "MySQL从库复制延迟过高, 实例 {{ $labels.instance }}"description: "该实例MySQL的复制延迟超过5s，当前值 {{ $value }}s"

2）检查告警规则

7、导入mysql图形

导入一个MySQL的Grafana模板。Dashboard ID为 7362、9625 ，而监控MySQL主从的Dashboard可以使用 11323