grafana promsql如何使用条件

由于业务需求，MySQL主从同步经常发生故障，因此研究了Prometheus来监控和报警MySQL主从同步的状态监控报警。

相关组件

Prometheus Server: 用于收集和存储时间序列数据。
Exporters: 用于暴露已有的第三方服务的 metrics 给 Prometheus。
Alertmanager: 从 Prometheus server 端接收到 alerts 后，会进行去除重复数据，分组，并路由到对收的接受方式，发出报警。常见的接收方式有：电子邮件，pagerduty，OpsGenie, webhook 等。

 

环境:

IP	角色	服务部署
192.168.26.101	Prometheus	Prometheus-Server ,Grafana
192.168.26.102	MySQL-master	mysql_exporter ,node_exporter
192.168.26.103	MySQL-slave	mysql_exporter,node_exporter

一，安装promethues,grafana

 

进入官网 ：  https://prometheus.io/download/

进入下载页面后选择Operating system为Linux，Architecture为amd64后选择所需组件下载

 

1.1安装promethues

[root@xkf ~]# systemctl stop firewalld.service 
[root@xkf ~]# setenforce 0
root@xkf ~]# rz -E
rz waiting to receive.
[root@xkf ~]# mkdir -p /usr/share/prome
[root@xkf ~]# tar -xzvf prometheus-2.43.0.linux-amd64.tar.gz -C /usr/share/prome/

1.2安装grafana

[root@xkf ~]# wget https://dl.grafana.com/enterprise/release/grafana-enterprise-7.4.5-1.x86_64.rpm
[root@xkf ~]# yum install grafana-enterprise-7.4.5-1.x86_64.rpm
[root@xkf ~]# systemctl start grafana-server
[root@xkf ~]# systemctl enable grafana-server
[root@xkf ~]# netstat -lntp | grep grafana
tcp6       0      0 :::3000                 :::*                    LISTEN      2623/grafana-server

二，实现MySQL主从复制

2.1主节点master配置

