mysql MGR搭建和模拟切换演练

  • 水逸冰

    2020-07-26 17:29:29
  • MySQL
  • 原创

mysql MGR

环境

序号 IP port
1 192.168.61.16 3310
2 192.168.61.17 3310
3 192.168.61.18 3310

mgr配置的部分参数文件

#for GTID


gtid_mode = on


enforce_gtid_consistency = on


#for MTS


binlog_group_commit_sync_delay = 10000


binlog_group_commit_sync_no_delay_count = 100


slave-parallel-type=LOGICAL_CLOCK


slave-parallel-workers=4        #一般建议设置4-8，太多的线程会增加线程之间的同步开销


master_info_repository=TABLE


relay_log_info_repository=TABLE


relay_log_recovery=ON






#for semi sync


plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"


rpl-semi-sync-master-enabled = 1


rpl-semi-sync-slave-enabled = 1






#group replication config


binlog_checksum = NONE


transaction-write-set-extraction = XXHASH64


loose-group_replication_start_on_boot = OFF


loose-group_replication_bootstrap_group = OFF


loose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057


loose-group_replication_local_address = '192.168.61.18:33310'


loose-group_replication_group_seeds = '192.168.61.16:33310,192.168.61.17:33310,192.168.61.18:33310'


loose-group_replication_single_primary_mode=true


loose-group_replication_enforce_update_everywhere_checks=false

部分参数含义

参数名 含义
binlog_checksum 禁用binlog_checksum，避免不同版本间出现的checksum的问题。也可以不配置，不是必须的。
transaction-write-set-extraction 组复制要求每个表必须要有主键，该参数是指server层采集被更新的主键信息被哈希后存储起来。同时一个组所有的成员必须要配置相同的哈希算法。
group_replication_start_on_boot  Server启动时不自动启动组复制
group_replication_group_name  将加入或者创建的复制组命名为5e1f9814-c91d-11e7-8f9b-000c29ec1057，可自定义(通过cat /proc/sys/kernel/random/uuid)。任何成员生成的gtid都会使用这个UUID。同时成员加入组是会检查组名和加入的组名是否一致。
group_replication_local_address 设置成员本地地址
group_replication_group_seeds 设置种子成员的地址。新成员加入组时要和组内成员通信，因此需要至少一个成员的地址。
group_replication_single_primary_mode 单主模式
group_replication_enforce_update_everywhere_checks 在单主模式下，该参数必须被设置为 FALSE。当主节点宕掉，自动会根据服务器的server_uuid变量和group_replication_member_weight变量值，选择下一个slave谁作为主节点，group_replication_member_weight的值最高的成员被选为新的主节点，在group_replication_member_weight值相同的情况下，group根据数据字典中 server_uuid排序，排序在最前的被选择为主节点

此外，group_replication相关变量使用的loose-前缀，是指示Server启用时尚未加载复制插件也将继续启动。

开启单主模式

mysql> install plugin group_replication soname "group_replication.so";


Query OK, 0 rows affected (0.38 sec)






#启用第一个成员时，用下面的参数告诉group replication插件，这是该组第一个成员，需要做一些初始化操作。


mysql> set global group_replication_bootstrap_group=on;


