主从复制直接转换MGR_5.7验证试验

IP port role info 192.168.188.51 4000 node1 master 192.168.188.52 4000 node2 slave1 192.168.188.53 4000 node3 slave2

  • CentOS Linux release 7.8.2003 (Core)
  • mysql-5.7.30-linux-glibc2.12-x86_64

在三个节点上部署好MySQL

  • 所有节点配置
mysql> set global super_read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'rep'@'192.168.188.%' identified by 'rep';
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication slave on *.* to 'rep'@'192.168.188.%';
Query OK, 0 rows affected (0.12 sec)

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)
  • master节点配置
mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.05 sec)

mysql> reset master;
Query OK, 0 rows affected (0.14 sec)
  • slave节点配置
mysql> set global rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.188.51',master_port=4000,master_user='rep',master_password='rep',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.64 sec)

mysql> reset master;
Query OK, 0 rows affected (0.23 sec)
  • slave 启动复制
mysql>  start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
  • master查看半同步状态
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

mysql> create database kk;
Query OK, 1 row affected (0.12 sec)

mysql> use kk
Database changed
mysql> create table k1 ( id int auto_increment primary key , dtl varchar(20) default 'abc');
Query OK, 0 rows affected (0.51 sec)
  • 开启一个session,运行脚本产生事务
[14:13:16] root@ms51:~ # while :; do  echo "insert into kk.k1(dtl) values('duangduangduang');" | mysql -S /data/mysql/mysql4000/tmp/mysql.sock; sleep 1;done

配置Master,将Master转为MGR

  • 配置参数
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.15 sec)

mysql> set global binlog_checksum=NONE;
Query OK, 0 rows affected (0.18 sec)

mysql> set global transaction_write_set_extraction=XXHASH64;
Query OK, 0 rows affected (0.00 sec)

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| a3e8c286-e375-11ea-868b-0242c0a8bc33 |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> set global group_replication_group_name='a3e8c286-e375-11ea-868b-0242c0a8bc33';
Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_local_address="192.168.188.51:13306"
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_group_seeds="192.168.188.51:13306,192.168.188.52:13306,192.168.188.53:13306";
Query OK, 0 rows affected (0.01 sec)

mysql> set global group_replication_start_on_boot=off;
Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.82 sec)

mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | e630ab09-e372-11ea-8a64-0242c0a8bc33 | ms51        |        4000 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

补充,还有这关键的一步(原因见文末):
mysql> change master to master_user='rep',master_password='rep' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

由于MySQL 5.7 不支持参数持久化语法 set persist,因此MGR相关的参数我们要手动合并到master实例的my.cnf文件

binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name='a3e8c286-e375-11ea-868b-0242c0a8bc33'
loose-group_replication_local_address="192.168.188.51:13306"
loose-group_replication_group_seeds="192.168.188.51:13306,192.168.188.52:13306,192.168.188.53:13306"
loose-group_replication_bootstrap_group=off
loose-group_replication_start_on_boot=off

去配置slave1 ,转换为MGR

mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)

mysql>  set global binlog_checksum=NONE;
Query OK, 0 rows affected (0.23 sec)

mysql>  set global transaction_write_set_extraction=XXHASH64;
Query OK, 0 rows affected (0.00 sec)

mysql>  set global group_replication_group_name='a3e8c286-e375-11ea-868b-0242c0a8bc33';
Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_local_address="192.168.188.52:13306";
Query OK, 0 rows affected (0.00 sec)

mysql>  set global group_replication_group_seeds="192.168.188.51:13306,192.168.188.52:13306,192.168.188.53:13306";
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_user='rep',master_password='rep' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.47 sec)

mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

mysql> exit

启动MGR失败,按照提示去检查errorlog,原来复制进行时无法启动MGR

[14:50:50] root@ms52:~ # less /data/mysql/mysql4000/logs/error.log
...

2020-08-21T14:50:43.791879+08:00 2 [Note] Plugin group_replication reported: 'Member configuration: member_id: 524000; member_uuid: "aee85149-e379-11ea-8c01-0242c0a8bc34"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; '
2020-08-21T14:50:43.791901+08:00 2 [ERROR] Plugin group_replication reported: 'Can't start group replication on secondary member with single primary-mode while asynchronous replication channels are running.'
2020-08-21T14:50:43.791930+08:00 2 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2020-08-21T14:50:43.791944+08:00 2 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

停止复制,启动MGR便成功了,稍等片刻,节点状态便ONLINE了。

[14:51:32] root@ms52:~ # mysql -S /data/mysql/mysql4000/tmp/mysql.sock

mysql> stop slave;
Query OK, 0 rows affected (0.09 sec)

