项目说明
概述
该项目共分为2个子项目，由MYSQL集群高可用和数据监控平台两部分组成
MYSQL集群高可用属于云原生高级课数据库运维部分的知识
数据监控平台属于云原生拔高项目，旨在让学生增加知识面，提高项目实习经历，充实简历
业务需求
某企业由于业务增加，超出了单库性能阈值，则需要构建高可用的数据库服务器集群，且在业务增长初期，读写业务各半，需选择Active-Active Cluster集群架构，为了保证活性，使用恰当的技术实现high availability，要求架构图如下：
为了实现自动化运维，预使用开源监控实时报警软件实现服务器的检测、大屏展示，要求使用独立主机安装软件检测数据库集群的性能，以仪表盘形式展示：
具体要求
网络配置：确保集群中的MySQL服务器之间的网络连接稳定可靠，避免网络延迟或丢包对集群性能造成影响。
同步参数配置：对于需要数据同步的集群方案（如主从复制和组复制），需要合理配置同步参数，确保数据的实时性和一致性。
备份和恢复策略：制定完善的备份和恢复策略，定期备份集群数据，并测试恢复流程的可行性，以确保在发生故障时能够快速恢复数据和服务。
监控和告警：使用监控工具对集群进行实时监控，并设置合理的告警阈值。当集群出现异常情况时，能够及时发现并处理。
项目设计思路
MYSQL集群高可用使用双主双活+keepalived实现
MYSQL数据监控平台使用mysqld_exporter+prometheus+Grafana三件套实现
集群及监控平台搭建完毕后可以实现企业内部的mysql数据库双主机在线增加高可用性，通过keepalived的故障检测和VIP漂移能力使得发生故障后使用者无感知，增加系统的容错能力，通过监控平台实现mysql数据库监控可视化
项目组织方式及时间
时间：建议1.5天至2天内完成所有的项目搭建、压力测试、问题总结
方式：通过VmWare17虚拟机实现平台搭建
人数：1人
项目特点
综合项目从主从复制原理、数据同步方式、主从架构模式、主从架构/双主架构、高可用、数据可视化一步步从零搭建出一个完善的数据库集群/监控平台
综合项目完成后可以实现对MYSQL数据库高级运维知识的巩固，通过参与实际项目增加工作实战经验，提升技术能力、故障判断检测维护能力、充实简历项目经历
通过prometheus数据库监控三件套的项目搭建，可以掌握数据监控的知识应用能力，通过监控大屏数据的展示使得数据可视化得以实现，使得运维更自动化、直观化
该项目进一步巩固和掌握云原生高级数据库部分的知识以及数据监控可视化的知识点
项目背景知识
MySQL中的主从复制技术
MySQL是基于它自身的Bin-log日志来完成数据的异步复制，因为Bin-log日志中会记录所有对数据库产生变更的语句，包括DML数据变更和DDL结构变更语句，数据的同步过程如下：
集群机构模式
一主一从/多从架构
一主一从或一主多从，这是传统的主从复制模型，也就是多个主从节点组成的集群中，只有一个主节点，剩余的所有节点都为其附属关系，结构如下：
双主/多主架构
若公司项目中读写请求的比例对半开，同时整体的并发量也不算低，至少超出了单库的承载阈值，这时就可以选用双主/多主架构，结构如下：
keepalived高可用方案
Keepalived是一个轻量级别的高可用解决方案，使用VRRP（Vritrual Router Redundancy Protocol,虚拟路由冗余协议)的VIP虚拟IP的漂移功能，实现单点故障转移
可视化监控平台三件套
Mysqld_exporter：是一款轻量级的mysql监控工具，用来收集MysQL数据库相关指标并将其暴露给prometheus进行监控和告警
Prometheus：普罗米修斯，一个开源的服务监控系统，它负责采集和存储应用的监控指标数据，并以可视化的方式进行展示，以便于用户实时掌握系统的运行情况，并对异常进行检测
Grafana：格拉法娜，是一个跨平台的开源的度量分析和可视化工具,可以从prometheus获取数据进行可视化数据大屏展示。
总结：mysqld_exporter用于抓取mysql监控指标数据，prometheus接收到数据后进行整理分析，grafana从prometheus获取数据使用大屏模版进行仪表盘展示
项目环境
项目拓扑结构
软硬件环境清单
| 主机名 | IP地址 | 硬件 | 软件 |
| master1 | 192.168.208.129 | cpu:1颗2核 内 存：2GB HDD：20GB 网 络：NAT |
VmWare17 OpenEuler22.03 SP4 MySql8.0.37 Keepalived2.2.4 |
| master2 | 192.168.208.130 | cpu:1颗2核 内 存：2GB HDD：20GB 网 络：NAT |
VmWare17 OpenEuler22.03 SP4 MySql8.0.37 Keepalived2.2.4 |
| monitor | 192.168.208.131 | cpu:1颗2核 内 存：2GB HDD：20GB 网 络：NAT |
VmWare17 OpenEuler22.03 SP4 Mysqld_Exporter-0.15.1 Prometheus-2.53.2 grafana-enterprise-11.1.2 |
软件下载地址：https://pan.xunlei.com/s/VO7mgCtMz3PDDndQcUGWjyGDA1?pwd=eenx#
项目任务清单
系统平台部署
安装VmWare17
虚拟出三台计算机
安装OpenEuler22.03 SP4 LTS 操作系统
系统设置：主机名、防火墙、SELinux、hosts映射、IP地址：
安装2台MySql服务器
使用二进制包进行安装
配置MySql系统服务
部署高可用MySql双主集群
配置账户
配置主主复制
安装keepalived并配置
安装配置监控平台
安装Mysqld_Exporter+Prometheus+grafana-enterprise
配置监控平台组件
压力测试
项目实现步骤
OpenEuler系统安装
系统配置
环境配置
主机名
[root@localhost ~]# hostnamectl set-hostname master1[root@localhost ~]# hostnamectl set-hostname master2[root@localhost ~]# hostnamectl set-hostname monitor关闭三台主机防火墙及SELinux
systemctl stop firewalld # 关闭防火墙 systemctl disable firewalld # 取消开机启动 reboot # 重启三台主机下载所需软件并升级
yum install vim make gcc tree net-tools tar -y yum update三台主机重新设置登录系统密码
passwd root三台主机时间同步
vim /etc/chrony.conf # 定位第3行，删除后添加阿里的时间同步服务地址 server ntp.aliyun.com iburst[root@master1 ~]# systemctl restart chronyd [root@master1 ~]# chronyc sources -v .-- Source mode '^' = server, '=' = peer, '#' = local clock. / .- Source state '*' = current best, '+' = combined, '-' = not combined, | / 'x' = may be in error, '~' = too variable, '?' = unusable. || .- xxxx [ yyyy ] +/- zzzz || Reachability register (octal) -. | xxxx = adjusted offset, || Log2(Polling interval) --. | | yyyy = measured offset, || \ | | zzzz = estimated error. || | | \ MS Name/IP address Stratum Poll Reach LastRx Last sample =============================================================================== ^* 203.107.6.88 2 6 17 6 -1058us[-9474us] +/- 35ms [root@master1 ~]# timedatectl Local time: Mon 2025-05-12 11:25:29 CST Universal time: Mon 2025-05-12 03:25:29 UTC RTC time: Mon 2025-05-12 03:25:29 Time zone: Asia/Shanghai (CST, +0800) System clock synchronized: yes NTP service: active RTC in local TZ: no[root@master2 ~]# vim /etc/chrony.conf [root@master2 ~]# systemctl restart chronyd [root@master2 ~]# chronyc sources -v .-- Source mode '^' = server, '=' = peer, '#' = local clock. / .- Source state '*' = current best, '+' = combined, '-' = not combined, | / 'x' = may be in error, '~' = too variable, '?' = unusable. || .- xxxx [ yyyy ] +/- zzzz || Reachability register (octal) -. | xxxx = adjusted offset, || Log2(Polling interval) --. | | yyyy = measured offset, || \ | | zzzz = estimated error. || | | \ MS Name/IP address Stratum Poll Reach LastRx Last sample =============================================================================== ^* 203.107.6.88 2 6 17 1 +640us[+2927us] +/- 40ms [root@master2 ~]# [root@master2 ~]# [root@master2 ~]# timedatectl Local time: Mon 2025-05-12 11:25:56 CST Universal time: Mon 2025-05-12 03:25:56 UTC RTC time: Mon 2025-05-12 03:25:56 Time zone: Asia/Shanghai (CST, +0800) System clock synchronized: yes NTP service: active RTC in local TZ: no[root@monitor ~]# vim /etc/chrony.conf [root@monitor ~]# systemctl restart chronyd [root@monitor ~]# chronyc sources -v .-- Source mode '^' = server, '=' = peer, '#' = local clock. / .- Source state '*' = current best, '+' = combined, '-' = not combined, | / 'x' = may be in error, '~' = too variable, '?' = unusable. || .- xxxx [ yyyy ] +/- zzzz || Reachability register (octal) -. | xxxx = adjusted offset, || Log2(Polling interval) --. | | yyyy = measured offset, || \ | | zzzz = estimated error. || | | \ MS Name/IP address Stratum Poll Reach LastRx Last sample =============================================================================== ^* 203.107.6.88 2 6 7 0 -239us[-7720us] +/- 39ms [root@monitor ~]# timedatectl Local time: Mon 2025-05-12 11:26:20 CST Universal time: Mon 2025-05-12 03:26:20 UTC RTC time: Mon 2025-05-12 03:26:21 Time zone: Asia/Shanghai (CST, +0800) System clock synchronized: yes NTP service: active RTC in local TZ: no制作三台主机快照
生产环境二进制包安装MySql
注意：2台MySql服务器安装过程相同，以下以master1主机为例
下载安装包
解压缩
[root@master1 ~]# tar xvf mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz
[root@master1 ~]# cd mysql-8.0.37-linux-glibc2.17-x86_64
[root@master1 mysql-8.0.37-linux-glibc2.17-x86_64]# ls
bin docs include lib LICENSE man README share support-files
使用前的准备
[root@master1 mysql-8.0.37-linux-glibc2.17-x86_64]# cd ~
[root@master1 ~]# mv mysql-8.0.37-linux-glibc2.17-x86_64 /usr/local/mysql
[root@master1 ~]# cd /usr/local/mysql
[root@master1 mysql]# groupadd mysql # 创建名为mysql的用户组
[root@master1 mysql]# useradd -r -g mysql -s /bin/false mysql # 创建名为 mysql的系统用户，将其添加到mysql用户组中，并设置其登录shell为/bin/false，以限制该用户的登录权限
[root@master1 mysql]# mkdir data
# 创建用于存放MySQL数据文件目录
# 设置mysql目录的账户及工作组，生产环境中不要使用root
[root@master1 mysql]# chown -R mysql:mysql /usr/local/mysql
初始化软件
[root@master1 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data # 注意：需要复制密码 My.er4t5opKM
[root@master1 mysql]# bin/mysqld_safe --user=mysql & # 使用后台方式以mysql用户身份启动 MySQL 服务器，mysqld_safe 是一个用于启动和监控 MySQL 服务器的脚本
# 注意：此时上述命令执行完毕处于后台运行状态，需要另行启动一个终端
[root@master1 ~]# ps -ef | grep mysql # 查看进程运行状态
[root@master1 ~]# cd /usr/local/mysql
[root@master1 ~]# bin/mysql -uroot -p # 登录，可能报错
# 报错，需要找到下面的文件进行软连接
[root@master1 ~]# ln -s /usr/lib64/libncurses.so.6.3 /usr/lib64/libncurses.so.5
[root@master1 ~]# ln -s /usr/lib64/libtinfo.so.6.3 /usr/lib64/libtinfo.so.5
[root@master1 ~]# bin/mysql -u root -p
Enter password: # 粘贴之前的初始密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456'; 修改密码
mysql> flush privileges; # 刷新
mysql> use mysql; # 查看账户信息
mysql> select user, host, plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
mysql>exit
[root@master1 ~]# ps -ef | grep mysql
root 5394 5147 0 13:52 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysq
mysql 5476 5394 0 13:52 pts/0 00:00:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master1.err --pid-file=master1.pid
root 5584 5147 0 14:01 pts/0 00:00:00 grep --color=auto mysql
[root@master1 ~]# kill -9 5476
设置mysql的配置文件
# 回到之前的终端，敲一个回车，显示进程以杀死
[root@master1 mysql]# vim /etc/my.cnf # 新建配置文件，输入以下内容：
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
socket = /tmp/mysql.sock
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-storage-engine=INNODB
log_error = error.log
配置启动脚本
[root@master1 ~]# cd /usr/local/mysql/support-files
[root@master1 support-files]# cp -a mysql.server /etc/init.d/mysql
[root@master1 support-files]# vim /etc/init.d/mysql # 增加=之后的内容
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
[root@master1 init.d]# cd ~
[root@master1 ~]# vim ~/.bash_profile # 设置环境变量需添加如下语句
export PATH=$PATH:/usr/local/mysql/bin
[root@master1 ~]# source ~/.bash_profile
[root@master1 ~]# systemctl daemon-reload # 重载系统配置
[root@master1 ~]# systemctl start mysql
[root@master1 ~]# /usr/lib/systemd/systemd-sysv-install enable mysql # 开机启动
[root@master1 ~]# mysql -uroot -p
mysql> update mysql.user set host="%" where user="root";
mysql> flush privileges;
mysql> exit
注意
mysql安装完毕后需要做快照
若选择安装完毕后进行克隆主机作为mater2，则必须要修改master2的server-uuid，否则无法进行下列主从服务器的搭建
[root@master1 ~]# cat /usr/local/mysql/data/auto.cnf [auto] server-uuid=3c5037c1-2ef5-11f0-9807-000c29734115 # 只需删除master2的/usr/local/mysql/data/auto.cnf文件重启即可重新生成[root@master2 ~]# tar xvf mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz [root@master2 ~]# cd mysql-8.0.37-linux-glibc2.17-x86_64 [root@master2 mysql-8.0.37-linux-glibc2.17-x86_64]# ls bin docs include lib LICENSE man README share support-files [root@master2 mysql-8.0.37-linux-glibc2.17-x86_64]# cd ~ [root@master2 ~]# mv mysql-8.0.37-linux-glibc2.17-x86_64 /usr/local/mysql [root@master2 ~]# cd /usr/local/mysql [root@master2 mysql]# groupadd mysql [root@master2 mysql]# useradd -r -g mysql -s /bin/false mysql [root@master2 mysql]# mkdir data [root@master2 mysql]# chown -R mysql:mysql /usr/local/mysql [root@master2 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 2025-05-12T06:24:48.842233Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.37) initializing of server in progress as process 5519 2025-05-12T06:24:48.846494Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2025-05-12T06:24:49.027393Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2025-05-12T06:24:50.074186Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: G5z0rpIGr)*u [root@master2 mysql]# bin/mysqld_safe --user=mysql & [1] 5559 [root@master2 mysql]# Logging to '/usr/local/mysql/data/master2.err'. 2025-05-12T06:25:26.498614Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data [root@master2 mysql]# cd [root@master2 ~]# ps -ef | grep mysql root 5559 5154 0 14:25 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysq mysql 5641 5559 1 14:25 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master2.err --pid-file=master2.pid root 5687 5154 0 14:26 pts/0 00:00:00 grep --color=auto mysql [root@master2 ~]# cd /usr/local/mysql [root@master2 mysql]# bin/mysql -uroot -p bin/mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@master2 mysql]# ln -s /usr/lib64/libncurses.so.6.3 /usr/lib64/libncurses.so.5 [root@master2 mysql]# ln -s /usr/lib64/libtinfo.so.6.3 /usr/lib64/libtinfo.so.5 [root@master2 mysql]# bin/mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.37 Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select user, host, plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | mysql_native_password | +------------------+-----------+-----------------------+ 4 rows in set (0.00 sec) mysql> exit Bye [root@master2 mysql]# cd [root@master2 ~]# ps -ef | grep mysql root 5559 5154 0 14:25 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysq mysql 5641 5559 0 14:25 pts/0 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master2.err --pid-file=master2.pid root 5694 5154 0 14:28 pts/0 00:00:00 grep --color=auto mysql [root@master2 ~]# kill -9 5641 [root@master2 ~]# bin/mysqld_safe: line 199: 5641 Killed env MYSQLD_PARENT_PID=5559 nohup /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master2.err --pid-file=master2.pid < /dev/null > /dev/null 2>&1 2025-05-12T06:28:13.254458Z mysqld_safe Number of processes running now: 0 2025-05-12T06:28:13.256812Z mysqld_safe mysqld restarted [root@master2 ~]# vim /etc/my.cnf [root@master2 ~]# cd /usr/local/mysql/support-files [root@master2 support-files]# cp -a mysql.server /etc/init.d/mysql [root@master2 support-files]# vim /etc/init.d/mysql [root@master2 support-files]# cd [root@master2 ~]# vim ~/.bash_profile [root@master2 ~]# source ~/.bash_profile [root@master2 ~]# systemctl daemon-reload [root@master2 ~]# systemctl start mysql [root@master2 ~]# /usr/lib/systemd/systemd-sysv-install enable mysql [root@master2 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> update mysql.user set host="%" where user="root"; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@master2 ~]# cat /usr/local/mysql/data/auto.cnf [auto] server-uuid=ce205258-2ef9-11f0-9e6f-000c29daac3cmsyql集群搭建
说明
搭建主-主集群，两个节点都是主库，也都属于对方的从库，也就是两者之间会相互同步数据，这时为了防止主键出现冲突，一般都会通过设置数据库自增步长的方式来防重
主-主架构集群适用于中读写请求的比例对半开，同时整体的并发量也不算低，至少超出了单库的承载阈值的场景下，架构图如下：
两台服务器信息如下：
| 主机名 | IP地址 | 系统 / 软件 |
| matser1 | 192.168.208.129 | OpenEuler22.03 / MySql8.0.37 |
| matser2 | 192.168.208.130 | OpenEuler22.03 / MySql8.0.37 |
注意：两台机子的server-uuid不能相同
步骤
修改2个主节点的配置文件
创建一个用于同步数据的账号
建立2个主节点的相互复制
测试
master1节点配置
修改配置文件
[root@master1 ~]# systemctl stop mysql
[root@master1 ~]# vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
socket = /tmp/mysql.sock
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-storage-engine=INNODB
log_error = error.log
# ------主节点配置-------
# 当前节点在集群中的唯一标识
server-id=1
# 开启bin-log日志，并为bin-log日志取个前缀名（有默认值可不写）
log-bin=mysql-bin-log
# 同步复制时过滤的库（主要将一些不需要备份/同步库写进来）
# 也可以通过binlog-do-db=xx1,xx2... 来指定要复制的目标库
binlog-ignore-db=mysql
# 指定bin-log日志的格式为混合模式（默认为statement）
binlog_format=mixed
# 设置单个binlog日志文件的最大容量
max_binlog_size=1024M
# ------从节点配置-------
# 开启relay-log日志（同样可以指定前缀名）
relay_log=mysql-relay-log
# 开启存储过程、函数、触发器等内容的同步功能
log_bin_trust_function_creators=true
# 同步执行跳过一些错误码（防止同步写入时出现错误导致复制中断）
slave_skip_errors=1062
# ------自增序列配置-------
# 设置自增初始值为1
auto_increment_offset=1
# 设置自增步长为2，自增序列为{1、3、5、7、9.....}
auto_increment_increment=2注意
server-id=1，为集群中本机标识，必须唯一
由于master1即使主节点又是从节点，则必须设置从节点配置
为了保证2个节点数据的一致性，需要开启自增序列配置，master1节点跳步为1 3 5 7 9...
创建用于数据同步的账号m1
[root@master1 ~]# systemctl start mysql [root@master1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create user 'mback'@'%' identified with mysql_native_password by '123456'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'mback'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> exit Byemaster2节点配置
修改配置文件
[root@master2 ~]# systemctl stop mysql [root@master2 ~]# vim /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 basedir = /usr/local/mysql datadir = /usr/local/mysql/data tmpdir = /tmp socket = /tmp/mysql.sock character-set-server = utf8mb4 collation-server = utf8mb4_general_ci default-storage-engine=INNODB log_error = error.log # ------主节点配置------- # 当前节点在集群中的唯一标识 server-id=2 # 开启bin-log日志，并为bin-log日志取个前缀名（有默认值可不写） log-bin=mysql-bin-log # 同步复制时过滤的库（主要将一些不需要备份/同步库写进来） # 也可以通过binlog-do-db=xx1,xx2... 来指定要复制的目标库 binlog-ignore-db=mysql # 指定bin-log日志的格式为混合模式（默认为statement） binlog_format=mixed # 设置单个binlog日志文件的最大容量 max_binlog_size=1024M # ------从节点配置------- # 开启relay-log日志（同样可以指定前缀名） relay_log=mysql-relay-log # 开启存储过程、函数、触发器等内容的同步功能 log_bin_trust_function_creators=true # 同步执行跳过一些错误码（防止同步写入时出现错误导致复制中断） slave_skip_errors=1062 # ------自增序列配置------- # 设置自增初始值为2 auto_increment_offset=2 # 设置自增步长为2，自增序列为{1、3、5、7、9.....} auto_increment_increment=2注意：
master2节点的server-id=2，不能和master1的server-id相同
设置自增初始值为2，则master2节点跳步为2 4 6 8 ...
创建用于数据同步的账号m2
[root@master2 ~]# systemctl start mysql [root@master2 ~]# mysql -uroot -p mysql> create user 'mback'@'%' identified with mysql_native_password by '123456'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'mback'@'%'; Query OK, 0 rows affected (0.00 sec)建立master1 节点主从关系
由于建立的是主-主架构集群，相互为对方的从节点，则两个节点都通过
root账号登录此时master1为从节点，master2为主节点
先在master2上查看同步的日志名称及同步点号
# 注意：在master2上执行 mysql> show master status; +----------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+ | mysql-bin-log.000001 | 664 | | mysql | | +----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 日志文件：mysql-bin-log.000001 # 日志数据点：664master1 节点上建立复制关系：
mysql>change master to master_host='192.168.208.130',master_user='mback',master_password='123456',master_port=3306,master_log_file='mysql-bin-log.000001',master_log_pos=664; mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.208.130 Master_User: mback Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-log.000001 Read_Master_Log_Pos: 664 Relay_Log_File: mysql-relay-log.000003 Relay_Log_Pos: 330 Relay_Master_Log_File: mysql-bin-log.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 664 Relay_Log_Space: 713 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: ce205258-2ef9-11f0-9e6f-000c29daac3c Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
建立master2 节点主从关系
此时master2为从节点，master1为主节点
先在master1上查看同步的日志名称及同步点号
# 注意：在master1上执行 mysql> show master status; +----------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+ | mysql-bin-log.000001 | 664 | | mysql | | +----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 日志文件：mysql-bin-log.000001 # 日志数据点：664master2节点上建立复制关系：
mysql> change master to master_host='192.168.88.12+',master_user='mback',master_password='123456',master_port=3306,master_log_file='mysql-bin-log.000001',master_log_pos=664; Query OK, 0 rows affected, 9 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.208.129 Master_User: mback Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-log.000001 Read_Master_Log_Pos: 664 Relay_Log_File: mysql-relay-log.000002 Relay_Log_Pos: 330 Relay_Master_Log_File: mysql-bin-log.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 664 Relay_Log_Space: 540 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3c5037c1-2ef5-11f0-9807-000c29734115 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)局部测试
测试主主架构是否能相互同步数据
master1执行，master2查看
master1上建立数据库
mysql> create database test1; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test1 | +--------------------+ 5 rows in set (0.01 sec)在master2上查看是否同步
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test1 | +--------------------+ 5 rows in set (0.00 sec)此时master1(主)--master2(从)构建完毕
master2执行，master1查看
master2中执行创建表
mysql> use test1; mysql> create table back_test (`user_id`int(8) not null,`user_name` varchar(255) not null,`user_sex` varchar(255) not null,`passwordd` varchar(255) not null,`register_time` datetime); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> desc back_test; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | user_id | int | NO | | NULL | | | user_name | varchar(255) | NO | | NULL | | | user_sex | varchar(255) | NO | | NULL | | | password | varchar(255) | NO | | NULL | | | register_time | datetime | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> exit在master1上查看是否有同步表数据
mysql> use test1; mysql> desc back_test; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | user_id | int | NO | | NULL | | | user_name | varchar(255) | NO | | NULL | | | user_sex | varchar(255) | NO | | NULL | | | password | varchar(255) | NO | | NULL | | | register_time | datetime | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> exit至此master1(从)--master2(主)构建完毕
Keepalived故障转移的高可用环境
说明
Keepalived即保持存活，是一种轻量化的高可用方案，用来防止单点故障
Keepalived的工作原理是VRRP（Virtual Router Redundancy Protocol）虚拟路由冗余协议。
Keepalived通过VIP虚拟IP的漂移实现高可用，在相同集群内发送组播包，master1和master2主通过VRRP协议发送组播包，告诉从主的状态。
Keepalived会虚拟出一个VIP地址代理2台服务器向外界提供服务
当Keepalived实际代理master1时，若master1出现故障，keepalived通过自身机制，自动将master2作为实际代理主服务器，不需要人工干预去修改mysql配置信息，从而实现了高可用
步骤
2台MySql服务器安装keepalived软件包
配置服务
模拟故障进行测试
master1节点配置
安装安装包
也给master2节点配置
安装安装包
[root@master1 ~]# yum install keepalived -y master1节点配置keepalived [root@master1 ~]# vim /etc/keepalived/keepalived.conf # 删除所有，拷贝下列配置数据 ! Configuration File for keepalived global_defs { router_id mysql-master01 # keepalived服务器的一个标识，每台机子不同 } vrrp_instance VI_1 { state BACKUP # 指定keepalived的角色, BACKUP模式将根据优先级决定主或从 interface ens160 # 监测的网卡名 virtual_router_id 51 # 虚拟路由标识，确保和master2相同 priority 100 # 用来选举master的数值 nopreempt advert_int 1 authentication { # 认证区域 auth_type PASS auth_pass 1111 } virtual_ipaddress { # 指定VIP地址 192.168.208.200 } } # 虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开 virtual_server 192.168.208.200 3306 { delay_loop 6 # 设置运行情况检查时间，单位是秒 lb_algo rr # 设置后端调度算法 lb_kind DR # 设置LVS实现负载均衡的机制 persistence_timeout 50 # 会话保持时间，单位是秒 protocol TCP # 指定转发协议类型 real_server 192.168.208.129 3306 { # 真实服务器IP地址及端口 notify_down /etc/keepalived/chk_mysql.sh weight 1 # 配置服务节点的权值 TCP_CHECK { connect_port 3306 # 健康检查端口 connect_timeout 3 # 连接超时时间 retry 3 # 重连次数 delay_before_retry 3 # 重连间隔时间 } } }# 配置mysql健康检查脚本 [root@master1 ~]# vim /etc/keepalived/chk_mysql.sh #!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l) if [ "${counter}" -eq 0 ]; then killall keepalived fi # 通过端口记录数判断mysql是否运行，mysql停止后终止keepalived，当然也可以在抢救一下[root@master1 ~]# chmod +x /etc/keepalived/chk_mysql.sh [root@master1 ~]# systemctl start keepalived keepalived.service - LVS and VRRP High Availability Monitor Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; ven> Active: active (running) since Mon 2025-05-12 17:26:00 CST; 7s ago Process: 7914 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=ex> Main PID: 7915 (keepalived) Tasks: 3 (limit: 8934) Memory: 1.0M CGroup: /system.slice/keepalived.service ├─ 7915 /usr/sbin/keepalived -D ├─ 7916 /usr/sbin/keepalived -D └─ 7918 /usr/sbin/keepalived -D May 12 17:26:04 master1 Keepalived_vrrp[7918]: (VI_1) Receive advertisement t> May 12 17:26:04 master1 Keepalived_vrrp[7918]: (VI_1) Entering MASTER STATE May 12 17:26:04 master1 Keepalived_vrrp[7918]: (VI_1) setting VIPs. May 12 17:26:04 master1 Keepalived_vrrp[7918]: (VI_1) Sending/queueing gratui> May 12 17:26:04 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens1> May 12 17:26:04 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens1> lines 1-18/22 74% root@master1 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 link/ether 00:0c:29:73:41:15 brd ff:ff:ff:ff:ff:ff inet 192.168.208.129/24 brd 192.168.208.255 scope global dynamic noprefixroute ens160 valid_lft 1134sec preferred_lft 1134sec inet 192.168.208.200/32 scope global ens160 # 注意会产生新的VIP valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe73:4115/64 scope link noprefixroute valid_lft forever preferred_lft forever[root@master2 ~]# yum install keepalived -y master2节点配置keepalived [root@master2 ~]# vim /etc/keepalived/keepalived.conf # 删除所有，拷贝下列配置数据 ! Configuration File for keepalived global_defs { router_id mysql-master02 # 注意与master1区分开来 } vrrp_instance VI_1 { state BACKUP interface ens160 # 注意网卡接口名 virtual_router_id 51 priority 50 # 注意选举参数 nopreempt advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.208.200 } } virtual_server 192.168.208.200 3306 { delay_loop 6 lb_algo rr lb_kind DR persistence_timeout 50 protocol TCP real_server 192.168.208.130 3306 { # master2地址及端口 notify_down /etc/keepalived/chk_mysql.sh weight 1 TCP_CHECK { connect_port 3306 connect_timeout 3 retry 3 delay_before_retry 3 } } }[root@master2 ~]# vim /etc/keepalived/chk_mysql.sh #!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l) if [ "${counter}" -eq 0 ]; then killall keepalived fi [root@master2 ~]# vim /etc/keepalived/keepalived.conf [root@master2 ~]# systemctl start keepalived [root@master2 ~]# systemctl status keepalived ● keepalived.service - LVS and VRRP High Availability Monitor Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled) Active: active (running) since Mon 2025-05-12 18:05:33 CST; 31s ago Process: 7618 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS) Main PID: 7619 (keepalived) Tasks: 3 (limit: 8934) Memory: 1.1M CGroup: /system.slice/keepalived.service ├─ 7619 /usr/sbin/keepalived -D ├─ 7621 /usr/sbin/keepalived -D └─ 7622 /usr/sbin/keepalived -D局部测试
原理：当master1服务器宕机后，VIP会自动漂移至master2服务器并继续向外提供mysql服务
步骤：
在master1中停用mysql
查看VIP是否漂移
故障修复
master1中执行
[root@master1 ~]# systemctl status mysql ● mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql; generated) Active: active (running) since Mon 2025-05-12 16:46:51 CST; 1h 38min ago Docs: man:systemd-sysv-generator(8) Tasks: 48 (limit: 8934) Memory: 392.5M CGroup: /system.slice/mysql.service ├─ 7274 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysq> └─ 7605 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/> May 12 16:46:50 master1 systemd[1]: Starting LSB: start and stop MySQL... May 12 16:46:51 master1 mysql[7263]: Starting MySQL. SUCCESS! May 12 16:46:51 master1 systemd[1]: Started LSB: start and stop MySQL. [root@master1 ~]# systemctl status keepalived ● keepalived.service - LVS and VRRP High Availability Monitor Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset> Active: active (running) since Mon 2025-05-12 17:26:00 CST; 59min ago Process: 7914 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, stat> Main PID: 7915 (keepalived) Tasks: 3 (limit: 8934) Memory: 1.0M CGroup: /system.slice/keepalived.service ├─ 7915 /usr/sbin/keepalived -D ├─ 7916 /usr/sbin/keepalived -D └─ 7918 /usr/sbin/keepalived -D May 12 17:53:00 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:00 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:00 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:00 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:05 master1 Keepalived_vrrp[7918]: (VI_1) Sending/queueing gratuitous ARPs > May 12 17:53:05 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:05 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:05 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:05 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> May 12 17:53:05 master1 Keepalived_vrrp[7918]: Sending gratuitous ARP on ens160 for 192> [root@master1 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 link/ether 00:0c:29:73:41:15 brd ff:ff:ff:ff:ff:ff inet 192.168.208.129/24 brd 192.168.208.255 scope global dynamic noprefixroute ens160 valid_lft 1358sec preferred_lft 1358sec inet 192.168.208.200/32 scope global ens160# 此时VIP正在监听 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe73:4115/64 scope link noprefixroute valid_lft forever preferred_lft forever [root@master1 ~]# systemctl stop mysql [root@master1 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 link/ether 00:0c:29:73:41:15 brd ff:ff:ff:ff:ff:ff inet 192.168.208.129/24 brd 192.168.208.255 scope global dynamic noprefixroute ens160 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe73:4115/64 scope link noprefixroute valid_lft forever preferred_lft forever [root@master1 ~]# systemctl status keepalived # 已停用 ○ keepalived.service - LVS and VRRP High Availability Monitor Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled) Active: inactive (dead)master2执行
[root@master2 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 link/ether 00:0c:29:73:41:15 brd ff:ff:ff:ff:ff:ff inet 192.168.208.130/24 brd 192.168.208.255 scope global dynamic noprefixroute ens160 valid_lft 1461sec preferred_lft 1461sec inet 192.168.208.200/32 scope global ens160 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe73:4115/64 scope link noprefixroute valid_lft forever preferred_lft forevermaster1中恢复msyql服务和keepalived服务，注意必须先恢复msyql服务
[root@master1 ~]# systemctl start mysql [root@master1 ~]# systemctl start keepalived [root@master1 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 link/ether 00:0c:29:73:41:15 brd ff:ff:ff:ff:ff:ff inet 192.168.208.129/24 brd 192.168.208.255 scope global dynamic noprefixroute ens160 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe73:4115/64 scope link noprefixroute valid_lft forever preferred_lft forever # 即使master1恢复后，VIP不会被强占安装监控平台
说明
涉及的软件及端口
服务 端口 Prometheus 9090 Mysqld_exporter 9194 Grafana 3000 部署Mysqld_exporter
Mysql_exporter是用来收集MysQL数据库相关指标且需要连接到数据库并有相关权限。
下载安装包并解压缩：Download | Prometheus
# 192.168.88.163 monitor主机操作
[root@monitor ~]# ls
anaconda-ks.cfg mysqld_exporter-0.15.1.linux-amd64.tar.gz
[root@monitor ~]# tar xvf mysqld_exporter-0.15.1.linux-amd64.tar.gz
mysqld_exporter-0.15.1.linux-amd64/
mysqld_exporter-0.15.1.linux-amd64/LICENSE
mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter
mysqld_exporter-0.15.1.linux-amd64/NOTICE
[root@monitor ~]# mv mysqld_exporter-0.15.1.linux-amd64 /usr/local/mysqld_exporter
[root@monitor ~]# cd /usr/local/mysqld_exporter
[root@monitor mysqld_exporter]# ls
LICENSE mysqld_exporter NOTICE
常见配置文件
[root@monitor mysqld_exporter]# vim .my.cnf # 注意为隐藏文件
[client]
user = exporter # 该账户需要再2台mysql节点新建
password = 123456 # 登录密码
host = 192.168.208.200 # 使用VIP访问
port = 33062台mysql节点创建用户并授权
# master1操作
[root@master1 ~]# mysql -uroot -p
Enter password:
mysql> create user 'exporter'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant process, replication client, select on *.* to 'exporter'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit# master2操作，由于设置了主-主集群架构，master1创建账户后会同步到master2，只需查看即可
[root@master2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1431
Server version: 8.0.37 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| exporter | % |
| mback | % |
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
mysql> exit
Bye
配置mysqld_exporter的系统服务
# monitor节点操作
[root@monitor ~]# vim /usr/lib/systemd/system/mysqld_exporter.service
[Unit]
Description=https://prometheus.io
[Service]
Restart=on-failure
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf --web.listen-address=:9104
[Install]
WantedBy=multi-user.target刷新服务配置并启动服务
[root@monitor ~]# systemctl daemon-reload
[root@monitor ~]# systemctl start mysqld_exporter.service
[root@monitor ~]# systemctl enable mysqld_exporter.service
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld_exporter.service → /usr/lib/systemd/system/mysqld_exporter.service.
[root@monitor ~]# systemctl status mysqld_exporter.service
● mysqld_exporter.service - https://prometheus.io
Loaded: loaded (/usr/lib/systemd/system/mysqld_exporter.service; enabled; vendor p>
Active: active (running) since Mon 2025-05-12 19:04:48 CST; 21s ago
Main PID: 6929 (mysqld_exporter)
Tasks: 4 (limit: 8934)
Memory: 2.4M
CGroup: /system.slice/mysqld_exporter.service
└─ 6929 /usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/lo>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.084Z caller=mysql>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.085Z caller=tls_c>
May 12 19:04:48 monitor mysqld_exporter[6929]: ts=2025-05-12T11:04:48.085Z caller=tls_c
部署Promethues
Prometheus是一个开源系统监控和警报工具包，最初由SoundCloud构建。自2012年成立以来，许多公司和组织都采用了Prometheus，该项目拥有非常活跃的开发人员和用户社区。它现在是一个独立的开源项目，独立于任何公司维护。
Prometheus于2016年加入了云原生计算基金会，成为继Kubernetes之后的第二个托管项目。
架构图：
下载安装包并解压缩：Download | Prometheus ，选择2.53.2 LTS版本
# monitor操作
[root@monitor ~]# tar xvf prometheus-2.53.2.linux-amd64.tar.gz
[root@monitor ~]# mv prometheus-2.53.2.linux-amd64 /usr/local/prometheus
[root@monitor ~]# cd /usr/local/prometheus
[root@monitor prometheus]# ls
console_libraries consoles LICENSE NOTICE prometheus prometheus.yml promtool
[root@monitor prometheus]# cd ~新建prometheus账户
[root@monitor ~]# useradd --no-create-home --shell /bin/false prometheus
[root@monitor ~]# chown -R prometheus:prometheus /usr/local/prometheus配置prometheus系统服务
# monitor操作
[root@monitor ~]# vim /usr/lib/systemd/system/prometheus.service
[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target
[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/prometheus/prometheus --config.file /usr/local/prometheus/prometheus.yml --storage.tsdb.path /usr/local/prometheus/data
[Install]
WantedBy=multi-user.target刷新服务配置并启动服务
[root@monitor ~]# systemctl daemon-reload
[root@monitor ~]# systemctl enable --now prometheus
Created symlink /etc/systemd/system/multi-user.target.wants/prometheus.service → /usr/lib/systemd/system/prometheus.service.
[root@monitor ~]# systemctl status prometheus
● prometheus.service - Prometheus
Loaded: loaded (/usr/lib/systemd/system/prometheus.service; enabled; vendor preset>
Active: active (running) since Mon 2025-05-12 19:29:45 CST; 19s ago
Main PID: 7225 (prometheus)
Tasks: 8 (limit: 8934)
Memory: 18.9M
CGroup: /system.slice/prometheus.service
└─ 7225 /usr/local/prometheus/prometheus --config.file /usr/local/promethe>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.040Z caller=head.go:72>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.040Z caller=head.go:79>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.040Z caller=head.go:83>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.042Z caller=main.go:11>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.043Z caller=main.go:11>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.043Z caller=main.go:13>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.179Z caller=main.go:13>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.179Z caller=main.go:14>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.179Z caller=main.go:11>
May 12 19:29:45 monitor prometheus[7225]: ts=2025-05-12T11:29:45.179Z caller=manager.go
Mysqld_exporter对接Prometheus
# monitor操作，打开文件，添加如下内容，注意对齐格式,# 注意：是监控机IP和Mysqld_exporter端口
[root@monitor ~]# vim /usr/local/prometheus/prometheus.yml
重启Prometheus服务
[root@monitor ~]# systemctl restart prometheus输入http://192.168.208.131:9090 测试是否监控MySql
部署Grafana
Grafana是一个功能强大、灵活性高、易于使用的数据可视化和监控工具，广泛应用于IT运维、应用性能监控、工业物联网等领域
使用Grafana，用户可以轻松地创建各种图表、图形和面板，以直观和动态的方式展示数据趋势、指标和警报。其灵活的插件系统和丰富的图形化选项使用户能够根据自己的需求定制仪表板，并将其集成到现有的监控系统中
下载并解压缩：
[root@monitor ~]# yum install grafana-enterprise-11.1.2-1.x86_64.rpm -y修改配置文件
[root@monitor ~]# vim /etc/grafana/grafana.ini
[server]
# Protocol (http, https, h2, socket)
protocol = http # 启用
# This is the minimum TLS version allowed. By default, this value is empty. Accepted values are: TLS1.2, TLS1.3. If nothing is set TLS1.2 would be taken
;min_tls_version = ""
# The ip address to bind to, empty will bind to all interfaces
;http_addr =
# The http port to use
http_port = 3000 # 启用
# The public facing domain name used to access grafana from a browser
domain = localhost # 启用
# Redirect to correct domain if host header does not match domain
# Prevents DNS rebinding attacks
;enforce_domain = false
# The full public facing url you use in browser, used for redirects and emails
# If you use reverse proxy and sub path specify full url (with sub path)
root_url = %(protocol)s://%(domain)s:%(http_port)s/ # 启用启动
[root@monitor ~]# systemctl enable --now grafana-server
Synchronizing state of grafana-server.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable grafana-server
Created symlink /etc/systemd/system/multi-user.target.wants/grafana-server.service → /usr/lib/systemd/system/grafana-server.service.
[root@monitor ~]# systemctl status grafana-server
● grafana-server.service - Grafana instance
Loaded: loaded (/usr/lib/systemd/system/grafana-server.service; enabled; vendor pr>
Active: active (running) since Mon 2025-05-12 19:58:07 CST; 8s ago
Docs: http://docs.grafana.org
Main PID: 7610 (grafana)
Tasks: 11 (limit: 8934)
Memory: 56.1M
CGroup: /system.slice/grafana-server.service
└─ 7610 /usr/share/grafana/bin/grafana server --config=/etc/grafana/grafan>
May 12 19:58:07 monitor grafana[7610]: logger=ngalert.scheduler t=2025-05-12T19:58:07.4>
May 12 19:58:07 monitor grafana[7610]: logger=ticker t=2025-05-12T19:58:07.438535156+08>
May 12 19:58:07 monitor grafana[7610]: logger=report t=2025-05-12T19:58:07.441016957+08>
May 12 19:58:07 monitor grafana[7610]: logger=ngalert.multiorg.alertmanager t=2025-05-1>
May 12 19:58:07 monitor grafana[7610]: logger=sqlstore.transactions t=2025-05-12T19:58:>
May 12 19:58:07 monitor grafana[7610]: logger=grafana-apiserver t=2025-05-12T19:58:07.7>
May 12 19:58:07 monitor grafana[7610]: logger=grafana-apiserver t=2025-05-12T19:58:07.7>
May 12 19:58:08 monitor grafana[7610]: logger=grafana.update.checker t=2025-05-12T19:58>
May 12 19:58:08 monitor grafana[7610]: logger=plugin.angulardetectorsprovider.dynamic t>
May 12 19:58:08 monitor grafana[7610]: logger=plugins.update.checker t=2025-05-12T19:58关联Promethues并设置仪表盘模版
# 浏览器其中输入：http://192.168.208.131:3000
# 初始账户/密码：admin/admin
# 设置新的密码：123456
# 在设置界面中选择Home > Connections > Data sources > prometheus
# 设置prometheus监听地址：http://192.168.208.131:9090
# save&test
# 选择监控模版来显示mysql的关键指标，模版ID为7362
压力测试
查看VIP所在服务器，停止当前节点的mysql，查看VIP是否漂移，刷新监控界面，看是否高可用
MySQL自带的压力测试工具——Mysqlslap
#修改最大连接数 vim /etc/my.cnf max_connections=1024 systemctl restart mysql # 完成压测，查看仪表盘 mysqlslap --defaults-file=/etc/my.cnf --concurrency=200,400 --iterations=1 --number-int-cols=50 --number-char-cols=60 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=2000 -uroot -p123456 --verbose