mysql8.0.x-mgr组复制
概述
在mysql8.x搭建组复制，发现优化5.7.x的不展示节点类型的bug.丢弃了部分参数
关于mysql停机后重启是否可以自动拉起，待研究
开干
env
- centos7.x 2c4g x3
- mysql-8.0.x
支持mgr(mysql5.7.17+)
x00、requirement
- dis firewalld
- dis selinux
- ad hosts
tee >>/etc/hosts<<EOF
172.24.20.20 c7-20-20
172.24.20.21 c7-20-21
172.24.20.22 c7-20-22
EOF
一定要跟主机名一致,支持1各节点down不影响集群
x01、mysql安装
x02、my.cnf
[mysql]
auto-rehash
default-character-set = utf8mb4
connect-timeout = 3
[mysqld]
server-id = 11764
port = 3306
#base
basedir = /servyou/mysql
datadir = /servyou/mysql_data
#innodb
default-storage-engine = INNODB
innodb_buffer_pool_size = 2G
#set character
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'
#optimazed
skip-name-resolve
#default_authentication_plugin = mysql_native_password
authentication_policy = mysql_native_password
max_allowed_packet = 250M
lower_case_table_names = 1
max_connections = 2000
max_user_connections = 1500
explicit_defaults_for_timestamp = true
default-time-zone = system
#log
binlog_format = ROW
log-bin = mysql-binlog
max_binlog_size = 1G
#expire_logs_days = 30
binlog_expire_logs_seconds = 2592000
slow_query_log = 1
long-query-time = 30
log_timestamps = SYSTEM
log_bin_trust_function_creators = 1
relay-log=relay-binlog
binlog_checksum=NONE
#gtid
gtid_mode=ON
enforce_gtid_consistency=ON
relay_log_recovery=ON
#log_slave_updates=ON
log_replica_updates=1
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#add plugin
plugin-load=group_replication.so
#mgr
#transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "c7edeea5-ff2d-11e6-a6c7-0800279704c8"
loose-group_replication_start_on_boot = off
#每个节点ip不一样
loose-group_replication_local_address = "172.24.20.20:13306"
loose-group_replication_group_seeds = "172.24.20.20:13306,172.24.20.21:13306,172.24.20.22:13306"
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = FALSE
loose-group_replication_enforce_update_everywhere_checks = TRUE
loose-group_replication_ip_whitelist="172.0.0.0/8"
group-replication-auto-increment-increment=3
#group_replication_allow_local_disjoint_gtids_join=ON
[mysqld_safe]
socket = /servyou/mysql_data/mysql.sock
log-error = /servyou/mysql_data/mysql_error.log
pid-file = /servyou/mysql_data/mysqld.pid
注释的都是即将或者已经丢弃的参数,修改server_id、group_replication_local_address
x03、新增repl账户
all nodes
//设置binlog不记录 SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=0;
create user repl@'172.%' identified by 'repl_321';
grant replication slave on *.* to repl@'172.%';
flush privileges;
SET SQL_LOG_BIN=1;
//开启记录binlog SET SQL_LOG_BIN=1;
### 如果不临时关闭binlog 需要 reset master;在各个节点上
create user repl@'172.%' identified by 'repl_321';
grant replication slave on *.* to repl@'172.%';
flush privileges;
x04、开启组复制
开启复制
在20上开启组复制
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl_321' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
在21开启复制：
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl_321' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
STOP GROUP_REPLICATION;
查看集群状态：
SELECT * FROM performance_schema.replication_group_members;
3306 | ONLINE | PRIMARY
3306 | ONLINE | PRIMARY
select @@read_only,@@super_read_only;
error
2022-02-19T18:42:02.022292+08:00 0 [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
reset master; //清空Executed_Gtid_Set
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl_321' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
