mysql8 window 单机配置Group Replication(单主和多主)

 之前在windows上 配置了mysql 的异步复制，可以参考如下文档

mysql8 window安装,链式复制，双主复制,数据库的负载均衡_xuejianxinokok的博客-优快云博客by xuejianxinokok@163.com 2021年3月25日 周四 15:06:43====================================1. 下载地址https://dev.mysql.com/downloads/mysql/2. 下载文件名称为: mysql-8.0.23-winx64.zip 到 3. 安装准备4. 初始化种子库4.1 在D:\soft\mysql\mysql8023\bin 目录下执行以下命令mysqld...https://blog.youkuaiyun.com/xuejianxinokok/article/details/118695575

下边开始配置组复制，myql版本为8.0.28

3台mysql 的端口号为 8030 , 8031,8032

1. 编写8030配置文件

配置文件下载地址

mysqldoc: mysql学习的一些文档和总结 - Gitee.com

[mysqld]
port=8030
default_authentication_plugin=mysql_native_password
basedir=D:/soft/mysql/mysql8023/soft
datadir=D:/soft/mysql/mysql8023/data/8030
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character-set-server=utf8mb4

#默认为REPEATABLE-READ,容易产生死锁,修改为RC
transaction-isolation= READ-COMMITTED
#READ-COMMITTED 隔离级别下binlog_format 必须是ROW
binlog_format = ROW

#复制相关
server-id = 8030
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"


log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

#在linux上是 group_replication.dll ,windows上为 group_replication.dll
plugin_load_add='group_replication.dll'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_bootstrap_group=off

#由于是单机复制需要配置 report_host  https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-locally.html
report_host=127.0.0.1
#注意这里是18030
group_replication_local_address= "127.0.0.1:18030"
group_replication_group_seeds= "127.0.0.1:18030,127.0.0.1:18031,127.0.0.1:18032"

#复制白名单
#group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8"
#多主复制
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=O

2. 编写8031配置文件

[mysqld]

port=8031
default_authentication_plugin=mysql_native_password
basedir=D:/soft/mysql/mysql8023/soft
datadir=D:/soft/mysql/mysql8023/data/8031
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character-set-server=utf8mb4

#默认为REPEATABLE-READ,容易产生死锁,修改为RC
transaction-isolation= READ-COMMITTED
#READ-COMMITTED 隔离级别下binlog_format 必须是ROW
binlog_format = ROW

#复制相关
server-id = 8031
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"


log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64


plugin_load_add='group_replication.dll'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_bootstrap_group=off

report_host=127.0.0.1
group_replication_local_address= "127.0.0.1:18031"
group_replication_group_seeds= "127.0.0.1:18030,127.0.0.1:18031,127.0.0.1:18032"

#复制白名单
#group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8"
#多主复制
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=O

3. 编写8032配置文件

[mysqld]

port=8032
default_authentication_plugin=mysql_native_password
basedir=D:/soft/mysql/mysql8023/soft
datadir=D:/soft/mysql/mysql8023/data/8032
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character-set-server=utf8mb4

#默认为REPEATABLE-READ,容易产生死锁,修改为RC
transaction-isolation= READ-COMMITTED
#READ-COMMITTED 隔离级别下binlog_format 必须是ROW
binlog_format = ROW

#复制相关
server-id = 8032
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"


log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64


plugin_load_add='group_replication.dll'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_bootstrap_group=off

report_host=127.0.0.1
group_replication_local_address= "127.0.0.1:18032"
group_replication_group_seeds= "127.0.0.1:18030,127.0.0.1:18031,127.0.0.1:18032"


#复制白名单
#group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8"
#多主复制
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=O

4. 复制数据库

把本地8023的种子库 复制3份，依次修改为 8030,8031,8032

 

 5. 安装服务并启动服务

mysqld --install mysql8030 --defaults-file="D:/soft/mysql/mysql8023/conf/my8030.conf"
mysqld --install mysql8031 --defaults-file="D:/soft/mysql/mysql8023/conf/my8031.conf"
mysqld --install mysql8032 --defaults-file="D:/soft/mysql/mysql8023/conf/my8032.conf"

最好把服务类型设置为 手动

 6. 配置服务账户并开始复制

依次启动 mysql8030, mysql8031, mysql8032

在 mysql8030上执行

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='password'  FOR CHANNEL 'group_replication_recovery';
# 只在第一个节点设置
SET GLOBAL group_replication_bootstrap_group=ON;        
START GROUP_REPLICATION;
#只在第一个节点设置
SET GLOBAL group_replication_bootstrap_group=OFF;   

在 mysql8031 和 mysql8032上执行

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='password'  FOR CHANNEL 'group_replication_recovery';  
START GROUP_REPLICATION;

7. 测试复制

查询复制信息

SELECT * FROM performance_schema.replication_group_members;

# 在8030 上执行
CREATE DATABASE test;
USE test;

CREATE TABLE `demo` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`) USING BTREE
)
ENGINE=InnoDB
;


# 在8030 上执行
INSERT INTO test.demo(name) VALUES('8030');

# 在8031 上执行
INSERT INTO test.demo(name) VALUES('8031');

# 在8032 上执行
INSERT INTO test.demo(name) VALUES('8032');

8. 重启后的问题

需要重新执行以下脚本

#需要在主节点8030执行

SET GLOBAL group_replication_bootstrap_group=ON;        
START GROUP_REPLICATION;
#只在第一个节点设置
SET GLOBAL group_replication_bootstrap_group=OFF;     


#在次节点执行8031,8032
START GROUP_REPLICATION;

9. 参考文档https://blog.youkuaiyun.com/wnccmyr/article/details/108096651https://blog.youkuaiyun.com/wnccmyr/article/details/108096651

MySQL :: MySQL 8.0 Reference Manual :: 18 Group Replicationhttps://dev.mysql.com/doc/refman/8.0/en/group-replication.html