mysql MGR集群

r囧r小猫 2023-07-22 10:27 32阅读 0赞

## 介绍 ##

**MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MySQL Group Replication提供了高可用、高扩展、高可靠的MySQL集群服务**

## MGR基础结构要求： ##

**1、引擎必须为innodb,因为需事务支持在commit时对各节点进行冲突检查  
2、每个表必须有主键，在进行事务冲突检测时需要利用主键值对比  
3、必须开启binlog且为row格式  
4、开启GTID，且主从状态信息存于表中（–master-info-repository=TABLE 、–relay-log-info-repository=TABLE），–log-slave-updates打开  
5.一致性检测设置–transaction-write-set-extraction=XXHASH64**

## 部署MGR集群 ##

**1.准备三台mysql，三台都设置hostname和ip映射**  
`vim /etc/hosts` 输入以下

192.168.100.10   master
    192.168.100.20   slave1
    192.168.100.30   slave2

**2.配置master主配置文件**  
`vim /etc/my.cnf` 添加以下 \#\#红色部分修改组名和自己主机名

server_id=100  #服务ID
    gtid_mode=ON  #全局事务
    enforce_gtid_consistency=ON  #强制GTID的一致性
    master_info_repository=TABLE  #将master.info元数据保存在系统表中
    relay_log_info_repository=TABLE  #将relay.info元数据保存在系统表中
    binlog_checksum=NONE  #禁用二进制日志事件校验
    log_slave_updates=ON  #级联复制
    log_bin=binlog   #开启二进制日志记录
    binlog_format=ROW  #以行的格式记录
    3.transaction_write_set_extraction=XXHASH64 #使用哈希算法将其编码为散列
    loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名
    loose-group_replication_start_on_boot=off     #不自动启用组复制集群
    loose-group_replication_local_address='cong11:33061'
    #以本机端口33061接受来自组中成员的传入连接
    loose-group_replication_group_seeds='cong11:33061,cong12:33061,cong13:33061'
    #组中成员访问表
    loose-group_replication_bootstrap_group=off #不启用引导组

**Uuidgen 可以自动生成组名**  
**Systemctl restart mysqld** \#\#重启  
**3，授权账户**  
进入mysql里

mysql> set SQL_LOG_BIN=0;   #停掉日志记录
    mysql> grant replication slave on *.* to repl@' 192.168.1.%' identified by '123456'; mysql> flush privileges; mysql> set SQL_LOG_BIN=1; #开启日志记录 mysql>change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';  #构建group replication集群，如果报错，重启mysql，再执行。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70]  
**4,安装group replication**

install PLUGIN group_replication SONAME 'group_replication.so';

**查看**

Show plugins

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 1]  
**5，启动master上mysql的group replication**  
`Set global group_replication_bootstrap_group=ON;` **\#\#group\_replication\_bootstrap\_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。**

start group_replication;
    set global group_replication_bootstrap_group=OFF;

**查看mgr状态**

select * from performance_schema.replication_group_members;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 2]

**测试插入一些数据**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 4]

## 6，复制组添加新实例slave1，修改配置文件 ##

第二台虚拟机配置  
`vim /etc/my.cnf` \#\#添加以下 注意，ip不一样，组名三台都要一样

server_id=101   #注意服务ID不一样
    gtid_mode=ON
    enforce_gtid_consistency=ON
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    binlog_checksum=NONE
    log_slave_updates=ON
    log_bin=binlog
    binlog_format=ROW
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856'
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address='cong12:33061'
    loose-group_replication_group_seeds='cong11:33061,cong12:33061,cong13:33061'
    loose-group_replication_bootstrap_group=off

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 5]

`Systemctl restart mysqld` \#\#重启  
**7，授权，和上面一样**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 6]  
**8，安装group replication 插件**  
和上面一样  
把实例添加到之前的复制组

set global group_replication_allow_local_disjoint_gtids_join=ON;
    start group_replication;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 7]  
**查看复制组状态**

select * from performance_schema.replication_group_members;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 9]  
**9，复制组添加新实例slave2，修改配置文件，添加以下**  
`vim /etc/my.cnf` \#\#组名，主机名和id

server_id=102   #注意服务ID不一样
    gtid_mode=ON
    enforce_gtid_consistency=ON
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    binlog_checksum=NONE
    log_slave_updates=ON
    log_bin=binlog
    binlog_format=ROW
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856'
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address='cong13:33061'
    loose-group_replication_group_seeds='cong11:33061,cong12:33061,cong13:33061'
    loose-group_replication_bootstrap_group=off

`systemctl restart mysqld` \#\#重启

## 配置slave2 ##

**10，授权**  
同上  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 10]  
**安装group replication**  
同上  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 11]  
**查看状态**

select * from performance_schema.replication_group_members;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 12]  
**同步完成**  
**11，查看集群参数设置列表**

show variables like 'group_replication%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 13]  
**查看主服务器id**

SHOW STATUS LIKE 'group_replication_primary_member';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 14]

## multi-primary模式(多主master模式) ##

**1，由单主改为多主**  
在原来单主的主节点上  
进入mysql

stop GROUP_REPLICATION;
    set global group_replication_single_primary_mode=off;         #关闭单master模式
    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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 15]

## 2,在slave1和slave2上配置 ##

配置两个一样  
进入mysql

