MySQL :: MySQL Shell 8.4 :: 7.7 Monitoring InnoDB Cluster

MySQL Shell 8.4 / MySQL InnoDB Cluster / Monitoring InnoDB Cluster

7.7 Monitoring InnoDB Cluster

This section describes how to use AdminAPI to monitor an InnoDB Cluster.

Using `Cluster.describe()`

To get information about the structure of the InnoDB Cluster itself, use the Cluster.describe() function:

mysql-js> cluster.describe();
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "ic-1:3306",
                "label": "ic-1:3306",
                "role": "HA"
            },
            {
                "address": "ic-2:3306",
                "label": "ic-2:3306",
                "role": "HA"
            },
            {
                "address": "ic-3:3306",
                "label": "ic-3:3306",
                "role": "HA"
            }
        ]
    }
}

The output from this function shows the structure of the InnoDB Cluster including all of its configuration information, and so on. The address, label and role values match those described at Checking a cluster's Status with Cluster.status() .

Checking a cluster's Status with `Cluster.status()`

Cluster objects provide the status() method that enables you to check how a cluster is running. Before you can check the status of the InnoDB Cluster, you need to get a reference to the InnoDB Cluster object by connecting to any of its instances. However, if you want to make changes to the configuration of the cluster, you must connect to a "R/W" instance. Issuing status() retrieves the status of the cluster based on the view of the cluster which the server instance you are connected to is aware of and outputs a status report.

Important

The instance's state in the cluster directly influences the information provided in the status report. Therefore ensure the instance you are connected to has a status of ONLINE.

For information about how the InnoDB Cluster is running, use the cluster's status() method:

mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.status()
{
    "clusterName": "testcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "ic-1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "ic-1:3306": {
                "address": "ic-1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE"
                "version": "8.0.30"
            },
            "ic-2:3306": {
                "address": "ic-2:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE"
                "version": "8.0.30"
            },
            "ic-3:3306": {
                "address": "ic-3:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE"
                "version": "8.0.30"
            }
        }
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysql://icadmin@ic-1:3306"
}

The output of Cluster.status() provides the following information:

clusterName: name assigned to this cluster during dba.createCluster().
defaultReplicaSet: the server instances which belong to an InnoDB Cluster and contain the data set.
primary: displayed when the cluster is operating in single-primary mode only. Shows the address of the current primary instance. If this field is not displayed, the cluster is operating in multi-primary mode.
ssl: whether secure connections are used by the cluster or not. Shows values of REQUIRED or DISABLED, depending on how the memberSslMode option was configured during either createCluster() or addInstance(). The value returned by this parameter corresponds to the value of the group_replication_ssl_mode server variable on the instance. See Section 7.6, “Securing InnoDB Cluster”.
status: The status of the InnoDB Cluster. The status describes the high availability provided by this cluster. The status is one of the following:
- OK: The cluster is online and can tolerate up to n failures. There are three or more members in the cluster, and they are functioning.
- OK_PARTIAL: The cluster is online and can tolerate up to n failures. At least three of the member servers in the cluster are in Group Replication's online state. However, one or more member servers are not currently participating as active members of the cluster.
- OK_NO_TOLERANCE: The cluster is not tolerant to any failures.
- OK_NO_TOLERANCE_PARTIAL: The cluster is not tolerant to any failures. One or two member servers in the cluster are online, but one or more servers are in an offline, recovering, error, or unreachable state. The cluster does not have sufficient tolerance for failures because of the unavailability of some members.
- NO_QUORUM: The cluster does not have quorum, meaning that a majority of the replication group's member servers are unavailable for agreeing on a decision, and cannot process write transactions.
- OFFLINE: All members of the group are offline.
- ERROR: There are no online members in the cluster.
- UNREACHABLE: There is no connectivity to any online members.
- UNKNOWN: There is no connectivity to any online members.
- FENCED_WRITES: The cluster is fenced from write traffic.
topology: The status of the MySQL Server instance. The status is one of the following:
- Host name of instance: The host name of an instance, for example "localhost:3310".
- memberRole the Member Role as reported by the Group Replication plugin, see the MEMBER_ROLE column of the replication_group_members table.
- mode: whether the server is read-write ("R/W") or read-only ("R/O"). This is derived from the current state of the super_read_only variable on the instance, and whether the cluster has quorum. In previous versions the value of mode was derived from whether the instance was serving as a primary or secondary instance. Usually if the instance is a primary, then the mode is "R/W", and if the instance is a secondary the mode is "R/O". Any instances in a cluster that have no visible quorum are marked as "R/O", regardless of the state of the super_read_only variable.
  
  Note
  
  If the member status is anything other than ONLINE, mode is reported as n/a.
