Documentation Home
MySQL Shell 9.5
Download this Manual
PDF (US Ltr) - 2.5Mb
PDF (A4) - 2.5Mb


MySQL Shell 9.5  / MySQL InnoDB Cluster  /  Monitoring InnoDB Cluster

8.7 Monitoring InnoDB Cluster

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

UsingCluster.describe()

To get information about the structure of the InnoDB Cluster itself, use theCluster.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 atChecking a cluster's Status withCluster.status() .

Checking a cluster's Status withCluster.status()

Cluster objects provide thestatus() 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. Issuingstatus() 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 ofONLINE.

For information about how the InnoDB Cluster is running, use the cluster'sstatus() 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 ofCluster.status() provides the following information:

  • clusterName: name assigned to this cluster duringdba.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 ofREQUIRED orDISABLED, depending on how thememberSslMode option was configured during eithercreateCluster() oraddInstance(). The value returned by this parameter corresponds to the value of thegroup_replication_ssl_mode server variable on the instance. SeeSection 8.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 ton failures. There are three or more members in the cluster, and they are functioning.

    • OK_PARTIAL: The cluster is online and can tolerate up ton 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 theMEMBER_ROLE column of thereplication_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 thesuper_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 thesuper_read_only variable.

      Note

      If the memberstatus is anything other thanONLINE,mode is reported asn/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 entersERROR state, thesuper_read_only option is set toON. To leave theERROR state you must manually configure the instance withsuper_read_only=OFF.

      • (MISSING): The state of an instance which is part of the configured cluster, but is currently unavailable.

        Note

        TheMISSING 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. SeeChecking the MySQL Version on Instances for more information.

To display more information about the cluster use theextended option. Theextended option supports integer or Boolean values. To configure the additional information thatCluster.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.

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

When you issueCluster.status({'extended':1}), or theextended option is set totrue, the output includes:

  • the following additional attributes for thedefaultReplicaSet object:

  • the following additional attributes for each object of thetopology object:

    • fenceSysVars a list containing the name of the fenced system variables which are configured by AdminAPI. Currently the fenced system variables considered areread_only,super_read_only andoffline_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 thesuper_read_only variable is not set toON, 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 theMEMBER_STATE column of thereplication_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 forextended. 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 thereplication_group_member_stats table, see the descriptions of the matching columns for more information. Instances which areONLINE have atransactions section included in the output. Instances which areRECOVERING have arecovery section included in the output. When you setextended to 2, in either case, these sections can contain the following:

  • appliedCount: seeCOUNT_TRANSACTIONS_REMOTE_APPLIED

  • checkedCount: seeCOUNT_TRANSACTIONS_CHECKED

  • committedAllMembers: seeTRANSACTIONS_COMMITTED_ALL_MEMBERS

  • conflictsDetectedCount: seeCOUNT_CONFLICTS_DETECTED

  • inApplierQueueCount: seeCOUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE

  • inQueueCount: seeCOUNT_TRANSACTIONS_IN_QUEUE

  • lastConflictFree: seeLAST_CONFLICT_FREE_TRANSACTION

  • proposedCount: seeCOUNT_TRANSACTIONS_LOCAL_PROPOSED

  • rollbackCount: seeCOUNT_TRANSACTIONS_LOCAL_ROLLBACK

When you setextended to 3, theconnection section shows information from thereplication_connection_status table.

ThecurrentlyQueueing section has information about the transactions currently queued:

  • immediateCommitTimestamp: seeQUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP

  • immediateCommitToNowTime: seeQUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minusNOW()

  • originalCommitTimestamp: seeQUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP

  • originalCommitToNowTime: seeQUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minusNOW()

  • startTimestamp: seeQUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP

  • transaction: seeQUEUEING_TRANSACTION

  • lastHeartbeatTimestamp: seeLAST_HEARTBEAT_TIMESTAMP