stop GROUP_REPLICATION;      ##停止复制组
    set global group_replication_allow_local_disjoint_gtids_join=ON;               #即使含有组中不存在的事务，也允许当前server加入组
    set global group_replication_single_primary_mode=off;          #关闭单master模式
    set global group_replication_enforce_update_everywhere_checks=ON;                  #这个参数设置多主模式下各个节点严格一致性检查
     start group_replication;    #加入到复制组

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 16]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 17]

## 3,搭建多主master模式 在master上 ##

`vim /etc/my.cnf` \#\#进入添加 ， 再把主机名改一下

#roup Replication
    server_id = 100  #服务ID
    gtid_mode = ON  #全局事务
    enforce_gtid_consistency = ON  #强制GTID的一致性
    master_info_repository = TABLE  #将master.info元数据保存在系统表中
    relay_log_info_repository = TABLE  #将relay.info元数据保存在系统表中
    binlog_checksum = NONE  #禁用二进制日志事件校验
    log_slave_updates = ON  #级联复制
    log_bin = binlog   #开启二进制日志记录
    binlog_format= ROW  #以行的格式记录
    
    transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
    loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名
    loose-group_replication_start_on_boot = off #不自动启用组复制集群
    loose-group_replication_local_address = 'cong11:33061' #以本机端口33061接受来自组中成员的传入连接
    loose-group_replication_group_seeds = 'cong11:33061,cong12:33062,cong13:33063' #组中成员访问表
    loose-group_replication_bootstrap_group = off  #不启用引导组
    loose-group_replication_single_primary_mode = off  #关闭单master模式
    loose-group_replication_enforce_update_everywhere_checks = ON  #多主一致性检查

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 18]

**进入mysql**

stop GROUP_REPLICATION;
    reset master;   #重置所有的日志

## 4，在从服务器上配置，slave1 slave2 ##

`Vim /etc/my.cnf` \#\#添加以下 修改主机名

# Group Replication
    server_id = 101  #服务ID
    gtid_mode = ON  #全局事务
    enforce_gtid_consistency = ON  #强制GTID的一致性
    master_info_repository = TABLE  #将master.info元数据保存在系统表中
    relay_log_info_repository = TABLE  #将relay.info元数据保存在系统表中
    binlog_checksum = NONE  #禁用二进制日志事件校验
    log_slave_updates = ON  #级联复制
    log_bin = binlog   #开启二进制日志记录
    binlog_format= ROW  #以行的格式记录
    
    transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
    loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名
    loose-group_replication_start_on_boot = off #不自动启用组复制集群
    loose-group_replication_local_address = 'cong12:33061' #以本机端口33061接受来自组中成员的传入连接
    loose-group_replication_group_seeds = 'cong11:33061,cong12:33062,cong13:33063' #组中成员访问表
    loose-group_replication_bootstrap_group = off  #不启用引导组
    loose-group_replication_single_primary_mode = off  #关闭单master模式
    loose-group_replication_enforce_update_everywhere_checks = ON  #多主一致
    性检查

`/etc/init.d/mysqld restart` \#\#重启

select * from performance_schema.replication_group_members;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 19]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 20]  
**Slave2配置同上**  
配置完重启，查看状态

select * from performance_schema.replication_group_members;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 21]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 22]  
完成

**实验到这里就结束了，如果有问题请小伙伴们留言，谢谢~~**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70]: /images/20230528/70642d6aeb8440648dbd86d0d684f8f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 1]: /images/20230528/9bf7d7f4759945148ed9b8a1eccf8f67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 2]: /images/20230528/d3ab86ebbc7e4e78b40193bbd152cef8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 3]: /images/20230528/c5affcb19a194ed487b432f865592941.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 4]: /images/20230528/268c7ba0a9514046bd3df1ace4b71762.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 5]: /images/20230528/2076586954a143418323441cf9958dfa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 6]: /images/20230528/4aaa88919d594a169a0093df0c800208.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 7]: /images/20230528/c26c9a2613574bfe8022bb308422d0f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 8]: /images/20230528/2c89632419434bb3b030a10bcfb29fc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 9]: /images/20230528/bf2947dd1861494aab9d9ba49b6c6199.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 10]: /images/20230528/8b3d5231876341c4a2c8bcbc800043ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 11]: /images/20230528/9f265a8f7b0f484a9c3ef3ca790278bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 12]: /images/20230528/4cb076c04e5e469e841cdee39aba4478.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 13]: /images/20230528/57d4c7f5755743a9bc9932a38b0f7c0a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 14]: /images/20230528/39d84a3b18084154ab9c1ef7655ef818.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 15]: /images/20230528/d62d4d3f18a94b8db2507d44a0a4d244.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 16]: /images/20230528/177c442cec8c439c9d8033173b06faf6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 17]: /images/20230528/d805793bed48446a9b87bbfd9c5646be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 18]: /images/20230528/5508487ef38c4502982cb2d8643abc7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 19]: /images/20230528/b3d1f33c8bb44692804d0d595ae89663.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 20]: /images/20230528/27e36c475f82406ab9c394a36488b4f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 21]: /images/20230528/c6e6e9aa74e64db48bc70f71f7d89f7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjM4MTY2OA_size_16_color_FFFFFF_t_70 22]: /images/20230528/9357c2cdf92046b8af8eea8fd8759d50.png