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 PIPE, run

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