#!/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
