check_mysql_cluster.py查看mysql集群信息

  sre

DHcbZD.jpg

#!/usr/bin/python
from __future__ import division
import sys
import MySQLdb
import getopt
import json
import os
import commands
import time
import re


def get_arginfo(argv):
    try:
        options,args = getopt.getopt(argv , "hf:u:p:" , ["help" , "ip="  , "user=" , "passwd="])
    except getopt.GetoptError:
        sys.exit()
    arginfo = {}
    for option,value in options:
        value_1 = format(value)
        option_1 = format(option)
        arginfo[option_1] = value_1
    return arginfo

class SqlExecuter:
    def __init__(self, ip='192.168.31.13', user='liu', passwd='liu', port=3306):
        self.conn = MySQLdb.connect(host=ip, user=user, passwd=passwd, port=port)
        self.cursor = self.conn.cursor()

    def execute(self, sql):
        return self.cursor.execute(sql)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

    def dispose(self):
        self.cursor.close()
        self.conn.close()


def get_nodeinfo(c):
    c.execute('show slave status;')
    result = c.fetchall()
    len_res = len(result)
    if ( len_res == 0 ) :
        node_role = 'master'
    else :
        io_thread = str(result[0][10])
        sql_thread = str(result[0][11])
        Seconds_Behind_Master = result[0][32]
        node_role = 'slave'

    # get mysql used conn
    c.execute('select count(*) from information_schema.processlist;')
    used_conn = int(c.fetchone()[0])

    # get mysql slow sql
    c.execute('select count(*) from information_schema.processlist where info is not null and time>5;')
    slow_sql = int(c.fetchone()[0])

    # get mysql max conn
    c.execute("show variables like 'max_connections';")
    max_conn = int(c.fetchone()[1])

    # get mysql lock info
    c.execute("show global status like 'Innodb_row_lock_current_waits';")
    lock_sql = int(c.fetchone()[1])

    #get mysql com_commit
    c.execute("show global status like 'COM_COMMIT';")
    COM_COMMIT = int(c.fetchone()[1])

    #get mysql com_rollback
    c.execute("show global status like 'COM_ROLLBACK';")
    COM_ROLLBACK = int(c.fetchone()[1])



    #get mysql uptime
    c.execute("show global status like 'UPTIME';")
    UPTIME = int(c.fetchone()[1])

    #get mysql questions
    c.execute("show global status like 'QUESTIONS';")
    QUESTIONS = int(c.fetchone()[1])

    #get mysql tps,qps
    tps = (COM_COMMIT + COM_ROLLBACK)/UPTIME
    qps = (QUESTIONS)/UPTIME


    current = time.time()
    time_nvl = current - UPTIME
    start_time = time.asctime(time.localtime(time_nvl))


    detail = {}
    detail["start_time"] = start_time
    detail["MySQL_used_conn"] = used_conn
    detail["MySQL_avail_conn"] = max_conn - used_conn
    detail["slow_sql"] = slow_sql
    detail["lock_sql"] = lock_sql
    detail["tps"] = tps
    detail["qps"] = qps
    detail["uptime"] = UPTIME
    detail["node_role"] = node_role


    if ( node_role == 'slave' ):
        detail["io_thread"] = io_thread
        detail["sql_thread"] = sql_thread
        detail["Seconds_Behind_Master"] = Seconds_Behind_Master

        if ( io_thread == 'Yes' and sql_thread == 'Yes' ):
            detail["replicate_status"] = 'green'
        elif ( io_thread == 'No' and sql_thread == 'Yes' ):
            detail["replicate_status"] = 'yellow'
        elif ( io_thread == 'Yes' and sql_thread == 'No'):
            detail["replicate_status"] = 'yellow'
        else :
            detail["replicate_status"] = 'red'

    else :
        detail["Seconds_Behind_Master"] = 0
        detail["replicate_status"] =  'green'
    return detail

