MySQL MGR

本文所演示的多主MGR三个节点均部署在同一台虚拟机，所以需先搭建MySQL多实例具体步骤可参考本人另一篇blog

 

#所有节点执行

#安装MGR插件

 修改server_id,server_uuid确保同一复制组的各个服务器的值都不一样

chown -R mysql:mysql  /mysql

install plugin group_replication soname 'group_replication.so';

create user 'replica_user'@'%' identified with mysql_native_password by 'Pas5W@rd';

grant replication slave on *.* to 'replica_user'@'%';

 

change master to
master_user='replica_user',
master_password='Pas5W@rd'
for channel 'group_replication_recovery';

 

reset master

 

 

#主节点

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

 

#其他从节点

START GROUP_REPLICATION;

 

#结果验证及问题排查

select * from performance_schema.replication_group_members;
select * from performance_schema.replication_group_member_stats \G ;
select * from performance_schema.replication_connection_status\G ;

 

my.cnf配置

[client]
user=root
password=Wjb123
socket=/data/p3306/mysql3306.sock

[mysqld]
# basic settings #
user = mysql
basedir = /mysql
autocommit = 1
character_set_server = utf8
collation_server = utf8_general_ci

# innodb settings #
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 16
innodb_page_size = 16384
max_binlog_size = 100M
innodb_flush_log_at_trx_commit = 1
default-storage-engine = INNODB
lower_case_table_names = 1
max_connections = 2000
log-bin-trust-function-creators = 1
#skip-grant-tables

# log settings #
#expire_logs_days = 7
#604800=7*24*60*60
binlog_expire_logs_seconds = 604800

# replication settings #
log-bin = mysql-bin
gtid_mode = on
enforce_gtid_consistency = on
binlog_checksum = none
log_slave_updates = ON
binlog_format = ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
group_replication_single_primary_mode=OFF　　　　　　　　#配置单主模式切换这两个配置即可
group_replication_enforce_update_everywhere_checks=ON
#report_host=127.0.0.1

[mysqld_multi]
log = /data/mysqld_multi.err
mysqld = /mysql/bin/mysqld_safe
mysqladmin = /mysql/bin/mysqladmin

[mysqld3306]
server-id = 1026
port = 3306
mysqlx_port = 33060
basedir = /mysql
datadir = /data/p3306
pid-file = /data/p3306/mysql3306.pid
log-error = /data/p3306/mysql3306.err
socket = /data/p3306/mysql3306.sock
mysqlx_socket = /data/p3306/mysqlx3306.sock

[mysqld3307]
server-id = 1027
port = 3307
mysqlx_port = 33070
basedir = /mysql
datadir = /data/p3307
socket = /data/p3307/mysql3307.sock
pid-file = /data/p3307/mysql3307.pid
log-error = /data/p3307/mysql3307.err
mysqlx_socket = /data/p3307/mysqlx3307.sock

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="671aba90-3a9a-11eb-a702-000c295e6111"
group_replication_start_on_boot=off
group_replication_bootstrap_group=off
group_replication_local_address= "127.0.0.1:33071"
group_replication_group_seeds= "127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091"

[mysqld3308]
server-id = 1028
port = 3308
mysqlx_port = 33080
basedir = /mysql
datadir = /data/p3308
socket = /data/p3308/mysql3308.sock
pid-file = /data/p3308/mysql3308.pid
log-error = /data/p3308/mysql3308.err
mysqlx_socket = /data/p3308/mysqlx3308.sock

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="671aba90-3a9a-11eb-a702-000c295e6111"
group_replication_start_on_boot=off
group_replication_bootstrap_group=off
group_replication_local_address= "127.0.0.1:33081"
group_replication_group_seeds= "127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091"

[mysqld3309]
server-id = 1029
port = 3309
mysqlx_port = 33090
basedir = /mysql
datadir = /data/p3309
socket = /data/p3309/mysql3309.sock
pid-file = /data/p3309/mysql3309.pid
log-error = /data/p3309/mysql3309.err
mysqlx_socket = /data/p3309/mysqlx3309.sock

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="671aba90-3a9a-11eb-a702-000c295e6111"
group_replication_start_on_boot=off
group_replication_bootstrap_group=off
group_replication_local_address= "127.0.0.1:33091"
group_replication_group_seeds= "127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091"