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