色妞视频亚州,久久人妻一区精品,激情短篇爽文jiqing

一、項目說明

該項目共分為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

聲明：本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人，不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用，如有內容侵權或者其他違規問題，請聯系本站處理。舉報投訴