测试环境
3台MySQL 8.0.36
系统Redhat 8.6
内存16G
一 MySQL搭建
主节点和2个辅助节点安装
注:启用binlog、relaylog、guid
vi /etc/my.cnf
[mysqld]
tls_version=''
log_bin=/data/mysqllogs/logbin.log
relay_log=/data/mysqllogs/relay-bin
gtid_mode = ON
enforce-gtid-consistency = ON
二 配置第一台组复制
所有节点修改配置文件
vi /etc/my.cnf
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,ARCHIVE
skip_name_resolve
binlog_rows_query_log_events = ON
log_replica_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
transaction_write_set_extraction = XXHASH64
binlog_checksum = NONE
group_replication_single_primary_mode = ON
plugin-load = group_replication.so
group_replication = FORCE_PLUS_PERMANENT
group_replication_enforce_update_everywhere_checks = OFF
group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER
group_replication_start_on_boot = OFF
#以下按照实际情况配置
group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_local_address = "t-mysql11:33061"
group_replication_group_seeds = "t-mysql11:33061,t-mysql12:33061,t-mysql13:33061"
参数配置解释:
#设置server_id不同服务器要不同ID号
#禁用不支持存储引擎disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#在 MySQL 8.0.20 之前(包括 MySQL 8.0.20),以下设置是 还需要:binlog_checksum=NONE
#group_replication_local_address为本地址和端口号
#group_replication_group_seeds为集群中所有节点地址和端口号
#group_replication_group_name:所有group名必须相同。可以使用 mysql> select uuid();获得一个随机值。
#group_replication中的主机名必须可解析,可以用本地解析/etc/hosts。注意不要解析成127.0.0.1
所有节点重启服务
systemctl restart mysqld
三 主节点配置
登录数据库,创建同步账号。
主节点创建用户
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER greplica@'%' IDENTIFIED BY '密码';
mysql> GRANT REPLICATION SLAVE ON *.* TO greplica@'%';
mysql> FLUSH PRIVILEGES;
主节点启动引导
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
检查主节点状态
SELECT * FROM performance_schema.replication_group_members;
四 添加副本节点
登录副本库添加节点
mysql> SET SQL_LOG_BIN=0;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='greplica', SOURCE_PASSWORD='密码' FOR CHANNEL 'group_replication_recovery';
mysql> SET SQL_LOG_BIN=1;
mysql> START GROUP_REPLICATION;
主库检查节点
SELECT * FROM performance_schema.replication_group_members;
五 创建测试数据
主节点创建测试数据
mysql> CREATE DATABASE mytest;
mysql> USE mytest;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'A');
成员服务器检查同步
mysql> SELECT * FROM mytest.t1;