mysql mgr单主模式_MGR组mysql复制组-----单主模式项目实施

MGR组mysql复制组-----单主模式项目实施：

主机：

192.168.1.51 itpuxdb1

192.168.1.52 itpuxdb2

192.168.1.53 itpuxdb3

开始操作：

51上：

vi /mysql/data/3306/my.cnf

####bind_address

server-id改一下：

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

log_bin_index=/mysql/log/3306/binlog/itpuxdb-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/itpuxdb-relay.log

relay-log-index = /mysql/log/3306/relaylog/itpuxdb-relay.index master_info_repository=table

relay_log_info_repository=table

plugin_load=“group_replication=group_replication.so” transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name=“aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa aa” loose-group_replication_start_on_boot=off loose-group_replication_local_address= “192.168.1.53:33006”

loose-group_replication_group_seeds= “192.168.1.51:33006,192.168.1.52:33006,192.168.1.53:33006” loose-group_replication_bootstrap_group= off

开始配置：

51：

创建复制用户

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’;

创建复制组

set global group_replication_bootstrap_group=on;

start group_replication;

set global group_replication_bootstrap_group=off;

select * from performance_schema.replication_group_members；

第二节点：

52上：

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’;

加入前面创建的复制组里面

start group_replication;

select * from performance_schema.replication_group_members;

第三个节点

跟上面52一样

在51上查看：

select @@read_only, @@super_read_only;

都是0

在52 、53上面查看都是1，只读模式

select @@read_only, @@super_read_only;

如何确定集群中的主节点：

select b.member_id, b.member_host, b.member_port from performance_schema.global_status a join performance_schema.replication_group_members b on a.variable_value = b.member_id where a.variable_name= ‘group_replication_primary_member’;

备注：

比如当前的主节点是51，当51挂掉后，集群会自动选择新的主；当51这个修复好了，重新启动了，那么需要再加入集群中，用start group_replication; 这个命令就可以加入到集群中了；