prometheus 构建MySQL主从监控

一.服务器详情

   主机IP                        服务部署
192.168.80.2     mysql/master、mysqld_exporter node_exporter
192.168.80.3     mysql/slave、mysqld_exporter  node_exporter
192.168.80.4     Prometheus、grafana

二.配置过程

2.1 部署promethues,grafana

•安装Prometheus

[root@prometheus ~]# tar xzf prometheus-2.27.1.linux-amd64.tar.gz  -C /usr/local/ //需上传
[root@prometheus ~]# cd /usr/local/  &&  mv prometheus-2.27.1.linux-amd64 prometheus  

•Grafana安装

wget https://dl.grafana.com/oss/release/grafana-7.3.7-1.x86_64.rpm
systemctl start grafana-server.service
systemctl  enable grafana-server.service
netstat -lntp | grep grafana

•访问192.168.80.4:3000/ 添加data sources，点击添加选择prometheus即可

2.2 MySQL节点服务器部署主从复制

①master服务器修改配置文件，提权

[root@master ~]# echo -e "log_bin=master-bin\nlog_slave_updates=true\nserver_id=11" >> /etc/my.cnf 
[root@master ~]# systemctl restart mysqld.service 
mysql> grant replication slave on *.* to 'myslave'@'192.168.80.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status; 
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      603 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

②selave服务器修改配置文件,同步master

[root@slave ~]# echo -e "server_id = 22\nrelay-log=relay-log-bin\nrelay-log-index=slave-relay-bin.index" >> /etc/my.cnf 
[root@slave ~]# systemctl restart mysqld.service 
mysql> change master to master_host='192.168.80.2',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

2.3 mysql_exporter部署

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

①下载mysql_exporter并解压

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz
tar -zxvf mysqld_exporter-0.12.1.linux-amd64.tar.gz -C /usr/local/

②需要授权用户给exporter使用

mysql> create user 'exporter'@'127.0.0.1' identified by '123456';
mysql> grant process,replication client,select on *.* to 'exporter'@'127.0.0.1';
mysql> flush privileges;

③在mysqld_exporter路径下创建my.cnf，添加刚才创建的exporter用户和密码

[root@master mysqld_exporter-0.12.1.linux-amd64]# pwd 
/usr/local/mysqld_exporter-0.12.1.linux-amd64
[root@master mysqld_exporter-0.12.1.linux-amd64]# cat my.cnf  //文件需创建
[client]
user=exporter
password=123456

④添加system系统服务

[root@master ~]# 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.12.1.linux-amd64/mysqld_exporter \
--config.my-cnf /usr/local/mysqld_exporter-0.12.1.linux-amd64/my.cnf \
--collect.info_schema.processlist

Restart=on-failure

[Install]
WantedBy=multi-user.target

[root@master ~]# systemctl daemon-reload
[root@master ~]# systemctl start mysqld_exporter.service
[root@master ~]# netstat -lntup | grep "9104"

2.4 node_exporter部署

①安装node_exporter

注：两台MySQL节点都要部署

[root@master ~]# tar zxf node_exporter-1.1.2.linux-amd64.tar.gz  -C /opt   //需上传包
[root@master ~]# cd /opt 
[root@master opt]# mv node_exporter-1.1.2.linux-amd64   node_exporter

②system系统启动node_exproter

[root@master opt]# vim  /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
[root@master opt]# ss -antp | grep 9100

③修改prometheus.yml.启动prometheus

  - job_name: 'mysql-mater-slave'
    scrape_interval: 5s
    static_configs:
    - targets: ['192.168.80.2:9104','192.168.80.3:9104']
  - job_name: 'nodes'
    scrape_interval: 5s
    static_configs:
    - targets: ['192.168.80.2:9100','192.168.80.3:9100']

[root@prometheus prometheus]# ./prometheus  //启动

三.浏览器访问

①浏览器访问prometheus

②Grafana创建模板监控
添加主从主群监控模板7371：

监控系统资源模板：8919