本文已参与「新人创作礼」活动，一起开启掘金创作之路。
之前在windows上 配置了mysql 的异步复制，可以参考如下文档
下边开始配置组复制，myql版本为8.0.28
3台mysql 的端口号为 8030 , 8031,8032
1. 编写8030配置文件
配置文件下载地址 gitee.com/xuejianxino…
[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;
