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;
