MySQL MGR高可用集群搭建
MySQL Group Replication(MGR)-5.7后期之秀-单(荐)/多主
支持多主模式，但官方推荐单主模式：
● 多主模式下，客户端可以随机向MySQL节点写入数据
● 单主模式下，MGR集群会选出primary节点负责写请求，primary节点与其它节点都可以进行读请求处理
优点：
● 基本无延迟，延迟比异步的小很多
● 支持多写模式，但是目前还不是很成熟
● 数据的强一致性，可以保证数据事务不丢失
缺点：
● 仅支持innodb
● 只能用在GTID模式下，且日志格式为row格式
适用的业务场景：
● 希望对对写服务提供高可用，又不想安装第三方软件
● 数据强一致的场景
搭建环境
3台CentOS7系统虚拟机，2G内存2核CPU，10G硬盘
单主MGR拓扑
系统环境配置
每一台主机都需要进行下面的配置
设置静态IP
# 根据不同的主机修改下面的值
cd /etc/sysconfig/network-scripts
sed -i 's/dhcp/static/g' ifcfg-ens33
sudo cat >> ifcfg-ens33 <<-'EOF'
IPADDR=192.168.0.79
GATEWAY=192.168.0.1
NETMASK=255.255.255.0
DNS1=114.114.114.114
DNS2=8.8.8.8
EOF
service network restart修改主机名
#注意配置的主机是那一套 n0 192.168.0.79,n1 192.168.0.78,192.168.0.77 n2
hostnamectl set-hostname n0配置Hosts
sudo cat > /etc/hosts <<-'EOF'
192.168.0.79 n0
192.168.0.78 n1
192.168.0.77 n2
EOF更换YUM镜像
# CentOS 7仓库已经被归档，现在使用默认的yum镜像源会直接报错
# 备份默认的 YUM
mv /etc/yum.repos.d /etc/yum.repos.d.backup
# 设置新的 YUM 目录
mkdir /etc/yum.repos.d
# 下载阿里 YUM 配置到该目录中
sudo curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
# 重建缓存
yum clean all && yum makecache关闭selinux
# 非生产环境可以这样做
sed -i 's/enforcing/disabled/' /etc/selinux/config
setenforce 0防火墙设置
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=24901/tcp --permanent
firewall-cmd --reloadMySQL安装与配置
MySQL 安装
# 配置镜像源
cd /home/
wget --no-check-certificate https://repo.mysql.com/mysql80-community-release-el7-5.noarch.rpm
yum localinstall -y mysql80-community-release-el7-5.noarch.rpm
# 安装MySQL,这里我并没有指定版本，文档中安装的是8.0.28，我这里安装的是8.0.41这个版本
# 最好指定版本，不要用太新的版本，可能会有参数不一样或Spring Boot驱动版本或yaml参数问题
# 不添加--nogpgcheck会出现GPG密钥报错
yum install -y mysql-community-server --nogpgcheck
修改配置文件my.cnf
Master节点配置（n0）
sudo cat >> /etc/my.cnf <<-'EOF'
#使用mysql_native_password密码策略，防止navicat连不上mysql8
default_authentication_plugin=mysql_native_password
#设置MySQL插件所在目录，因为MGR基于插件，所以必须设置插件路径
plugin_dir=/usr/lib64/mysql/plugin
#服务器编号，Master=1
server_id=1
#开启binlog的GTID模式
gtid_mode=ON
#开启后MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行
enforce_gtid_consistency=ON
#关闭binlog校验
binlog_checksum=NONE
#定义用于事务期间哈希写入提取的算法，组复制模式下必须设置为 XXHASH64。
transaction_write_set_extraction=XXHASH64
#确定组复制恢复时是否应该应用 SSL，通常设置为“开”，但默认设置为“关”。
loose-group_replication_recovery_use_ssl=ON
#该服务器的实例所在复制组的名称，必须是有效的 UUID，所有节点必须相同。
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
#确定服务器是否应该在服务器启动期间启动组复制。
loose-group_replication_start_on_boot=OFF
#大坑2：
#为复制组中其他的成员提供的网络地址，指定为“主机:端口”的格式化字符串。
#很多人想当然认为端口应该是3306，起始不然，MGR需要开启新端口24901同步交换
#所以这里不要写错，同时，前面我们配置了hosts文件做了主机名与IP的映射，这里直接写主机名即可
loose-group_replication_local_address="n0:24901"
#用于建立新成员到组的连接组成员列表。
#这个列表指定为由分隔号间隔的组成员网络地址列表，类似 host1:port1、host2:port2 的格式。
#同样采用n0~n2的主机名替代
loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"
#配置此服务器为引导组，这个选项必须仅在一台服务器上设置，
#并且仅当第一次启动组或者重新启动整个组时。成功引导组启动后，将此选项设置为关闭。
loose-group_replication_bootstrap_group=OFF
EOFSlave节点配置（n1）
sudo cat >> /etc/my.cnf <<-'EOF'
default_authentication_plugin=mysql_native_password
plugin_dir=/usr/lib64/mysql/plugin
#设置唯一的服务器编号
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
#这个参数决定primary节点到secondary节点的请求是否为基于 RSA 密钥对的密码交换所需的公钥
loose-group_replication_recovery_get_public_key=ON
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
#设置本机地址n1:24901
loose-group_replication_local_address="n1:24901"
loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"
loose-group_replication_bootstrap_group=OFF
EOFSlave节点配置（n2）
sudo cat >> /etc/my.cnf <<-'EOF'
default_authentication_plugin=mysql_native_password
plugin_dir=/usr/lib64/mysql/plugin
#设置唯一的服务器编号
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
#这个参数决定primary节点到secondary节点的请求是否为基于 RSA 密钥对的密码交换所需的公钥
loose-group_replication_recovery_get_public_key=ON
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
#设置本机地址n2:24901
loose-group_replication_local_address="n2:24901"
loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"
loose-group_replication_bootstrap_group=OFF
EOF启动MySQL并配置账号
# 启动MySQl服务器
systemctl start mysqld
# 获取初始密码
grep 'temporary password' /var/log/mysqld.log
# 运行结果：
2025-03-07T10:58:14.577426Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ucl*RZz%y8lj
# 登录MySQL
mysql -uroot -pucl*RZz%y8lj
# 修改root密码为asAS123456!
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'asAS123456!';
# 创建rpl_user账户，此账户用于实现主从数据同步
CREATE USER rpl_user@'%' IDENTIFIED BY 'asAS123456!';
# 赋予主从同步权限
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
#创建一个远程连接用户，这个用户用在Navcate、JDBC登录的时候，直接用root登录不好
create user 'remote'@'%' identified with mysql_native_password by 'asAS123456!';
#为remote用户赋予所有数据库资源的访问权限，熟悉grant的小伙伴可以自己调整
grant all privileges on *.* to remote@'%';
# 删除已产生的Binlog
# 前面执行了很多高权限的命令，如果不清空，在RelayLog重放时，这些命令会无法正确执行，导导致从属服务器卡死在"RECEVERING"状态
RESET MASTER;安装MGR插件
在登录MySQL的情况下
# 安装MGR插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
# 查看结果，出现PLUGIN_STATUS=ACTIVE，说明安装成功了
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication' \G
进行组网数据同步
Master节点配置（n0）
# 我们在 primary.cnf 配置文件中把 group_replication_bootstrap_group 参数设置成 OFF，在 primary 服务器启动时并不会直接启动复制组，通过下面的命令动态的开启复制组是我们的集群更安全。
# 第一条命令是标记当前节点作为集群的主服务器
#第二条命令是创建组
#第三条命令关闭了，是因为已经在创建的组中成为了主服务器，这个操作是一次性的，为了防止其他错误就将它关闭了
# 在Master（n0）节点上运行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
Slave节点配置（n1,n2）
#指定主从账户与指定通信频道
CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="asAS123456!" FOR CHANNEL 'group_replication_recovery';
#开启组网数据同步
START GROUP_REPLICATION;
当两个从节点都运行完毕后，运行下面结果进行验证。
SELECT * FROM performance_schema.replication_group_members;
ProxySQL代理安装
环境配置
设置静态IP
在新的服务器上需要设置，我这里依旧在192.168.0.79（n0）上运行所以不需要重新配置
# 根据不同的主机修改下面的值
cd /etc/sysconfig/network-scripts
sed -i 's/dhcp/static/g' ifcfg-ens33
sudo cat >> ifcfg-ens33 <<-'EOF'
IPADDR=192.168.0.69
GATEWAY=192.168.0.1
NETMASK=255.255.255.0
DNS1=114.114.114.114
DNS2=8.8.8.8
EOF
service network restart防火墙设置
# 开放防火墙，6032是ProxySQL的管理端口，6033是ProxySQL的客户端接入端口
firewall-cmd --zone=public --add-port=6032/tcp --permanent
firewall-cmd --zone=public --add-port=6033/tcp --permanent
firewall-cmd --reload安装MySQL
# 配置镜像源
cd /home/
wget --no-check-certificate https://repo.mysql.com/mysql80-community-release-el7-5.noarch.rpm
yum localinstall -y mysql80-community-release-el7-5.noarch.rpm
# 安装MySQL,这里我并没有指定版本，安装的是8.0.41这个版本
# 不添加--nogpgcheck会出现GPG密钥报错
yum install -y mysql-community-client --nogpgcheck安装ProxySQL
cd /home/
wget --no-check-certificate https://manongbiji.oss-cn-beijing.aliyuncs.com/ittailkshow/mgr/download/proxysql-2.2.0-1-centos7.x86_64.rpm
yum localinstall -y proxysql-2.2.0-1-centos7.x86_64.rpm --nogpgcheck
# 启动SQLProxy服务
service proxysql start登入ProxySQL Admin，进行设置
# ProxySQL Admin，它是一个伪装为MySQL的ProxySQL服务端
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'n0',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'n1',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'n2',3306);
load mysql servers to runtime;
save mysql servers to disk;
# 查看配置结果
select * from mysql_servers;
在主节点（n0）进行配置
下面需要在主节点192.168.0.79（no）的mysql数据库中运行这些SQL，这样可以同步到其他节点
创建监控账号与程序账号给ProxySQL使用
# MySQL降低密码强度
set global validate_password.policy=0;
set global validate_password.length=4;
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;
FLUSH PRIVILEGES;创建gr_member_routing_candidate_status视图，这是主从故障切换关键所在
# 下面的SQL最好分开执行
# 切换
use sys;
# 1
CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);
# 2
CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
END
# 3
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert'
from performance_schema.replication_group_member_stats where member_id=my_id();回到 ProxySQL 进行配置
# 注意，此处又回到了ProxySQL上，不是在主节点的MySQL上执行
# 如果之前你退出了，使用下面的命令登入
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 在ProxySQL中设置监控账号与程序账号
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@1025';
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)
values('proxysql','proxysql@1025',1,10,1);
# 读写分组
# 主负责写、从负责读，当MGR主库切换后，代理自动识别主从。
# ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40，
# 注意：max_transactions_behind 是设置延迟大小，可以给大点,建议自己去开个并行复制。
insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,
offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);
# 启用规则
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;
状态检查
status：写组10、备写组20、读组30、离线组40
select hostgroup_id, hostname, port,status from runtime_mysql_servers;
错误检查
通过下面命令查看后台执行日志，如有错误在error里面会显示
select hostname,port,viable_candidate,read_only,transactions_behind,error from mysql_server_group_replication_log order by time_start_us desc;配置读写分离规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',30,1);
load mysql query rules to runtime;
save mysql query rules to disk;
大坑一（Unknown system variable 'query_cache_size' ）
Spring Boot 调用SQL时报错Unknown system variable 'query_cache_size' 。
可能是pom.xml的mysql-connector-java太老，MySQL版本是8.0.x以上，那么驱动版本也得是8.0.以上
如果你绕过ProxySQL代理，Spring Boot直接通过主库3306端口，发现可以正常执行SQL，那么就是proxysql的问题
proxysql目前安装最新的，内置的mysql版本也才是5.5.30的，所以如果你的数据库是8.0及以上的。
一定要记得修改proxysql内置mysql的版本号 登上proxysql的应用端执行以下命令
# 8.0.xu以上就行，我这里是修改为41，因为我mysql主从节点都是41
update global_variables set variable_value="8.0.41 (ProxySQL)" where variable_name='mysql-server_version';
load mysql variables to run;
save mysql variables to disk;我现在MySQL版本是8.0.41
POM文件的mysql-connector-java点进去查看的是8.0.27
<!--mysql jdbc-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>yaml
spring:
datasource:
url: jdbc:mysql://192.168.0.79:6033/workdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
username: proxysql
password: proxysql@1025
driver-class-name: com.mysql.cj.jdbc.Driver服务器重启后重新组网
所有服务proxySQL和所有节点全部重启后，需要重新组网，proxySQL正常启动即可
# 登录proxySQL
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 查询节点状态
select hostgroup_id, hostname, port,status from runtime_mysql_servers;所有节点全部都重启了，需要先在主节点上运行命令，再到从节点运行命令。
# 主节点运行
mysql -uroot -p...
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点运行
mysql -uroot -p...
START GROUP_REPLICATION;如果是某个节点掉了，无论主从都执行以下命令即可
mysql -uroot -p...
START GROUP_REPLICATION;
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果