MySQL Shell 8.0 (part of MySQL 8.0)
This section describes how to use AdminAPI to monitor an InnoDB Cluster.
To get information about the structure of the InnoDB Cluster
itself, use the
function:
Cluster
.describe()
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()
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.
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
provides the following information:
Cluster
.status()
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.
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.
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
provides, use the following values:
Cluster
.status({'extended':value
})
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
,
or the Cluster
.status({'extended':1})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.
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
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.
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 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. A value of 3 is the equivalent of setting the
deprecated queryMembers
option to
true
. 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 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
The output of
shows information about the progress of recovery operations
for instances in Cluster
.status()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
output, trimmed for brevity:
Cluster
.status()
... "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 utilises 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 Thread States.
source
: The source of the
transactions which are being applied.
Example
output, trimmed for brevity:
Cluster
.status()
... "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" }, ...
From MySQL 8.0.16, Group Replication has the concept of a communication protocol for the group, see 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
function with the Cluster
.status()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.
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
operation detects instances which do not belong to the
cluster, a Cluster
.rescan()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" } ]