- replicationLag: returns one of the following values:
  - The time difference between the last transaction commit timestamp and the last transaction applied timestamp, in HH:MM:SS format.
    
    If multiple workers are used, the value is retrieved from the worker executing the oldest transaction.
  - null: The replication connection or SQL thread is not running.
  - applier_queue_applied: The applier queue has applied everything. That is, if the last queued transaction and the last applied transaction are the same, or the applying transaction is 0.
- role: what function this instance provides in the cluster. Currently only HA, for high availability.
- status: The status of this element of the cluster. The status is one of the following:
  - ONLINE: The instance is online and participating in the cluster.
  - OFFLINE: The instance has lost connection to the other instances.
  - RECOVERING: The instance is attempting to synchronize with the cluster by retrieving transactions it needs before it can become an online member.
  - UNREACHABLE: The instance has lost communication with the cluster.
  - ERROR: The instance has encountered an error during the recovery phase or while applying a transaction.
    
    Important
    
    Once an instance enters ERROR state, the super_read_only option is set to ON. To leave the ERROR state you must manually configure the instance with super_read_only=OFF.
  - (MISSING): The state of an instance which is part of the configured cluster, but is currently unavailable.
    
    Note
    
    The MISSING state is specific to InnoDB Cluster, it is not a state generated by Group Replication. MySQL Shell uses this state to indicate instances that are registered in the metadata, but cannot be found in the live cluster view.
- groupInformationSourceMember: the internal connection used to get information about the cluster, shown as a URI-like connection string. Usually the connection initially used to create the cluster.
version: the MySQL Server version running on the instance. See Checking the MySQL Version on Instances for more information.

To display more information about the cluster use the extended option. The extended option supports integer or Boolean values. To configure the additional information that Cluster.status({'extended':value}) provides, use the following values:

0: disables the additional information, the default
1: includes information about the Group Replication Protocol Version, Group name, communication stack, cluster member UUIDs, cluster member roles and states as reported by Group Replication, and the list of fenced system variables
2: includes information about transactions processed by connection and applier
3: includes more detailed statistics about the replication performed by each cluster member.

Setting extended using Boolean values is the equivalent of setting the integer values 0 and 1.

When you issue Cluster.status({'extended':1}), or the extended option is set to true, the output includes:

the following additional attributes for the defaultReplicaSet object:
- GRProtocolVersion: the Group Replication Protocol Version being used in the cluster.
  
  Tip
  
  InnoDB Cluster manages the Group Replication Protocol version being used automatically, see InnoDB Cluster and Group Replication Protocol for more information.
- communicationStack: the communication stack in use by the cluster. Possible values are XCOM or MYSQL. See Section 7.5.9, “Configuring the Group Replication Communication Stack” for more information.
- groupName: the group's name, a UUID.
- groupViewChangeUuid: the value of group_replication_view_change_uuid.
- groupViewId: the current view identifier for this group. This value is taken from the VIEW_ID column of the replication_group_member_stats table.
- paxosSingleLeader: displays the value of group_replication_paxos_single_leader.
  
  Note
  
  This is only available on MySQL Server 8.0.31, or higher, because MySQL Shell requires the information provided by WRITE_CONSENSUS_SINGLE_LEADER_CAPABLE in the replication_group_communication_information table, which was introduced in MySQL 8.0.31.
