MYSQL数据库集群高可用和数据监控平台
MYSQL数据库集群高可用和数据监控平台
- 一、项目说明
- 1.1
- 三级目录
- 二、项目实现步骤
- 2.1 OpenEuler系统安装
- 2.1.1 创建虚拟机实例
- 2.1.2 OpenEuler22.03操作系统的安装部署
- 2.1.3 配置OpenEuler22.03
- 2.2 生产环境二进制包安装MySql
- 2.2.1 解压缩安装包
- 2.2.2 初始化工作
- 2.2.3 设置mysql的配置文件
- 2.3 mysql集群搭建
- 2.3.1 master1节点配置
- 2.3.2 master2节点配置
- 2.3.3 建立master1 节点主从关系
- 2.3.4 建立master2 节点主从关系
- 2.4 局部测试
- 2.4.1 master1执行
- 2.4.2 master2执行
- 2.5 Keepalived故障转移的高可用环境
- 2.5.1 master1节点配置
- 2.5.2 master2节点配置
- 2.5.3 局部测试
- 2.6 安装监控平台
- 2.6.1 部署Mysqld_exporter
- 2.6.2 部署Promethues
- 2.6.3 部署Grafana
- 2.7 压力测试
- 2.7.
一、项目说明
1.1
三级目录
二、项目实现步骤
2.1 OpenEuler系统安装
2.1.1 创建虚拟机实例
- 第一步：文件菜单->新建虚拟机->典型->下一步
- 第二步：稍后安装操作系统
- 第三步：选择操作系统类型，由于OpenEuler22.03 LTS SP3使用Linux5.10内核则选择如下：
- 第四步：命名虚拟机
- 第五步：设置磁盘空间20G,动态空间申请，设置为单个文件
- 第六步：自定义硬件，设置硬件参数
- 第七步：设置自定义硬件
- 内存：推荐2GB
- 处理器：1颗、2核心
- 新CD/DVD：适应ISO映像文件，点击浏览按钮，选择之前下载好的openEuler-22.03-LTS-SP2-x86_64-dvd.iso镜像文件
- 网络适配器：选择NAT模式
- 显示器：去掉"加速3D图形“的对钩
- 最终：
- 选择关闭、完成
2.1.2 OpenEuler22.03操作系统的安装部署
选择语言：中文或英文
-
安装信息摘要设置
-
安装目的地：显示安装位置，一般为硬盘，点击自定义，然后点击完成：
- /boot：系统启动分区，推荐500M或1GB
- swap：交换分区，4G
- /：根分区，期望容量省略，表示将剩余空间全部分配
方法：点击下图的加号，设置挂载点及期望容量（重复多次）
- 点击完成，接受更改
- 网络和主机名：打开网卡连接
-
主机名：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点击保存
- 配置：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
- 创建用户：创建一个普通账户KD，设置密码：OPENlab123
- 安装完成后点击"重启系统"
2.1.3 配置OpenEuler22.03
- 登录
- 账号：root
- 密码：OPENlab123
- 修改主机名
[root@moniter ~]# hostnamectl set-hostname monitor
- 三台主机重新设置登录系统密码为 123
[root@moniter ~]# passwd root
更改用户 root 的密码 。
新的密码：
无效的密码： 密码少于 8 个字符
重新输入新的密码：
passwd：所有的身份验证令牌已经成功更新。
[root@moniter ~]#
- 重启，重新连接
reboot
- 关闭三台主机防火墙及SELinux
[root@monitor ~]# vi /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# 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
yum update
- 三台主机时间同步
[root@master2 ~]# vim /etc/chrony.conf
[root@master2 ~]# systemctl restart chronyd
[root@master2 ~]# chronyc sources -v.-- Source mode '^' = server, '=' = peer, '#' = local clock./ .- Source state '*' = current best, '+' = combined, '-' = not combined,
| / 'x' = may be in error, '~' = too variable, '?' = unusable.
|| .- xxxx [ yyyy ] +/- zzzz
|| Reachability register (octal) -. | xxxx = adjusted offset,
|| Log2(Polling interval) --. | | yyyy = measured offset,
|| \ | | zzzz = estimated error.
|| | | \
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 203.107.6.88 2 6 17 4 +785us[+7288us] +/- 31ms
[root@master2 ~]# chronyc sources -v.-- Source mode '^' = server, '=' = peer, '#' = local clock./ .- Source state '*' = current best, '+' = combined, '-' = not combined,
| / 'x' = may be in error, '~' = too variable, '?' = unusable.
|| .- xxxx [ yyyy ] +/- zzzz
|| Reachability register (octal) -. | xxxx = adjusted offset,
|| Log2(Polling interval) --. | | yyyy = measured offset,
|| \ | | zzzz = estimated error.
|| | | \
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 203.107.6.88 2 6 17 6 +785us[+7288us] +/- 31ms
[root@master2 ~]# chronyc sources -v.-- Source mode '^' = server, '=' = peer, '#' = local clock./ .- Source state '*' = current best, '+' = combined, '-' = not combined,
| / 'x' = may be in error, '~' = too variable, '?' = unusable.
|| .- xxxx [ yyyy ] +/- zzzz
|| Reachability register (octal) -. | xxxx = adjusted offset,
|| Log2(Polling interval) --. | | yyyy = measured offset,
|| \ | | zzzz = estimated error.
|| | | \
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 203.107.6.88 2 6 17 6 +785us[+7288us] +/- 31ms
[root@master2 ~]# chronyc sources -v.-- Source mode '^' = server, '=' = peer, '#' = local clock./ .- Source state '*' = current best, '+' = combined, '-' = not combined,
| / 'x' = may be in error, '~' = too variable, '?' = unusable.
|| .- xxxx [ yyyy ] +/- zzzz
|| Reachability register (octal) -. | xxxx = adjusted offset,
|| Log2(Polling interval) --. | | yyyy = measured offset,
|| \ | | zzzz = estimated error.
|| | | \
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 203.107.6.88 2 6 17 8 +785us[+7288us] +/- 31ms
[root@master2 ~]# chronyc sources -v.-- Source mode '^' = server, '=' = peer, '#' = local clock./ .- Source state '*' = current best, '+' = combined, '-' = not combined,
| / 'x' = may be in error, '~' = too variable, '?' = unusable.
|| .- xxxx [ yyyy ] +/- zzzz
|| Reachability register (octal) -. | xxxx = adjusted offset,
|| Log2(Polling interval) --. | | yyyy = measured offset,
|| \ | | zzzz = estimated error.
|| | | \
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 203.107.6.88 2 6 17 8 +785us[+7288us] +/- 31ms
[root@master2 ~]# timedatectlLocal time: 六 2025-02-15 15:42:41 CSTUniversal time: 六 2025-02-15 07:42:41 UTCRTC time: 六 2025-02-15 07:42:41Time zone: Asia/Shanghai (CST, +0800)
System clock synchronized: yesNTP service: activeRTC in local TZ: 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
[root@master1 mysql-8.0.37-linux-glibc2.17-x86_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@master1 mysql]# groupadd mysql # 创建名为mysql的用户组
[root@master1 mysql]# useradd -r -g mysql -s /bin/false mysql # 创建名为 mysql 的系统用户，将其添加到mysql用户组中，并设置其登录shell为/bin/false，以限制该用户的登录权限
# -r：创建系统帐户
# -g：为所创建的用户指定基本组的 id .如果该组 id 不存在会报错，如果存在则创建成功
# -s：用于指定所创建用户可操作的脚本
## 删除用户命令：userdel
[root@master1 mysql]# mkdir data # 创建用于存放MySQL数据文件目录
# 设置mysql目录的账户及工作组，生产环境中不要使用root
[root@master1 mysql]# chown -R mysql:mysql /usr/local/mysql
# -R：表示递归修改指定目录下所有文件及其子目录的权限
# chmod 命令是用于给文件或目录设置权限，如果是目录，我们需要使用 -R 选项，如果是文件这个选项可以不用指定。
# chown 命令是用于给文件或目录修改所属者和所属组权限。
2.2.2 初始化工作
master1：
# 初始化数据目录
[root@master1 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data # 注意：需要复制密码
2025-02-16T07:15:42.081778Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.37) initializing of server in progress as process 5283
2025-02-16T07:15:42.086988Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-02-16T07:15:42.326267Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-02-16T07:15:43.605271Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ;Ay,miVu;9k1 #生成临时密码：;Ay,miVu;9k1
[root@master1 mysql]# bin/mysqld_safe --user=mysql & # 使用后台方式以mysql用户身份启动
#MySQL 服务器，mysqld_safe 是一个用于启动和监控 MySQL 服务器的脚本
[1] 5328
[root@master1 mysql]# Logging to '/usr/local/mysql/data/master1.err'.
2025-02-16T07:17:08.488162Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data初始化数据目录: https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/data-directory-initialization.html
master2：
[root@master2 mysql]# groupadd mysql
[root@master2 mysql]# useradd -r -g mysql -s /bin/false mysql
[root@master2 mysql]# mkdir data
[root@master2 mysql]# chown -R mysql:mysql /usr/local/mysql
# 初始化数据目录
[root@master2 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usrlocal/mysql --datadir=/usr/local/mysql/data
2025-02-16T05:53:39.774317Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.37) initializing of server in progress as process 1854
2025-02-16T05:53:39.774344Z 0 [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-16T05:53:39.781243Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-02-16T05:53:40.015072Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-02-16T05:53:40.481966Z 0 [Warning] [MY-013829] [Server] Missing data directory for ICU regular expressions: /usrlocal/mysql/lib/private/.
2025-02-16T05:53:41.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@master1 mysql]# ps -ef | grep mysql# 查看进程运行状态
root 5328 5048 0 15:17 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql 5410 5328 0 15:17 pts/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 5458 0 15:20 pts/1 00:00:00 grep --color=auto mysql
[root@master1 mysql]# cd /usr/local/mysql
找到下面的文件进行软连接
[root@master1 mysql]# ln -s /usr/lib64/libtinfo.so.6.3 /usr/lib64/libtinfo.so.5
[root@master1 mysql]# ln -s /usr/lib64/libncurses.so.6.3 /usr/lib64/libncurses.so.5
## 删除软链接：rm -rf
[root@master1 mysql]# bin/mysql -uroot -p
Enter password: # 粘贴之前的初始密码：;Ay,miVu;9k1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.37Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456'; #修改密码
Query OK, 0 rows affected (0.00 sec)mysql> flush privileges; #刷新
Query OK, 0 rows affected (0.04 sec)mysql> use mysql; # 查看账户信息
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)mysql> exit
Bye
[root@master1 mysql]# ps -ef | grep mysql
root 5328 5048 0 15:17 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql 5410 5328 0 15:17 pts/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 5458 0 15:25 pts/1 00:00:00 grep --color=auto mysql
# 在当前终端关闭运行的mysql
[root@master1 mysql]# kill -9 5328
[root@master1 mysql]# kill -9 5410
[root@master1 mysql]# kill -9 5533
-bash: kill: (5533) - No such process
[root@master1 mysql]# kill -9 5458Connection closed.Disconnected from remote host(master1) at 15:26:04.Type `help' to learn how to use Xshell prompt.
[C:\~]$
2.2.3 设置mysql的配置文件
回到之前的终端，敲一个回车，显示进程以杀死
新建配置文件，输入以下内容：
[root@master1 mysql]# vim /etc/my.cnf # 新建配置文件，输入以下内容：
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
socket = /tmp/mysql.sock
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-storage-engine=INNODB
log_error = error.log
[root@monitor mysql]# vim /etc/my.cnf
[root@monitor mysql]# cd /usr/local/mysql/support-files
[root@master1 support-files]# cp -a mysql.server /etc/init.d/mysql
[root@master1 support-files]# vim /etc/init.d/mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
[root@master1 support-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 ~]# systemctl start mysql
[root@master1 ~]# /usr/lib/systemd/systemd-sysv-install enable mysql # 开机启动
[root@master1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> update mysql.user set host="%" where user="root";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)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.3 mysql集群搭建
- 修改2个主节点的配置文件
- 创建一个用于同步数据的账号
- 建立2个主节点的相互复制
- 测试
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 ~]# systemctl start mysql
[root@master1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create user 'mback'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.06 sec)mysql> grant replication slave on *.* to 'mback'@'%';
Query OK, 0 rows affected (0.05 sec)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 ~]# systemctl start mysql
[root@master2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create user 'mback'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.03 sec)mysql> grant replication slave on *.* to 'mback'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> 2.3.3 建立master1 节点主从关系
- 由于建立的是主-主架构集群，相互为对方的从节点，则两个节点都通过 root 账号登录
- 此时master1为从节点，master2为主节点
- 先在master2上查看同步的日志名称及同步点号
# 注意：在master2上执行mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-log.000001 | 664 | | mysql | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)# 日志文件：mysql-bin-log.000001
# 日志数据点：664
- master1 节点上建立复制关系：
master_host='192.168.88.162
[root@master1 ~]# mysql -uroot -p
Enter password:
...mysql> change master to master_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, 0 rows affected, 9 warnings (0.07 sec)mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.88.162Master_User: mbackMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin-log.000001Read_Master_Log_Pos: 664Relay_Log_File: mysql-relay-log.000002Relay_Log_Pos: 330Relay_Master_Log_File: mysql-bin-log.000001Slave_IO_Running: Yes # 这里必须是yesSlave_SQL_Running: Yes # 这里必须是yesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 664Relay_Log_Space: 540Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2Master_UUID: 5ef6aa2e-ec2a-11ef-8cb0-000c293e0dbaMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.01 sec)2.3.4 建立master2 节点主从关系
此时master2为从节点，master1为主节点
- 先在master1上查看同步的日志名称及同步点号
# 注意：在master1上执行mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-log.000001 | 664 | | mysql | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)# 日志文件：mysql-bin-log.000001
# 日志数据点：664
- master2 节点上建立复制关系：
master_host='192.168.88.161
[root@master2 ~]# mysql -uroot -p
Enter password:
...mysql> change master to master_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, 0 rows affected, 9 warnings (0.07 sec)mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.88.161Master_User: mbackMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin-log.000001Read_Master_Log_Pos: 843Relay_Log_File: mysql-relay-log.000002Relay_Log_Pos: 330Relay_Master_Log_File: mysql-bin-log.000001Slave_IO_Running: Yes # 这里必须为yesSlave_SQL_Running: Yes # 这里必须为yesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 843Relay_Log_Space: 540Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: d4e30409-ec35-11ef-acfe-000c29244098Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)2.4 局部测试
测试主主架构是否能相互同步数据
2.4.1 master1执行
[root@master1 ~]# systemctl status mysql
● mysql.service - LSB: start and stop MySQLLoaded: loaded (/etc/rc.d/init.d/mysql; generated)Active: active (running) since Mon 2025-02-17 13:19:16 CST; 2min 25s agoDocs: man:systemd-sysv-generator(8)Process: 1151 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS)Tasks: 45 (limit: 8934)Memory: 450.3MCGroup: /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月 17 13:19:13 master1 systemd[1]: Starting LSB: start and stop MySQL...
2月 17 13:19:16 master1 mysql[1151]: Starting MySQL... SUCCESS!
2月 17 13:19:16 master1 systemd[1]: Started LSB: start and stop MySQL.[root@master1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- 建立数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)mysql> create database test1;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.00 sec)- 在master2上查看是否同步
[root@master2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.01 sec)此时 master1(主)–master2(从) 构建完毕
2.4.2 master2执行
- 创建表
mysql> use test1
Database changed
mysql> create table back_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, 0 rows affected, 1 warning (0.03 sec)mysql> desc back_test;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| user_id | int | NO | | NULL | |
| user_name | varchar(255) | NO | | NULL | |
| user_sex | varchar(255) | NO | | NULL | |
| password | varchar(255) | NO | | NULL | |
| register_time | datetime | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)- 在master1上查看是否有同步表数据
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| back_test |
+-----------------+
1 row in set (0.00 sec)mysql> desc back_test;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| user_id | int | NO | | NULL | |
| user_name | varchar(255) | NO | | NULL | |
| user_sex | varchar(255) | NO | | NULL | |
| password | varchar(255) | NO | | NULL | |
| register_time | datetime | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)至此 master1(从)–master2(主) 构建完毕
2.5 Keepalived故障转移的高可用环境
- 2台MySql服务器安装keepalived软件
- 配置服务
- 模拟故障进行测试
2.5.1 master1节点配置
- 安装安装包
[root@master1 ~]# yum install keepalived -y
- master1节点配置keepalived
[root@master1 ~]# vim /etc/keepalived/keepalived.conf
# 删除所有，拷贝下列配置数据! Configuration File for keepalivedglobal_defs {router_id mysql-master1 # keepalived服务器的一个标识，每台机子不同
}
vrrp_instance VI_1 {state BACKUP # 指定keepalived的角色, BACKUP模式将根据优先级决定主或从interface ens33 # 监测的网卡名，注意自己网卡名称virtual_router_id 51 # 虚拟路由标识，确保和master2相同priority 100 # 用来选举master的数值nopreemptadvert_int 1authentication { # 认证区域auth_type PASSauth_pass 1111}virtual_ipaddress { # 指定VIP地址192.168.88.200}
}# 虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
virtual_server 192.168.88.200 3306 {delay_loop 6 # 设置运行情况检查时间，单位是秒lb_algo rr # 设置后端调度算法lb_kind DR # 设置LVS实现负载均衡的机制persistence_timeout 50 # 会话保持时间，单位是秒protocol TCP # 指定转发协议类型real_server 192.168.88.161 3306 { # 真实服务器IP地址及端口notify_down /etc/keepalived/chk_mysql.shweight 1 # 配置服务节点的权值TCP_CHECK {connect_port 3306 # 健康检查端口connect_timeout 3 # 连接超时时间retry 3 # 重连次数delay_before_retry 3 # 重连间隔时间}}
}
配置mysql健康检查脚本
# 配置mysql健康检查脚本[root@master1 ~]# vim /etc/keepalived/chk_mysql.sh#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; thenkillall keepalived
fi
# 通过端口记录数判断mysql是否运行，mysql停止后终止keepalived，当然也可以在抢救一下
[root@master1 ~]# chmod +x /etc/keepalived/chk_mysql.sh # 设置脚本执行权限
[root@master1 ~]# systemctl start keepalived
[root@master1 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability MonitorLoaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)Active: active (running) since Mon 2025-02-17 16:35:29 CST; 2s agoProcess: 2525 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)Main PID: 2526 (keepalived)Tasks: 3 (limit: 8934)Memory: 2.1MCGroup: /system.slice/keepalived.service├─ 2526 /usr/sbin/keepalived -D├─ 2528 /usr/sbin/keepalived -D└─ 2529 /usr/sbin/keepalived -D2月 17 16:35:29 master1 Keepalived_vrrp[2529]: (VI_1) removing VIPs.
2月 17 16:35:29 master1 Keepalived_healthcheckers[2528]: WARNING - default user 'keepalived_script' for s>
2月 17 16:35:29 master1 Keepalived[2526]: Startup complete
2月 17 16:35:29 master1 Keepalived_vrrp[2529]: (VI_1) Entering BACKUP STATE (init)
2月 17 16:35:29 master1 Keepalived_vrrp[2529]: VRRP sockpool: [ifindex( 2), family(IPv4), proto(112), fd>
2月 17 16:35:29 master1 Keepalived_healthcheckers[2528]: Please add a #! shebang to script /etc/keepalive>
2月 17 16:35:29 master1 Keepalived_healthcheckers[2528]: SECURITY VIOLATION - check scripts are being exe>
2月 17 16:35:29 master1 Keepalived_healthcheckers[2528]: Initializing ipvs
2月 17 16:35:29 master1 Keepalived_healthcheckers[2528]: Gained quorum 1+0=1 <= 1 for VS [192.168.88.200]>
2月 17 16:35:29 master1 Keepalived_healthcheckers[2528]: Activating healthchecker for service [192.168.88>
lines 1-22/22 (END)
^C
[root@master1 ~]# ip ad
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000link/ether 00:0c:29:24:40:98 brd ff:ff:ff:ff:ff:ffinet 192.168.88.161/24 brd 192.168.88.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.88.200/32 scope global ens33valid_lft forever preferred_lft forever # 注意会产生新的VIPinet6 fe80::20c:29ff:fe24:4098/64 scope link noprefixroute valid_lft forever preferred_lft forever
2.5.2 master2节点配置
- 安装安装包
[root@master2 ~]# yum install keepalived -y
- master2节点配置keepalived
[root@master2 ~]# vim /etc/keepalived/keepalived.conf
# 删除所有，拷贝下列配置数据! Configuration File for keepalivedglobal_defs {router_id mysql-master2 # 注意与master1区分开来
}
vrrp_instance VI_1 {state BACKUP # 指定keepalived的角色, BACKUP模式将根据优先级决定主或从interface ens33 # 监测的网卡名，注意自己网卡名称virtual_router_id 51 # 虚拟路由标识，确保和master2相同priority 50 # 用来选举master的数值nopreemptadvert_int 1authentication { # 认证区域auth_type PASSauth_pass 1111}virtual_ipaddress { # 指定VIP地址192.168.88.200}
}# 虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
virtual_server 192.168.88.200 3306 {delay_loop 6 # 设置运行情况检查时间，单位是秒lb_algo rr # 设置后端调度算法lb_kind DR # 设置LVS实现负载均衡的机制persistence_timeout 50 # 会话保持时间，单位是秒protocol TCP # 指定转发协议类型real_server 192.168.88.162 3306 { # master2地址及端口notify_down /etc/keepalived/chk_mysql.shweight 1 # 配置服务节点的权值TCP_CHECK {connect_port 3306 # 健康检查端口connect_timeout 3 # 连接超时时间retry 3 # 重连次数delay_before_retry 3 # 重连间隔时间}}
}
[root@master2 ~]# vim /etc/keepalived/chk_mysql.sh#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; thenkillall keepalived
fi
[root@master2 ~]# chmod +x /etc/keepalived/chk_mysql.sh
[root@master2 ~]# systemctl start keepalived
2.5.3 局部测试
原理：当master1服务器宕机后，VIP会自动漂移至master2服务器并继续向外提供mysql服务
- 在master1中停用mysql
- 查看VIP是否漂移
- 故障修复
- master1中执行
[root@master1 ~]# systemctl status mysql # 查看mysql状态
● mysql.service - LSB: start and stop MySQLLoaded: loaded (/etc/rc.d/init.d/mysql; generated)Active: active (running) since Mon 2025-02-17 13:37:40 CST; 4h 44min ago[root@master1 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability MonitorLoaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)Active: active (running) since Mon 2025-02-17 16:35:29 CST; 1h 46min ago[root@master1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000link/ether 00:0c:29:24:40:98 brd ff:ff:ff:ff:ff:ffinet 192.168.88.161/24 brd 192.168.88.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.88.200/32 scope global ens33 # 此时VIP正在监听valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe24:4098/64 scope link noprefixroute valid_lft forever preferred_lft forever[root@master1 ~]# systemctl stop mysql # 模拟宕机
[root@master1 ~]# ip a # VIP消失
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000link/ether 00:0c:29:24:40:98 brd ff:ff:ff:ff:ff:ffinet 192.168.88.161/24 brd 192.168.88.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe24:4098/64 scope link noprefixroute valid_lft forever preferred_lft forever
[root@master1 ~]# systemctl status keepalived # 已停用
○ keepalived.service - LVS and VRRP High Availability MonitorLoaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)Active: inactive (dead)- master2中执行
[root@master2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000link/ether 00:0c:29:3e:0d:ba brd ff:ff:ff:ff:ff:ffinet 192.168.88.162/24 brd 192.168.88.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.88.200/32 scope global ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe3e:dba/64 scope link noprefixroute valid_lft forever preferred_lft forever- master1中恢复msyql服务和keepalived服务，注意必须先恢复msyql服务
[root@master1 ~]# systemctl start mysql
[root@master1 ~]# systemctl start keepalived
[root@master1 ~]# ip ad
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000link/ether 00:0c:29:24:40:98 brd ff:ff:ff:ff:ff:ffinet 192.168.88.161/24 brd 192.168.88.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe24:4098/64 scope link noprefixroute valid_lft forever preferred_lft forever
# 即使master1恢复后，VIP不会被强占
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.163 monitor主机操作
[root@monitor ~]# ls
anaconda-ks.cfg mysqld_exporter-0.15.1.linux-amd64.tar.gz
[root@monitor ~]# tar xvf mysqld_exporter-0.15.1.linux-amd64.tar.gz
mysqld_exporter-0.15.1.linux-amd64/
mysqld_exporter-0.15.1.linux-amd64/LICENSE
mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter
mysqld_exporter-0.15.1.linux-amd64/NOTICE
[root@monitor ~]# mv mysqld_exporter-0.15.1.linux-amd64 /usr/local/mysqld_exporter
[root@monitor ~]# cd /usr/local/mysqld_exporter
[root@monitor mysqld_exporter]# ls
LICENSE mysqld_exporter NOTICE
- 常见配置文件
[root@monitor mysqld_exporter]# vim .my.cnf # 注意为隐藏文件
[client]
user = exporter # 该账户需要再2台mysql节点新建
password = 123456 # 登录密码
host = 192.168.88.200 # 使用VIP访问
port = 3306
- 2台mysql节点创建用户并授权
master1操作
[root@master1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create user 'exporter'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.07 sec)mysql> grant process,replication client, select on *.* to 'exporter'@'%';
Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)mysql> exit
Bye
master2操作，由于设置了主-主集群架构，master1创建账户后会同步到master2，只需查看即可
[root@master2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select user ,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| exporter | % | # 已经同步
| mback | % |
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)mysql> exit
Bye
- 配置mysqld_exporter的系统服务
# monitor节点操作
[root@monitor ~]# vim /usr/lib/systemd/system/mysqld_exporter.service
[Unit]
Description=https://prometheus.io[Service]
Restart=on-failure
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf --web.listen-address=:9104[Install]
WantedBy=multi-user.target
- 刷新服务配置并启动服务
# monitor节点操作
[root@monitor ~]# systemctl daemon-reload
[root@monitor ~]# systemctl start mysqld_exporter.service
[root@monitor ~]# systemctl enable mysqld_exporter.service
[root@monitor ~]# systemctl status mysqld_exporter.service
● mysqld_exporter.service - https://prometheus.ioLoaded: loaded (/usr/lib/systemd/system/mysqld_exporter.service; enabled; vendor preset: disabled)Active: active (running) since Tue 2025-02-18 16:43:41 CST; 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@monitor prometheus]# ls
console_libraries consoles LICENSE NOTICE prometheus prometheus.yml promtool
- 新建prometheus账户
[root@monitor prometheus]# useradd --no-create-home --shell /bin/false prometheus
[root@monitor prometheus]# chown -R prometheus:prometheus /usr/local/prometheus
- 配置prometheus系统服务
# monitor操作
[root@monitor ~]# vim /usr/lib/systemd/system/prometheus.service
[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/prometheus/prometheus --config.file=/usr/local/prometheus/prometheus.yml --storage.tsdb.path=/usr/local/prometheus/data[Install]
WantedBy=multi-user.target
- 刷新服务配置并启动服务
[root@monitor ~]# systemctl daemon-reload
[root@monitor ~]# systemctl start prometheus
[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 - PrometheusLoaded: loaded (/usr/lib/systemd/system/prometheus.service; enabled; vendor preset: disabled)Active: active (running) since Tue 2025-02-18 17:09:21 CST; 1min 5s agoMain PID: 2840 (prometheus)Tasks: 7 (limit: 8934)Memory: 23.0MCGroup: /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.ymlscrape_configs:- job_name: "prometheus" #自己定义的监控的job_namestatic_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，用户可以轻松地创建各种图表、图形和面板，以直观和动态的方式展示数据趋势、指标和警报。其灵活的插件系统和丰富的图形化选项使用户能够根据自己的需求定制仪表板，并将其集成到现有的监控系统中
- 下载并解压缩：
[root@monitor ~]# yum install grafana-enterprise-11.1.2-1.x86_64.rpm
- 修改配置文件
################################# Server #################################
[server]
# Protocol (http, https, h2, socket)
protocol = http # 启用# This is the minimum TLS version allowed. By default, this value is empty.
Accepted values are: TLS1.2, TLS1.3. If nothing is set TLS1.2 would be taken
;min_tls_version = ""# The ip address to bind to, empty will bind to all interfaces
;http_addr =# The http port to use
http_port = 3000 # 启用# The public facing domain name used to access grafana from a browser
domain = localhost # 启用# Redirect to correct domain if host header does not match domain
# Prevents DNS rebinding attacks
;enforce_domain = false# The full public facing url you use in browser, used for redirects and emails
# If you use reverse proxy and sub path specify full url (with sub path)
root_url = %(protocol)s://%(domain)s:%(http_port)s/ # 启用
- 启动
[root@monitor ~]# systemctl start grafana-server
[root@monitor ~]# systemctl status grafana-server
[root@monitor ~]# systemctl enable grafana-server
- 关联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
2.7 压力测试
查看VIP所在服务器，停止当前节点的mysql，查看VIP是否漂移，刷新监控界面，看是否高可用
MySQL自带的压力测试工具——Mysqlslap
2.7.
# 修改最大连接数
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