banshee/services-collection/services/mysql/ssh/replication

MySQL and MariaDB replication, by SSH

Returns, for each replication channel of a MySQL or MariaDB replica, whether its I/O thread (which fetches the binary log of the source) and its SQL thread (which applies it) run, and how many seconds it lags behind its source, read by the mysql client on the replica over SSH. Five watches are offered, each one set on the probe. No password leaves banshee.

Versions

MySQL 5.6 and later, MariaDB 10.0 and later. Generic: the collector asks SHOW REPLICA STATUS (MySQL 8.0.22, MariaDB 10.5.1 and later), and SHOW SLAVE STATUS when the server does not know it; it reads both namings of the columns.

How it works

The collector runs, on the replica, LC_ALL=C mysql --batch -e 'SHOW REPLICA STATUS\G', the statement quoted as one argument, over the local socket; on a syntax error (ERROR 1064), SHOW SLAVE STATUS\G instead. The answer is one block per channel:

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: db1.example
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
        Seconds_Behind_Source: 0
                 Channel_Name:

A channel is named by its Channel_Name (MySQL), or default for the default channel and on MariaDB.
io and sql are Replica_IO_Running and Replica_SQL_Running (Slave_IO_Running and Slave_SQL_Running on MariaDB and on MySQL before 8.0.22): Yes, No, or Connecting for an I/O thread that cannot reach its source.
lag is Seconds_Behind_Source (Seconds_Behind_Master); it is NULL, and not returned, while a thread is stopped.
channels is the number of channels: 0 on a server that is no replica, which "No replication" raises.
A server whose socket refuses or that does not answer (ERROR 2002, ERROR 2003) is critical: "MySQL is not running". Any other failure of the client stops the collector misconfigured, with what the client said (a missing privilege reads ERROR 1227).
Nothing is written: SHOW only reads.

Prerequisites

The same account as mysql/ssh/connections (an SSH account without write rights, the mysql client, a database account of its name logged in by auth_socket, unix_socket or its own ~/.my.cnf, no sudo, the fingerprint of the server's host key), given the one privilege that reads the replication status:

MySQL: GRANT REPLICATION CLIENT ON *.* TO 'monitoring'@'localhost';
MariaDB 10.5.9 and later: GRANT REPLICA MONITOR ON *.* TO 'monitoring'@'localhost'; (before 10.5.9, REPLICATION CLIENT).

Outputs

Name	Label	Unit	Per instance
`channels`	Replication channels	—	no
`io`	I/O thread	—	yes, per channel
`sql`	SQL thread	—	yes, per channel
`lag`	Lag behind the source	s	yes, per channel

Watches

Name	Title	Kind	Defaults	Checked by default
`no-channel`	No replication	below a threshold, on `channels`	critical below 1	yes
`io-stopped`	I/O thread not running	expected value, on `io`	critical when anything but `Yes`, for 3 runs	yes
`sql-stopped`	SQL thread not running	expected value, on `sql`	critical when anything but `Yes`	yes
`lag`	Replication late	above a threshold, on `lag`	warning above 60 s, critical above 300 s	yes
`missing`	Channel missing	expected instances, on `io`	no name given: it raises nothing until names are set on the probe	no

Deploy the collector on the replicas; a source has no channel, and "No replication" would raise there.

Limits

The multi-source replication of MariaDB (SHOW ALL REPLICAS STATUS) is not read: only its default connection.
The reason a thread stopped (Last_IO_Error, Last_SQL_Error) is not returned: read it on the replica.
Seconds_Behind_Source counts from the events received: when the I/O thread lags, it can read 0 while the replica is behind.
Group Replication and Galera clusters are not read here.
Not checked against a MySQL or a MariaDB: the statements and columns are those of the MySQL reference manual ("SHOW REPLICA STATUS Statement") and of the MariaDB knowledge base ("SHOW REPLICA STATUS").