04 . Prometheus(联邦集群)监控MySQL

List

CentOS7.3
alertmanager-0.19.0.linux-amd64.tar.gz
mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar
mysqld_exporter-0.12.1.linux-amd64.tar.gz
prometheus-2.13.0.linux-amd64.tar.gz

节点名	IP	软件版本	说明
prometheus-master	172.19.0.55	alertmanager-0.19/prometheus-2.13.0	Prometheus联邦集群Master节点
prometheus-slave	172.19.0.56\192.168.50.10	prometheus-2.13	Prometheus联邦集群slave节点(proxy)
prometheus_mysql	192.168.50.5	mysqld_exporter-0.10/mysql-5.7.23.rpm-bundle.tar	Prometheus的Mysql_Test节点

初始化系统环境

1.初始化
init_security() {
systemctl stop firewalld
systemctl disable firewalld &>/dev/null
setenforce 0
sed -i '/^SELINUX=/ s/enforcing/disabled/'  /etc/selinux/config
sed -i '/^GSSAPIAu/ s/yes/no/' /etc/ssh/sshd_config
sed -i '/^#UseDNS/ {s/^#//;s/yes/no/}' /etc/ssh/sshd_config
systemctl enable sshd crond &> /dev/null
rpm -e postfix --nodeps
echo -e "\033[32m [安全配置] ==> OK \033[0m"
}
init_security