the following additional attributes for each object of the topology object:
- fenceSysVars a list containing the name of the fenced system variables which are configured by AdminAPI. Currently the fenced system variables considered are read_only, super_read_only and offline_mode. The system variables are listed regardless of their value.
- instanceErrors for each instance, displaying any diagnostic information that can be detected for the instance. For example, if the instance is a secondary and the super_read_only variable is not set to ON, then a warning is shown. This information can be used to troubleshoot errors.
- memberId Each cluster member UUID.
- memberState the Member State as reported by the Group Replication plugin, see the MEMBER_STATE column of the replication_group_members table.

To see information about recovery and regular transaction I/O, applier worker thread statistics and any lags; applier coordinator statistics, if the parallel replication applier is enabled; error, and other information from the receiver and applier threads, use a value of 2 or 3 for extended. When you use these values, a connection to each instance in the cluster is opened so that additional instance specific statistics can be queried. The exact statistics that are included in the output depend on the state and configuration of the instance and the server version. This information matches that shown in the replication_group_member_stats table, see the descriptions of the matching columns for more information. Instances which are ONLINE have a transactions section included in the output. Instances which are RECOVERING have a recovery section included in the output. When you set extended to 2, in either case, these sections can contain the following:

appliedCount: see COUNT_TRANSACTIONS_REMOTE_APPLIED
checkedCount: see COUNT_TRANSACTIONS_CHECKED
committedAllMembers: see TRANSACTIONS_COMMITTED_ALL_MEMBERS
conflictsDetectedCount: see COUNT_CONFLICTS_DETECTED
inApplierQueueCount: see COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE
inQueueCount: see COUNT_TRANSACTIONS_IN_QUEUE
lastConflictFree: see LAST_CONFLICT_FREE_TRANSACTION
proposedCount: see COUNT_TRANSACTIONS_LOCAL_PROPOSED
rollbackCount: see COUNT_TRANSACTIONS_LOCAL_ROLLBACK

When you set extended to 3, the connection section shows information from the replication_connection_status table.

The currentlyQueueing section has information about the transactions currently queued:

immediateCommitTimestamp: see QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
immediateCommitToNowTime: see QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minus NOW()
originalCommitTimestamp: see QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP
originalCommitToNowTime: see QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minus NOW()
startTimestamp: see QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP
transaction: see QUEUEING_TRANSACTION
lastHeartbeatTimestamp: see LAST_HEARTBEAT_TIMESTAMP

The lastQueued section has information about the most recently queued transaction:

endTimestamp: see LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP
immediateCommitTimestamp: see LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
immediateCommitToEndTime: LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minus NOW()
originalCommitTimestamp: see LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP
originalCommitToEndTime: LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minus NOW()
queueTime: LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP minus LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP
startTimestamp: see LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP
transaction: see LAST_QUEUED_TRANSACTION
receivedHeartbeats: see COUNT_RECEIVED_HEARTBEATS
receivedTransactionSet: see RECEIVED_TRANSACTION_SET
threadId: see THREAD_ID

Instances which are using a multithreaded replica have a workers section which contains information about the worker threads, and matches the information shown by the replication_applier_status_by_worker table.

The lastApplied section shows the following information about the last transaction applied by the worker:

applyTime: see LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP minus LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP
endTimestamp: see LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP
immediateCommitTimestamp: see LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
immediateCommitToEndTime: see LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minus NOW()
originalCommitTimestamp: see LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP
originalCommitToEndTime: see LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minus NOW()
startTimestamp: see LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP
transaction: see LAST_APPLIED_TRANSACTION

The currentlyApplying section shows the following information about the transaction currently being applied by the worker:

immediateCommitTimestamp: see APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
immediateCommitToNowTime: see APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minus NOW()
originalCommitTimestamp: see APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP
originalCommitToNowTime: see APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minus NOW()
startTimestamp: see APPLYING_TRANSACTION_START_APPLY_TIMESTAMP
transaction: see APPLYING_TRANSACTION

