InnoDB Cluster Diagnostics

MySQL Shell

mysqlsh

\c icadmin@node1

var cluster = dba.getCluster()

You may find you need to specify the cluster name (but usually the command above will work)...var cluster = dba.getCluster('myCluster')

cluster.status()

cluster.describe()

cluster.options()

dba.checkInstanceConfiguration()

cluster.status({extended:true})

cluster.status({queryMembers:true})

The !queryMembers" syntax is deprecated. Use the following instead...

cluster.status({extended:3})

To perform a quick check on your innodb cluster...

mysqlsh icadmin@node1 -- cluster status | grep -v WARNING | grep -v 'Write operations on the InnoDB cluster will not be allowed.' | jq .defaultReplicaSet.status

Status returned will be one of...

MySQL Shell Reporting Framework

If you haven't done so already, download and install lefred's addition_to_sys_GR.sql script on the cluster primary...

mysqlsh --sql root@localhost < addition_to_sys_GR.sql

If you haven't already done so, create the ~/.mysqlsh/init.d directory and put gr_info.py and gr_recovery_progress.py into it...

mkdir ~/.mysqlsh/init.d

mv gr_info.py ~/.mysqlsh/init.d

mv gr_recovery_progress.py ~/.mysqlsh/init.d

Usage

mysqlsh icadmin@localhost

\show gr_info

+--------------------+-----------+---------+--------+-----------+------------+-----------+----------+| server             | role      | version | quorum | tx behind | tx to cert | remote tx | local tx |+--------------------+-----------+---------+--------+-----------+------------+-----------+----------+| innodbcnode01:3306 | PRIMARY   | 8.0.24  | YES    | 0         | 0          | 189       | 18846834 || innodbcnode02:3306 | SECONDARY | 8.0.24  | YES    | 0         | 0          | 18828763  | 0        || innodbcnode03:3306 | SECONDARY | 8.0.24  | YES    | 0         | 0          | 93867     | 0        |+--------------------+-----------+---------+--------+-----------+------------+-----------+----------+

If a cluster node is lagging (and has a status suggesting it's "recovering"), then use this (connected to the lagging MySQL instance) to track progress...

mysqlsh icadmin@localhost

\show gr_recovery_progress

\watch gr_recovery_progress

+----------------+| trx_to_recover |+----------------+| 0              |+----------------+

jq

Command-line JSON processor

mysqlsh icadmin@localhost -- cluster status | grep -v WARNING | grep -v 'Write operations on the InnoDB cluster will not be allowed.' | jq .defaultReplicaSet.status

"OK_NO_TOLERANCE"
"NO_QUORUM"

MySQL

These commands work from the mysql command prompt... ...or any connected query tool

mysql -u icadmin@node1 -p

SELECT * 

  FROM performance_schema.replication_group_members;

To check there is at least one Primary...

SELECT COUNT(*)
  FROM performance_schema.replication_group_members
  WHERE member_id = (SELECT variable_value
                        FROM performance_schema.global_status

                     WHERE variable_name = 'group_replication_primary_member')

   AND member_state = 'ONLINE';

MySQL Enterprise Monitor

You can see InnoDB clusters by selecting a Replication Topology or InnDB Cluster from the dropdown, and selecting All Targets...


This image shows MEM reporting that a node is down.

NOTE: If MEM shows a node is down and all other checks from the earlier sections suggest this to be untrue then check the Agent on that node...

Bibliography