Check MySQL replication using Python

Simple python script to check the state of MySQL replication and send an e-mail in case of problems. Use with cron or systemd timers.

#!/usr/bin/env python3

import sys
from subprocess import run, PIPE

KEYS = ['slave_io_running', 'slave_sql_running', 'last_errno', 'last_error', 'last_io_errno', 'last_io_error', 'last_sql_errno', 'last_sql_error', 'seconds_behind_master']

mailtext, problem = '', False
status = run('mysql --defaults-extra-file=/etc/mysql/debian.cnf', shell=True, text=True, input='show slave status\G', stdout=PIPE).stdout

for line in status.split('\n'):
    if ':' in line:
        key, value = line.split(':', 1)
        key, value = key.strip().lower(), value.strip()
        if key in KEYS:
            mailtext += '%22s: %s\n' % (key, value)
            if ('running' in key) and (value != 'Yes'): problem = True
            if ('errno' in key) and (value != '0'): problem = True
            if ('error' in key) and (value != ''): problem = True
mailtext += '\n%22s: %s\n' % ('problem', 'yes' if problem else 'no')

if problem:
    mailtext = 'Replication problem found\n\n' + mailtext
    run('mail -s "Problem with replication on `hostname`" root', shell=True, text=True, input=mailtext)

if sys.stdout.isatty():
    print()
    print(mailtext)

Sample output

      slave_io_running: Yes
     slave_sql_running: Yes
            last_errno: 0
            last_error:
 seconds_behind_master: 0
         last_io_errno: 0
         last_io_error:
        last_sql_errno: 0
        last_sql_error:

               problem: no