The lastProcessed section has the following information about the last transaction processed by the worker:

bufferTime: LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP minus LAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP
endTimestamp: see LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP
immediateCommitTimestamp: see LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
immediateCommitToEndTime: LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minus LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP
originalCommitTimestamp: see LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP
originalCommitToEndTime: LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minus LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP
startTimestamp: see LAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP
transaction: see LAST_PROCESSED_TRANSACTION

If the parallel replication applier is enabled, then the number of objects in the workers array in transactions or recovery matches the number of configured workers and an additional coordinator object is included. The information shown matches the information in the replication_applier_status_by_coordinator table. The object can contain:

The currentlyProcessing section has the following information about the transaction being processed by the worker:

immediateCommitTimestamp: see PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP
immediateCommitToNowTime: PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minus NOW()
originalCommitTimestamp: see PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP
originalCommitToNowTime: PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minus NOW()
startTimestamp: see PROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP
transaction: see PROCESSING_TRANSACTION

worker objects have the following information if an error was detected in the replication_applier_status_by_worker table:

lastErrno: see LAST_ERROR_NUMBER
lastError: see LAST_ERROR_MESSAGE
lastErrorTimestamp: see LAST_ERROR_TIMESTAMP

connection objects have the following information if an error was detected in the replication_connection_status table:

lastErrno: see LAST_ERROR_NUMBER
lastError: see LAST_ERROR_MESSAGE
lastErrorTimestamp: see LAST_ERROR_TIMESTAMP

coordinator objects have the following information if an error was detected in the replication_applier_status_by_coordinator table:

lastErrno: see LAST_ERROR_NUMBER
lastError: see LAST_ERROR_MESSAGE
lastErrorTimestamp: see LAST_ERROR_TIMESTAMP

Monitoring Recovery Operations

The output of Cluster.status() shows information about the progress of recovery operations for instances in RECOVERING state. Information is shown for instances recovering using either MySQL Clone, or incremental recovery. Monitor these fields:

The recoveryStatusText field includes information about the type of recovery being used. When MySQL Clone is working the field shows “Cloning in progress”. When incremental recovery is working the field shows “Distributed recovery in progress”.
When MySQL Clone is being used, the recovery field includes a dictionary with the following fields:
- cloneStartTime: The timestamp of the start of the clone process
- cloneState: The state of the clone progress
- currentStage: The current stage which the clone process has reached
- currentStageProgress: The current stage progress as a percentage of completion
- currentStageState: The current stage state
Example Cluster.status() output, trimmed for brevity:
```
...
"recovery": {
"cloneStartTime": "2019-07-15 12:50:22.730",
"cloneState": "In Progress",
"currentStage": "FILE COPY",
"currentStageProgress": 61.726837675213865,
"currentStageState": "In Progress"
},
"recoveryStatusText": "Cloning in progress",
...
```
When incremental recovery is being used and the extended option is set to 1 or greater, the recovery field includes a dictionary with the following fields:
- state: The state of the group_replication_recovery channel
- recoveryChannel : Displayed for instances performing incremental recovery or in which the recovery channel status is not off. Incremental recovery utilizes the receiver thread to receive transactions from the source, and the applier thread applies the received transactions on the instance. Provides the following information:
  - applierQueuedTransactionSetSize: The number of transactions currently queued, which are waiting to be applied.
  - applierState: The current state of the replication applier, either ON or OFF.
  - applierStatus: The current status of the applier threads. An aggregation of the states shown in the applierThreadState field. Can be one of:
    
    APPLIED_ALL: there are no queued transactions waiting to be applied
    
    APPLYING: there are transactions being applied
    
    ON: thread is connected and there are no queued transactions
    
    ERROR: there was an error while applying transactions
    
    OFF: the applier thread is disabled
  - applierThreadState: The current state of any applier threads. Provides detailed information about exactly what the applier thread is doing. For more information, see Replication SQL Thread States.
  - receiverStatus: The current status of the receiver thread. An aggregation of the states shown in the receiverThreadState field. Can be one of:
    
    ON: the receiver thread has successfully connected and is ready to receive
    
    CONNECTING: the receiver thread is connecting to the source
    
    ERROR: there was an error while receiving transactions
    
    OFF: the receiver thread has gracefully disconnected
  - receiverThreadState: The current state of the receiver thread. Provides detailed information about exactly what the receiver thread is doing. For more information, see Replication I/O (Receiver) Thread States.
  - source: The source of the transactions which are being applied.
