replication

// The replication of a MySQL or MariaDB replica, by SSH and the mysql client on its server: for each
// replication channel, 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.
// The client connects over the local socket as the MySQL user named like the SSH account (auth_socket on
// MySQL, unix_socket on MariaDB), or as the user of that account's ~/.my.cnf: no password leaves banshee,
// none is written on a command line.
need('protocol:ssh');

per('channel', 'Replication channels');
output('channels', 'number', array('label' => 'Replication channels'));
output('io', 'text', array('label' => 'I/O thread', 'per' => 'channel'));
output('sql', 'text', array('label' => 'SQL thread', 'per' => 'channel'));
output('lag', 'number', array('label' => 'Lag behind the source', 'unit' => 's', 'per' => 'channel'));

watch.below('no-channel', 'No replication', 'channels', array('critical' => 1));
watch.expect('io-stopped', 'I/O thread not running', 'io', array('critical' => array('Yes'), 'for' => '3 runs'));
watch.expect('sql-stopped', 'SQL thread not running', 'sql', array('critical' => array('Yes')));
watch.above('lag', 'Replication late', 'lag', array('warning' => 60, 'critical' => 300));
watch.missing('missing', 'Channel missing', 'io', array('enabled' => false));

// One run of the mysql client. LC_ALL=C keeps its messages in English. A server whose socket refuses, or that
// does not answer (ERROR 2002, 2003), is not running: critical.
function run_mysql($sql) {
    $read = ssh.run('LC_ALL=C mysql --batch', arguments=array('-e', $sql));
    if (list.count(text.match($read['errors'], 'ERROR 200[23] ')) > 0) {
        fail('critical', 'MySQL is not running', $read['errors']);
    }
    return $read;
}

// SHOW REPLICA STATUS from MySQL 8.0.22 and MariaDB 10.5.1 on; SHOW SLAVE STATUS before, which MySQL 8.4
// no longer knows. An older server answers the first with a syntax error (ERROR 1064).
$read = run_mysql('SHOW REPLICA STATUS\G');
if ($read['exit'] != 0 && text.contains($read['errors'], 'ERROR 1064')) {
    $read = run_mysql('SHOW SLAVE STATUS\G');
}
if ($read['exit'] != 0) {
    fail('misconfigured', 'mysql failed', $read['errors']);
}

// One block per channel, "*************************** 1. row ***************************", then one line
// "Field: value" per column. MySQL 8.0.22 and later name the columns Replica_IO_Running and
// Seconds_Behind_Source; MariaDB and the older MySQL Slave_IO_Running and Seconds_Behind_Master.
$rows = array();
$row = -1;
foreach (text.lines($read['output']) as $line) {
    if (list.count(text.match($line, '^\*+ [0-9]+\. row \*+$')) > 0) {
        $row = $row + 1;
        $rows[$row] = array();
        continue;
    }
    $m = text.match($line, '^([A-Za-z_]+): ?(.*)$');
    if ($row >= 0 && list.count($m) > 0) {
        $rows[$row][$m[1]] = $m[2];
    }
}

value('channels', list.count($rows));
foreach ($rows as $columns) {
    $name = list.get($columns, 'Channel_Name', '');
    if ($name == '') {
        $name = 'default';
    }
    value('io', list.get($columns, 'Replica_IO_Running', list.get($columns, 'Slave_IO_Running', '')), $name);
    value('sql', list.get($columns, 'Replica_SQL_Running', list.get($columns, 'Slave_SQL_Running', '')), $name);
    // NULL when the threads do not run: the lag is then unknown, and not returned.
    $lag = list.get($columns, 'Seconds_Behind_Source', list.get($columns, 'Seconds_Behind_Master', ''));
    if (text.is_number($lag)) {
        value('lag', $lag, $name);
    }
}