MYSQL集群高可用和数据监控平台实现方案-电子发烧友网

一、项目说明

该项目共分为2个子项目，由MYSQL集群高可用和数据监控平台两部分组成

• 具体要求

• 网络配置：确保集群中的MySQL服务器之间的网络连接稳定可靠，避免网络延迟或丢包对集群性能造成影响。

• 同步参数配置：对于需要数据同步的集群方案（如主从复制和组复制），需要合理配置同步参数，确保数据的实时性和一致性。

• 备份和恢复策略：制定完善的备份和恢复策略，定期备份集群数据，并测试恢复流程的可行性，以确保在发生故障时能够快速恢复数据和服务。

• 监控和告警：使用监控工具对集群进行实时监控，并设置合理的告警阈值。当集群出现异常情况时，能够及时发现并处理。

• 设计思路

• MYSQL ==集群高可用== 使用双主双活+keepalived实现

• MYSQL ==数据监控平台== 使用mysqld_exporter+prometheus+Grafana三件套实现

• 集群及监控平台搭建完毕后可以实现企业内部的mysql数据库双主机在线增加高可用性，通过keepalived的故障检测和VIP漂移能力使得发生故障后使用者无感知，增加系统的容错能力，通过监控平台实现mysql数据库监控可视化

1.1 MySQL中的主从复制技术

MySQL 是基于它自身的 Bin-log 日志来完成数据的异步复制，因为 Bin-log 日志中会记录所有对数据库产生变更的语句，包括 DML 数据变更和 DDL 结构变更语句，数据的同步过程如下：

在这里插入图片描述

1.2 keepalived高可用方案

Keepalived是一个轻量级别的高可用解决方案，使用VRRP（Vritrual Router Redundancy Protocol,虚拟路由冗余协议)的VIP虚拟IP的漂移功能，实现单点故障转移

在这里插入图片描述

1.3 可视化监控平台三件套

• Mysqld_exporter：是一款轻量级的mysql监控工具，用来收集MysQL数据库相关指标并将其暴露给prometheus进行监控和告警

• Prometheus：普罗米修斯，一个开源的服务监控系统，它负责采集和存储应用的监控指标数据，并以可视化的方式进行展示，以便于用户实时掌握系统的运行情况，并对异常进行检测

• Grafana：格拉法娜，是一个跨平台的开源的度量分析和可视化工具,可以从prometheus获取数据进行可视化数据大屏展示。

总结：mysqld_exporter用于抓取mysql监控指标数据，prometheus接收到数据后进行整理分析，grafana从prometheus获取数据使用大屏模版进行仪表盘展示

1.4 项目任务清单

1. 系统平台部署

• 安装VmWare17

• 虚拟出三台计算机

• 安装OpenEuler22.03 SP4 LTS 操作系统

• 系统设置：主机名、防火墙、SELinux、hosts映射、IP地址：

2. 安装2台MySql服务器

• 使用二进制包进行安装

• 配置MySql系统服务

3. 部署高可用MySql双主集群

• 配置账户

• 配置主主复制

• 安装keepalived并配置

4. 安装配置监控平台

• 安装Mysqld_Exporter+Prometheus+grafana-enterprise

• 配置监控平台组件

5. 压力测试

二、项目实现步骤

项目拓扑结构

在这里插入图片描述
软硬件环境清单：

在这里插入图片描述

2.1 OpenEuler系统安装

2.1.1 创建虚拟机实例

• 第一步：文件菜单->新建虚拟机->典型->下一步

image-20230426105520639

• 第二步：稍后安装操作系统

image-20230426105559441

• 第三步：选择操作系统类型，由于OpenEuler22.03 LTS SP3使用Linux5.10内核则选择如下：

image-20231115113531955

• 第四步：命名虚拟机

在这里插入图片描述

• 第五步：设置磁盘空间20G,动态空间申请，设置为单个文件

在这里插入图片描述

• 第六步：自定义硬件，设置硬件参数

在这里插入图片描述

• 第七步：设置自定义硬件

• 内存：推荐2GB

• 处理器：1颗、2核心

• ==新CD/DVD：适应ISO映像文件，点击浏览按钮，选择之前下载好的openEuler-22.03-LTS-SP2-x86_64-dvd.iso镜像文件==

• 网络适配器：选择NAT模式

• 显示器：去掉"加速3D图形“的对钩

• 最终：

image-20231115114047316

• 选择关闭、完成

2.1.2 OpenEuler22.03操作系统的安装部署

选择语言：中文或英文

• 安装信息摘要设置

image-20240524091942939

• 安装目的地：显示安装位置，一般为硬盘，点击自定义，然后点击完成：

在这里插入图片描述

• /boot：系统启动分区，推荐500M或1GB

• swap：交换分区，4G

• /：根分区，期望容量省略，表示将剩余空间全部分配

方法：点击下图的加号，设置挂载点及期望容量（重复多次）

image-20240524092325368

image-20240524092439307

image-20240524092505325

image-20240524092533410

• 点击完成，接受更改

image-20240524092603436

• 网络和主机名：打开网卡连接

image-20240524092626997

• 主机名：moniter、master1、master2，点击应用

• 配置：IPv4设置，手动，分别配置IP192.168.88.163、192.168.88.161、192.168.88.162，子网掩码255.255.255.0，网关192.168.88.2，DNS服务器192.168.88.2点击保存

在这里插入图片描述

• root账户密码设置：密码为OPENlab123

image-20240524092743232

• 创建用户：创建一个普通账户KD，设置密码：OPENlab123

在这里插入图片描述

• 安装完成后点击"重启系统"

image-20240524092900894

2.1.3 配置OpenEuler22.03

• 登录

• 账号：root

• 密码：OPENlab123

在这里插入图片描述

• 修改主机名

[root@moniter~]# hostnamectlset-hostname monitor

• 三台主机重新设置登录系统密码为123

