redhat8.9 安装 MySQL 8.4.7 三节点MGR

一、 环境配置

IP	版本	角色
192.168.1.10	redhat 8.9	主
192.168.1.11	redhat 8.9	从1
192.168.1.12	redhat 8.9	从2

 

1. 关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

2. 关闭 SELinux

setenforce 0
sed -i 's/^SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

## 安装 MySQL 8.4（所有节点）

1. 创建 mysql 用户（三节点）

useradd -m -d /home/mysql -s /bin/bash mysql
usermod -aG mysql mysql
echo 'mysql' | passwd mysql --stdin 

2. 创建安装目录（三节点）

mkdir /opt/mysql
mkdir /opt/logs
mkdir /home/mysql/etc
chown -R mysql:mysql /opt/mysql /mysql /opt/logs /home/mysql/

3. 下载并解压 MySQL 8.4 安装包（我是下载的Percona MySQL包）（主节点）

tar -zxvf Percona-Server-8.4.7-7-Linux.x86_64.glibc2.28.tar.gz -C /opt/mysql --strip-components=1
chown -R mysql:mysql /opt/mysql/
scp -r /opt/mysql/* mysql@192.168.1.11:/opt/mysql/
scp -r /opt/mysql/* mysql@192.168.1.12:/opt/mysql/
scp /home/mysql/etc/my.cnf mysql@192.168.1.11:/home/mysql/etc
scp /home/mysql/etc/my.cnf mysql@192.168.1.12:/home/mysql/etc

4. 配置系统环境变量（三节点）

su - mysql
echo 'export PATH=/opt/mysql/bin:$PATH' >> .bash_profile
source .bash_profile

二、 配置 MGR 核心参数（所有节点）

-- 创建 MySQL 配置文件 /home/mysql/etc/my.cnf

[mysqld]
basedir = /opt/mysql
datadir = /mysql/data
socket = /mysql/data/mysql.sock
pid-file = /mysql/data/mysqld.pid
log-error = /opt/log/mysqld.log
port = 3306
user = mysql
server_id = 10 # 每个节点唯一，如节点1=10，节点2=11，节点3=12
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
-- MGR 核心配置 -- 启用 GTID（MGR 必需）
gtid_mode = ON
enforce_gtid_consistency = ON
-- 启用二进制日志
log_bin = /mysql/binlog/mysql-bin
-- 启用中继日志
relay_log = /mysql/data/mysql-relay-bin
relay_log_recovery = ON
-- MGR 插件配置
plugin_load_add = group_replication.so
group_replication_group_name = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee' # 自定义 UUID，所有节点一致 ,可用uuidgen生成
group_replication_local_address = '192.168.1.10:33061' # 本机IP+MGR通信端口，每个节点改IP
group_replication_group_seeds = '192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061' # 所有节点的MGR地址
group_replication_bootstrap_group = OFF # 仅初始化集群时设为ON，其余节点OFF
group_replication_single_primary_mode = true # 单主模式（推荐）
group_replication_enforce_update_everywhere_checks = OFF # 单主模式设为OFF
mysql_native_password=ON # 保持原mysql5密码插件，可选
-- binlog_transaction_dependency_tracking参数已弃用
-- 禁用超级只读（避免主节点只读）
super_read_only = OFF
read_only = OFF

三、 搭建 MGR 集群

1. 初始化操作（所有节点）

su - mysql
cp /home/mysql/etc/my.cnf /home/mysql/etc/my.cnf.bak
vi /home/mysql/etc/my.cnf
mysqld --defaults-file=/home/mysql/etc/my.cnf --initialize-insecure
mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
mysql -uroot -p --socket=/mysql/data/mysql.sock
alter user 'root'@'localhost' identified by '12345678';
flush privileges;
install plugin group_replication soname 'group_replication.so';
install plugin clone soname 'mysql_clone.so';
shutdown;

-- 取消注释（所有节点）

vi /home/mysql/etc/my.cnf
mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
mysql -uroot -p12345678 --socket=/mysql/data/mysql.sock

2. 配置mgr同步用户（主库执行）

CREATE USER 'repl'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'repl';
GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3. 配置 MGR 复制通道（所有节点）

CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

4. 启动 MGR 集群（仅主节点执行）

set global group_replication_bootstrap_group = ON;
start group_replication;
set global group_replication_bootstrap_group = OFF;

5. 验证集群状态

select a.member_id master_uuid,a.member_host,a.member_port mgrpot,a.member_state mgrstat,a.member_role mgrrole, b.COUNT_TRANSACTIONS_IN_QUEUE cfct_qsz, b.COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE apl_qsz, b.COUNT_TRANSACTIONS_CHECKED ckd_t, b.COUNT_CONFLICTS_DETECTED cfct_dtd, replace(a.channel_name,'group_replication','gr') chan_name from performance_schema.replication_group_members a, performance_schema.replication_group_member_stats b where a.MEMBER_ID=b.member_id order by member_role,member_host;

输出中 MEMBER_STATE 为 ONLINE 表示主节点启动成功。

6. 加入其他节点（从节点，如 192.168.1.11/12）

-- 注意先通过上一步完成初始化

reset binary logs and gtids;
START GROUP_REPLICATION;

-- 再次验证集群状态

7. 数据同步验证 sql

-- 在主节点创建测试库/表

CREATE DATABASE mgr_test;
use mgr_test;
CREATE TABLE test_tb (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO test_tb VALUES (1, 'mgr_test');

-- 在从节点查询，验证数据同步

use mgr_test;
SELECT * FROM test_tb; -- 应显示 (1, 'mgr_test')