mysql> start group_replication;
Query OK, 0 rows affected (6.61 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | ab726fa8-e379-11ea-a4c2-0242c0a8bc33 | ms51        |        4000 | ONLINE       |
| group_replication_applier | aee85149-e379-11ea-8c01-0242c0a8bc34 | ms52        |        4000 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

同样的,由于MySQL 5.7不支持参数直接持久化保存,需要手动将原slave1的相关参数补充到slave1实例的my.cnf文件中

binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name='a3e8c286-e375-11ea-868b-0242c0a8bc33'
loose-group_replication_local_address="192.168.188.52:13306"
loose-group_replication_group_seeds="192.168.188.51:13306,192.168.188.52:13306,192.168.188.53:13306"
loose-group_replication_bootstrap_group=off
loose-group_replication_start_on_boot=off

同理,将slave2转换为MGR成员,步骤略,记得保存参数到实例的my.cnf文件。

  • 最终转换完成后,三个节点都顺利ONLINE。
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | ab726fa8-e379-11ea-a4c2-0242c0a8bc33 | ms51        |        4000 | ONLINE       |
| group_replication_applier | aee85149-e379-11ea-8c01-0242c0a8bc34 | ms52        |        4000 | ONLINE       |
| group_replication_applier | b24d5bc3-e379-11ea-8eae-0242c0a8bc35 | ms53        |        4000 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
  • 将三节点全部关掉
mysql > shutdown ;

  • 启动第一个节点(slave2)
[15:02:58] root@ms53:~ # mysqld --defaults-file=/data/mysql/mysql4000/my4000.cnf &
[1] 604
[15:04:51] root@ms53:~ # mysql -S /data/mysql/mysql4000/tmp/mysql.sock

mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.53 sec)

mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | b24d5bc3-e379-11ea-8eae-0242c0a8bc35 | ms53        |        4000 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
  • 启动第二个节点(slave1)
[15:03:20] root@ms52:~ # mysqld --defaults-file=/data/mysql/mysql4000/my4000.cnf &
[1] 593
[15:04:47] root@ms52:~ # mysql -S /data/mysql/mysql4000/tmp/mysql.sock

