美文网首页
MySQL ( MGR ) 07-切换到多主模式 & 切回单主模

MySQL ( MGR ) 07-切换到多主模式 & 切回单主模

作者: 轻飘飘D | 来源:发表于2021-02-02 22:18 被阅读0次

    MGR切换模式需要重新启动组复制,因些需要在所有节点上先关闭组复制,
    设置 group_replication_single_primary_mode=OFF 等参数,再启动组复制。
    1.切换到多主模式
    1.1.停止组复制(在所有MGR节点上执行)

    stop group_replication;
    set global group_replication_single_primary_mode=OFF;
    set global group_replication_enforce_update_everywhere_checks=ON;
    
    my.cnf文件中修改(如要永久生效则如下修改)
    loose-group_replication_single_primary_mode = OFF
    loose-group_replication_enforce_update_everywhere_checks = ON
    

    1.2.开启(group_replication_bootstrap_group=ON)引导功能 (随便选择某个MGR节点)

    #这里选择xag211 上
    SET GLOBAL group_replication_bootstrap_group=ON;
    START GROUP_REPLICATION;
    SET GLOBAL group_replication_bootstrap_group=OFF;
    

    1.3.其他的MGR节点(xag212,xag213)

    START GROUP_REPLICATION;
    
    root@xag213:testdb [:53: ] 13 SQL->SELECT * FROM performance_schema.replication_group_members;
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
    | group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212      |        3306 | ONLINE       |
    | group_replication_applier | 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213      |        3306 | ONLINE       |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    
    root@xag213:testdb [:54: ] 17 SQL->show global variables like 'group_replication_single_primary_mode';
    +---------------------------------------+-------+
    | Variable_name                         | Value |
    +---------------------------------------+-------+
    | group_replication_single_primary_mode | OFF   |
    +---------------------------------------+-------+
    
    

    1.4.多主测试

    root@xag213:testdb [:56: ] 21 SQL->delete from t1 where Id=5;
    
    root@xag212:testdb [:57: ] 15 SQL->delete from t1 where Id=4;
    
    root@xag211:testdb [:58: ] 26 SQL->delete from t1 where Id=3;
    
    root@xag211:testdb [:58: ] 27 SQL->select * from t1;
    +----+----------+
    | Id | TestName |
    +----+----------+
    |  1 | a        |
    |  2 | b        |
    +----+----------+
    
    

    2.切回单主模式
    2.1 停止组复制(在所有MGR节点上执行)

    stop group_replication;
    set global group_replication_enforce_update_everywhere_checks=OFF;
    set global group_replication_single_primary_mode=ON;
    
    my.cnf文件中修改(如要永久生效则如下修改)
    loose-group_replication_single_primary_mode = ON
    loose-group_replication_enforce_update_everywhere_checks = OFF
    

    2.2 选择一个节点作为主节点, 在主节点上执行 (这里选择xag211节点作为主节点)

    SET GLOBAL group_replication_bootstrap_group=ON;
    START GROUP_REPLICATION;
    SET GLOBAL group_replication_bootstrap_group=OFF;
    
    root@xag211:testdb [:13: ] 39 SQL->select * from performance_schema.replication_group_members;
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    
    

    2.3 在其他剩余的节点, 也就是从库节点上执行 (这里从库节点指的就是xag212和xag213)

    START GROUP_REPLICATION;
    
    root@xag211:testdb [:15: ] 41 SQL->select * from performance_schema.replication_group_members;
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    | group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
    | group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212      |        3306 | ONLINE       |
    | group_replication_applier | 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213      |        3306 | ONLINE       |
    +---------------------------+--------------------------------------+-------------+-------------+--------------+
    
    root@xag211:testdb [:15: ] 42 SQL->
    SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM 
    performance_schema.global_status ta,performance_schema.replication_group_members tb 
    WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;
    +----------------------------------+--------------------------------------+-------------+-------------+--------------+
    | VARIABLE_NAME                    | VARIABLE_VALUE                       | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    +----------------------------------+--------------------------------------+-------------+-------------+--------------+
    | group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211      |        3306 | ONLINE       |
    +----------------------------------+--------------------------------------+-------------+-------------+--------------+
    
    

    相关文章

      网友评论

          本文标题:MySQL ( MGR ) 07-切换到多主模式 & 切回单主模

          本文链接:https://www.haomeiwen.com/subject/zalutltx.html