init_yumsource() {
if [ ! -d /etc/yum.repos.d/backup ];then
    mkdir /etc/yum.repos.d/backup
fi
mv /etc/yum.repos.d/* /etc/yum.repos.d/backup 2>/dev/null
if ! ping -c2 www.baidu.com &>/dev/null    
then
    echo "您无法上外网，不能配置yum源"
    exit    
fi
    curl -o /etc/yum.repos.d/163.repo http://mirrors.163.com/.help/CentOS7-Base-163.repo &>/dev/null
    curl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo &>/dev/null
    yum clean all
    timedatectl set-timezone Asia/Shanghai
    echo "nameserver 114.114.114.114" > /etc/resolv.conf
    echo "nameserver 8.8.8.8" >> /etc/resolv.conf
    chattr +i /etc/resolv.conf
    yum -y install ntpdate
    ntpdate -b  ntp1.aliyun.com        # 对时很重要，避免zookeeper因为时间不准找不到主机
    echo -e "\033[32m [YUM　Source] ==> OK \033[0m"
}
init_yumsource
# 配置主机名解析
tail -2 /etc/hosts
172.19.0.55 prometheus-master
172.19.0.56 prometheus-slave

部署Mysql及MySQLD Exporter(Prometheus_Mysql上安装)

# 为了简化测试环境复杂度，这里使用Docker Compose定义并启动MySQL以及MySQLD Exporter：
cat docker-compose.yml 
version: '3'
services:
  mysql:
    image: daocloud.io/library/mysql:5.7
    ports:
      - "3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=XUANji.20
      - MYSQL_DATABASE=database
  mysqlexporter:
    image: prom/mysqld-exporter
    ports:
      - "9104:9104"
    environment:
      - DATA_SOURCE_NAME=root:XUANji.20@(mysql:3306)/database
# 这里通过环境变量DATA_SOURCE_NAME方式定义监控目标,使用Docker Compose启动测试用的Mysql示例以及MySQLD Exporter

# docker-compose up -d

# 启动完成后，可以通过以下命令登录到Mysql容器当中，并执行Mysql相关的指令.
docker exec -it cb2017 mysql -uroot -pXUANji.20
mysql>

如果不是容器里面的Mysql，需要下载mysqld_exporter的tar包，并且安装好Mysql服务

安装Mysql

init_mysql() {
rpm -e mariadb-libs --nodeps
rm -rf /var/lib/mysql
rm -rf /etc/my.cnf
tar xvf /root/mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar -C /usr/local/
cd /usr/local
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm \
mysql-community-client-5.7.23-1.el7.x86_64.rpm \
mysql-community-common-5.7.23-1.el7.x86_64.rpm \
mysql-community-libs-5.7.23-1.el7.x86_64.rpm 
rm -rf mysql-community-* 
}
changepass() {
sed -i '/\[mysqld]/ a skip-grant-tables' /etc/my.cnf
systemctl restart mysqld
mysql <<EOF
        update mysql.user set authentication_string='' where user='root' and Host='localhost';
        flush privileges;
EOF
sed -i '/skip-grant/d' /etc/my.cnf
systemctl restart mysqld
yum -y install expect 
expect <<-EOF
spawn  mysqladmin -uroot -p password "ZHOUjian.20"
        expect {
                "password" { send "\r"  }
}
        expect eof
EOF
systemctl restart mysqld
}
main() {
init_mysql
changepass
}
main

安装mysqld_exporter

wget \   
https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz  
tar xvf mysqld_exporter-0.10.0.linux-amd64.tar.gz -C /usr/local/  
cd /usr/local/  
mv mysqld_exporter-0.10.0.linux-amd64/ /usr/local/mysqld_exporter  
  
# 加载mysqld_exporter 添加配置文件（需要MySQL授权用户）   
# mysqld_exporter需要连接到MySQL,需要授权   
grant replication client, process on *.* to prometheus@"localhost" identified by "ZHOUjian.20";   
grant select on performance_schema.* to prometheus@"localhost";  
cat .my.cnf  
[client]  
user=prometheus  
password=ZHOUjian.20  
  
systemctl restart mysqld
nohup ./mysqld_exporter --config.my-cnf=.my.cnf &

我们可以通过那台Mysql服务器IP去浏览器访问

可以再/metrics查看mysql_up指标判断当前MySQLD Exporter是否正常连接到了MySQL实例，当指标值为1时表示能够正常获取监控数据:

修改Prometheus配置文件/etc/prometheus/prometheus.yml,增加对MySQLD Exporter实例的采集任务配置.

  - job_name: 'mysqld'
    static_configs:
    - targets: ['172.19.0.27:9104']
systemctl restart prometheus

为了确保数据库的稳定运行，通常会关注一下四个与性能和资源利用率相关的指标：查询吞吐量、连接情况、缓冲池使用情况以及查询执行性能等。

监控数据库吞吐量

对于数据库而言，最重要的工作就是实现对数据的增、删、改、查。为了衡量数据库服务器当前的吞吐量变化情况。在MySQL内部通过一个名为Questions的计数器，当客户端发送一个查询语句后，其值就会+1。可以通过以下MySQL指令查询Questions等服务器状态变量的值:

show global status like "Questions";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 545   |
+---------------+-------+

MySQLD Exporter中返回的样本数据中通过mysql_global_status_questions反映当前Questions计数器的大小.

通过以下PromQL可以查看当前MySQL实例查询速率的变化情况，查询数量的突变往往暗示着可能发生了某些严重的问题，因此用于用户应该关注并且设置响应的告警规则，以及时获取该指标的变化情况：

rate(mysql_global_status_questions[2m])

一般还可以监控读操作和写操作的执行情况进行判断。通过MySQL全局状态中的Com_select可以查询到当前服务器执行查询语句的总次数：相应的，也可以通过Com_insert、Com_update以及Com_delete的总量衡量当前服务器写操作的总次数，例如，可以通过以下指令查询当前MySQL实例insert语句的执行次数总量：

show global status like "Com_insert";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 0     |
+---------------+-------+

从MySQLD Exporter的/metrics返回的监控样本中，可以通过global_status_commands_total获取当前实例各类指令执行的次数：

用户可以通过以下PromQL查看当前MySQL实例写操作速率的变化情况.
sum(rate(mysql_global_status_commands_total{command=~"insert|update|delete"}[2m])) without (command)

为了方便看出效果建议去mysql创建一个库一个表，使用for循环不断的插入

docker exec -it  cb2017 /bin/bash
create database test;
use test
create table student(id int,name varchar(50),sex enum('male','emale'),age int);
insert into student values(1,'tom','male',12),(2,'jack','male',13),(3,'alice','male',14);
for i in {1..1000}; do mysql -uroot -pZHOUjian.20 -e "use test;insert into student values(1,'tom','male',12),(2,'jack','male',13),(3,'alice','male',14);" ;done

连接情况

在MySQL中通过全局设置max_connections限制了当前服务器允许的最大客户端连接数量。一旦可用连接数被用尽，新的客户端连接都会被直接拒绝。因此当监控MySQL运行状态时，需要时刻关注MySQL服务器的连接情况。用户可以通过以下指令查看当前MySQL服务的max_connections配置

 show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)
# Mysql默认最大连接数为151，临时调整最大连接数，可以通过以下指令进行设置.

set global max_connections = 200;

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+

# 如果想永久化设置，则需要通过MySQL配置文件my.cnf，添加以下内容.
max_connections = 200

通过Global Status中的Threads_connected、Aborted_connects、Connection_errors_max_connections以及Threads_running可以查看当前MySQL实例的连接情况。

例如，通过以下指令可以直接查看当前Mysql实例的连接数

show global status like "Threads_connected";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 1     |
+-------------------+-------+

当所有可用连接都被占用时，如果一个客户端尝试连接至MySQL，会出现“Too many connections(连接数过多)”错误，同时Connection_errors_max_connections的值也会增加。为了防止出现此类情况，你应该监控可用连接的数量，并确保其值保持在max_connections限制以内。同时如果Aborted_connects的数量不断增加时，说明客户端尝试连接到MySQL都失败了。此时可以通过Connection_errors_max_connections以及Connection_errors_internal分析连接失败的问题原因。

下面列举了与MySQL连接相关的监控指标

* mysql_global_variables_max_connections： 允许的最大连接数；
* mysql_global_status_threads_connected： 当前开放的连接；
* mysql_global_status_threads_running：当前开放的连接；
* mysql_global_status_aborted_connects：当前开放的连接；
* mysql_global_status_connection_errors_total{error="max_connections"}：由于超出最大连接数导致的错误；
* mysql_global_status_connection_errors_total{error="internal"}：由于系统内部导致的错误；

通过PromQL查询当前剩余的可用连接数.

mysql_global_variables_max_connections - mysql_global_status_threads_connected

使用PromQL查询当前MySQL实例连接拒绝数

mysql_global_status_aborted_connects

监控缓冲池使用情况

MySQL默认的存储引擎InnoDB使用了一片称为缓冲池的内存区域，用于缓存数据表以及索引的数据。当缓冲池的资源使用超出限制后，可能会导致数据库性能的下降，同时很多查询命令会直接在磁盘中执行，导致磁盘I/O不断攀升。因此，应该关注MySQL缓冲池的资源使用情况，并且在合理的时间扩大缓冲池的大小可以优化数据库的性能。

Innodb_buffer_pool_pages_total反映了当前缓冲池中的内存页的总页数。可以通过以下指令查看:

show global status like "Innodb_buffer_pool_pages_total";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 8191  |
+--------------------------------+-------+

MySQLD Exporter通过以下指标返回缓冲池中各类内存页的数量:

a# 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"} 516
mysql_global_status_buffer_pool_pages{state="dirty"} 0
mysql_global_status_buffer_pool_pages{state="free"} 7675
mysql_global_status_buffer_pool_pages{state="misc"} 0

Innodb_buffer_pool_read_requests记录了正常从缓冲池读取数据的请求数量。可以通过以下指令查看:

show global status like "Innodb_buffer_pool_read_requests";
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_read_requests | 1481  |
+----------------------------------+-------+

MySQLD Exporter通过以下指标返回缓冲池中Innodb_buffer_pool_read_requests的值:

# HELP mysql_global_status_innodb_buffer_pool_read_requests Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_innodb_buffer_pool_read_requests untyped
mysql_global_status_innodb_buffer_pool_read_requests 1481

当缓冲池无法满足时，MySQL只能从磁盘中读取数据。Innodb_buffer_pool_reads即记录了从磁盘读取数据的请求数量。通常来说从内存中读取数据的速度要比从磁盘中读取快很多，因此，如果Innodb_buffer_pool_reads的值开始增加，可能意味着数据库的性能有问题。可以通过以下只能查看Innodb_buffer_pool_reads的数量.

show global status like "Innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 408   |
+--------------------------+-------+

在MySQLD Exporter中可以通过以下指标查看Innodb_buffer_pool_reads的数量.

通过以下PromQL可以得到各个MySQL实例的缓冲池利用率。一般来说还需要结合Innodb_buffer_pool_reads的增长率情况来结合判断缓冲池大小是否合理：

(sum(mysql_global_status_buffer_pool_pages) by (instance) - sum(mysql_global_status_buffer_pool_pages{state="free"}) by (instance)) / sum(mysql_global_status_buffer_pool_pages) by (instance)

也可以通过以下PromQL计算2分钟内磁盘读取请求次数的增长率的变化情况：

rate(mysql_global_status_innodb_buffer_pool_reads[2m])

查询性能

MySQL还提供了一个Slow_queries的计数器，当查询的执行时间超过long_query_time的值后，计数器就会+1，其默认值为10秒，可以通过以下指令在MySQL中查询当前long_query_time的设置：

show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

通过以下指令可以查看当前Mysql实例中Slow_queries的数量:

show global status like "slow_queries";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+

MySQLD Exporter返回的样本数据中，通过以下指标展示当前的Slow_queries的值:

# HELP mysql_global_status_slow_queries Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_slow_queries untyped
mysql_global_status_slow_queries 0

通过监控Slow_queries的增长率，可以反映出当前MySQL服务器的性能状态，可以通过以下PromQL查询Slow_queries的增长情况

rate(mysql_global_status_slow_queries[2m])

在MySQL中还可以通过安装response time插件，从而支持记录查询时间区间的统计信息。启动该功能后MySQLD Exporter也会自动获取到相关数据，从而可以细化MySQL查询响应时间的分布情况。感兴趣的读者可以自行尝试。

安装Prometheus服务(联邦集群的两台Prometheus服务都要安装)

下载Prometheus二进制安装包并配置启动

wget https://github.com/prometheus/prometheus/releases/download/v2.13.0/prometheus-2.13.0.linux-amd64.tar.gz

tar xvf prometheus-2.13.0.linux-amd64.tar.gz -C /usr/local/
cd /usr/local/
ln -s prometheus-2.13.0.linux-amd64/ prometheus

# 为了安全，我们使用普通用户来启动prometheus服务
# 作为一个时序型数据库产品，prometheus的数据默认会存放在应用目录下，我们需修改为/data/prometheus下 

useradd -s /sbin/nologin -M prometheus 
mkdir -p /data/prometheus 
chown -R prometheus:prometheus /usr/local/prometheus 
chown -R prometheus:prometheus /data/prometheus/

vim /usr/lib/systemd/system/prometheus.service
[Unit]
Description=Prometheus
Documentation=https://prometheus.io/
After=network.target
[Service]
Type=simple
User=prometheus
ExecStart=/usr/local/prometheus/prometheus --config.file=/usr/local/prometheus/prometheus.yml \
--storage.tsdb.path=/data/prometheus
Restart=on-failure
[Install]
WantedBy=multi-user.target

#在此配置文件里面，定义了启动的命令，定义了数据存储在/data/prometheus路径下
#否则默认会在prometheus二进制目录的data下.
systemctl start prometheus
systemctl status prometheus
systemctl enable prometheus

安装alertmanager报警插件

(联邦集群的主PrometheusServer安装）

alertmanager是用来接收prometheus发出的告警，然后按照配置文件的要求，将告警用对应的方式发送出去。将告警集中到alertmanager，可以对告警进行更细致的管理。
alertmanager的安装和启动

wget https://github.com/prometheus/alertmanager/releases/download/v0.19.0/alertmanager-0.19.0.linux-amd64.tar.gz

# 从官网下载好包后我们可以对他进行解压
tar xvf alertmanager-0.19.0.linux-amd64.tar.gz -C /usr/local/
cd /usr/local/alertmanager-0.19.0.linux-amd64/
./alertmanager &>/dev/null  &
# 接下来我们访问http://172.19.0.51:9093/#/alerts，就可以打开alertmanager的页面,

# 接下来我们编辑alertmanager.yml文件，配置邮箱账号授权码相关配置
cat alertmanager.yml
global:		# 全局配置，报警策略，报警渠道等.
  smtp_smarthost: 'smtp.163.com:25'
  smtp_from: '18621048481@163.com'
  smtp_auth_username: '18621048481@163.com'
  smtp_auth_password:  'ZHOUjian22' 		# 邮箱授权码
  smtp_require_tls: false 

route:		# 分发策略
  group_by: ['alertname'] 
  group_wait: 30s
  group_interval: 5m
  repeat_interval: 5m
  receiver: 'email'

receivers:		# 接受者
- name: 'email'
  email_configs:
  - to: '18621048481@163.com'		# 接受的邮箱
    send_resolved: true
inhibit_rules:		# 抑制策略，当存在另一组匹配的警报，抑制规则将禁止与另一组匹配的警报.
  - source_match:
      serverity: 'critical'

接下来我们重启一下服务使配置生效

将alertmanager加入到systemd服务

cat /usr/lib/systemd/system/alertmanager.service 
[Unit]
Description=Alertmanager
After=network.target

[Service]
Type=simple
User=prometheus
ExecStart=//usr/local/alertmanager-0.19.0.linux-amd64/alertmanager \
  --config.file=/usr/local/alertmanager-0.19.0.linux-amd64/alertmanager.yml \
  --storage.path=/usr/local/alertmanager-0.19.0.linux-amd64/data
Restart=on-failure

[Install]
WantedBy=multi-user.target

systemctl restart alertmanager
# 如果服务启动失败报错，可以先systemctl daemon-reload，再重启

将mysql这台机器加入到prometheus其中一个节点，以这个节点为proxy传向联邦集群的一个主PormetheusServer

将mysql服务监控项加入到联邦集群的slave(proxy)

scrape_configs:
  - job_name: 'prometheus'

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

  - job_name: 'mysqld'
    static_configs:
    - targets: ['172.19.0.56:9104']

  - job_name: 'mysql'
    static_configs:
    - targets: ['172.19.0.27:9104']
systemctl restart prometheus

修改联邦集群的master去拉取slave集群上的监控项.

vim prometheus.yml
    - "first_rules.yml"

scrape_configs:
  - job_name: 'mysqld'
    scrape_interval: 15s
    honor_labels: true
    metrics_path: '/federate'

    params:
      'match[]':
        - '{job="prometheus"}'
        - '{__name__=~"mysql_up.*"}'
        - '{__name__=~"mysql_up.*"}'

    static_configs:
      - targets:
        - 'prometheus-slave2:9090'

  - job_name: 'mysql'
    scrape_interval: 15s
    honor_labels: true
    metrics_path: '/federate'

    params:
      'match[]':
        - '{job="prometheus"}'
        - '{__name__=~"mysql_up.*"}'
        - '{__name__=~"mysql_up.*"}'

    static_configs:
      - targets:
        - 'prometheus-slave2:9090'

自定义报警规则，然后宕掉那台机器引起报警

vim /usr/local/prometheus/first_rules.yml
groups:
- name: mysql_up
  rules:
  - alert: mysql_up
    expr: up == 0
    for: 15s
    labels:
      severity: 1
      team: Prometheus_mysql
    annotations:
      summary: "{{$labels.instance}}Instance has been down for more than 5 minutes"
systemctl restart prometheus alertmanager