mysql> start group_replication;
Query OK, 0 rows affected (6.37 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | aee85149-e379-11ea-8c01-0242c0a8bc34 | ms52        |        4000 | ONLINE       |
| group_replication_applier | b24d5bc3-e379-11ea-8eae-0242c0a8bc35 | ms53        |        4000 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
  • 启动第三个节点(master),踩了个坑
[15:04:33] root@ms51:~ # mysqld --defaults-file=/data/mysql/mysql4000/my4000.cnf &
[1] 7410
[15:04:43] root@ms51:~ # mysql -S /data/mysql/mysql4000/tmp/mysql.sock

mysql> start group_replication;
Query OK, 0 rows affected (5.98 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | ab726fa8-e379-11ea-a4c2-0242c0a8bc33 | ms51        |        4000 | RECOVERING   |
| group_replication_applier | aee85149-e379-11ea-8c01-0242c0a8bc34 | ms52        |        4000 | ONLINE       |
| group_replication_applier | b24d5bc3-e379-11ea-8eae-0242c0a8bc35 | ms53        |        4000 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

原master节点长时间处于recovering,查看status后猛然想起,先前转换MGR时,只对slave1、slave2做了change master for channel,把master给忘记了。
(该步骤已重新补充到master转换的操作步骤中。)

mysql> change master to master_user='rep',master_password='rep' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> stop group_replication;
Query OK, 0 rows affected (10.65 sec)

mysql> start group_replication;
Query OK, 0 rows affected (3.27 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | ab726fa8-e379-11ea-a4c2-0242c0a8bc33 | ms51        |        4000 | ONLINE       |
| group_replication_applier | aee85149-e379-11ea-8c01-0242c0a8bc34 | ms52        |        4000 | ONLINE       |
| group_replication_applier | b24d5bc3-e379-11ea-8eae-0242c0a8bc35 | ms53        |        4000 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

当前master
mysql> select * from performance_schema.global_status where variable_name='group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | b24d5bc3-e379-11ea-8eae-0242c0a8bc35 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

噢,忘了检查事务情况。 三节点结果一样,转换全面成功。

mysql> select count(*) from kk.k1;
+----------+
| count(*) |
+----------+
|      277 |
+----------+
1 row in set (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                     |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| mysql-bin.000003 |     1686 |              |                  | a3e8c286-e375-11ea-868b-0242c0a8bc33:1-240,
ab726fa8-e379-11ea-a4c2-0242c0a8bc33:1-46 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Original: https://www.cnblogs.com/konggg/p/13571581.html
Author: 孔个个
Title: 主从复制直接转换MGR_5.7验证试验

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/622773/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • Java数据结构和算法

    一、数据结构 数据结构是计算机存储、组织数据的方式,指相互之间存在一种或多种特定关系的数据元素的集合。 通常情况下,精心选择的数据结构可以带来更高的运行或者存储效率。数据结构往往同…

    数据库 2023年6月11日
    0113
  • BigDecimal 设置小数位数、小数比例转换整数

    控制小数位数 DecimalFormat decimalFormat = new DecimalFormat("0.00"); decimalFormat.fo…

    数据库 2023年6月6日
    0105
  • IDEA插件和个性化配置推荐

    插件推荐 我自己现在使用的一些插件和一些自己感觉比较舒服配置分析给大家 idea如何安装插件: 如果打开设置没有看到,直接搜索plugins 然后在这里搜索即可 CodeGlanc…

    数据库 2023年6月16日
    0108
  • Postgres14.4(Docker安装)

    Postgres14.4(Docker安装) 一,Docker拉取镜像 docker pull postgres:14.4 #检查镜像是否拉取成功 docker images | …

    数据库 2023年6月11日
    0116
  • 社招两年半10个公司28轮面试面经(含字节、拼多多、美团、滴滴……)

    个人情况 学历:二本 工作年限:2 年半 面试结果:拿到了字节、拼多多、美团、滴滴、欧科云链….等公司的offer 面试准备 复习 你信我,真学习还得看书,看书能让你的…

    数据库 2023年6月16日
    0108
  • Javaweb-Ajax详解

    一、概念 Ajax = Asynchronous JavaScript and XML(异步的JavaScript和XML) Ajax不是一种新的编程语言,而是一种用于创建更好更快…

    数据库 2023年6月16日
    095
  • 从源码角度谈谈open_files_limit的生成逻辑及”Too many open files”的解决思路

    “Too many open files”是一个比较常见的错误,不仅仅是在 MySQL 中。只要是在 Linux 中启动的进程,都有可能遇到这个错误。 究其…

    数据库 2023年6月11日
    0165
  • MySQL事务提交流程

    有binlog的CR方式(重点核心!!):有binlog情况下,commit动作开始时,会有一个Redo XID 的动作记录写到redo,然后写data到binlog,binlog…

    数据库 2023年6月16日
    0109
  • 国内访问github很慢或者访问不了,解决办法

    国内因为某些问题,会屏蔽国外一些网站。有时GitHub访问的时候会出现错误,无法访问。解决办法 进入https://fastly.net.ipaddress.com/(全球最好的I…

    数据库 2023年6月6日
    0142
  • MySQL之自关联查询

    假设要设计两张表,一张省份表(provinces),一张城市表(citys) 省份表结构 id ptitle 城市表结构 id ctitle proid(表示城市所属的省,对应着省…

    数据库 2023年5月24日
    0152
  • innoDB对MVCC的实现

    InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题: 1、执行普通 select,此时会以 MVCC 快照读的方式读取数据 在快照读的…

    数据库 2023年6月16日
    0102
  • [LeetCode]26. 删除排序数组中的重复项

    给定一个排序数组,你需要在 原地 删除重复出现的元素,使得每个元素只出现一次,返回移除后数组的新长度。 不要使用额外的数组空间,你必须在 原地 修改输入数组 并在使用 O(1) 额…

    数据库 2023年6月9日
    0133
  • B树-插入

    B树系列文章 1. B树-介绍 2. B树-查找 3. B树-插入 4. B树-删除 插入 根据B树的以下两个特性 每一个结点最多有m个子结点 有k个子结点的非叶子结点拥有 k −…

    数据库 2023年6月14日
    0114
  • Redis+Lua实现简易的秒杀抢购

    1 商品抢购 主要逻辑是:减库存,记录抢购成功的用户 @RestController public class DemoController { @Resource private…

    数据库 2023年6月14日
    0123
  • [转]万智牌规则和异能详解

    下面这些都是之前的旧文档了,直到我发现了一个神奇的网站。建议大家有任何疑问,都可以到这里查看规则文档 点击网站的右上方可以搜索 最近游戏过程中发现规则和异能详解的文档很少,找起来非…

    数据库 2023年6月9日
    0200
  • 手写LRU缓存淘汰算法

    概述 LRU算法全称为 Least Recently Used是一种常见的页面缓存淘汰算法,当缓存空间达到达到预设空间的情况下会删除那些 最久没有被使用的数据 。 常见的页面缓存淘…

    数据库 2023年6月11日
    0143
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球