ITPub博客

首页 > 数据库 > MySQL > mysql MGR搭建和模拟切换演练

mysql MGR搭建和模拟切换演练

原创 MySQL 作者:水逸冰 时间:2020-07-26 17:29:29 0 删除 编辑

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;



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31480688/viewspace-2707032/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
精通oracle,mysql和linux,热衷于研究数据库,擅长shell和Python自动化运维。VX:18302174682

注册时间:2017-08-05

  • 博文量
    108
  • 访问量
    122833