一、項(xiàng)目說(shuō)明

該項(xiàng)目共分為2個(gè)子項(xiàng)目，由MYSQL集群高可用和數(shù)據(jù)監(jiān)控平臺(tái)兩部分組成

? 具體要求

? 網(wǎng)絡(luò)配置：確保集群中的MySQL服務(wù)器之間的網(wǎng)絡(luò)連接穩(wěn)定可靠，避免網(wǎng)絡(luò)延遲或丟包對(duì)集群性能造成影響。

? 同步參數(shù)配置：對(duì)于需要數(shù)據(jù)同步的集群方案（如主從復(fù)制和組復(fù)制），需要合理配置同步參數(shù)，確保數(shù)據(jù)的實(shí)時(shí)性和一致性。

? 備份和恢復(fù)策略：制定完善的備份和恢復(fù)策略，定期備份集群數(shù)據(jù)，并測(cè)試恢復(fù)流程的可行性，以確保在發(fā)生故障時(shí)能夠快速恢復(fù)數(shù)據(jù)和服務(wù)。

? 監(jiān)控和告警：使用監(jiān)控工具對(duì)集群進(jìn)行實(shí)時(shí)監(jiān)控，并設(shè)置合理的告警閾值。當(dāng)集群出現(xiàn)異常情況時(shí)，能夠及時(shí)發(fā)現(xiàn)并處理。

? 設(shè)計(jì)思路

? MYSQL ==集群高可用== 使用雙主雙活+keepalived實(shí)現(xiàn)

? MYSQL ==數(shù)據(jù)監(jiān)控平臺(tái)== 使用mysqld_exporter+prometheus+Grafana三件套實(shí)現(xiàn)

? 集群及監(jiān)控平臺(tái)搭建完畢后可以實(shí)現(xiàn)企業(yè)內(nèi)部的mysql數(shù)據(jù)庫(kù)雙主機(jī)在線增加高可用性，通過(guò)keepalived的故障檢測(cè)和VIP漂移能力使得發(fā)生故障后使用者無(wú)感知，增加系統(tǒng)的容錯(cuò)能力，通過(guò)監(jiān)控平臺(tái)實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)監(jiān)控可視化

1.1 MySQL中的主從復(fù)制技術(shù)

MySQL 是基于它自身的 Bin-log 日志來(lái)完成數(shù)據(jù)的異步復(fù)制，因?yàn)?Bin-log 日志中會(huì)記錄所有對(duì)數(shù)據(jù)庫(kù)產(chǎn)生變更的語(yǔ)句，包括 DML 數(shù)據(jù)變更和 DDL 結(jié)構(gòu)變更語(yǔ)句，數(shù)據(jù)的同步過(guò)程如下：

在這里插入圖片描述

1.2 keepalived高可用方案

Keepalived是一個(gè)輕量級(jí)別的高可用解決方案，使用VRRP（Vritrual Router Redundancy Protocol,虛擬路由冗余協(xié)議)的VIP虛擬IP的漂移功能，實(shí)現(xiàn)單點(diǎn)故障轉(zhuǎn)移

在這里插入圖片描述

1.3 可視化監(jiān)控平臺(tái)三件套

? Mysqld_exporter：是一款輕量級(jí)的mysql監(jiān)控工具，用來(lái)收集MysQL數(shù)據(jù)庫(kù)相關(guān)指標(biāo)并將其暴露給prometheus進(jìn)行監(jiān)控和告警

? Prometheus：普羅米修斯，一個(gè)開源的服務(wù)監(jiān)控系統(tǒng)，它負(fù)責(zé)采集和存儲(chǔ)應(yīng)用的監(jiān)控指標(biāo)數(shù)據(jù)，并以可視化的方式進(jìn)行展示，以便于用戶實(shí)時(shí)掌握系統(tǒng)的運(yùn)行情況，并對(duì)異常進(jìn)行檢測(cè)

? Grafana：格拉法娜，是一個(gè)跨平臺(tái)的開源的度量分析和可視化工具,可以從prometheus獲取數(shù)據(jù)進(jìn)行可視化數(shù)據(jù)大屏展示。

