高可用监控平台企业级SOP

我的紫霞辣辣

已于 2022-01-21 18:04:31 修改

阅读量587

点赞数

分类专栏：监控文章标签： mysql 数据库 java

于 2021-09-15 15:45:06 首次发布

本文链接：https://blog.csdn.net/Yosigo_/article/details/120292225

版权

本文详细介绍了如何构建高可用的监控平台，包括mysql数据库双主架构、Zabbix集群的安装与配置、Grafana的对接以及分布式Proxy部署。在数据库层面，采用keepalived+Mysql实现双主高可用；在监控组件层面，通过Zabbix Server和Proxy的集群确保服务稳定；同时，还涵盖了Grafana的初始化和与数据库的连接测试，确保了整个系统的监控和可视化能力。

摘要由CSDN通过智能技术生成

架构图

在这里插入图片描述

数据库集群(keepalived+Mysql双主架构)

ip地址	作用
192.168.15.210	VIP，Zabbix集群连接MySQL集群专⽤
192.168.15.204 (zabbix04)	MySQL集群本地IP，Local IP
192.168.15.205 (zabbix05)	MySQL集群本地IP，Local IP

数据库双主架构

下载包，安装数据库

# 数据库客户端程序
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-client-8.0.23-14.1.el7.x86_64.rpm	

# 数据库主程序
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-server-8.0.23-14.1.el7.x86_64.rpm

# 数据库主程序依赖
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-shared-8.0.23-14.1.el7.x86_64.rpm

# 数据库主程序依赖
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-shared-compat-8.0.23-14.1.el7.x86_64.rpm

# 安装数据库
yum install -y *.rpm

修改配置文件

vim /etc/my.cnf
[mysqld]
...
server-id=51
binlog_format=row
log-bin=mysql-bin
skip-name-resolve 		# 跳过域名解析（非必须）
log-slave-updates=1 	# slave更新是否记入日志（5.6必须的）
relay_log_purge = 0		 # 关闭relay_log自动清除功能，保障故障时的数据一致

vim /etc/my.cnf
[mysqld]
...
server-id=52
binlog_format=row
log-bin=mysql-bin
skip-name-resolve 		# 跳过域名解析（非必须）
log-slave-updates=1 	# slave更新是否记入日志（5.6必须的）
relay_log_purge = 0		 # 关闭relay_log自动清除功能，保障故障时的数据一致

数据库初始化

mysqld --defaults-file=/etc/my.cnf --datadir=/data --user=mysql --initialize-insecure
systemctl start mysqld

# 查看数据库密码
cat /var/log/mysqld.log | grep password		

# 登陆数据库
mysql -uroot -p(密码)

# 重新设置数据库管理员密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Na123***' PASSWORD EXPIRE NEVER;

# 创建主从库复制用户
create user 'rep'@'192.168.15.205' identified by 'Na123***';
grant replication slave on *.* to rep@192.168.15.205;

# 刷新授权表
flush privileges;
exit;

# 将mysqld设置为开机自启
systemctl enable mysqld

mysqld --defaults-file=/etc/my.cnf --datadir=/data --user=mysql --initialize-insecure
systemctl start mysqld

# 查看数据库密码
cat /var/log/mysqld.log | grep password		

# 登陆数据库
mysql -uroot -p(密码)

# 重新设置数据库管理员密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Na123***' PASSWORD EXPIRE NEVER;

# 创建主从库复制用户
create user 'rep'@'192.168.15.204' identified by 'Na123***';
grant replication slave on *.* to rep@192.168.15.204;

# 刷新授权表
flush privileges;
exit;

# 将mysqld设置为开机自启
systemctl enable mysqld

配置双主架构

mysql -uroot -p'Na123***'

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 1221
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

change master to 
get_master_public_key=1,
master_host='192.168.15.204',
master_user='rep',
master_password='Na123***',
master_log_file='mysql-bin.000002',
master_log_pos=1221;


start slave;

# 查看主从复制服务配置是否完成
mysql> show slave status\G
#      Slave_IO_Running: Yes			IO线程
#      Slave_SQL_Running: Yes			SQL线程

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 1454
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

change master to 
get_master_public_key=1,
master_host='192.168.15.205',
master_user='rep',
master_password='Na123***',
master_log_file='mysql-bin.000002',
master_log_pos=1454;


