首页 > 数据库 >【MySQL MGR管理】MySQL5.7 MGR集群切换测试

【MySQL MGR管理】MySQL5.7 MGR集群切换测试

时间：2024-07-09 09:20:25浏览次数：22  
标签：00 group replication MySQL5.7 mysql MGR MySQL lft 0321

一、服务器环境
MySQL5.7 MGR集群切换
目标：故障切换，启用备选主之后。还原MGR，切换回原主
服务器信息
[root@0321-mysqlmgr3 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.82.11.22 0321-mysqlmgr1.test.com 0321-mysqlmgr1
10.82.11.23 0321-mysqlmgr2.test.com 0321-mysqlmgr2
10.82.11.24 0321-mysqlmgr3.test.com 0321-mysqlmgr3
mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
二、MGR发生切换，备选主提升为主库
此时mysqlmgr3为主库,读写，其他节点只读
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 8e12773f-c7a9-11e9-a014-005056bf2bfd | 0321-mysqlmgr3 | 3306 | ONLINE |
| group_replication_applier | 94c4ba24-c7a9-11e9-87a0-005056bf300d | 0321-mysqlmgr2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
+--------------------------------------+
| variable_value |
+--------------------------------------+
| 8e12773f-c7a9-11e9-a014-005056bf2bfd |
+--------------------------------------+
1 row in set (0.00 sec)
原主库状态：
[root@0321-mysqlmgr1 ~]# systemctl status keepalived.service
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
Active: inactive (dead)
[root@0321-mysqlmgr1 ~]# ps -ef |grep mysql
root 6947 4865 0 09:13 pts/1 00:00:00 grep --color=auto mysql
原主库mysqlmgr1起库，启动GR
[root@0321-mysqlmgr1 ~]#/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my.cnf --user=mysql &
mysql> start group_replication;

此时的MGR架构：
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 23fb82d9-c3bf-11e9-a56e-005056bf4fbc | 0321-mysqlmgr1 | 3306 | ONLINE |
| group_replication_applier | 8e12773f-c7a9-11e9-a014-005056bf2bfd | 0321-mysqlmgr3 | 3306 | ONLINE |
| group_replication_applier | 94c4ba24-c7a9-11e9-87a0-005056bf300d | 0321-mysqlmgr2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
3 rows in set (0.00 sec)
此时mysqlmgr3为主库
mysql> select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
+--------------------------------------+
| variable_value |
+--------------------------------------+
| 8e12773f-c7a9-11e9-a014-005056bf2bfd |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> system ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:bf:2b:fd brd ff:ff:ff:ff:ff:ff
inet 10.82.11.24/24 brd 10.82.11.255 scope global ens192
valid_lft forever preferred_lft forever
inet 10.82.11.25/32 scope global ens192
valid_lft forever preferred_lft forever
mysql> system hostname
0321-mysqlmgr3
VIP绑定在mysqlmgr3

三、原主库mysqlmgr1，设置权重，切换MGR为旧的主库
mysql> select @@group_replication_member_weight;
+-----------------------------------+
| @@group_replication_member_weight |
+-----------------------------------+
| 50 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> set global group_replication_member_weight=100;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@group_replication_member_weight;
+-----------------------------------+
| @@group_replication_member_weight |
+-----------------------------------+
| 100 |
+-----------------------------------+
1 row in set (0.00 sec)
开启mysqlmgr1的keepalive -->重启mysqlmgr3的MySQL实例，启动keepalive--> 重置mysqlmgr1的group_replication_member_weight=50
--> mysqlmgr3 mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; start group_replication;
主库切换回原主，
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 23fb82d9-c3bf-11e9-a56e-005056bf4fbc | 0321-mysqlmgr1 | 3306 | ONLINE |
| group_replication_applier | 8e12773f-c7a9-11e9-a014-005056bf2bfd | 0321-mysqlmgr3 | 3306 | ONLINE |
| group_replication_applier | 94c4ba24-c7a9-11e9-87a0-005056bf300d | 0321-mysqlmgr2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
+--------------------------------------+
| variable_value |
+--------------------------------------+
| 23fb82d9-c3bf-11e9-a56e-005056bf4fbc |
+--------------------------------------+
1 row in set (0.00 sec)
VIP绑定在原主的host
[root@0321-mysqlmgr1 keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:bf:4f:bc brd ff:ff:ff:ff:ff:ff
inet 10.82.11.22/24 brd 10.82.11.255 scope global ens192
valid_lft forever preferred_lft forever
inet 10.82.11.25/32 scope global ens192
valid_lft forever preferred_lft forever

标签：00,group,replication,MySQL5.7,mysql,MGR,MySQL,lft,0321
From： https://www.cnblogs.com/helontian/p/18291069

相关文章

  • docker安装mysql8.0.23
    拉取镜像dockerpullmysql:8.0.23创建挂载文件mkdir-p/home/docker/mysql/confmkdir-p/home/docker/mysql/datamkdir-p/home/docker/mysql/logcd/home/docker/mysql/conftouchmy.cnf编辑my.cnfvimy.cnf内容如下：default-character-set=utf8[mysql]defa......
  • Ubuntu安装MySQL8步骤
    在Ubuntu系统上搭建MySQL的过程：1、首先先将系统通过命令更新到最新，命令如下sudoapt-getupdatesudoapt-getupgrade 2、执行命令行安装MySQL8sudoapt-getinstallmysql-server 3、第一次进入需要用权限进入，可以跳过输入密码的过程，然后等进入MySQL以后通过命令修改......
  • redis如何与mysql数据保持一致？
    redis如何与mysql数据保持一致？同步双写：cacheasidepattern，读：先读缓存再读数据库，一个缓存的过期时间，实现起来简单好用极限情况还会有数据不一致的风险。CAP定理：c一致性a可用性p分区容错性，cp或者是ap异步双写：基于消息队列实现，写：生产者：先更新数据库，向队列发消息，消费者：监听消......
  • json数据写入到mysql数据中
    importpymysql#json文件中格式一个列表包含一个个的字典数据#[{"title":"胖猫事件-21岁游戏代练胖猫跳江身亡，PUA捞女女主谭竹遭网友报告视频","cover":"https://suvip888.com/20240516/U8NEMN2P/1.jpg","m3u8_url":"https://vodvip888.com/20240516/U8NEMN2P/......
  • MySQL数据库基本操作-DDL和DML
    1.DDL解释DDL(DataDefinitionLanguage)，数据定义语言，该语言部分包括以下内容：对数据库的常用操作对表结构的常用操作修改表结构2.对数据库的常用操作功能SQL查看所有的数据库showdatabases；查看有印象的数据库show databaseslike'_xx%'（_一个字符；%任意字符）创建数据......
  • .NetCore中EFCore for MySql整理MySql.EntityFrameworkCore
    一、MySql.EntityFrameworkCore 这个是官方给的一个EF操作MySql数据库的框架。使用方法跟EFforSqlServer一样。 二、安装命令NuGet\Install-PackageMySql.EntityFrameworkCore-Version8.0.5  项目依赖 安装后的结果：   三、EFCodeFirst模式连......
  • Java面试八股之MySQL主从复制机制简述
    MySQL主从复制机制简述MySQL的主从复制机制是一种数据复制方案，用于在多个服务器之间同步数据。此机制允许从一个服务器（主服务器）到一个或多个其他服务器（从服务器）进行数据的复制，从而增强数据冗余、提高读取性能，并且为灾难恢复提供保障。以下是MySQL主从复制机制的简要概述：复制......
  • mysql函数大全
    描述：巩固一下，避免和oracle函数混淆。字符串函数函数描述ASCII(str)返回字符串str的第一个字符的ASCII码CHAR_LENGTH(str)返回字符串str的字符数CONCAT(str1,str2,...strn)将字符串str1、str2、...strn合并为一个字符串CONCAT_WS(splitStr,str1,str2，.........
  • MySQL MVCC实现原理
    MySQL的InnoDB存储引擎使用多版本并发控制（MVCC，Multi-VersionConcurrencyControl）机制来支持高并发的读写操作，同时保证事务的隔离性和一致性。MVCC允许不同的事务看到不同的数据版本，从而减少了锁的竞争，提高了数据库的并发性能。隐式字段InnoDB在每行记录中添加了几个隐藏的......
  • Mysql中索引的分类、增删改查与存储引擎对应关系
    场景Mysql中存储引擎简介、修改、查询、选择：https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/140269624上面介绍Mysql的存储引擎。下面介绍索引的分类和使用。注：博客：https://blog.csdn.net/badao_liumang_qizhi实现Mysql中索引的分类1、普通索引和唯一索引......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99