總結(jié)：mysqld_exporter用于抓取mysql監(jiān)控指標(biāo)數(shù)據(jù)，prometheus接收到數(shù)據(jù)后進(jìn)行整理分析，grafana從prometheus獲取數(shù)據(jù)使用大屏模版進(jìn)行儀表盤展示

1.4 項(xiàng)目任務(wù)清單

1. 系統(tǒng)平臺(tái)部署

? 安裝VmWare17

? 虛擬出三臺(tái)計(jì)算機(jī)

? 安裝OpenEuler22.03 SP4 LTS 操作系統(tǒng)

? 系統(tǒng)設(shè)置：主機(jī)名、防火墻、SELinux、hosts映射、IP地址：

2. 安裝2臺(tái)MySql服務(wù)器

? 使用二進(jìn)制包進(jìn)行安裝

? 配置MySql系統(tǒng)服務(wù)

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

? 配置賬戶

? 配置主主復(fù)制

? 安裝keepalived并配置

4. 安裝配置監(jiān)控平臺(tái)

? 安裝Mysqld_Exporter+Prometheus+grafana-enterprise

? 配置監(jiān)控平臺(tái)組件

5. 壓力測(cè)試

二、項(xiàng)目實(shí)現(xiàn)步驟

項(xiàng)目拓?fù)浣Y(jié)構(gòu)

在這里插入圖片描述
軟硬件環(huán)境清單：

在這里插入圖片描述

2.1 OpenEuler系統(tǒng)安裝

2.1.1 創(chuàng)建虛擬機(jī)實(shí)例

? 第一步：文件菜單->新建虛擬機(jī)->典型->下一步

image-20230426105520639

? 第二步：稍后安裝操作系統(tǒng)

image-20230426105559441

? 第三步：選擇操作系統(tǒng)類型，由于OpenEuler22.03 LTS SP3使用Linux5.10內(nèi)核則選擇如下：

image-20231115113531955

? 第四步：命名虛擬機(jī)

在這里插入圖片描述

? 第五步：設(shè)置磁盤空間20G,動(dòng)態(tài)空間申請(qǐng)，設(shè)置為單個(gè)文件

在這里插入圖片描述

? 第六步：自定義硬件，設(shè)置硬件參數(shù)

在這里插入圖片描述

? 第七步：設(shè)置自定義硬件

? 內(nèi)存：推薦2GB

? 處理器：1顆、2核心

? ==新CD/DVD：適應(yīng)ISO映像文件，點(diǎn)擊瀏覽按鈕，選擇之前下載好的openEuler-22.03-LTS-SP2-x86_64-dvd.iso鏡像文件==

? 網(wǎng)絡(luò)適配器：選擇NAT模式

? 顯示器：去掉"加速3D圖形“的對(duì)鉤

? 最終：

image-20231115114047316

? 選擇關(guān)閉、完成

2.1.2 OpenEuler22.03操作系統(tǒng)的安裝部署

選擇語(yǔ)言：中文或英文

? 安裝信息摘要設(shè)置

image-20240524091942939

? 安裝目的地：顯示安裝位置，一般為硬盤，點(diǎn)擊自定義，然后點(diǎn)擊完成：

在這里插入圖片描述

? /boot：系統(tǒng)啟動(dòng)分區(qū)，推薦500M或1GB

? swap：交換分區(qū)，4G

? /：根分區(qū)，期望容量省略，表示將剩余空間全部分配

方法：點(diǎn)擊下圖的加號(hào)，設(shè)置掛載點(diǎn)及期望容量（重復(fù)多次）

image-20240524092325368

image-20240524092439307

image-20240524092505325

image-20240524092533410

? 點(diǎn)擊完成，接受更改

image-20240524092603436

? 網(wǎng)絡(luò)和主機(jī)名：打開網(wǎng)卡連接

image-20240524092626997

? 主機(jī)名：moniter、master1、master2，點(diǎn)擊應(yīng)用

? 配置：IPv4設(shè)置，手動(dòng)，分別配置IP192.168.88.163、192.168.88.161、192.168.88.162，子網(wǎng)掩碼255.255.255.0，網(wǎng)關(guān)192.168.88.2，DNS服務(wù)器192.168.88.2點(diǎn)擊保存

在這里插入圖片描述

? root賬戶密碼設(shè)置：密碼為OPENlab123