start slave;

# 查看主从复制服务配置是否完成
mysql> show slave status\G
#      Slave_IO_Running: Yes			IO线程
#      Slave_SQL_Running: Yes			SQL线程

创建zabbix服务相关的数据

create user zabbix@"%" identified by 'Na123***';

create database zabbix character set utf8 collate utf8_bin;

grant all privileges on zabbix.* to 'zabbix'@'%';

flush privileges;

系统优化

# 关闭防火墙和selinux

# linux资源限制设置
vim /etc/security/limits.conf
# soft是一个警告值，而hard则是一个真正意义的阀值，超过就会报错
* soft nproc 65535		 # 任何用户可以打开的最大进程数 
* hard nproc 65535
* soft nofile 102400	    # 任何用户可以打开的最大的文件描述符数量，默认1024，这里的数值会限制tcp连接
* hard nofile 102400

# 统一时区
timedatectl set-timezone "Asia/Shanghai"

# 重启数据库
reboot

数据库高可用

高可用工具

yum install -y keepalived
systemctl enable keepalived

vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   
 notification_email {
   
 	sysadmin@firewall.loc
 }
 notification_email_from localhost
 smtp_server 127.0.0.1
 smtp_connect_timeout 30
 router_id MySQL_Cluster
}

vrrp_instance MySQL_HA {
   
 state BACKUP
 interface eth0
 virtual_router_id 31
 priority 100
 advert_int 1
 nopreempt
 authentication {
   
    auth_type PASS
 	auth_pass 1111
 }
 
 virtual_ipaddress {
   
 	192.168.15.210 				# 虚拟IP
 }
}

virtual_server 192.168.15.210 3306 {
   
 delay_loop 6
 persistence_timeout 30
 protocol TCP
 
 real_server 192.168.15.204 3306 {
   
 	notify_down /etc/keepalived.sh
 	TCP_CHECK {
   
 		connect_port 3306
 		connect_timeout 3
 		nb_get_retry 1
 		delay_before_retry 1
 		}
 	}
}

vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   
 notification_email {
   
 	sysadmin@firewall.loc
 }
 notification_email_from localhost
 smtp_server 127.0.0.1
 smtp_connect_timeout 30
 router_id MySQL_Cluster
}

vrrp_instance MySQL_HA {
   
 state BACKUP
 interface eth0
 virtual_router_id 31
 priority 90
 nopreempt
 advert_int 1
 authentication {
   
 	auth_type PASS
 	auth_pass 1111
 }
 virtual_ipaddress {
   
 	192.168.15.210
 }
}

virtual_server 192.168.15.210 3306 {
   
 delay_loop 6
 persistence_timeout 30
 protocol TCP
 
 real_server 192.168.15.205 3306 {
   
 	notify_down /etc/keepalived.sh
    TCP_CHECK {
   
 		connect_port 3306
 		connect_timeout 3
 		nb_get_retry 1
        delay_before_retry 1
 		}
 	}
}

vim /etc/keepalived.sh
#!/bin/bash
systemctl stop keepalived

chmod +x /etc/keepalived.sh

# 重启keepalived并设置开机自启
systemctl restart keepalived
systemctl enable keepalived

测试

关闭虚拟ip地址所在主机的mysqld服务，看一下虚拟ip地址是否会漂移。

Zabbix集群

ip地址	作用
192.168.15.211	VIP，Zabbix对外提供的IP地址，WEB集群通讯⼊⼝IP地址
192.168.15.202(zabbix02)	Zabbix集群节点和Web节点本地IP，Local IP
192.168.15.203(zabbix03)	Zabbix集群节点和Web节点本地IP，Local IP

安装MySQL客户端

# MySQL客户端软件包
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-client-8.0.23-14.1.el7.x86_64.rpm

# MySQL驱动依赖程序包
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-devel-8.0.23-14.1.el7.x86_64.rpm

# MySQL驱动依赖程序包
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-shared-8.0.23-14.1.el7.x86_64.rpm

# MySQL驱动依赖程序包
wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.23-14/binary/redhat/7/x86_64/percona-server-shared-compat-8.0.23-14.1.el7.x86_64.rpm

# 数据库驱动依赖安装
yum -y install percona