Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
PDF (RPM) - 39.9Mb
HTML Download (TGZ) - 10.6Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.2Mb
Man Pages (TGZ) - 241.2Kb
Man Pages (Zip) - 346.4Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Monitoring InnoDB Cluster

21.2.3 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", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "ic-2:3306": {
                "address": "ic-2:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "ic-3:3306": {
                "address": "ic-3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "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 Securing your Cluster.

  • status: The status of this element of the cluster. For the overall cluster this describes the high availability provided by this 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.

  • topology: The instances which have been added to the cluster.

  • Host name of instance: The host name of an instance, for example localhost:3310.

  • role: what function this instance provides in the cluster. Currently only HA, for high availability.

  • mode: whether the server is read-write ("R/W") or read-only ("R/O"). From version 8.0.17, 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.

  • 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.

To display more information about the cluster use the extended option. From version 8.0.17, 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, 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. In versions prior to 8.0.17, the extended option was only Boolean. Similarly prior versions used the queryMembers Boolean option to provide more information about the instances in the cluster, which is the equivalent of setting extended to 3. The queryMembers option is deprecated and scheduled to be removed in a future release.

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 is 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.

    • groupName is the group's name, a UUID.

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

    • fenceSysVars a list containing the name of the fenced system variables which are enabled. Currently the fenced system variables considered are read_only, super_read_only and offline_mode.

    • memberId Each cluster member UUID.

    • memberRole the Member Role as reported by the Group Replication plugin, see the MEMBER_ROLE column of the replication_group_members table.

    • 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 parallel apply is enabled; error, and other information from I/O and applier threads issue use the values 2 and 3. A value of 3 is the equivalent of setting the deprecated queryMembers option to true. 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 connection section can contain the following:

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 parallel applier workers are 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, the recovery field includes a dictionary with the following field:

    • state: The state of the group_replication_recovery channel

    Example output Cluster.status(), trimmed for brevity:

    ...
    "recovery": {
    "state": "ON"
    }, 
    ...

InnoDB Cluster and Group Replication Protocol

From MySQL 8.0.16, Group Replication has the concept of a communication protocol for the group, see Section 18.4.1.4, “Setting a Group's Communication Protocol Version” for background 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 (or the deprecated queryMembers is true).

    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"
    }

    For example on an instance running version 5.7.24:

    "topology": {
        "ic-1:3306": {
            "address": "ic-1:3306",
            "mode": "R/W",
            "readReplicas": {},
            "role": "HA",
            "status": "ONLINE",
            "version": "5.7.24"
    }
  • 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"
        }
    ]