image-20240524092743232

? 創(chuàng)建用戶：創(chuàng)建一個(gè)普通賬戶KD，設(shè)置密碼：OPENlab123

在這里插入圖片描述

? 安裝完成后點(diǎn)擊"重啟系統(tǒng)"

image-20240524092900894

2.1.3 配置OpenEuler22.03

? 登錄

? 賬號(hào)：root

? 密碼：OPENlab123

在這里插入圖片描述

? 修改主機(jī)名

[root@moniter~]# hostnamectlset-hostname monitor

? 三臺(tái)主機(jī)重新設(shè)置登錄系統(tǒng)密碼為123

[root@moniter~]# passwd root
更改用戶 root 的密碼 。
新的密碼：
無(wú)效的密碼： 密碼少于8個(gè)字符
重新輸入新的密碼：
passwd：所有的身份驗(yàn)證令牌已經(jīng)成功更新。
[root@moniter~]#

? 重啟，重新連接

reboot

? 關(guān)閉三臺(tái)主機(jī)防火墻及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 #關(guān)閉防火墻
[root@monitor~]# systemctl disable firewalld # 取消開機(jī)啟動(dòng)
Removed/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed/etc/systemd/system/multi-user.target.wants/firewalld.service.
[root@monitor~]# reboot #重啟

? 三臺(tái)主機(jī)下載所需軟件并升級(jí)

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

? 三臺(tái)主機(jī)時(shí)間同步

