MGR停服时长理论测试

2024年 5月 20日 43.7k 0

【环境信息】

192.168.1.181

192.168.1.182

192.168.1.183

mysql> SELECT member_host,member_port,member_state,member_role,member_communication_stack FROM performance_schema.replication_group_members;
+---------------+-------------+--------------+-------------+----------------------------+
| member_host | member_port | member_state | member_role | member_communication_stack |
+---------------+-------------+--------------+-------------+----------------------------+
| 192.168.1.183 | 3306 | ONLINE | SECONDARY | XCom |
| 192.168.1.182 | 3306 | ONLINE | PRIMARY | XCom |
| 192.168.1.181 | 3306 | ONLINE | SECONDARY | XCom |
+---------------+-------------+--------------+-------------+----------------------------+
3 rows in set (0.00 sec)

【测试项目】

1、MGR主节点宕机,新的主节点被选举出来耗时多久?

2、MGR主节点服务器异常关闭、重启,新的主节点被选举出来耗时多久?

3、MGR主节点正常关闭MySQL服务,新的主节点被选举出来耗时多久?

4、MGR主节点正常关闭、重启操作系统,新的主节点被选举出来耗时多久?

5、MGR主节点网络断开,新的主节点被选举出来耗时多久?

【测试结论】

1、MGR主节点宕机,新的主节点被选举出来耗时多久?

测试结论:16秒。

2、MGR主节点服务器异常关闭、重启,新的主节点被选举出来耗时多久?

测试结论:16秒。

3、MGR主节点正常关闭MySQL服务,新的主节点被选举出来耗时多久?

测试结论:1秒。

4、MGR主节点正常关闭、重启操作系统,新的主节点被选举出来耗时多久?

测试结论:1秒。

5、MGR主节点网络断开,新的主节点被选举出来耗时多久?

测试结论:16秒。

测试项目1:MGR主节点宕机

操作:直接kill掉mysqld进程。

2024-05-20T09:41:49.386642+08:00 0 [Warning] [MY-011493] [Repl] Plugin group_replication reported: 'Member with address 192.168.1.181:3306 has become unreachable.'
2024-05-20T09:42:05.395399+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.1.181:3306'
2024-05-20T09:42:05.395508+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.1.181:3306 left the group. Electing new Primary.'
2024-05-20T09:42:05.396457+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.1.182:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2024-05-20T09:42:05.397503+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.1.183:3306, 192.168.1.182:3306 on view 17158297796508489:78.'
2024-05-20T09:42:05.400140+08:00 46 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2024-05-20T09:42:05.401665+08:00 31 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'
2024-05-20T09:42:05.401785+08:00 31 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2024-05-20T09:42:05.402757+08:00 31 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.'
2024-05-20T09:42:05.406238+08:00 46 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

测试项目2:MGR主节点服务器异常关闭、重启

操作:直接关闭服务器电源。

2024-05-20T09:48:55.125556+08:00 0 [Warning] [MY-011493] [Repl] Plugin group_replication reported: 'Member with address 192.168.1.182:3306 has become unreachable.'
2024-05-20T09:49:08.857188+08:00 59 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-05-20T09:49:11.137746+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.1.182:3306'
2024-05-20T09:49:11.137829+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.1.182:3306 left the group. Electing new Primary.'
2024-05-20T09:49:11.139017+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.1.181:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2024-05-20T09:49:11.139735+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.1.183:3306, 192.168.1.181:3306 on view 17158297796508489:80.'
2024-05-20T09:49:11.142212+08:00 63 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2024-05-20T09:49:11.143636+08:00 42 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'
2024-05-20T09:49:11.143757+08:00 42 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2024-05-20T09:49:11.144733+08:00 42 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.'
2024-05-20T09:49:11.150054+08:00 63 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

测试项目3:MGR主节点正常关闭MySQL服务

操作:systemctl stop mysqld

2024-05-20T09:59:14.896128+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.1.181:3306'
2024-05-20T09:59:14.896332+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.1.181:3306 left the group. Electing new Primary.'
2024-05-20T09:59:14.897064+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.1.182:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2024-05-20T09:59:14.898647+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.1.183:3306, 192.168.1.182:3306 on view 17158297796508489:82.'
2024-05-20T09:59:14.902250+08:00 93 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2024-05-20T09:59:14.904199+08:00 69 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'
2024-05-20T09:59:14.904474+08:00 69 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2024-05-20T09:59:14.905984+08:00 69 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.'
2024-05-20T09:59:14.910025+08:00 93 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

测试项目4:MGR主节点正常关闭、重启操作系统

操作:shutdown -h now

# shutdown -h now
2024-05-20T10:03:25.919560+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.1.182:3306'
2024-05-20T10:03:25.919643+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.1.182:3306 left the group. Electing new Primary.'
2024-05-20T10:03:25.920499+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.1.181:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2024-05-20T10:03:25.921217+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.1.183:3306, 192.168.1.181:3306 on view 17158297796508489:84.'
2024-05-20T10:03:25.923837+08:00 55 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2024-05-20T10:03:25.926773+08:00 39 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'
2024-05-20T10:03:25.926893+08:00 39 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2024-05-20T10:03:25.928047+08:00 39 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.'
2024-05-20T10:03:25.931689+08:00 55 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

5、MGR主节点网络断开,新的主节点被选举出来耗时多久?

操作:断开MGR主节点的网络

2024-05-20T10:28:51.110934+08:00 0 [Warning] [MY-011493] [Repl] Plugin group_replication reported: 'Member with address 192.168.1.182:3306 has become unreachable.'
2024-05-20T10:28:53.573612+08:00 51 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-05-20T10:29:07.121797+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.1.182:3306'
2024-05-20T10:29:07.121879+08:00 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address 192.168.1.182:3306 left the group. Electing new Primary.'
2024-05-20T10:29:07.122553+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.1.181:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2024-05-20T10:29:07.123586+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.1.183:3306, 192.168.1.181:3306 on view 17158297796508489:88.'
2024-05-20T10:29:07.127313+08:00 55 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2024-05-20T10:29:07.128560+08:00 36 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'
2024-05-20T10:29:07.128744+08:00 36 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2024-05-20T10:29:07.129578+08:00 36 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.'
2024-05-20T10:29:07.133046+08:00 55 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论