def getcluster_info (info):
    culster_info={}
    pid = "ps aux |grep mysqld|grep -v mysqld_safe| grep -v grep | grep -v ps.sh | grep -v dump |head -n 1|awk '{print $2}'"
    cmd1 = "nc -w 10 -z " +  info["ip_1"] +  " "+ str(info["port_1"]) + " | grep -i succeeded | wc -l"
    cmd2 = "nc -w 10 -z " +  info["ip_2"] +  " "+ str(info["port_2"]) + " | grep -i succeeded | wc -l"

    host_1 = {}
    host_2 = {}

    (status, result) = commands.getstatusoutput(cmd1)
    if ( result == 1):
        host_1["mysql_proc"] = 'alive'
    else :
        host_1["mysql_proc"] = 'die'

    (status, result) = commands.getstatusoutput(cmd2)
    if (result == 1):
        host_2["mysql_proc"] = 'alive'
    else:
        host_2["mysql_proc"] = 'die'

    (status, result) = commands.getstatusoutput(pid)
    if ( len(result) >0 ):
        pid = str(result)
    else :
        pid = ' '
    host_1["pid"] = pid
    host_2["pid"] = pid
    culster_info["cpname"] = "mysql_cluster"

    info = info
    executer1 = None
    executer2 = None

    try:
        executer1 = SqlExecuter(ip = info["ip_1"], user= info["ip_dbuser"], passwd= info["ip_dbpasswd"], port=info["port_1"])
        executer2 = SqlExecuter(ip = info["ip_2"], user= info["ip_dbuser"], passwd= info["ip_dbpasswd"], port=info["port_2"])
        result_1 =  get_nodeinfo(executer1)
        result_2 =  get_nodeinfo(executer2)
    except Exception as e:
        result_1 = 0
        result_2 = 0
    finally:
        if executer1 is not None:
            executer1.dispose()
        if executer2 is not None:
            executer2.dispose()


    cluster_name = info["ip_1"] + ':' + str(info["port_1"])
    culster_info["clusterName"] = cluster_name
    culster_info["FLAGPID"] = pid

    if ( result_1 == 0 and result_2 == 0 ):
        culster_info["error"] = 'both can not connect'
        culster_info["replicate_status"] = 'green' + '_' + 'green'
        culster_info["Seconds_Behind_Master"] = '0'
        culster_info["MySQL_avail_conn"] = '0'
        culster_info["slow_sql"] = '0'
        culster_info["lock_sql"] = '0'
        culster_info["tps"] = '0'
        culster_info["qps"] = '0'
        culster_info["runtime"] = '0'
        culster_info["starttime"] = time.asctime(time.localtime(time.time()))

        if ( host_1["mysql_proc"] == 'alive' and  host_2["mysql_proc"] == 'alive' ):
            culster_info["mysql_status"] = 'green_green'
        elif ( host_1["mysql_proc"] == 'die' and  host_2["mysql_proc"] == 'alive' ):
            culster_info["mysql_status"] = 'red_green'
        elif ( host_1["mysql_proc"] == 'alive' and  host_2["mysql_proc"] == 'die' ):
            culster_info["mysql_status"] = 'green_red'
        else :
            culster_info["mysql_status"] = 'red_red'


    elif ( result_1 == 0 and result_2 != 0 ):
        culster_info["error"] = host_1["hostname"] + ' can not connect'
        culster_info["runtime"] = result_2["uptime"]
        culster_info["starttime"] = result_2["start_time"]
        culster_info["replicate_status"] = 'green' + '_' + result_2["replicate_status"]
        culster_info["Seconds_Behind_Master"] = '0' + '_' + result_2["Seconds_Behind_Master"]

        culster_info["MySQL_avail_conn"] = result_2["MySQL_avail_conn"]
        culster_info["slow_sql"] = result_2["slow_sql"]
        culster_info["lock_sql"] = result_2["lock_sql"]
        culster_info["tps"] = result_2["tps"]
        culster_info["qps"] = result_2["qps"]
        if ( host_1["mysql_proc"] == 'alive' ):
            culster_info["mysql_status"] = 'green_green'
        else :
            culster_info["mysql_status"] = 'red_green'
    elif (result_1 != 0 and result_2 == 0):
        culster_info["error"] = host_2["hostname"] + ' can not connect'
        culster_info["runtime"] = result_1["uptime"]
        culster_info["starttime"] = result_1["start_time"]
        culster_info["replicate_status"] = result_1["replicate_status"] + '_' + 'green'
        culster_info["Seconds_Behind_Master"] = result_1["Seconds_Behind_Master"]
        culster_info["MySQL_avail_conn"] = result_1["MySQL_avail_conn"]
        culster_info["slow_sql"] = result_1["slow_sql"]
        culster_info["lock_sql"] = result_1["lock_sql"]
        culster_info["tps"] = result_1["tps"]
        culster_info["qps"] = result_1["qps"]
        if (host_2["mysql_proc"] == 'alive'):
            culster_info["mysql_status"] = 'green_green'
        else:
            culster_info["mysql_status"] = 'green_red'
    else :
        culster_info["error"] = 'OK'
        if ( result_1["uptime"] >=result_2["uptime"] ):
            culster_info["runtime"] = result_1["uptime"]
        else :
            culster_info["runtime"] = result_2["uptime"]

        if ( result_1["start_time"] >=result_2["start_time"] ):
            culster_info["starttime"] = result_1["start_time"]
        else :
            culster_info["starttime"] = result_2["start_time"]

        culster_info["replicate_status"] =  result_1["replicate_status"] + '_' +  result_2["replicate_status"]

        if ( result_1["Seconds_Behind_Master"] >= result_2["Seconds_Behind_Master"] ):
            culster_info["Seconds_Behind_Master"] = result_1["Seconds_Behind_Master"]
        else :
            culster_info["Seconds_Behind_Master"] = result_2["Seconds_Behind_Master"]

        if ( result_1["MySQL_avail_conn"]  >= result_2["MySQL_avail_conn"] ):
            culster_info["MySQL_avail_conn"] = result_2["MySQL_avail_conn"]
        else :
            culster_info["MySQL_avail_conn"] = result_1["MySQL_avail_conn"]

        if ( result_1["slow_sql"]  >= result_2["slow_sql"] ):

            culster_info["slow_sql"] =  result_1["slow_sql"]
        else :
            culster_info["slow_sql"] = result_2["slow_sql"]

        if ( result_1["lock_sql"]  >= result_2["lock_sql"] ):
            culster_info["lock_sql"] = result_1["lock_sql"]
        else :
            culster_info["lock_sql"] = result_2["lock_sql"]

        if ( result_1["tps"] >= result_2["tps"] ):
            culster_info["tps"] = result_1["tps"]
        else :
            culster_info["tps"] = result_2["tps"]

        if ( result_1["qps"] >= result_2["qps"] ):
            culster_info["qps"] = result_1["qps"]
        else :
            culster_info["qps"] = result_2["qps"]

        culster_info["mysql_status"] = 'green_green'

    if ( culster_info["mysql_status"] ==  'green_green' ):

        culster_info["CLUSTER_HEALTH"] = 'green'
    elif ( culster_info["mysql_status"] ==  'red_green' ):
        culster_info["CLUSTER_HEALTH"] = 'yellow'

    elif  ( culster_info["mysql_status"] ==  'green_red' ):
        culster_info["CLUSTER_HEALTH"] = 'yellow'
    else :
        culster_info["CLUSTER_HEALTH"] = 'red'
    return culster_info

if __name__ == '__main__':
    info = {}
    arginfo = get_arginfo(sys.argv[1:])
    ipinfo = re.split('[,:]', arginfo["-f"])
    info["ip_1"] = ipinfo[0]
    info["port_1"] = int(ipinfo[1])
    info["ip_2"] = ipinfo[2]
    info["port_2"] = int(ipinfo[3])

    for key in arginfo:
        if ( key == "-p" ):
            info["ip_dbpasswd"] = arginfo["-p"]
            break
        else :
            info["ip_dbpasswd"] = ""

    for key in arginfo:
        if ( key == "-u" ):
            info["ip_dbuser"] = arginfo["-u"]
            break
        else :
            info["ip_dbuser"] = ""


    culster_info = getcluster_info(info)
    jsonStr = json.dumps(culster_info)
    print "%s" % jsonStr

LEAVE A COMMENT

Captcha Code