[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 生產(chǎn)環(huán)境二進(jìn)制包安裝MySql

下載安裝包
網(wǎng)址鏈接: https://dev.mysql.com/downloads/mysql/

在這里插入圖片描述

2.2.1 解壓縮安裝包

? 連接xshell、在下shell點(diǎn)擊xftp圖標(biāo)

在這里插入圖片描述

在這里插入圖片描述

? 解壓安裝包、準(zhǔn)備工作：

[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@master1mysql-8.0.37-linux-glibc2.17-x86_64]# ls
bin docs include lib LICENSE man README share support-files

# 移動(dòng)到默認(rèn)安裝目錄，也可自行修改
[root@master1~]# cd~#~是一個(gè)特殊的符號(hào)，表示當(dāng)前用戶的主目錄
[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 # 創(chuàng)建名為mysql的用戶組
[root@master1mysql]# useradd-r-g mysql-s/bin/falsemysql # 創(chuàng)建名為 mysql 的系統(tǒng)用戶，將其添加到mysql用戶組中，并設(shè)置其登錄shell為/bin/false，以限制該用戶的登錄權(quán)限
#-r：創(chuàng)建系統(tǒng)帳戶
#-g：為所創(chuàng)建的用戶指定基本組的 id .如果該組 id 不存在會(huì)報(bào)錯(cuò)，如果存在則創(chuàng)建成功
#-s：用于指定所創(chuàng)建用戶可操作的腳本
## 刪除用戶命令：userdel
[root@master1mysql]# mkdir data # 創(chuàng)建用于存放MySQL數(shù)據(jù)文件目錄
# 設(shè)置mysql目錄的賬戶及工作組，生產(chǎn)環(huán)境中不要使用root
[root@master1mysql]# chown-R mysql:mysql/usr/local/mysql
#-R：表示遞歸修改指定目錄下所有文件及其子目錄的權(quán)限
# chmod 命令是用于給文件或目錄設(shè)置權(quán)限，如果是目錄，我們需要使用-R 選項(xiàng)，如果是文件這個(gè)選項(xiàng)可以不用指定。
# chown 命令是用于給文件或目錄修改所屬者和所屬組權(quán)限。

2.2.2 初始化工作

在這里插入圖片描述
master1：

# 初始化數(shù)據(jù)目錄
[root@master1mysql]# bin/mysqld--initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data # 注意：需要復(fù)制密碼
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 #生成臨時(shí)密碼：;Ay,miVu;9k1
[root@master1mysql]# bin/mysqld_safe--user=mysql & # 使用后臺(tái)方式以mysql用戶身份啟動(dòng)
#MySQL 服務(wù)器，mysqld_safe 是一個(gè)用于啟動(dòng)和監(jiān)控 MySQL 服務(wù)器的腳本
[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

初始化數(shù)據(jù)目錄: 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
# 初始化數(shù)據(jù)目錄
[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

生成臨時(shí)密碼：;Ay,miVu;9k1、+AshIk4eerzq

注意：此時(shí)上述命令執(zhí)行完畢處于后臺(tái)運(yùn)行狀態(tài)，需要另行啟動(dòng)一個(gè)終端

在這里插入圖片描述

[root@master1~]# cd "/usr/local/mysql"
[root@master1mysql]# ps-ef|grep mysql# 查看進(jìn)程運(yùn)行狀態(tài)
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

找到下面的文件進(jìn)行軟連接

[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
# 在當(dāng)前終端關(guān)閉運(yùn)行的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 設(shè)置mysql的配置文件

回到之前的終端，敲一個(gè)回車，顯示進(jìn)程以殺死

在這里插入圖片描述

新建配置文件，輸入以下內(nèi)容：

[root@master1mysql]# vim/etc/my.cnf # 新建配置文件，輸入以下內(nèi)容：

在這里插入圖片描述

[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

? 配置啟動(dòng)腳本：

[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 # 設(shè)置環(huán)境變量需添加如下語(yǔ)句

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

在這里插入圖片描述

? 配置啟動(dòng)腳本

#~是一個(gè)特殊的符號(hào)，表示當(dāng)前用戶的主目錄
[root@master1~]# source~/.bash_profile #重新加載當(dāng)前用戶的 .bash_profile 文件中的配置
[root@master1~]# systemctl daemon-reload # 重載系統(tǒng)配置
[root@master1~]# systemctlstartmysql
[root@master1~]#/usr/lib/systemd/systemd-sysv-install enable mysql # 開機(jī)啟動(dòng)
[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個(gè)主節(jié)點(diǎn)的配置文件

2. 創(chuàng)建一個(gè)用于同步數(shù)據(jù)的賬號(hào)

3. 建立2個(gè)主節(jié)點(diǎn)的相互復(fù)制

4.測(cè)試

2.3.1 master1節(jié)點(diǎn)配置

? 修改配置文件

[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

#------主節(jié)點(diǎn)配置-------
# 當(dāng)前節(jié)點(diǎn)在集群中的唯一標(biāo)識(shí)
server-id=1 #server-id=1，為集群中本機(jī)標(biāo)識(shí)，必須唯一

# 開啟bin-log日志，并為bin-log日志取個(gè)前綴名（有默認(rèn)值可不寫）
log-bin=mysql-bin-log

# 同步復(fù)制時(shí)過(guò)濾的庫(kù)（主要將一些不需要備份/同步庫(kù)寫進(jìn)來(lái)）
# 也可以通過(guò)binlog-do-db=xx1,xx2... 來(lái)指定要復(fù)制的目標(biāo)庫(kù)
binlog-ignore-db=mysql

# 指定bin-log日志的格式為混合模式（默認(rèn)為statement）
binlog_format=mixed

# 設(shè)置單個(gè)binlog日志文件的最大容量
max_binlog_size=1024M

#------從節(jié)點(diǎn)配置-------
# 開啟relay-log日志（同樣可以指定前綴名）
relay_log=mysql-relay-log

# 開啟存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器等內(nèi)容的同步功能
log_bin_trust_function_creators=true

# 同步執(zhí)行跳過(guò)一些錯(cuò)誤碼（防止同步寫入時(shí)出現(xiàn)錯(cuò)誤導(dǎo)致復(fù)制中斷）
slave_skip_errors=1062

#------自增序列配置-------
# 設(shè)置自增初始值為1
auto_increment_offset=1

# 設(shè)置自增步長(zhǎng)為2，自增序列為{1、3、5、7、9.....}
auto_increment_increment=2

server-id=1，為集群中本機(jī)標(biāo)識(shí)，必須唯一
由于master1即是主節(jié)點(diǎn)又是從節(jié)點(diǎn)，則必須設(shè)置===從節(jié)點(diǎn)配置==
為了保證2個(gè)節(jié)點(diǎn)數(shù)據(jù)的一致性，需要開啟自增序列配置，master1節(jié)點(diǎn)跳步為1 3 5 7 9…

? 創(chuàng)建用于數(shù)據(jù)同步的賬號(hào)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節(jié)點(diǎn)配置

[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
#------主節(jié)點(diǎn)配置-------
# 當(dāng)前節(jié)點(diǎn)在集群中的唯一標(biāo)識(shí)
server-id=2

# 開啟bin-log日志，并為bin-log日志取個(gè)前綴名（有默認(rèn)值可不寫）
log-bin=mysql-bin-log

# 同步復(fù)制時(shí)過(guò)濾的庫(kù)（主要將一些不需要備份/同步庫(kù)寫進(jìn)來(lái)）
# 也可以通過(guò)binlog-do-db=xx1,xx2... 來(lái)指定要復(fù)制的目標(biāo)庫(kù)
binlog-ignore-db=mysql

# 指定bin-log日志的格式為混合模式（默認(rèn)為statement）
binlog_format=mixed

# 設(shè)置單個(gè)binlog日志文件的最大容量
max_binlog_size=1024M

#------從節(jié)點(diǎn)配置-------
# 開啟relay-log日志（同樣可以指定前綴名）
relay_log=mysql-relay-log

# 開啟存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器等內(nèi)容的同步功能
log_bin_trust_function_creators=true

# 同步執(zhí)行跳過(guò)一些錯(cuò)誤碼（防止同步寫入時(shí)出現(xiàn)錯(cuò)誤導(dǎo)致復(fù)制中斷）
slave_skip_errors=1062

#------自增序列配置-------
# 設(shè)置自增初始值為2
auto_increment_offset=2

# 設(shè)置自增步長(zhǎng)為2，自增序列為{1、3、5、7、9.....}
auto_increment_increment=2

master2節(jié)點(diǎn)的server-id=2，不能和master1的server-id相同
設(shè)置自增初始值為2，則master2節(jié)點(diǎn)跳步為2 4 6 8 …

? 創(chuàng)建用于數(shù)據(jù)同步的賬號(hào)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 節(jié)點(diǎn)主從關(guān)系

? 由于建立的是主-主架構(gòu)集群，相互為對(duì)方的從節(jié)點(diǎn)，則兩個(gè)節(jié)點(diǎn)都通過(guò) root 賬號(hào)登錄

? 此時(shí)master1為從節(jié)點(diǎn)，master2為主節(jié)點(diǎn)

1. 先在master2上查看同步的日志名稱及同步點(diǎn)號(hào)

# 注意：在master2上執(zhí)行

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
# 日志數(shù)據(jù)點(diǎn)：664

2. master1 節(jié)點(diǎn)上建立復(fù)制關(guān)系：
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 節(jié)點(diǎn)主從關(guān)系

此時(shí)master2為從節(jié)點(diǎn)，master1為主節(jié)點(diǎn)

1. 先在master1上查看同步的日志名稱及同步點(diǎn)號(hào)

# 注意：在master1上執(zhí)行

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
# 日志數(shù)據(jù)點(diǎn)：664

2.master2節(jié)點(diǎn)上建立復(fù)制關(guān)系：
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 局部測(cè)試

測(cè)試主主架構(gòu)是否能相互同步數(shù)據(jù)

2.4.1 master1執(zhí)行

[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.

? 建立數(shù)據(jù)庫(kù)

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)

此時(shí)master1(主)–master2(從)構(gòu)建完畢

2.4.2 master2執(zhí)行

? 創(chuàng)建表

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上查看是否有同步表數(shù)據(jù)

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(主)構(gòu)建完畢

2.5 Keepalived故障轉(zhuǎn)移的高可用環(huán)境

在這里插入圖片描述

1. 2臺(tái)MySql服務(wù)器安裝keepalived軟件

2.配置服務(wù)

3. 模擬故障進(jìn)行測(cè)試

2.5.1 master1節(jié)點(diǎn)配置

? 安裝安裝包

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

? master1節(jié)點(diǎn)配置keepalived

[root@master1~]# vim/etc/keepalived/keepalived.conf
# 刪除所有，拷貝下列配置數(shù)據(jù)

!Configuration Fileforkeepalived

global_defs {
  router_id mysql-master1 # keepalived服務(wù)器的一個(gè)標(biāo)識(shí)，每臺(tái)機(jī)子不同
}
vrrp_instance VI_1 {
  state BACKUP # 指定keepalived的角色, BACKUP模式將根據(jù)優(yōu)先級(jí)決定主或從
  interface ens33   # 監(jiān)測(cè)的網(wǎng)卡名，注意自己網(wǎng)卡名稱
  virtual_router_id51  # 虛擬路由標(biāo)識(shí)，確保和master2相同
  priority100  # 用來(lái)選舉master的數(shù)值
  nopreempt
  advert_int1
  authentication {   # 認(rèn)證區(qū)域
    auth_type PASS
    auth_pass1111
  }
  virtual_ipaddress {   # 指定VIP地址
   192.168.88.200
  }
}

# 虛擬服務(wù)器，需要指定虛擬IP地址和服務(wù)端口，IP與端口之間用空格隔開
virtual_server192.168.88.2003306{
  delay_loop6  # 設(shè)置運(yùn)行情況檢查時(shí)間，單位是秒
  lb_algo rr   # 設(shè)置后端調(diào)度算法
  lb_kind DR   # 設(shè)置LVS實(shí)現(xiàn)負(fù)載均衡的機(jī)制
  persistence_timeout50  # 會(huì)話保持時(shí)間，單位是秒
  protocol TCP   # 指定轉(zhuǎn)發(fā)協(xié)議類型
  real_server192.168.88.1613306{   # 真實(shí)服務(wù)器IP地址及端口
  notify_down/etc/keepalived/chk_mysql.sh
    weight1  # 配置服務(wù)節(jié)點(diǎn)的權(quán)值
    TCP_CHECK {
      connect_port3306  # 健康檢查端口
      connect_timeout3  # 連接超時(shí)時(shí)間
      retry3  # 重連次數(shù)
      delay_before_retry3  # 重連間隔時(shí)間
    }
  }
}

配置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
# 通過(guò)端口記錄數(shù)判斷mysql是否運(yùn)行，mysql停止后終止keepalived，當(dāng)然也可以在搶救一下

[root@master1~]# chmod+x/etc/keepalived/chk_mysql.sh # 設(shè)置腳本執(zhí)行權(quán)限
[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 # 注意會(huì)產(chǎn)生新的VIP
  inet6 fe80::20c:29ff4098/64scopelink noprefixroute
   valid_lft forever preferred_lft forever
,multicast,up,lower_up>,up,lower_up>

2.5.2 master2節(jié)點(diǎn)配置

? 安裝安裝包

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

在這里插入圖片描述

? master2節(jié)點(diǎn)配置keepalived

[root@master2~]# vim/etc/keepalived/keepalived.conf
# 刪除所有，拷貝下列配置數(shù)據(jù)

!Configuration Fileforkeepalived

global_defs {
  router_id mysql-master2 # 注意與master1區(qū)分開來(lái)
}
vrrp_instance VI_1 {
  state BACKUP # 指定keepalived的角色, BACKUP模式將根據(jù)優(yōu)先級(jí)決定主或從
  interface ens33   # 監(jiān)測(cè)的網(wǎng)卡名，注意自己網(wǎng)卡名稱
  virtual_router_id51  # 虛擬路由標(biāo)識(shí)，確保和master2相同
  priority50  # 用來(lái)選舉master的數(shù)值
  nopreempt
  advert_int1
  authentication {   # 認(rèn)證區(qū)域
    auth_type PASS
    auth_pass1111
  }
  virtual_ipaddress {   # 指定VIP地址
   192.168.88.200
  }
}

# 虛擬服務(wù)器，需要指定虛擬IP地址和服務(wù)端口，IP與端口之間用空格隔開
virtual_server192.168.88.2003306{
  delay_loop6  # 設(shè)置運(yùn)行情況檢查時(shí)間，單位是秒
  lb_algo rr   # 設(shè)置后端調(diào)度算法
  lb_kind DR   # 設(shè)置LVS實(shí)現(xiàn)負(fù)載均衡的機(jī)制
  persistence_timeout50  # 會(huì)話保持時(shí)間，單位是秒
  protocol TCP   # 指定轉(zhuǎn)發(fā)協(xié)議類型
  real_server192.168.88.1623306{   # master2地址及端口
  notify_down/etc/keepalived/chk_mysql.sh
    weight1  # 配置服務(wù)節(jié)點(diǎn)的權(quán)值
    TCP_CHECK {
      connect_port3306  # 健康檢查端口
      connect_timeout3  # 連接超時(shí)時(shí)間
      retry3  # 重連次數(shù)
      delay_before_retry3  # 重連間隔時(shí)間
    }
  }
}

[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 局部測(cè)試

原理：當(dāng)master1服務(wù)器宕機(jī)后，VIP會(huì)自動(dòng)漂移至master2服務(wù)器并繼續(xù)向外提供mysql服務(wù)

1. 在master1中停用mysql

2. 查看VIP是否漂移

3. 故障修復(fù)

? master1中執(zhí)行

[root@master1~]# systemctl status mysql # 查看mysql狀態(tài)
● 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 # 此時(shí)VIP正在監(jiān)聽
   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 # 模擬宕機(jī)
[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中執(zhí)行

[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中恢復(fù)msyql服務(wù)和keepalived服務(wù)，注意必須==先恢復(fù)msyql服務(wù)==

[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恢復(fù)后，VIP不會(huì)被強(qiáng)占
,multicast,up,lower_up>,up,lower_up>

2.6 安裝監(jiān)控平臺(tái)

? 涉及的軟件及端口

服務(wù)	端口
Prometheus	9090
Mysqld_exporter	9194
Grafana	3000

2.6.1 部署Mysqld_exporter

Mysql_exporter是用來(lái)收集MysQL數(shù)據(jù)庫(kù)相關(guān)指標(biāo)且需要連接到數(shù)據(jù)庫(kù)并有相關(guān)權(quán)限
下載安裝包并解壓縮：https://prometheus.io/download/

在這里插入圖片描述
部署Mysqld_exporter之前，先在minitor主機(jī)上安裝好MySql，如2.2

#192.168.88.163monitor主機(jī)操作
[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臺(tái)mysql節(jié)點(diǎn)新建
password=123456# 登錄密碼
host=192.168.88.200# 使用VIP訪問(wèn)
port=3306

? 2臺(tái)mysql節(jié)點(diǎn)創(chuàng)建用戶并授權(quán)

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操作，由于設(shè)置了主-主集群架構(gòu)，master1創(chuàng)建賬戶后會(huì)同步到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    |%    |  # 已經(jīng)同步
|mback      |%    |
|root      |%    |
|mysql.infoschema|localhost|
|mysql.session  |localhost|
|mysql.sys    |localhost|
+------------------+-----------+
6rowsinset(0.00sec)

mysql>exit
Bye

? 配置mysqld_exporter的系統(tǒng)服務(wù)

# monitor節(jié)點(diǎn)操作
[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

? 刷新服務(wù)配置并啟動(dòng)服務(wù)

# monitor節(jié)點(diǎn)操作
[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

? 通過(guò)瀏覽器輸入鏈接: http://192.168.88.163:9104/metrics，查看監(jiān)控抓取msyql服務(wù)器的數(shù)據(jù)

在這里插入圖片描述

2.6.2 部署Promethues

Prometheus是一個(gè)開源系統(tǒng)監(jiān)控和警報(bào)工具包,現(xiàn)在是一個(gè)獨(dú)立的開源項(xiàng)目，獨(dú)立于任何公司維護(hù)。
架構(gòu)圖：

在這里插入圖片描述
下載安裝包并解壓縮： 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系統(tǒng)服務(wù)

# 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

? 刷新服務(wù)配置并啟動(dòng)服務(wù)

[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

? 通過(guò)瀏覽器輸入鏈接: http://192.168.88.163:9090，查看管理頁(yè)面，通過(guò)鏈接: http://192.168.88.163:9090/metrics查看監(jiān)控?cái)?shù)據(jù)

在這里插入圖片描述

在這里插入圖片描述

? Mysqld_exporter對(duì)接Prometheus

# monitor操作，打開文件，添加如下內(nèi)容，注意對(duì)齊格式,# 注意：是監(jiān)控機(jī)IP和Mysqld_exporter端口
[root@monitor~]# vim/usr/local/prometheus/prometheus.yml

scrape_configs:
-job_name: "prometheus"  #自己定義的監(jiān)控的job_name
  static_configs:  # 靜態(tài)指定，targets中的 host:port/metrics 將會(huì)作為metrics抓取對(duì)象
  -targets: ["192.168.88.163:9090"]
   
-job_name: "mysql"
  static_configs:    # 配置靜態(tài)規(guī)則，直接指定抓取的ip:port
  -targets: ["192.168.88.163:9104"]
    labels:
     instance: mysqld_exporter

scrape配置集合，?于定義監(jiān)控的?標(biāo)對(duì)象（target）的集合，以及描述如何抓取 （scrape）相關(guān)指標(biāo)數(shù)據(jù)的配置參數(shù)；
通常，每個(gè)scrape配置對(duì)應(yīng)于?個(gè)單獨(dú)的作業(yè)（job），
?每個(gè)targets可通過(guò)靜態(tài)配置（static_configs）直接給出定義，也可基于Prometheus?持的服務(wù)發(fā)現(xiàn)機(jī)制進(jìn) ??動(dòng)配置；

在這里插入圖片描述

? 重啟Prometheus，輸入鏈接: http://192.168.88.163:9090 測(cè)試是否監(jiān)控MySql

[root@monitor~]# systemctl restart prometheus

graph：查詢收集到的指標(biāo)數(shù)據(jù)，并提供簡(jiǎn)單的繪圖

在這里插入圖片描述

在這里插入圖片描述

2.6.3 部署Grafana

Grafana是一個(gè)功能強(qiáng)大、靈活性高、易于使用的數(shù)據(jù)可視化和監(jiān)控工具，廣泛應(yīng)用于IT運(yùn)維、應(yīng)用性能監(jiān)控、工業(yè)物聯(lián)網(wǎng)等領(lǐng)域

使用Grafana，用戶可以輕松地創(chuàng)建各種圖表、圖形和面板，以直觀和動(dòng)態(tài)的方式展示數(shù)據(jù)趨勢(shì)、指標(biāo)和警報(bào)。其靈活的插件系統(tǒng)和豐富的圖形化選項(xiàng)使用戶能夠根據(jù)自己的需求定制儀表板，并將其集成到現(xiàn)有的監(jiān)控系統(tǒng)中

在這里插入圖片描述
連接xshell，點(diǎn)擊xftp圖標(biāo)，找到 grafana-enterprise-11.1.2-1.x86_64.rpm 文件，傳輸?shù)教摂M機(jī)

? 下載并解壓縮：

[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/# 啟用

在這里插入圖片描述

? 啟動(dòng)

[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.

? 關(guān)聯(lián)Promethues并設(shè)置儀表盤模版

 瀏覽器其中輸入：http://192.168.88.163:3000

在這里插入圖片描述

初始賬戶/密碼：admin/admin
設(shè)置新的密碼：123456

在這里插入圖片描述

在設(shè)置界面中選擇Home > Connections > Data sources > prometheus

在這里插入圖片描述

在這里插入圖片描述

 設(shè)置prometheus監(jiān)聽地址：http://192.168.88.163:9090

在這里插入圖片描述

 下滑點(diǎn)擊 save&test

在這里插入圖片描述

在這里插入圖片描述

選擇監(jiān)控模版來(lái)顯示mysql的關(guān)鍵指標(biāo)，模版ID為7362

在這里插入圖片描述

在這里插入圖片描述

鏈接: https://grafana.com/grafana/dashboards ，在頁(yè)面中搜索 node exporter ，選擇適合的面板，搜索mysql，選擇適合的面板

? 7362：

在這里插入圖片描述

? 11074

在這里插入圖片描述

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

實(shí)時(shí)查看Grafana服務(wù)器的日志，以便追蹤可能的錯(cuò)誤或警告信息。

結(jié)果圖應(yīng)當(dāng)如下：

在這里插入圖片描述

2.7 壓力測(cè)試

查看VIP所在服務(wù)器，==停止當(dāng)前節(jié)點(diǎn)的mysql==，查看VIP是否漂移，刷新監(jiān)控界面，看是否高可用
MySQL自帶的壓力測(cè)試工具——Mysqlslap

# 修改最大連接數(shù)
vim/etc/my.cnf
max_connections=1024
systemctl restart mysql
# 完成壓測(cè)，查看儀表盤
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