ThelastQueued section has information about the most recently queued transaction:

  • endTimestamp: seeLAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP

  • immediateCommitTimestamp: seeLAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP

  • immediateCommitToEndTime:LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minusNOW()

  • originalCommitTimestamp: seeLAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP

  • originalCommitToEndTime:LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minusNOW()

  • queueTime:LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP minusLAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP

  • startTimestamp: seeLAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP

  • transaction: seeLAST_QUEUED_TRANSACTION

  • receivedHeartbeats: seeCOUNT_RECEIVED_HEARTBEATS

  • receivedTransactionSet: seeRECEIVED_TRANSACTION_SET

  • threadId: seeTHREAD_ID

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

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

  • applyTime: seeLAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP minusLAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP

  • endTimestamp: seeLAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP

  • immediateCommitTimestamp: seeLAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP

  • immediateCommitToEndTime: seeLAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minusNOW()

  • originalCommitTimestamp: seeLAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP

  • originalCommitToEndTime: seeLAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minusNOW()

  • startTimestamp: seeLAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP

  • transaction: seeLAST_APPLIED_TRANSACTION

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

  • immediateCommitTimestamp: seeAPPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP

  • immediateCommitToNowTime: seeAPPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minusNOW()

  • originalCommitTimestamp: seeAPPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP

  • originalCommitToNowTime: seeAPPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minusNOW()

  • startTimestamp: seeAPPLYING_TRANSACTION_START_APPLY_TIMESTAMP

  • transaction: seeAPPLYING_TRANSACTION

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

  • bufferTime:LAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP minusLAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP

  • endTimestamp: seeLAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP

  • immediateCommitTimestamp: seeLAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP

  • immediateCommitToEndTime:LAST_PROCESSED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minusLAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP

  • originalCommitTimestamp: seeLAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP

  • originalCommitToEndTime:LAST_PROCESSED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minusLAST_PROCESSED_TRANSACTION_END_BUFFER_TIMESTAMP

  • startTimestamp: seeLAST_PROCESSED_TRANSACTION_START_BUFFER_TIMESTAMP

  • transaction: seeLAST_PROCESSED_TRANSACTION

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

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

  • immediateCommitTimestamp: seePROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP

  • immediateCommitToNowTime:PROCESSING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP minusNOW()

  • originalCommitTimestamp: seePROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP

  • originalCommitToNowTime:PROCESSING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP minusNOW()

  • startTimestamp: seePROCESSING_TRANSACTION_START_BUFFER_TIMESTAMP

  • transaction: seePROCESSING_TRANSACTION

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

  • lastErrno: seeLAST_ERROR_NUMBER

  • lastError: seeLAST_ERROR_MESSAGE

  • lastErrorTimestamp: seeLAST_ERROR_TIMESTAMP

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

  • lastErrno: seeLAST_ERROR_NUMBER

  • lastError: seeLAST_ERROR_MESSAGE

  • lastErrorTimestamp: seeLAST_ERROR_TIMESTAMP

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

  • lastErrno: seeLAST_ERROR_NUMBER

  • lastError: seeLAST_ERROR_MESSAGE

  • lastErrorTimestamp: seeLAST_ERROR_TIMESTAMP

Monitoring Recovery Operations

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

  • TherecoveryStatusText 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, therecovery 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

    ExampleCluster.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 theextended option is set to 1 or greater, therecovery field includes a dictionary with the following fields:

    • state: The state of thegroup_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, eitherON orOFF.

      • applierStatus: The current status of the applier threads. An aggregation of the states shown in theapplierThreadState 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, seeReplication SQL Thread States.

      • receiverStatus: The current status of the receiver thread. An aggregation of the states shown in thereceiverThreadState 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, seeReplication I/O (Receiver) Thread States.

      • source: The source of the transactions which are being applied.

    ExampleCluster.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, seeSetting 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 theCluster.status() function with theextended option enabled. The communication protocol version is returned in theGRProtocolVersion 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 theCluster object session.

  • Cluster.status()

    If either of the following requirements are met, aversion string attribute is returned for each instance JSON object of thetopology object:

    • TheCluster object's current session is version 8.0.11 or later.

    • TheCluster object's current session is running a version earlier than version 8.0.11 but theextended 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 theCluster object's current session is version 8.0.11 or later, aversion string attribute is returned for each instance JSON object of thetopology 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 theCluster object's current session is version 8.0.11 or later, and theCluster.rescan() operation detects instances which do not belong to the cluster, aversion string attribute is returned for each instance JSON object of thenewlyDiscoveredInstance 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"    }]