[root@moniter~]# passwd root
更改用户 root 的密码 。
新的密码：
无效的密码： 密码少于8个字符
重新输入新的密码：
passwd：所有的身份验证令牌已经成功更新。
[root@moniter~]#

• 重启，重新连接

reboot

• 关闭三台主机防火墙及SELinux

[root@monitor~]# vi/etc/selinux/config
# This file controls the stateofSELinuxonthe system.
# SELINUX=can takeoneofthese threevalues:
#   enforcing-SELinux security policyisenforced.
#   permissive-SELinux prints warnings insteadofenforcing.
#   disabled-NoSELinux policyisloaded.
SELINUX=disabled
# SELINUXTYPE=can takeoneofthese threevalues:
#   targeted-Targeted processesareprotected,
#   minimum-Modificationoftargeted policy.Onlyselected processesareprotected.
#   mls-Multi Level Security protection.
SELINUXTYPE=targeted

[root@monitor~]# systemctl stop firewalld #关闭防火墙
[root@monitor~]# systemctl disable firewalld # 取消开机启动
Removed/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed/etc/systemd/system/multi-user.target.wants/firewalld.service.
[root@monitor~]# reboot #重启

• 三台主机下载所需软件并升级

yum install vim make gcc tree net-tools tar-y
yumupdate

• 三台主机时间同步

[root@master2~]# vim/etc/chrony.conf

在这里插入图片描述

[root@master2~]# systemctl restart chronyd
[root@master2~]# chronyc sources-v

 .-- Source mode '^' = server, '=' = peer, '#' = local clock.
/.-Source state'*'=currentbest,'+'=combined,'-'=notcombined,
|/      'x'=may beinerror,'~'=too variable,'?'=unusable.
||                        .-xxxx [ yyyy ]+/-zzzz
||   Reachability register (octal)-.     | xxxx=adjustedoffset,
||   Log2(Pollinginterval)--.   |     | yyyy = measured offset,
||                  |    | zzzz=estimated error.
||                | |     
MS Name/IP address     Stratum Poll Reach LastRxLastsample       
===============================================================================
^*203.107.6.88        2 6 17  4 +785us[+7288us]+/- 31ms
[root@master2~]# chronyc sources-v

 .-- Source mode '^' = server, '=' = peer, '#' = local clock.
/.-Source state'*'=currentbest,'+'=combined,'-'=notcombined,
|/      'x'=may beinerror,'~'=too variable,'?'=unusable.
||                        .-xxxx [ yyyy ]+/-zzzz
||   Reachability register (octal)-.     | xxxx=adjustedoffset,
||   Log2(Pollinginterval)--.   |     | yyyy = measured offset,
||                  |    | zzzz=estimated error.
||                | |     
MS Name/IP address     Stratum Poll Reach LastRxLastsample       
===============================================================================
^*203.107.6.88        2 6 17  6 +785us[+7288us]+/- 31ms
[root@master2~]# chronyc sources-v

 .-- Source mode '^' = server, '=' = peer, '#' = local clock.
/.-Source state'*'=currentbest,'+'=combined,'-'=notcombined,
|/      'x'=may beinerror,'~'=too variable,'?'=unusable.
||                        .-xxxx [ yyyy ]+/-zzzz
||   Reachability register (octal)-.     | xxxx=adjustedoffset,
||   Log2(Pollinginterval)--.   |     | yyyy = measured offset,
||                  |    | zzzz=estimated error.
||                | |     
MS Name/IP address     Stratum Poll Reach LastRxLastsample       
===============================================================================
^*203.107.6.88        2 6 17  6 +785us[+7288us]+/- 31ms
[root@master2~]# chronyc sources-v

 .-- Source mode '^' = server, '=' = peer, '#' = local clock.
/.-Source state'*'=currentbest,'+'=combined,'-'=notcombined,
|/      'x'=may beinerror,'~'=too variable,'?'=unusable.
||                        .-xxxx [ yyyy ]+/-zzzz
||   Reachability register (octal)-.     | xxxx=adjustedoffset,
||   Log2(Pollinginterval)--.   |     | yyyy = measured offset,
||                  |    | zzzz=estimated error.
||                | |     
MS Name/IP address     Stratum Poll Reach LastRxLastsample       
===============================================================================
^*203.107.6.88        2 6 17  8 +785us[+7288us]+/- 31ms
[root@master2~]# chronyc sources-v

 .-- Source mode '^' = server, '=' = peer, '#' = local clock.
/.-Source state'*'=currentbest,'+'=combined,'-'=notcombined,
|/      'x'=may beinerror,'~'=too variable,'?'=unusable.
||                        .-xxxx [ yyyy ]+/-zzzz
||   Reachability register (octal)-.     | xxxx=adjustedoffset,
||   Log2(Pollinginterval)--.   |     | yyyy = measured offset,
||                  |    | zzzz=estimated error.
||                | |     
MS Name/IP address     Stratum Poll Reach LastRxLastsample       
===============================================================================
^*203.107.6.88        2 6 17  8 +785us[+7288us]+/- 31ms
[root@master2~]# timedatectl
       Localtime: 六2025-02-1515:42:41CST
     Universaltime: 六2025-02-1507:42:41UTC
        RTCtime: 六2025-02-1507:42:41
       Timezone: Asia/Shanghai (CST,+0800)
Systemclock synchronized: yes
       NTP service: active
     RTCinlocalTZ:no

2.2 生产环境二进制包安装MySql

下载安装包
网址链接: https://dev.mysql.com/downloads/mysql/

在这里插入图片描述

2.2.1 解压缩安装包

• 连接xshell、在下shell点击xftp图标

在这里插入图片描述

• 解压安装包、准备工作：

