MySQL InnoDB Cluster节点告警Instance has 'offline_mode' enabled小结

案例描述

测试环境,一套版本为MySQL 8.4.8的InnoDB Cluster集群,由于一些操作,导致MySQL InnoDB Cluster中备节点出现错误.解决问题后,发现集群的从节点虽然状态联机(ONLINE)了,但是报实例错误:"WARNING: Instance has 'offline_mode' enabled.". 如下所示:

MySQL  mysqldbu02:7306 ssl  JS > cluster.status()
{
    "clusterName": "yssps", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysqldbu01:7306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysqldbu01:7306": {
                "address": "mysqldbu01:7306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.8"
            }, 
            "mysqldbu02:7306": {
                "address": "mysqldbu02:7306", 
                "instanceErrors": [
                    "WARNING: Instance has 'offline_mode' enabled."
                ], 
                "memberRole": "SECONDARY", 
                "mode": "n/a", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.8"
            }, 
            "mysqldbu03:7306": {
                "address": "mysqldbu03:7306", 
                "instanceErrors": [
                    "WARNING: Instance has 'offline_mode' enabled."
                ], 
                "memberRole": "SECONDARY", 
                "mode": "n/a", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.8"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysqldbu01:7306"
}

节点offline_mode=ON会拒绝普通业务账号新建连接，仅 SUPER / CONNECTION_ADMIN 管理员账号可登录;集群状态虽然 ONLINE，但 MySQL Router 转发过来的业务读连接会失败。

原因分析

官方文档描述如下所示:

When group_replication_exit_state_action is set to OFFLINE_MODE, if the member exits the group unintentionally or exhausts its auto-rejoin attempts, the instance switches MySQL to offline mode (by setting the system variable offline_mode to ON). In this mode, connected client users are disconnected on their next request and connections are no longer accepted, with the exception of client users that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). Group Replication also sets the system variable super_read_only to ON, so clients cannot make any updates, even if they have connected with the CONNECTION_ADMIN or SUPER privilege.

触发根源：Group Replication的节点意外离组时，系统变量group_replication_exit_state_action的默认值是 OFFLINE_MODE，MySQL因此自动设置为offline_mode(MySQL8.4 默认就是这个行为)

检查节点的系统变量:

mysql> SHOW VARIABLES LIKE 'group_replication_exit_state_action';
+-------------------------------------+--------------+
| Variable_name                       | Value        |
+-------------------------------------+--------------+
| group_replication_exit_state_action | OFFLINE_MODE |
+-------------------------------------+--------------+
1 row in set (0.00 sec)

mysql> 

系统变量group_replication_exit_state_action有三选项：

• OFFLINE_MODE：离组 ---> 开启 offline_mode（8.4 默认）
• READ_ONLY ：离组 ---> super_read_only
• ABORT_SERVER：离组 ---> 直接宕机

解决方案

检查MySQL的相关参数/系统变量,如下所示

mysql> SELECT @@offline_mode, @@group_replication_exit_state_action;
+----------------+---------------------------------------+
| @@offline_mode | @@group_replication_exit_state_action |
+----------------+---------------------------------------+
|              1 | OFFLINE_MODE                          |
+----------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'group_replication_exit_state_action';
+-------------------------------------+--------------+
| Variable_name                       | Value        |
+-------------------------------------+--------------+
| group_replication_exit_state_action | OFFLINE_MODE |
+-------------------------------------+--------------+
1 row in set (0.00 sec)

mysql> 

mysql> show variables like 'offline_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| offline_mode  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

登录MySQL InnoDB Cluster的各个备库/从节点, 执行下面语句中的一个,执行完SQL后，业务账号立刻可以连接该从节点，mysqlsh cluster.status()命令中的的这条warning消失。

-- 动态关闭，立即生效
SET GLOBAL offline_mode = OFF;

-- persist持久化（MySQL8.0+，写入mysqld-auto.cnf，重启不丢失）
SET PERSIST offline_mode = OFF;

验证检查发现变量offline_mode

mysql> show variables like 'offline_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| offline_mode  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)