MySQL 5.7 group Replication

MySQL 5.7 group Replication

• Single-Primary模式配置环境

(1) 主机环境准备

IP                     HOSTNAME    port    用途
10.10.10.211    crmdb01         3306    
10.10.10.212    crmdb02         3306    
10.10.10.213    crmdb03         3306    
10.10.10.214    crmdb04         3306    备用

(2) 安装插件

mysql -uroot -p
install plugin group_replication soname 'group_replication.so';
show plugins;
exit

(3) 配置参数
 

vi /etc/my.cnf

#MGR
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="10.10.10.211:33006"
loose-group_replication_group_seeds="10.10.10.211:33006,10.10.10.212:33006,10.10.10.213:33006"
loose-group_replication_bootstrap_group= off
loose-group_replication_member_weight=50            # 权重选择

systemctl restart mysqld

(4) 第一个节点

• 在10.10.10.211主机上mysql中执行（相当于群主建群）

① 创建用于复制的用户

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=''
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;

(5) 第二/三个节点
① 创建用于复制的用户

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;

(6) 检查3台机的状态

select @@read_only, @@super_read_only;

(7) 测试数据是否同步

① 在节点10.10.10.211创建库、表的数据

create database crmdb1;

show databases;

② 检查备库

show variables like 'read_only';

③ 在从库上面测试插入数据

show databases;

(8) 只有在主库才能有读写权限，从库只有读权限。

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