[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
[[email protected]_64]# ls
bin docs include lib LICENSE man README share support-files

# 移动到默认安装目录，也可自行修改
[root@master1~]# cd~#~是一个特殊的符号，表示当前用户的主目录
[root@master1~]# mv mysql-8.0.37-linux-glibc2.17-x86_64/usr/local/mysql
[root@master1/]# cd/usr/local/mysql
[root@master1mysql]# groupadd mysql # 创建名为mysql的用户组
[root@master1mysql]# useradd-r-g mysql-s/bin/falsemysql # 创建名为 mysql 的系统用户，将其添加到mysql用户组中，并设置其登录shell为/bin/false，以限制该用户的登录权限
#-r：创建系统帐户
#-g：为所创建的用户指定基本组的 id .如果该组 id 不存在会报错，如果存在则创建成功
#-s：用于指定所创建用户可操作的脚本
## 删除用户命令：userdel
[root@master1mysql]# mkdir data # 创建用于存放MySQL数据文件目录
# 设置mysql目录的账户及工作组，生产环境中不要使用root
[root@master1mysql]# chown-R mysql:mysql/usr/local/mysql
#-R：表示递归修改指定目录下所有文件及其子目录的权限
# chmod 命令是用于给文件或目录设置权限，如果是目录，我们需要使用-R 选项，如果是文件这个选项可以不用指定。
# chown 命令是用于给文件或目录修改所属者和所属组权限。

2.2.2 初始化工作

在这里插入图片描述
master1：

# 初始化数据目录
[root@master1mysql]# bin/mysqld--initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data # 注意：需要复制密码
2025-02-16T07:15:42.081778Z0[System] [MY-013169] [Server]/usr/local/mysql/bin/mysqld (mysqld8.0.37) initializingofserverinprogressasprocess5283
2025-02-16T07:15:42.086988Z1[System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-02-16T07:15:42.326267Z1[System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-02-16T07:15:43.605271Z6[Note] [MY-010454] [Server] A temporary passwordisgeneratedforroot@localhost: ;Ay,miVu;9k1 #生成临时密码：;Ay,miVu;9k1
[root@master1mysql]# bin/mysqld_safe--user=mysql & # 使用后台方式以mysql用户身份启动
#MySQL 服务器，mysqld_safe 是一个用于启动和监控 MySQL 服务器的脚本
[1]5328
[root@master1mysql]# Loggingto'/usr/local/mysql/data/master1.err'.
2025-02-16T07:17:08.488162Z mysqld_safe Starting mysqld daemonwithdatabasesfrom/usr/local/mysql/data

初始化数据目录: https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/data-directory-initialization.html

在这里插入图片描述

master2：

[root@master2mysql]# groupadd mysql
[root@master2mysql]# useradd-r-g mysql-s/bin/falsemysql
[root@master2mysql]# mkdir data
[root@master2mysql]# chown-R mysql:mysql/usr/local/mysql
# 初始化数据目录
[root@master2mysql]# bin/mysqld--initialize --user=mysql --basedir=/usrlocal/mysql --datadir=/usr/local/mysql/data
2025-02-16T05:53:39.774317Z0[System] [MY-013169] [Server]/usr/local/mysql/bin/mysqld (mysqld8.0.37) initializingofserverinprogressasprocess1854
2025-02-16T05:53:39.774344Z0[ERROR] [MY-010338] [Server] Can't find error-message file '/usrlocal/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2025-02-16T0539.781243Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-02-16T0540.015072Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-02-16T0540.481966Z 0 [Warning] [MY-013829] [Server] Missing data directory for ICU regular expressions: /usrlocal/mysql/lib/private/.
2025-02-16T0541.390467Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: +AshIk4eerzq

生成临时密码：;Ay,miVu;9k1、+AshIk4eerzq

注意：此时上述命令执行完毕处于后台运行状态，需要另行启动一个终端

在这里插入图片描述

[root@master1~]# cd "/usr/local/mysql"
[root@master1mysql]# ps-ef|grep mysql# 查看进程运行状态
root    5328 5048015:17pts/0 00:00:00/bin/sh bin/mysqld_safe--user=mysql
mysql   5410 5328015:17pts/0 00:00:01/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysq --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master1.err --pid-file=master1.pid
root    5525 5458015:20pts/1 00:00:00grep--color=auto mysql
[root@master1mysql]# cd/usr/local/mysql

找到下面的文件进行软连接

[root@master1mysql]# ln-s/usr/lib64/libtinfo.so.6.3/usr/lib64/libtinfo.so.5
[root@master1mysql]# ln-s/usr/lib64/libncurses.so.6.3/usr/lib64/libncurses.so.5
## 删除软链接：rm-rf

[root@master1mysql]# bin/mysql-uroot-p
Enter password:       # 粘贴之前的初始密码：;Ay,miVu;9k1
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis13
Server version:8.0.37

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

mysql>alteruser'root'@'localhost'identifiedwithmysql_native_passwordby'123456'; #修改密码
Query OK,0rowsaffected (0.00sec)

mysql>flush privileges; #刷新
Query OK,0rowsaffected (0.04sec)

mysql>use mysql; # 查看账户信息
Readingtableinformationforcompletionoftableandcolumnnames
You can turn off this featuretogeta quicker startupwith-A

Database changed
mysql>selectuser,host,pluginfrommysql.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|
+------------------+-----------+-----------------------+
4rowsinset(0.00sec)

mysql>exit
Bye
[root@master1mysql]# ps-ef|grep mysql
root    5328 5048015:17pts/0 00:00:00/bin/sh bin/mysqld_safe--user=mysql
mysql   5410 5328015:17pts/0 00:00:02/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysq --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master1.err --pid-file=master1.pid
root    5533 5458015:25pts/1 00:00:00grep--color=auto mysql
# 在当前终端关闭运行的mysql
[root@master1mysql]# kill-95328
[root@master1mysql]# kill-95410
[root@master1mysql]# kill-95533
-bash: kill: (5533)-Nosuch process
[root@master1mysql]# kill-95458

Connection closed.

Disconnectedfromremote host(master1)at15:26:04.

Type `help' to learn how to use Xshell prompt.
[C:~]$

在这里插入图片描述

2.2.3 设置mysql的配置文件

回到之前的终端，敲一个回车，显示进程以杀死

在这里插入图片描述

新建配置文件，输入以下内容：

[root@master1mysql]# 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@monitormysql]# cd/usr/local/mysql/support-files
[root@master1support-files]# cp-a mysql.server/etc/init.d/mysql
[root@master1support-files]# vim/etc/init.d/mysql

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

在这里插入图片描述

[root@master1support-files]# cd~
[root@master1~]# vim~/.bash_profile # 设置环境变量需添加如下语句

export PATH=$PATH:/usr/local/mysql/bin

在这里插入图片描述

• 配置启动脚本

#~是一个特殊的符号，表示当前用户的主目录
[root@master1~]# source~/.bash_profile #重新加载当前用户的 .bash_profile 文件中的配置
[root@master1~]# systemctl daemon-reload # 重载系统配置
[root@master1~]# systemctlstartmysql
[root@master1~]#/usr/lib/systemd/systemd-sysv-install enable mysql # 开机启动
[root@master1~]# mysql-uroot-p
Enter password:
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis8
Server version:8.0.37MySQL Community Server-GPL

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

mysql>updatemysql.usersethost="%"whereuser="root";
Query OK,1rowaffected (0.01sec)
Rowsmatched:1 Changed:1 Warnings:0

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

mysql>exit
Bye

UUID：

master1:
[root@master1 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=d4e30409-ec35-11ef-acfe-000c29244098

master2:
[root@master2 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=5ef6aa2e-ec2a-11ef-8cb0-000c293e0dba

2.3mysql集群搭建

1. 修改2个主节点的配置文件

2. 创建一个用于同步数据的账号

3. 建立2个主节点的相互复制

4.测试

2.3.1 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 #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~]# systemctlstartmysql
[root@master1~]# mysql-uroot-p
Enter password:
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis8
Server version:8.0.37MySQL Community Server-GPL

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

mysql>createuser'mback'@'%'identifiedwithmysql_native_passwordby'123456';
Query OK,0rowsaffected (0.06sec)

mysql>grantreplication slaveon*.*to'mback'@'%';
Query OK,0rowsaffected (0.05sec)

mysql>

2.3.2 master2节点配置

[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~]# systemctlstartmysql
[root@master2~]# mysql-uroot-p
Enter password:
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis8
Server version:8.0.37MySQL Community Server-GPL

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

mysql>createuser'mback'@'%'identifiedwithmysql_native_passwordby'123456';
Query OK,0rowsaffected (0.03sec)

mysql>grantreplication slaveon*.*to'mback'@'%';
Query OK,0rowsaffected (0.00sec)

mysql>

2.3.3 建立master1 节点主从关系

• 由于建立的是主-主架构集群，相互为对方的从节点，则两个节点都通过 root 账号登录

• 此时master1为从节点，master2为主节点

1. 先在master2上查看同步的日志名称及同步点号

# 注意：在master2上执行

mysql>showmaster status;
+----------------------+----------+--------------+------------------+-------------------+
|File        |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+----------------------+----------+--------------+------------------+-------------------+
|mysql-bin-log.000001|  664|      |mysql      |         |
+----------------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

# 日志文件：mysql-bin-log.000001
# 日志数据点：664

2. master1 节点上建立复制关系：
master_host='192.168.88.162

[root@master1~]# mysql-uroot-p
Enter password:
...

mysql>change mastertomaster_host='192.168.88.162',master_user='mback',master_password='123456',master_port=3306,master_log_file='mysql-bin-log.000001',master_log_pos=664;
Query OK,0rowsaffected,9warnings (0.07sec)

mysql>startslave;
Query OK,0rowsaffected,1warning (0.03sec)

mysql>showslave statusG
***************************1.row***************************
       Slave_IO_State: Waitingforsourcetosend event
         Master_Host:192.168.88.162
         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 # 这里必须是yes
      Slave_SQL_Running: Yes # 这里必须是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:2
         Master_UUID:5ef6aa2e-ec2a-11ef-8cb0-000c293e0dba
      Master_Info_File: mysql.slave_master_info
          SQL_Delay:0
     SQL_Remaining_Delay:NULL
   Slave_SQL_Running_State: Replica has readallrelay log; waitingformore 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:
1rowinset,1warning (0.01sec)

2.3.4 建立master2 节点主从关系

此时master2为从节点，master1为主节点

1. 先在master1上查看同步的日志名称及同步点号

# 注意：在master1上执行

mysql>showmaster status;
+----------------------+----------+--------------+------------------+-------------------+
|File        |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+----------------------+----------+--------------+------------------+-------------------+
|mysql-bin-log.000001|  664|      |mysql      |         |
+----------------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)

# 日志文件：mysql-bin-log.000001
# 日志数据点：664

2.master2节点上建立复制关系：
master_host='192.168.88.161

[root@master2~]# mysql-uroot-p
Enter password:
...

mysql>change mastertomaster_host='192.168.88.161',master_user='mback',master_password='123456',master_port=3306,master_log_file='mysql-bin-log.000001',master_log_pos=664;
Query OK,0rowsaffected,9warnings (0.07sec)

mysql>startslave;
Query OK,0rowsaffected,1warning (0.03sec)

mysql>showslave statusG
***************************1.row***************************
       Slave_IO_State: Waitingforsourcetosend event
         Master_Host:192.168.88.161
         Master_User: mback
         Master_Port:3306
        Connect_Retry:60
       Master_Log_File: mysql-bin-log.000001
     Read_Master_Log_Pos:843
       Relay_Log_File: mysql-relay-log.000002
        Relay_Log_Pos:330
    Relay_Master_Log_File: mysql-bin-log.000001
      Slave_IO_Running: Yes # 这里必须为yes
      Slave_SQL_Running: Yes # 这里必须为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:843
       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: d4e30409-ec35-11ef-acfe-000c29244098
      Master_Info_File: mysql.slave_master_info
          SQL_Delay:0
     SQL_Remaining_Delay:NULL
   Slave_SQL_Running_State: Replica has readallrelay log; waitingformore 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:
1rowinset,1warning (0.00sec)

2.4 局部测试

测试主主架构是否能相互同步数据

2.4.1 master1执行

[root@master1~]# systemctl status mysql
● mysql.service-LSB:startandstop MySQL
  Loaded: loaded (/etc/rc.d/init.d/mysql; generated)
  Active: active (running) since Mon2025-02-1713:19:16CST;2min25s ago
   Docs: man:systemd-sysv-generator(8)
  Process:1151ExecStart=/etc/rc.d/init.d/mysqlstart(code=exited, status=0/SUCCESS)
   Tasks:45(limit:8934)
  Memory:450.3M
  CGroup:/system.slice/mysql.service
      ├─1177/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/usr/local/mysql/data --pid-file=>
      └─1640/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql --datadir=/usr/local/mysql/da>

2月1713:19:13master1 systemd[1]: Starting LSB:startandstop MySQL...
2月1713:19:16master1 mysql[1151]: Starting MySQL... SUCCESS!
2月1713:19:16master1 systemd[1]: Started LSB:startandstop MySQL.

[root@master1~]# mysql-uroot-p
Enter password:
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis15
Server version:8.0.37MySQL Community Server-GPL

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

• 建立数据库

mysql>showdatabases;
+--------------------+
|Database     |
+--------------------+
|information_schema|
|mysql       |
|performance_schema|
|sys        |
+--------------------+
4rowsinset(0.01sec)

mysql>createdatabase test1;
Query OK,1rowaffected (0.01sec)

mysql>showdatabases;
+--------------------+
|Database     |
+--------------------+
|information_schema|
|mysql       |
|performance_schema|
|sys        |
|test1       |
+--------------------+
5rowsinset(0.00sec)

• 在master2上查看是否同步

[root@master2~]# mysql-uroot-p
Enter password:
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis15
Server version:8.0.37MySQL Community Server-GPL

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

mysql>showdatabases;
+--------------------+
|Database     |
+--------------------+
|information_schema|
|mysql       |
|performance_schema|
|sys        |
|test1       |
+--------------------+
5rowsinset(0.01sec)

此时master1(主)–master2(从)构建完毕

2.4.2 master2执行

• 创建表

mysql>use test1
Database changed
mysql>create tableback_test (`user_id`int(8)not null, `user_name`varchar(255)not null, `user_sex`varchar(255)not null, `password`varchar(255)not null, `register_time` datetime);
Query OK,0rowsaffected,1warning (0.03sec)

mysql>descback_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 |   |
+---------------+--------------+------+-----+---------+-------+
5rowsinset(0.01sec)

• 在master1上查看是否有同步表数据

mysql>use test1;
Readingtableinformationforcompletionoftableandcolumnnames
You can turn off this featuretogeta quicker startupwith-A

Database changed
mysql>showtables;
+-----------------+
|Tables_in_test1|
+-----------------+
|back_test   |
+-----------------+
1rowinset(0.00sec)

mysql>descback_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 |   |
+---------------+--------------+------+-----+---------+-------+
5rowsinset(0.00sec)

至此master1(从)–master2(主)构建完毕

2.5 Keepalived故障转移的高可用环境

在这里插入图片描述

1. 2台MySql服务器安装keepalived软件

2.配置服务

3. 模拟故障进行测试

2.5.1 master1节点配置

• 安装安装包

[root@master1~]# yum install keepalived-y

• master1节点配置keepalived

[root@master1~]# vim/etc/keepalived/keepalived.conf
# 删除所有，拷贝下列配置数据

!Configuration Fileforkeepalived

global_defs {
  router_id mysql-master1 # keepalived服务器的一个标识，每台机子不同
}
vrrp_instance VI_1 {
  state BACKUP # 指定keepalived的角色, BACKUP模式将根据优先级决定主或从
  interface ens33   # 监测的网卡名，注意自己网卡名称
  virtual_router_id51  # 虚拟路由标识，确保和master2相同
  priority100  # 用来选举master的数值
  nopreempt
  advert_int1
  authentication {   # 认证区域
    auth_type PASS
    auth_pass1111
  }
  virtual_ipaddress {   # 指定VIP地址
   192.168.88.200
  }
}

# 虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
virtual_server192.168.88.2003306{
  delay_loop6  # 设置运行情况检查时间，单位是秒
  lb_algo rr   # 设置后端调度算法
  lb_kind DR   # 设置LVS实现负载均衡的机制
  persistence_timeout50  # 会话保持时间，单位是秒
  protocol TCP   # 指定转发协议类型
  real_server192.168.88.1613306{   # 真实服务器IP地址及端口
  notify_down/etc/keepalived/chk_mysql.sh
    weight1  # 配置服务节点的权值
    TCP_CHECK {
      connect_port3306  # 健康检查端口
      connect_timeout3  # 连接超时时间
      retry3  # 重连次数
      delay_before_retry3  # 重连间隔时间
    }
  }
}

配置mysql健康检查脚本

# 配置mysql健康检查脚本

[root@master1~]# vim/etc/keepalived/chk_mysql.sh

#!/bin/bash
counter=$(netstat-na|grep "LISTEN"|grep "3306"|wc-l)
if [ "${counter}"-eq0];then
  killall keepalived
fi
# 通过端口记录数判断mysql是否运行，mysql停止后终止keepalived，当然也可以在抢救一下

[root@master1~]# chmod+x/etc/keepalived/chk_mysql.sh # 设置脚本执行权限
[root@master1~]# systemctlstartkeepalived
[root@master1~]# systemctl status keepalived
● keepalived.service-LVSandVRRP High Availability Monitor
  Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
  Active: active (running) since Mon2025-02-1716:35:29CST;2s ago
  Process:2525ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID:2526(keepalived)
   Tasks:3(limit:8934)
  Memory:2.1M
  CGroup:/system.slice/keepalived.service
      ├─2526/usr/sbin/keepalived-D
      ├─2528/usr/sbin/keepalived-D
      └─2529/usr/sbin/keepalived-D

2月1716:35:29master1 Keepalived_vrrp[2529]: (VI_1) removing VIPs.
2月1716:35:29master1 Keepalived_healthcheckers[2528]: WARNING-defaultuser'keepalived_script'fors>
2月1716:35:29master1 Keepalived[2526]: Startup complete
2月1716:35:29master1 Keepalived_vrrp[2529]: (VI_1) Entering BACKUP STATE (init)
2月1716:35:29master1 Keepalived_vrrp[2529]: VRRP sockpool: [ifindex( 2), family(IPv4), proto(112), fd>
2月1716:35:29master1 Keepalived_healthcheckers[2528]: Pleaseadda #!shebangtoscript/etc/keepalive>
2月1716:35:29master1 Keepalived_healthcheckers[2528]: SECURITY VIOLATION-checkscriptsarebeing exe>
2月1716:35:29master1 Keepalived_healthcheckers[2528]: Initializing ipvs
2月1716:35:29master1 Keepalived_healthcheckers[2528]: Gained quorum1+0=1<=1for VS [192.168.88.200]>
2月1716:35:29master1 Keepalived_healthcheckers[2528]: Activating healthcheckerforservice [192.168.88>
lines1-22/22(END)
^C
[root@master1~]# ip ad
1: lo:mtu65536qdisc noqueue stateUNKNOWNgroupdefaultqlen1000
  link/loopback00:00:00:00:00:00brd00:00:00:00:00:00
  inet127.0.0.1/8scopehost lo
   valid_lft forever preferred_lft forever
  inet6 ::1/128scopehost
   valid_lft forever preferred_lft forever
2: ens33:mtu1500qdisc fq_codel state UPgroupdefaultqlen1000
  link/ether00:0c:29:24:40:98brd ffffff:ff
  inet192.168.88.161/24brd192.168.88.255scopeglobalnoprefixroute ens33
   valid_lft forever preferred_lft forever
  inet192.168.88.200/32scopeglobalens33
   valid_lft forever preferred_lft forever # 注意会产生新的VIP
  inet6 fe80::20c:29ff4098/64scopelink noprefixroute
   valid_lft forever preferred_lft forever
,multicast,up,lower_up>,up,lower_up>

2.5.2 master2节点配置

• 安装安装包

[root@master2~]# yum install keepalived-y

在这里插入图片描述

• master2节点配置keepalived

[root@master2~]# vim/etc/keepalived/keepalived.conf
# 删除所有，拷贝下列配置数据

!Configuration Fileforkeepalived

global_defs {
  router_id mysql-master2 # 注意与master1区分开来
}
vrrp_instance VI_1 {
  state BACKUP # 指定keepalived的角色, BACKUP模式将根据优先级决定主或从
  interface ens33   # 监测的网卡名，注意自己网卡名称
  virtual_router_id51  # 虚拟路由标识，确保和master2相同
  priority50  # 用来选举master的数值
  nopreempt
  advert_int1
  authentication {   # 认证区域
    auth_type PASS
    auth_pass1111
  }
  virtual_ipaddress {   # 指定VIP地址
   192.168.88.200
  }
}

# 虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
virtual_server192.168.88.2003306{
  delay_loop6  # 设置运行情况检查时间，单位是秒
  lb_algo rr   # 设置后端调度算法
  lb_kind DR   # 设置LVS实现负载均衡的机制
  persistence_timeout50  # 会话保持时间，单位是秒
  protocol TCP   # 指定转发协议类型
  real_server192.168.88.1623306{   # master2地址及端口
  notify_down/etc/keepalived/chk_mysql.sh
    weight1  # 配置服务节点的权值
    TCP_CHECK {
      connect_port3306  # 健康检查端口
      connect_timeout3  # 连接超时时间
      retry3  # 重连次数
      delay_before_retry3  # 重连间隔时间
    }
  }
}

[root@master2~]# vim/etc/keepalived/chk_mysql.sh

#!/bin/bash
counter=$(netstat-na|grep "LISTEN"|grep "3306"|wc-l)
if [ "${counter}"-eq0];then
  killall keepalived
fi

[root@master2~]# chmod+x/etc/keepalived/chk_mysql.sh
[root@master2~]# systemctlstartkeepalived

2.5.3 局部测试

原理：当master1服务器宕机后，VIP会自动漂移至master2服务器并继续向外提供mysql服务

1. 在master1中停用mysql

2. 查看VIP是否漂移

3. 故障修复

• master1中执行

[root@master1~]# systemctl status mysql # 查看mysql状态
● mysql.service-LSB:startandstop MySQL
  Loaded: loaded (/etc/rc.d/init.d/mysql; generated)
  Active: active (running) since Mon2025-02-1713:37:40CST;4h44min ago
   
[root@master1~]# systemctl status keepalived
● keepalived.service-LVSandVRRP High Availability Monitor
  Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
  Active: active (running) since Mon2025-02-1716:35:29CST;1h46min ago
  
[root@master1~]# ip a
1: lo:mtu65536qdisc noqueue stateUNKNOWNgroupdefaultqlen1000
  link/loopback00:00:00:00:00:00brd00:00:00:00:00:00
  inet127.0.0.1/8scopehost lo
   valid_lft forever preferred_lft forever
  inet6 ::1/128scopehost
   valid_lft forever preferred_lft forever
2: ens33:mtu1500qdisc fq_codel state UPgroupdefaultqlen1000
  link/ether00:0c:29:24:40:98brd ffffff:ff
  inet192.168.88.161/24brd192.168.88.255scopeglobalnoprefixroute ens33
   valid_lft forever preferred_lft forever
  inet192.168.88.200/32scopeglobalens33 # 此时VIP正在监听
   valid_lft forever preferred_lft forever
  inet6 fe80::20c:29ff4098/64scopelink noprefixroute
   valid_lft forever preferred_lft forever
,multicast,up,lower_up>,up,lower_up>

[root@master1~]# systemctl stop mysql # 模拟宕机
[root@master1~]# ip a # VIP消失
1: lo:mtu65536qdisc noqueue stateUNKNOWNgroupdefaultqlen1000
  link/loopback00:00:00:00:00:00brd00:00:00:00:00:00
  inet127.0.0.1/8scopehost lo
   valid_lft forever preferred_lft forever
  inet6 ::1/128scopehost
   valid_lft forever preferred_lft forever
2: ens33:mtu1500qdisc fq_codel state UPgroupdefaultqlen1000
  link/ether00:0c:29:24:40:98brd ffffff:ff
  inet192.168.88.161/24brd192.168.88.255scopeglobalnoprefixroute ens33
   valid_lft forever preferred_lft forever
  inet6 fe80::20c:29ff4098/64scopelink noprefixroute
   valid_lft forever preferred_lft forever
[root@master1~]# systemctl status keepalived # 已停用
○ keepalived.service-LVSandVRRP High Availability Monitor
  Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
  Active: inactive (dead)
,multicast,up,lower_up>,up,lower_up>

• master2中执行

[root@master2~]# ip a
1: lo:mtu65536qdisc noqueue stateUNKNOWNgroupdefaultqlen1000
  link/loopback00:00:00:00:00:00brd00:00:00:00:00:00
  inet127.0.0.1/8scopehost lo
   valid_lft forever preferred_lft forever
  inet6 ::1/128scopehost
   valid_lft forever preferred_lft forever
2: ens33:mtu1500qdisc fq_codel state UPgroupdefaultqlen1000
  link/ether00:0c:29:3e:0d:ba brd ffffff:ff
  inet192.168.88.162/24brd192.168.88.255scopeglobalnoprefixroute ens33
   valid_lft forever preferred_lft forever
  inet192.168.88.200/32scopeglobalens33
   valid_lft forever preferred_lft forever
  inet6 fe80::20c:29ffdba/64scopelink noprefixroute
   valid_lft forever preferred_lft forever
,multicast,up,lower_up>,up,lower_up>

• master1中恢复msyql服务和keepalived服务，注意必须==先恢复msyql服务==

[root@master1~]# systemctlstartmysql
[root@master1~]# systemctlstartkeepalived
[root@master1~]# ip ad
1: lo:mtu65536qdisc noqueue stateUNKNOWNgroupdefaultqlen1000
  link/loopback00:00:00:00:00:00brd00:00:00:00:00:00
  inet127.0.0.1/8scopehost lo
   valid_lft forever preferred_lft forever
  inet6 ::1/128scopehost
   valid_lft forever preferred_lft forever
2: ens33:mtu1500qdisc fq_codel state UPgroupdefaultqlen1000
  link/ether00:0c:29:24:40:98brd ffffff:ff
  inet192.168.88.161/24brd192.168.88.255scopeglobalnoprefixroute ens33
   valid_lft forever preferred_lft forever
  inet6 fe80::20c:29ff4098/64scopelink noprefixroute
   valid_lft forever preferred_lft forever
# 即使master1恢复后，VIP不会被强占
,multicast,up,lower_up>,up,lower_up>

2.6 安装监控平台

• 涉及的软件及端口

服务	端口
Prometheus	9090
Mysqld_exporter	9194
Grafana	3000

2.6.1 部署Mysqld_exporter

Mysql_exporter是用来收集MysQL数据库相关指标且需要连接到数据库并有相关权限
下载安装包并解压缩：https://prometheus.io/download/

在这里插入图片描述
部署Mysqld_exporter之前，先在minitor主机上安装好MySql，如2.2

#192.168.88.163monitor主机操作
[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@monitormysqld_exporter]# ls
LICENSE mysqld_exporter NOTICE

• 常见配置文件

[root@monitormysqld_exporter]# vim .my.cnf # 注意为隐藏文件
[client]
user=exporter # 该账户需要再2台mysql节点新建
password=123456# 登录密码
host=192.168.88.200# 使用VIP访问
port=3306

• 2台mysql节点创建用户并授权

master1操作

[root@master1~]# mysql-uroot-p
Enter password:
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis15
Server version:8.0.37MySQL Community Server-GPL

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

mysql>createuser'exporter'@'%'identifiedwithmysql_native_passwordby'123456';
Query OK,0rowsaffected (0.07sec)

mysql>grantprocess,replication client,selecton*.*to'exporter'@'%';
Query OK,0rowsaffected (0.01sec)

mysql>flush privileges;
Query OK,0rowsaffected (0.01sec)

mysql>exit
Bye

master2操作，由于设置了主-主集群架构，master1创建账户后会同步到master2，只需查看即可

[root@master2~]# mysql-uroot-p
Enter password:
Welcometothe MySQL monitor. Commandsendwith;org.
Your MySQL connection idis15
Server version:8.0.37MySQL Community Server-GPL

Copyright (c)2000,2024, Oracleand/orits affiliates.

Oracleisa registered trademarkofOracle Corporationand/orits
affiliates. Other names may be trademarksoftheir respective
owners.

Type'help;'or'h'forhelp. Type'c'toclear thecurrentinput statement.

mysql>selectuser,hostfrommysql.user;
+------------------+-----------+
|user      |host   |
+------------------+-----------+
|exporter    |%    |  # 已经同步
|mback      |%    |
|root      |%    |
|mysql.infoschema|localhost|
|mysql.session  |localhost|
|mysql.sys    |localhost|
+------------------+-----------+
6rowsinset(0.00sec)

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

• 刷新服务配置并启动服务

# monitor节点操作
[root@monitor~]# systemctl daemon-reload
[root@monitor~]# systemctlstartmysqld_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 preset: disabled)
  Active: active (running) since Tue2025-02-1816:43:41CST;10min ago

• 通过浏览器输入链接: http://192.168.88.163:9104/metrics，查看监控抓取msyql服务器的数据

在这里插入图片描述

2.6.2 部署Promethues

Prometheus是一个开源系统监控和警报工具包,现在是一个独立的开源项目，独立于任何公司维护。
架构图：

在这里插入图片描述
下载安装包并解压缩： https://prometheus.io/download/ ，选择2.53.2 LTS版本

在这里插入图片描述

• monitor操作

# 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@monitorprometheus]# ls
console_libraries consoles LICENSE NOTICE prometheus prometheus.yml promtool

• 新建prometheus账户

[root@monitorprometheus]# useradd--no-create-home --shell /bin/false prometheus
[root@monitorprometheus]# 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~]# systemctlstartprometheus
[root@monitor~]# systemctl enable 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: disabled)
  Active: active (running) since Tue2025-02-1817:09:21CST;1min5s ago
 Main PID:2840(prometheus)
   Tasks:7(limit:8934)
  Memory:23.0M
  CGroup:/system.slice/prometheus.service
      └─2840/usr/local/prometheus/prometheus--config.file=/usr/local/prometheus/prometheus.yml --storage.tsdb.path=/usr/local/prometheus/data

• 通过浏览器输入链接: http://192.168.88.163:9090，查看管理页面，通过链接: http://192.168.88.163:9090/metrics查看监控数据

在这里插入图片描述

• Mysqld_exporter对接Prometheus

# monitor操作，打开文件，添加如下内容，注意对齐格式,# 注意：是监控机IP和Mysqld_exporter端口
[root@monitor~]# vim/usr/local/prometheus/prometheus.yml

scrape_configs:
-job_name: "prometheus"  #自己定义的监控的job_name
  static_configs:  # 静态指定，targets中的 host:port/metrics 将会作为metrics抓取对象
  -targets: ["192.168.88.163:9090"]
   
-job_name: "mysql"
  static_configs:    # 配置静态规则，直接指定抓取的ip:port
  -targets: ["192.168.88.163:9104"]
    labels:
     instance: mysqld_exporter

scrape配置集合，⽤于定义监控的⽬标对象（target）的集合，以及描述如何抓取（scrape）相关指标数据的配置参数；
通常，每个scrape配置对应于⼀个单独的作业（job），
⽽每个targets可通过静态配置（static_configs）直接给出定义，也可基于Prometheus⽀持的服务发现机制进⾏⾃动配置；

在这里插入图片描述

• 重启Prometheus，输入链接: http://192.168.88.163:9090 测试是否监控MySql

[root@monitor~]# systemctl restart prometheus

graph：查询收集到的指标数据，并提供简单的绘图

在这里插入图片描述

2.6.3 部署Grafana

Grafana是一个功能强大、灵活性高、易于使用的数据可视化和监控工具，广泛应用于IT运维、应用性能监控、工业物联网等领域

使用Grafana，用户可以轻松地创建各种图表、图形和面板，以直观和动态的方式展示数据趋势、指标和警报。其灵活的插件系统和丰富的图形化选项使用户能够根据自己的需求定制仪表板，并将其集成到现有的监控系统中

在这里插入图片描述
连接xshell，点击xftp图标，找到 grafana-enterprise-11.1.2-1.x86_64.rpm 文件，传输到虚拟机

• 下载并解压缩：

[root@monitor~]# yum install grafana-enterprise-11.1.2-1.x86_64.rpm

• 修改配置文件

[root@monitor~]# vim/etc/grafana/grafana.ini

################################# Server #################################
[server]
# Protocol (http, https, h2, socket)
protocol=http # 启用

# Thisisthe minimum TLS version allowed.Bydefault, thisvalueisempty.
Acceptedvaluesare: TLS1.2, TLS1.3. If nothingissetTLS1.2would be taken
;min_tls_version=""

# The ip addresstobindto,emptywill bindtoallinterfaces
;http_addr=

# The http porttouse
http_port=3000# 启用

# The public facing domain name usedtoaccess grafanafroma browser
domain=localhost # 启用

# Redirecttocorrect domain if host header doesnotmatchdomain
# Prevents DNS rebinding attacks
;enforce_domain=false

# Thefullpublic facing url you useinbrowser, usedforredirectsandemails
# If you use reverse proxyandsub path specifyfullurl (withsub path)
root_url=%(protocol)s://%(domain)s:%(http_port)s/# 启用

在这里插入图片描述

• 启动

[root@monitor~]# systemctlstartgrafana-server
[root@monitor~]# systemctl status grafana-server
[root@monitor~]# systemctl enable grafana-server
Synchronizing stateofgrafana-server.servicewithSysV service scriptwith/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.

• 关联Promethues并设置仪表盘模版

 浏览器其中输入：http://192.168.88.163:3000

在这里插入图片描述

初始账户/密码：admin/admin
设置新的密码：123456

在这里插入图片描述

在设置界面中选择Home > Connections > Data sources > prometheus

在这里插入图片描述

 设置prometheus监听地址：http://192.168.88.163:9090

在这里插入图片描述

 下滑点击 save&test

在这里插入图片描述

选择监控模版来显示mysql的关键指标，模版ID为7362

在这里插入图片描述

链接: https://grafana.com/grafana/dashboards ，在页面中搜索 node exporter ，选择适合的面板，搜索mysql，选择适合的面板

• 7362：

在这里插入图片描述

• 11074

在这里插入图片描述

[root@monitor~]# journalctl-u grafana-server-f
[root@monitor~]# cat/var/log/grafana/grafana.log

实时查看Grafana服务器的日志，以便追踪可能的错误或警告信息。

结果图应当如下：

在这里插入图片描述

2.7 压力测试

查看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 --numberint-cols=50 --number-char-cols=60 --auto-generate-sql --auto-generate-sql-addautoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-ofqueries=2000 -uroot -p123456 --verbose

链接：https://blog.csdn.net/weixin_74212619/article/details/145630717?spm=1001.2014.3001.5502

声明：本文内容及配图由入驻作者撰写或者入驻合作网站授权转载。文章观点仅代表作者本人，不代表电子发烧友网立场。文章及其配图仅供工程师学习之用，如有内容侵权或者其他违规问题，请联系本站处理。举报投诉