prometheus 构建MySQL主从监控

• 一.服务器详情
• 二.配置过程
• • 2.1 部署promethues,grafana
  • 2.2 MySQL节点服务器部署主从复制
  • 2.3 mysql_exporter部署
  • 2.4 node_exporter部署
• 三.浏览器访问

一.服务器详情

主机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

[[email protected] ~]# tar xzf prometheus-2.27.1.linux-amd64.tar.gz  -C /usr/local/ //需上传
[[email protected] ~]# 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服务器修改配置文件，提权

[[email protected] ~]# echo -e "log_bin=master-bin\nlog_slave_updates=true\nserver_id=11" >> /etc/my.cnf 
[[email protected] ~]# 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

[[email protected] ~]# echo -e "server_id = 22\nrelay-log=relay-log-bin\nrelay-log-index=slave-relay-bin.index" >> /etc/my.cnf 
[[email protected] ~]# 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用户和密码

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

④添加system系统服务

[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
           

[[email protected] ~]# systemctl daemon-reload
[[email protected] ~]# systemctl start mysqld_exporter.service
[[email protected] ~]# netstat -lntup | grep "9104"
           

2.4 node_exporter部署

①安装node_exporter

注：两台MySQL节点都要部署

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

②system系统启动node_exproter

[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
           

[[email protected] opt]# systemctl daemon-reload
[[email protected] opt]#   systemctl start node_exporter
[[email protected] opt]#   systemctl enable node_exporter
[[email protected] 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']
           

三.浏览器访问

①浏览器访问prometheus

②Grafana创建模板监控

添加主从主群监控模板7371：

监控系统资源模板：8919