mysql group replication(MGR）组复制部署

w23939296

已于 2022-11-06 14:18:07 修改

阅读量429

点赞数

文章标签： java 服务器前端

于 2022-09-22 22:39:40 首次发布

本文链接：https://blog.csdn.net/w975121565/article/details/126997808

版权

环境说明(安装好mysql8.0.30后打快照mysql-ok)
1   rockylinux8.6最小化   stu1:192.168.3.161   mysql8.0.30 rpm包安装   已做免密登录
2   rockylinux8.6最小化   stu2:192.168.3.162   mysql8.0.30 rpm包安装   已做免密登录
3   rockylinux8.6最小化   stu3:192.168.3.163   mysql8.0.30 rpm包安装   已做免密登录
三台配置 /etc/hosts

192.168.3.161 stu1

192.168.3.162 stu2

192.168.3.163 stu3

三台设置防火墙

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --permanent --add-port=33060/tcp 
firewall-cmd --permanent --add-port=33061/tcp 
firewall-cmd --reload

注意事项：

1、引擎必须为 innodb，暂不支持其它引擎

2、每个表必需有主键，事务冲突检测时利用主键对比

3、必需开启GTID

4、必需开启binlog，并且必需为row格式

5、metadata必需写入表

6、一致性检测： transaction_write_set_extraction=XXHASH64

配置步骤：

stu1:

1、增加数据

mysql
create database testdb;
use testdb
DROP TABLE IF EXISTS `tb1`;
 
CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
 
INSERT INTO `tb1` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `tb1` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `tb1` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `tb1` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `tb1` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `tb1` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `tb1` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `tb1` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
 
select * from tb1;

2、数据备份

mkdir /backup
mkdir /backup-dir
mysqlbackup -uroot -pAdmin@123 --backup-image=/backup/fulldb.mbi --backup-dir=/backup-dir backup-to-image

3、拷贝fulldb.mbi至 stu2 stu3 并在两台从库上恢复数据

stu2 stu3:

mkdir /backup
scp stu1:/backup/fulldb.mbi /backup

systemctl stop mysqld
rm -rf /var/lib/mysql/*
mysqlbackup --backup-image=/backup/fulldb.mbi --backup-dir=/tmp/backup copy-back-and-apply-log
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

mysql
use testdb
select *  from tb1;

4、安装插件及配置用户

stu1 stu2 stu3:

install plugin group_replication soname 'group_replication.so';
show plugins;

创建用户 stu1 stu2 stu3

set sql_log_bin=0;
create user repl@'192.168.3.%' identified by 'Admin@123';
grant replication slave on *.* to repl@'192.168.3.%';
set sql_log_bin=1;

5、修改配置文件

stu1:

vim /etc/my.cnf
在[mysqld]下加入
server_id=161
gtid_mode=on
binlog_checksum=NONE
enforce-gtid-consistency=on
disabled_storage_engines='MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY'
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE 
transaction_write_set_extraction=XXHASH64
loose_group_replication_recovery_use_ssl=on
group_replication_group_name='5de7369a-031f-4006-adee-ef023fc3b591'
group_replication_start_on_boot=off
group_replication_local_address='192.168.3.161:33061'
group_replication_group_seeds='192.168.3.161:33061,192.168.3.162:33061,192.168.3.163:33061'
group_replication_bootstrap_group=off

systemctl restart mysqld

group_name可由uuidgen生成

6、增加新数据至主库

mysql
use testdb
DROP TABLE IF EXISTS `tb2`;
CREATE TABLE `tb2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb2` VALUES ('1', 'zs');

select * from tb2;

7、启动

change master to
master_user='repl',
master_password='Admin@123'
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;

8、增加stu2

修改配置文件

vim /etc/my.cnf
在[mysqld]下加入
server_id=162
gtid_mode=on
binlog_checksum=NONE
enforce-gtid-consistency=on
disabled_storage_engines='MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY'
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE 
transaction_write_set_extraction=XXHASH64
loose_group_replication_recovery_use_ssl=on
loose_group_replication_group_name='5de7369a-031f-4006-adee-ef023fc3b591'
loose_group_replication_start_on_boot=off
loose_group_replication_local_address='192.168.3.162:33061'
loose_group_replication_group_seeds='192.168.3.161:33061,192.168.3.162:33061,192.168.3.163:33061'
loose_group_replication_bootstrap_group=off

systemctl restart mysqld

启动

change master to
master_user='repl',
master_password='Admin@123'
for channel 'group_replication_recovery';

start group_replication;

9、增加stu3

修改配置文件

vim /etc/my.cnf
在[mysqld]下加入
server_id=163
gtid_mode=on
binlog_checksum=NONE
enforce-gtid-consistency=on
disabled_storage_engines='MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY'
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE 
transaction_write_set_extraction=XXHASH64
loose_group_replication_recovery_use_ssl=on
loose_group_replication_group_name='5de7369a-031f-4006-adee-ef023fc3b591'
loose_group_replication_start_on_boot=off
loose_group_replication_local_address='192.168.3.163:33061'
loose_group_replication_group_seeds='192.168.3.161:33061,192.168.3.162:33061,192.168.3.163:33061'
loose_group_replication_bootstrap_group=off

systemctl restart mysqld

启动

change master to
master_user='repl',
master_password='Admin@123'
for channel 'group_replication_recovery';

start group_replication;

10、修改配置文件

stu1:

改 group_replication_start_on_boot=off 为 group_replication_start_on_boot=on

systemctl restart mysqld

stu2 stu3:

去掉loose_ 将 group_replication_start_on_boot=on （off改为on）

vim /etc/my.cnf
将配置文件改为
server_id=162
gtid_mode=on
binlog_checksum=NONE
enforce-gtid-consistency=on
disabled_storage_engines='MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY'
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE 
loose_group_replication_recovery_use_ssl=on
transaction_write_set_extraction=XXHASH64
group_replication_group_name='5de7369a-031f-4006-adee-ef023fc3b591'
group_replication_start_on_boot=on
group_replication_local_address='192.168.3.162:33061'
group_replication_group_seeds='192.168.3.161:33061,192.168.3.162:33061,192.168.3.163:33061'
group_replication_bootstrap_group=off

systemctl restart mysqld

11、验证

stu1:

select * from performance_schema.replication_group_members;
show variables like 'group_replication%';
show variables like '%read_only%';

stu2 stu3:

select * from testdb.tb1;
select * from testdb.tb2;
show variables like '%read_only%';

未备份的数据也已同步至从节点

12、多主模式

备节点插入数据（无法插入，只读模式）

use testdb
insert into tb2 values('2','lc');

主节点插入数据(可插入数据）

use testdb
insert into tb2 values('2','lc');

stu1:(主节点）

stop group_replication;

set global group_replication_single_primary_mode=off; 
set global group_replication_enforce_update_everywhere_checks=on;
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;

stu2 stu3(从节点）:

stop group_replication;

set global group_replication_single_primary_mode=off; 
set global group_replication_enforce_update_everywhere_checks=on;
start group_replication;

验证

select * from performance_schema.replication_group_members;
show variables like '%read_only%';

插入数据验证

stu1:

insert into testdb.tb2 values('3','stu1');

stu2:

insert into testdb.tb2 values('4','stu2');

stu3:

insert into testdb.tb2 values('5','stu3');

stu1 stu2 stu3:

select * from testdb.tb2;

也可在配置文件中加入以下两行直接部署多主模式

group_replication_single_primary_mode = off 关闭单master模式
group_replication_enforce_update_everywhere_checks = ON #多主一致性检查

注意：多点写入会存在冲突检查，会耗损服务器性能。官方建议采用网络分区功能，在程序端把相同的业务定位到同一节点，尽量减少冲突发生几率。

w23939296

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql group replication(MGR）组复制部署

mysql group replication(MGR）组复制部署
复制链接

扫一扫

mysql group replication(MGR）组复制部署

配置步骤：

“相关推荐”对你有帮助么？