Example Cluster.status() output, trimmed for brevity:
```
...
"recovery": {
                    "recoveryChannel": {
                        "applierQueuedTransactionSetSize": 2284, 
                        "applierStatus": "APPLYING", 
                        "applierThreadState": "Opening tables", 
                        "receiverStatus": "ON", 
                        "receiverThreadState": "Queueing master event to the relay log", 
                        "source": "ic-2:3306"
                    }, 
                    "state": "ON"
                },
...
```

InnoDB Cluster and Group Replication Protocol

Group Replication has the concept of a communication protocol for the group, see Setting a Group's Communication Protocol Version for more information. The Group Replication communication protocol version usually has to be managed explicitly, and set to accommodate the oldest MySQL Server version that you want the group to support. However, InnoDB Cluster automatically and transparently manages the communication protocol versions of its members, whenever the cluster topology is changed using AdminAPI operations. A cluster always uses the most recent communication protocol version that is supported by all the instances that are currently part of the cluster or joining it.

When an instance is added to, removed from, or rejoins the cluster, or a rescan or reboot operation is carried out on the cluster, the communication protocol version is automatically set to a version supported by the instance that is now at the earliest MySQL Server version.
When you carry out a rolling upgrade by removing instances from the cluster, upgrading them, and adding them back into the cluster, the communication protocol version is automatically upgraded when the last remaining instance at the old MySQL Server version is removed from the cluster prior to its upgrade.

To see the communication protocol version being used in a cluster, use the Cluster.status() function with the extended option enabled. The communication protocol version is returned in the GRProtocolVersion field, provided that the cluster has quorum and no cluster members are unreachable.

Checking the MySQL Version on Instances

The following operations can report information about the MySQL Server version running on the instance:

Cluster.status()
Cluster.describe()
Cluster.rescan()

The behavior varies depending on the MySQL Server version of the Cluster object session.

Cluster.status()

If either of the following requirements are met, a version string attribute is returned for each instance JSON object of the topology object:
- The Cluster object's current session is version 8.0.11 or later.
- The Cluster object's current session is running a version earlier than version 8.0.11 but the extended option is set to 3.
For example on an instance running version 8.0.16:
```
"topology": {
    "ic-1:3306": {
        "address": "ic-1:3306",
        "mode": "R/W",
        "readReplicas": {},
        "role": "HA",
        "status": "ONLINE",
        "version": "8.0.16"
}
```
Cluster.describe()

If the Cluster object's current session is version 8.0.11 or later, a version string attribute is returned for each instance JSON object of the topology object

For example on an instance running version 8.0.16:
```
"topology": [
    {
        "address": "ic-1:3306",
        "label": "ic-1:3306",
        "role": "HA",
        "version": "8.0.16"
    }
]
```
Cluster.rescan()

If the Cluster object's current session is version 8.0.11 or later, and the Cluster.rescan() operation detects instances which do not belong to the cluster, a version string attribute is returned for each instance JSON object of the newlyDiscoveredInstance object.

For example on an instance running version 8.0.16:
```
"newlyDiscoveredInstances": [
    {
        "host": "ic-4:3306",
        "member_id": "82a67a06-2ba3-11e9-8cfc-3c6aa7197deb",
        "name": null,
        "version": "8.0.16"
    }
]
```

PREV HOME UP NEXT