[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=102    #为当前节点设置一个全局惟一的ID号
log-bin=/data/mysql/logbin/mysql-bin  #将新产生的二进制文件放到此目录下便于查找
[root@master ~]# mkdir -p /data/mysql/logbin 
[root@master ~]# chown -R mysql.mysql /data/mysql  #修改目录的所有者，所属组为mysql
[root@master ~]# yum -y install psmisc
[root@master ~]# killall mysqld
[root@master ~]# systemctl restart mysqld
[root@master ~]# ll /data/mysql/logbin/
total 8
-rw-r-----. 1 mysql mysql 157 Mar 25 19:50 mysql-bin.000001
-rw-r-----. 1 mysql mysql  36 Mar 25 19:50 mysql-bin.index
##创建一个有复制权限的用户账号mysql> 
select @@server_id;+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
mysql>  show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       157 | No        |
+------------------+-----------+-----------+
mysql> create user xkf@'192.168.26.%' identified WITH mysql_native_password by '1234';
mysql> grant replication slave on *.* to xkf@'192.168.26.%';

2.2从节点slave配置

[root@slave ~]# vim /etc/my.cnf
server_id=103
read-only
[root@slave ~]# killall mysqld 
[root@slave ~]# systemctl restart mysqld 
##使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> change master to
    -> master_host='192.168.26.102',
    -> master_user='xkf',
    -> master_password='1234',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=157;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
##开启同步
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
##查看MySQL的复制状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.26.102
                  Master_User: xkf
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 680
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 849
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
yes表示主从复制部署成功

三，Prometheus监控MySQL主从

3.1部署mysql_exporter

注：master和slave数据库都要部署mysql_exporter

注意###第2步.需要授权用户给exporter使用，在master执行即可，其他步骤slave节点依然要全部执行

1.下载mysql_exporter并解压
[root@master ~]# rz -E
rz waiting to receive.
[root@master ~]# ls
\  anaconda-ks.cfg  mysqld_exporter-0.14.0.linux-amd64.tar.gz
[root@master ~]# tar -zxvf mysqld_exporter-0.14.0.linux-amd64.tar.gz -C /usr/local/
2.创建MySQL监控用户
mysql>  create user exporter@'192.168.26.%' identified  by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant  process,replication client,select on *.* to 'exporter'@'192.168.26.%';
Query OK, 0 rows affected (0.00 sec)
3.配置mysql连接信息
在mysqld_exporter路径下创建my.cnf，添加刚才创建的exporter用户和密码
[root@master ~]# cd /usr/local/mysqld_exporter-0.14.0.linux-amd64/
[root@master mysqld_exporter-0.14.0.linux-amd64]# vim my.cnf
[client]
user=exporter 
password=123456
4.添加system系统服务
[root@master systemd]# vim /usr/lib/systemd/system/mysqld_exporter.service
[Unit]
Description=mysqld_exporter
After=network.target

[Service]
User=root
Type=simple
ExecStart=/usr/local/mysqld_exporter-0.14.0.linux-amd64/mysqld_exporter \
--config.my-cnf /usr/local/mysqld_exporter-0.14.0.linux-amd64/my.cnf \
--collect.info_schema.processlist

Restart=on-failure

[Install]
WantedBy=multi-user.target

FLUSH PRIVILEGES;

node_exporter

注：两台MySQL节点都要部署，命令同样执行；

1.下载mysql_exporter并解压
[root@master systemd]# cd
[root@master ~]# rz -E
rz waiting to receive.
[root@master ~]# tar zxf node_exporter-1.5.0.linux-amd64.tar.gz -C /opt
[root@master ~]# cd /opt/
[root@master opt]# mv node_exporter-1.5.0.linux-amd64/ node_exporter
2.system系统启动node_exproter
[root@master opt]# vim  /usr/lib/systemd/system/node_exporter.service
[root@master opt]# cat /usr/lib/systemd/system/node_exporter.service
[Unit]
  Description=node_exporter
  Documentation=https://prometheus.io/
  After=network.target

  [Service]
  Type=simple
  User=root
  ExecStart=/opt/node_exporter/node_exporter
  Restart=on-failure

  [Install]
  WantedBy=multi-user.target
[root@master opt]# systemctl daemon-reload
[root@master opt]# systemctl start node_exporter
[root@master opt]# systemctl enable node_exporter
Created symlink from /etc/systemd/system/multi-user.target.wants/node_exporter.service to /usr/lib/systemd/system/node_exporter.service.
[root@master opt]# ss -antp | grep 9100
LISTEN     0      128       [::]:9100                  [::]:*                   users:(("node_exporter",pid=8600,fd=3))
[root@master opt]# 
[root@slave opt]# ss -antp | grep 9100
LISTEN     0      128       [::]:9100                  [::]:*                   users:(("node_exporter",pid=10873,fd=3)

.prometheus服务器配置

[root@xkf prometheus-2.43.0.linux-amd64]# pwd
/usr/share/prome/prometheus-2.43.0.linux-amd64
[root@xkf prometheus-2.43.0.linux-amd64]# vim prometheus.yml 
- job_name: 'mysql-mater-slave'
    scrape_interval: 5s
    static_configs:
    - targets: ['192.168.26.102:9104','192.168.26.103:9104']
  - job_name: 'nodes'
    scrape_interval: 5s
    static_configs:
    - targets: ['192.168.26.102:9100','192.168.26.103:9100']

启动

[root@xkf ~]# cd /usr/share/prome/prometheus-2.43.0.linux-amd64/
[root@xkf prometheus-2.43.0.linux-amd64]# nohup ./prometheus &
[1] 6556
[root@xkf prometheus-2.43.0.linux-amd64]# nohup: ignoring input and appending output to ‘nohup.out’
#检查nohup.out日志文件，如果有以下信息则说明启动成功msg="Server is ready to receive web requests."

 

3.4浏览器访问验证

  ##http://192.168.26.101:9090/targets?search=

 

四，Grafana可视化监控

4.1在浏览器中访问grafana

##http://ip:3000（默认账号密码为admin）

 

4.2Grafana创建模板监控

4.2.1创建控系统资源模板：8919

 ##在仪表板Dashboards的底部选+import 填id号8919

4.2.2创建监控MySQL主从数据库模板

##注意，监控的这俩数据值是从库的，因此选择从库是绿色的，选择主库是红色，这是正常的

 

 

注意：若监控MySQL主从显示not data 没有数据，先排除mysql_exporter部署是否有误，若是没问题则

点edit 进入      将host改为node  刷新