MGR多主配置

1. 配置参数

vi /mysql/data/3306/my.cnf                     #节点都加

注释掉：bind_address 

改掉loose-group_replication_local_address 和 server-id

log_bin=/mysql/log/3306/binlog/mysqldb-binlog

log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

binlog_checksum=none

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=4

slave_preserve_commit_order=1

gtid_mode = on

enforce_gtid_consistency = 1

log-slave-updates = 1

binlog_gtid_simple_recovery=1

relay_log = /mysql/log/3306/relaylog/mysqldb-relay.log

relay-log-index = /mysql/log/3306/relaylog/mysqldb-relay.index

master_info_repository=table

relay_log_info_repository=table

plugin_load="group_replication=group_replication.so"

#MGR参数  33006是集群通讯端口

plugin_load="group_replication=group_replication.so"

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "192.168.0.53:33006"

loose-group_replication_group_seeds="192.168.0.51:33006,192.168.0.52:33006,192.168.0.53:33006"

loose-group_replication_bootstrap_group= off

group_replication_single_primary_mode=off                #off的话就是多主，on的话就是单主，默认是on

group_replication_enforce_update_everywhere_checks=on

参数讲解：

 

1. 第一个节点：  四步

mysql -uroot -proot

set sql_log_bin=0;   #关闭二进制，等下要开启

create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

 

change master to

master_user='repuser',

master_password='repuser123'

for channel 'group_replication_recovery';

#检查安装的MGR插件group_replication soname 'group_replication.so';

show plugins;

假如说上面没安装插件，这里安装下，且重启下，重启才生效 ，下面几个节点一样

-- install plugin group_replication soname 'group_replication.so';

#建个群，初始化一个复制组，群主的概念

set global group_replication_bootstrap_group=on;

start group_replication;

set global group_replication_bootstrap_group=off;

select * from performance_schema.replication_group_members;

1. 第二个节点：

mysql -uroot -proot

set sql_log_bin=0;

create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

change master to

master_user='repuser',

master_password='repuser123'

for channel 'group_replication_recovery';

#检查安装的MGR插件group_replication soname 'group_replication.so';

show plugins;

| group_replication | ACTIVE | group replication | group_replication.so | GPL |

 

start group_replication;

select * from performance_schema.replication_group_members;

1. 其它节点：

mysql -uroot -proot

set sql_log_bin=0;

create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

change master to

master_user='repuser',

master_password='repuser123'

for channel 'group_replication_recovery';

#检查安装的MGR插件group_replication soname 'group_replication.so';

show plugins;

| group_replication | ACTIVE | group replication |group_replication.so | GPL |

 

start group_replication;

select * from performance_schema.replication_group_members;

 

1. 测试数据：

create database itpuxdb2;

create table itpuxdb2.itpuxbak11 (id int primary key ,name varchar(40));

insert into itpuxdb2.itpuxbak11 values(1,'itpux111'),(2,'itpux112'),(3,'itpux113'),(4,'itpux114'),(5,'itpux115');

commit;

select * from itpuxdb2.itpuxbak11;

insert into itpuxdb2.itpuxbak11 values(31,'itpux111'),(32,'itpux112'),(33,'itpux113'),(34,'itpux114'),(35,'itpux115');

commit;