Query OK, 0 rows affected (0.00 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.






日志：


2020-07-26T06:59:44.298053Z 2 [Warning] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.'


需要开启slave_preserve_commit_order










mysql> set global slave_preserve_commit_order = 1; 


Query OK, 0 rows affected (0.00 sec)


mysql> START GROUP_REPLICATION;


Query OK, 0 rows affected (2.12 sec)

加入其它成员，同时注意每个节点都要有repl账号

mysql> install plugin group_replication soname "group_replication.so";


Query OK, 0 rows affected (0.45 sec)






mysql> change master to master_user='repl',master_password='oracle' for channel 'group_replication_recovery';


Query OK, 0 rows affected, 2 warnings (0.01 sec)






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 | 68e12753-ce83-11ea-894d-000c297c40da | mdb02       |        3310 | ONLINE       |


| group_replication_applier | fff6c992-ce81-11ea-ae13-000c2970dcdf | mdb01       |        3310 | ONLINE       |


+---------------------------+--------------------------------------+-------------+-------------+--------------+


2 rows in set (0.00 sec)

加入三个节点

mysql> install plugin group_replication soname "group_replication.so";


Query OK, 0 rows affected (0.14 sec)






mysql> set global group_replication_local_address = '192.168.61.18:33310';  


Query OK, 0 rows affected (0.00 sec)






mysql> set global group_replication_group_seeds = '192.168.61.16:33310,192.168.61.17:33310,192.168.61.18:33310';


Query OK, 0 rows affected (0.00 sec)






mysql> change master to master_user='repl',master_password='oracle' for channel 'group_replication_recovery';


Query OK, 0 rows affected, 2 warnings (0.01 sec)






mysql>  START GROUP_REPLICATION;


Query OK, 0 rows affected (3.33 sec)

节点加入过程中可以看到recovering状态，完成后是online状态。

mysql> SELECT * FROM performance_schema.replication_group_members;


+---------------------------+--------------------------------------+-------------+-------------+--------------+


| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |


+---------------------------+--------------------------------------+-------------+-------------+--------------+


| group_replication_applier | 68e12753-ce83-11ea-894d-000c297c40da | mdb02       |        3310 | ONLINE       |


| group_replication_applier | 7eb54729-ce83-11ea-8ac5-000c294cc2bd | mdb03       |        3310 | RECOVERING   |


| group_replication_applier | fff6c992-ce81-11ea-ae13-000c2970dcdf | mdb01       |        3310 | ONLINE       |


+---------------------------+--------------------------------------+-------------+-------------+--------------+


3 rows in set (0.00 sec)






mysql> SELECT * FROM performance_schema.replication_group_members;


+---------------------------+--------------------------------------+-------------+-------------+--------------+


| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |


+---------------------------+--------------------------------------+-------------+-------------+--------------+


| group_replication_applier | 68e12753-ce83-11ea-894d-000c297c40da | mdb02       |        3310 | ONLINE       |


| group_replication_applier | 7eb54729-ce83-11ea-8ac5-000c294cc2bd | mdb03       |        3310 | ONLINE       |


| group_replication_applier | fff6c992-ce81-11ea-ae13-000c2970dcdf | mdb01       |        3310 | ONLINE       |


+---------------------------+--------------------------------------+-------------+-------------+--------------+


3 rows in set (0.00 sec)

mgr单主模式故障切换

自动切换规则：

当主节点宕掉，自动会根据服务器的server_uuid变量和group_replication_member_weight变量值，选择下一个slave谁作为主节点，group_replication_member_weight的值最高的成员被选为新的主节点，在group_replication_member_weight值相同的情况下，group根据数据字典中 server_uuid排序，排序在最前的被选择为主节点。

杀掉主库后，从库日志记录到主库不通，集群随之发生了重构。

2020-07-26T08:46:37.806614Z 0 [Warning] Plugin group_replication reported: 'Member with address mdb01:3310 has become unreachable.'


2020-07-26T08:46:38.678114Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: mdb01:3310'

此时从库mdb02已经被提升为主库，super_read_only和read_only参数已经被关闭

mysql> show global variables like '%read_only%';


+-----------------------+-------+


| Variable_name         | Value |


+-----------------------+-------+


| innodb_read_only      | OFF   |


| read_only             | OFF   |


| super_read_only       | OFF   |


| transaction_read_only | OFF   |


| tx_read_only          | OFF   |


+-----------------------+-------+


5 rows in set (0.00 sec)

因为mdb02 的UUID是‘06d6223d-cf1c-11ea-8b6c-000c297c40da’，mdb03是‘083931e6-cf1c-11ea-842a-000c294cc2bd’

mysql> select '083931e6-cf1c-11ea-842a-000c294cc2bd'>'06d6223d-cf1c-11ea-8b6c-000c297c40da';


+-------------------------------------------------------------------------------+


| '083931e6-cf1c-11ea-842a-000c294cc2bd'>'06d6223d-cf1c-11ea-8b6c-000c297c40da' |


+-------------------------------------------------------------------------------+


|                                                                             1 |


+-------------------------------------------------------------------------------+


1 row in set (0.00 sec)

老的主库重新加入集群

mysql> change master to master_user='repl',master_password='oracle' for channel 'group_replication_recovery';


Query OK, 0 rows affected, 2 warnings (0.01 sec)






mysql> start group_replication;


Query OK, 0 rows affected (2.83 sec)

正常关闭现在的主库mdb02.比较uuid，发现mdb03会被提升为主库，现在我们用group_replication_member_weight参数控制。

mdb01执行：

mysql> set global group_replication_member_weight=60;


Query OK, 0 rows affected (0.00 sec)

正常关闭mdb02，其他节点日志中只会出现

2020-07-26T09:04:06.564346Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: mdb02:3310'

mdb01如愿提升为主库

mysql>  show global variables like '%read_only%';


+-----------------------+-------+


| Variable_name         | Value |


+-----------------------+-------+


| innodb_read_only      | OFF   |


| read_only             | OFF   |


| super_read_only       | OFF   |


| transaction_read_only | OFF   |


| tx_read_only          | OFF   |


+-----------------------+-------+


5 rows in set (0.00 sec)

mgr单主模式切换到多主模式


# 停止组复制(所有节点执行)：



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;







# 其他节点执行



START 
GROUP_REPLICATION;

mgr多主模式切换到单主模式


# 所有节点执行



stop 
group_replication;



set 
global 
group_replication_enforce_update_everywhere_checks
=
OFF;



set 
global 
group_replication_single_primary_mode
=
ON;







# 主节点执行



SET 
GLOBAL 
group_replication_bootstrap_group
=
ON; 



START 
GROUP_REPLICATION; 



SET 
GLOBAL 
group_replication_bootstrap_group
=
OFF;







# 从节点执行



START 
GROUP_REPLICATION;



请使用浏览器的分享功能分享到微信等