MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster
How do I… Monitor the status & the configuration of my cluster?
Short answer
Use:
status()
Long answer…
Assuming you already have a MySQL InnoDB Cluster up and running. If not, please RTFM 🙂
Additionally you can read this tutorial and this article from my colleague lefred or this one on Windows Platform from my colleague Ivan.
I’m using MySQL 8.0.15
1 2 3 4 5 6 |
MySQL localhost:33060+ JS> session.sql('SELECT VERSION()') +-----------+ | VERSION() | +-----------+ | 8.0.15 | +-----------+ |
Let’s connect to my cluster
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$ mysqlsh root@localhost --cluster Please provide the password for 'root@localhost': **** MySQL Shell 8.0.15 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Creating a session to 'root@localhost' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 1520 (X protocol) Server version: 8.0.15 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. You are connected to a member of cluster 'pocCluster'. Variable 'cluster' is set. Use cluster.status() in scripting mode to get status of this cluster or cluster.help() for more commands. |
The “– – cluster” argument enables cluster management by setting the global variable.
This variable is a reference to the MySQL InnoDB Cluster object session. It will give you access (among others) to the status() method that allows you to check and monitor the cluster.
Ask for help
The built-in help is simply awesome!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
MySQL localhost:33060+ JS> cluster.help('status') NAME status - Describe the status of the cluster. SYNTAX <Cluster>.status([options]) WHERE options: Dictionary with options. RETURNS A JSON object describing the status of the cluster. DESCRIPTION This function describes the status of the cluster including its ReplicaSets and Instances. The following options may be given to control the amount of information gathered and returned. - extended: if true, includes information about transactions processed by connection and applier, as well as groupName and memberId values. - queryMembers: if true, connect to each Instance of the ReplicaSets to query for more detailed stats about the replication machinery. EXCEPTIONS MetadataError in the following scenarios: - If the Metadata is inaccessible. - If the Metadata update operation failed. |
Cluster status
So let’s discover the status of our cluster
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
MySQL localhost:33060+ JS> cluster.status() { "clusterName": "pocCluster", "defaultReplicaSet": { "name": "default", "primary": "172.19.0.11:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "172.19.0.11:3306": { "address": "172.19.0.11:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "172.19.0.12:3306": { "address": "172.19.0.12:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "172.19.0.13:3306": { "address": "172.19.0.13:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "172.19.0.11:3306" } |
Note:
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.
As you can see, by default status() gives you a lot of relevant information.
Thus it could be used to monitor your cluster although the best tool available to monitor your MySQL InnoDB Cluster (but also MySQL Replication, MySQL NDB Cluster and obviously your standalone MySQL servers) is MySQL Enterprise Monitor.
More details with “A Guide to MySQL Enterprise Monitor“.
Extended cluster status
MySQL Group Replication provides several metrics and detailed information about the underlying cluster in MySQL InnoDB clusters.
These metrics which are used for monitoring are based on these Performance Schema tables.
Some of these information are available through MySQL Shell. You can control the amount of information gathered and returned with 2 options: extended & queryMembers.
extended
if enabled, includes information about groupName and memberID for each member; and general statistics about the number of transactions checked, proposed, rejected by members…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
MySQL localhost:33060+ JS> cluster.status({extended:true}) { "clusterName": "pocCluster", "defaultReplicaSet": { "groupName": "72568575-561c-11e9-914c-0242ac13000b", "name": "default", "primary": "172.19.0.11:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "172.19.0.11:3306": { "address": "172.19.0.11:3306", "memberId": "4a85f6c4-561c-11e9-8401-0242ac13000b", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE", "transactions": { "appliedCount": 2, "checkedCount": 53, "committedAllMembers": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12, 72568575-561c-11e9-914c-0242ac13000b:1-51", "conflictsDetectedCount": 0, "inApplierQueueCount": 0, "inQueueCount": 0, "lastConflictFree": "72568575-561c-11e9-914c-0242ac13000b:56", "proposedCount": 53, "rollbackCount": 0 } }, "172.19.0.12:3306": { "address": "172.19.0.12:3306", "memberId": "4ad75450-561c-11e9-baa8-0242ac13000c", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE", "transactions": { "appliedCount": 44, "checkedCount": 43, "committedAllMembers": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12, 72568575-561c-11e9-914c-0242ac13000b:1-41", "conflictsDetectedCount": 0, "inApplierQueueCount": 0, "inQueueCount": 0, "lastConflictFree": "72568575-561c-11e9-914c-0242ac13000b:52", "proposedCount": 0, "rollbackCount": 0 } }, "172.19.0.13:3306": { "address": "172.19.0.13:3306", "memberId": "4b77c1ec-561c-11e9-9cc1-0242ac13000d", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE", "transactions": { "appliedCount": 42, "checkedCount": 42, "committedAllMembers": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12, 72568575-561c-11e9-914c-0242ac13000b:1-41", "conflictsDetectedCount": 0, "inApplierQueueCount": 0, "inQueueCount": 0, "lastConflictFree": "72568575-561c-11e9-914c-0242ac13000b:53", "proposedCount": 0, "rollbackCount": 0 } } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "172.19.0.11:3306" } |
queryMembers
if enabled, includes information about recovery and regular transaction I/O, applier worker thread statistic and any lags; applier coordinator statistic…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
MySQL localhost:33060+ JS> cluster.status({queryMembers:true}) { "clusterName": "pocCluster", "defaultReplicaSet": { "name": "default", "primary": "172.19.0.11:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "172.19.0.11:3306": { "address": "172.19.0.11:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE", "transactions": { "connection": { "lastHeartbeatTimestamp": "", "lastQueued": { "endTimestamp": "2019-04-03 14:26:33.394755", "immediateCommitTimestamp": "", "immediateCommitToEndTime": null, "originalCommitTimestamp": "", "originalCommitToEndTime": null, "queueTime": 0.000077, "startTimestamp": "2019-04-03 14:26:33.394678", "transaction": "72568575-561c-11e9-914c-0242ac13000b:13" }, "receivedHeartbeats": 0, "receivedTransactionSet": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12, 72568575-561c-11e9-914c-0242ac13000b:1-65", "threadId": null }, "workers": [ { "lastApplied": { "applyTime": 0.022927, "endTimestamp": "2019-04-03 14:26:33.417643", "immediateCommitTimestamp": "", "immediateCommitToEndTime": null, "originalCommitTimestamp": "", "originalCommitToEndTime": null, "retries": 0, "startTimestamp": "2019-04-03 14:26:33.394716", "transaction": "72568575-561c-11e9-914c-0242ac13000b:13" }, "threadId": 58 } ] } }, "172.19.0.12:3306": { "address": "172.19.0.12:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE", "transactions": { "connection": { "lastHeartbeatTimestamp": "", "lastQueued": { "endTimestamp": "2019-04-03 15:42:30.855989", "immediateCommitTimestamp": "", "immediateCommitToEndTime": null, "originalCommitTimestamp": "2019-04-03 15:42:30.854594", "originalCommitToEndTime": 0.001395, "queueTime": 0.000476, "startTimestamp": "2019-04-03 15:42:30.855513", "transaction": "72568575-561c-11e9-914c-0242ac13000b:65" }, "receivedHeartbeats": 0, "receivedTransactionSet": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12, 72568575-561c-11e9-914c-0242ac13000b:1-65", "threadId": null }, "workers": [ { "lastApplied": { "applyTime": 0.024685, "endTimestamp": "2019-04-03 15:42:30.880361", "immediateCommitTimestamp": "", "immediateCommitToEndTime": null, "originalCommitTimestamp": "2019-04-03 15:42:30.854594", "originalCommitToEndTime": 0.025767, "retries": 0, "startTimestamp": "2019-04-03 15:42:30.855676", "transaction": "72568575-561c-11e9-914c-0242ac13000b:65" }, "threadId": 54 } ] } }, "172.19.0.13:3306": { "address": "172.19.0.13:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE", "transactions": { "connection": { "lastHeartbeatTimestamp": "", "lastQueued": { "endTimestamp": "2019-04-03 15:42:30.855678", "immediateCommitTimestamp": "", "immediateCommitToEndTime": null, "originalCommitTimestamp": "2019-04-03 15:42:30.854594", "originalCommitToEndTime": 0.001084, "queueTime": 0.000171, "startTimestamp": "2019-04-03 15:42:30.855507", "transaction": "72568575-561c-11e9-914c-0242ac13000b:65" }, "receivedHeartbeats": 0, "receivedTransactionSet": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12, 72568575-561c-11e9-914c-0242ac13000b:1-65", "threadId": null }, "workers": [ { "lastApplied": { "applyTime": 0.021354, "endTimestamp": "2019-04-03 15:42:30.877398", "immediateCommitTimestamp": "", "immediateCommitToEndTime": null, "originalCommitTimestamp": "2019-04-03 15:42:30.854594", "originalCommitToEndTime": 0.022804, "retries": 0, "startTimestamp": "2019-04-03 15:42:30.856044", "transaction": "72568575-561c-11e9-914c-0242ac13000b:65" }, "threadId": 54 } ] } } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "172.19.0.11:3306" } |
To summarize
Q: How do I monitor the status & the configuration of my cluster?
A: Use status() or status({extended:true}) or status({queryMembers:true})
References
- status() method
- Working with InnoDB Cluster
- Checking a cluster’s Status with Cluster.status()
- MySQL InnoDB Cluster – Extended cluster information and rescan
- Performance Schema Replication Tables
- Tutoriel – Déployer MySQL 8.0 InnoDB Cluster (09-2019)
- MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster
- MySQL InnoDB Cluster – HowTo #2 – Validate an instance
Thanks for using MySQL!
Watch my videos on my YouTube channel and subscribe.
Thanks for using MySQL!

MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
or both at the same time 😉
cluster.status({extended:true,queryMembers:true})
Indeed!
Thanks for your feedback buddy 🙂
running cluster.status({extended:true,queryMembers:true})
mysqlsh Ver 8.0.15-commercial for Linux on x86_64 – for MySQL 8.0.15 (MySQL Enterprise Server – Commercial)
output : Error
cluster.status({queryMembers:true})
Cluster.status: get_uint(25): field value out of the allowed range (LogicError)
Thanks for your comment!
Probably related to https://bugs.mysql.com/bug.php?id=95191
We are working on a fix.
Thanks
[…] MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster […]