misc_scripts/mysql_replication_health.py at master · mikegriffin/misc_scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
from sys import exit
import MySQLdb

db = MySQLdb.connect(host='localhost',user='root',passwd='s4kila',db='information_schema')
cursor = db.cursor()

def run_query(db, query):
    cursor.execute(query)
    return cursor.fetchall()

def gen_health_command(db):
    try:
        query = "select substring_index(@@version_comment,' ', 1)"
        result = run_query(db, query)
        if result[0][0].find("MySQL") != -1 or result[0][0].find("Percona") != -1:
            query = "select substring_index(@@version,'.', 1)"
            result = run_query(db, query)
            if int(result[0][0]) >= 8:
                return "rs"
            else:
                return "ss"
        else:
            if result[0][0].find("MariaDB") != -1:
                return "ss"
            else:
                print("server version detection failed")
                exit(-1)
    except:
        print("gen_health_command failed")
        exit(-1)

def get_replication_delay(db):
    try:
        if health_command == "ss":
            query = "SHOW SLAVE STATUS"
        else:
            if health_command == "rs":
                query = "SHOW REPLICA STATUS"
            else:
                print("health command invalid")
                exit(-1)
        result = run_query(db, query)
        for idx, tup in enumerate(cursor.description):
            if tup[0] == "Replica_IO_Running" or tup[0] == "Slave_IO_Running":
                if result[0][idx] != "Yes":
                    print(tup[0], result[0][idx])
                    exit(-1)
            if tup[0] == "Replica_SQL_Running" or tup[0] == "Slave_SQL_Running":
                if result[0][idx] != "Yes":
                    print(tup[0], result[0][idx])
                    exit(-1)
            if tup[0] == "Seconds_Behind_Source" or tup[0] == "Seconds_Behind_Master":
                lag = result[0][idx]
            if tup[0] == "SQL_Delay":
                sql_delay = result[0][idx]
            if tup[0] == "SQL_Remaining_Delay":
                if result[0][idx] == "NULL":
                    sql_remaining_delay=0
                else:
                    sql_remaining_delay = result[0][idx]
            if tup[0] == "Replica_SQL_Running_State" or tup[0] == "Slave_SQL_Running_State":
                running_state = result[0][idx]
        if sql_delay > 0:
            if running_state.find("DELAY") != -1:
                print(0)
            else:
                if lag-sql_delay <= 0:
                    print(0)
                else:
                    print(lag-sql_delay)
        else:
            print(lag)
    except:
        print("get_replication_delay failed")
        exit(-1)


health_command = gen_health_command(db)
get_replication_delay(db)
cursor.close()
db.close()