python个人记录3 mysql监控

#!/usr/bin/python

import MySQLdb

import pycurl

def master_work(ip,port):

    print ip,port

    conn=MySQLdb.connect(host=str(ip),user='***',passwd='****',port=int(port))

    cur=conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

    cur.execute('show status;')

    result=cur.fetchall()

    #conn1=result[-5]['Value']

    for result_master in result:

        if result_master['Variable_name'] == 'Threads_running':

            conn1=result_master['Value']

    print str(ip),str(port),str(conn1)

    cur.execute('show global variables like "%conn%";')

    result_2=cur.fetchall()

    for results_master in result_2:

        if results_master['Variable_name'] == 'max_user_connections':

            max_conn=results_master['Value']

    print conn1

    if int(conn1) >= 700:

        print 'master_'+str(ip)+str(port)+str(conn1)

        master_monitor('master_'+str(ip)+'_'+str(port)+'_'+'threading:'+str(conn1)+'_max_:'+str(max_conn))

    cur.close()

    conn.close()

def slave_work(ip,port,role):

    print role

    conn=MySQLdb.connect(host=ip,user='***',passwd='****',port=int(port))

    cur=conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

    cur.execute('show slave status;')

    result=cur.fetchone()

    Slave_IO_Running=result['Slave_IO_Running']  

    Slave_SQL_Running=result['Slave_SQL_Running']

    Seconds_Behind_Master=result['Seconds_Behind_Master']

    if Slave_IO_Running != 'Yes' or Slave_SQL_Running != 'Yes':

        print str(ip),str(port)+'_error'

        slave_monitor(str(ip)+'_'+str(port)+'_mysql_slave_down')

    if Seconds_Behind_Master >= 86400 and role == 'backup':

        if str(ip) == '***' and str(port) == '***':

            return None 

        elif str(ip) == '****' and str(port) == '***':

            return None

        else:

            print str(ip),str(port)+'_Seconds_Behind_Master:'+str(Seconds_Behind_Master)

            slave_monitor(str(ip)+'_'+str(port)+'_Seconds_Behind_Master_'+str(Seconds_Behind_Master))

    elif Seconds_Behind_Master > 100 and role == 'slave':

        if str(ip) == '****' and str(port) == '****':

            return None 

        elif str(ip) == '****' and str(port) == '***':

            return None

        else:

            print str(ip),str(role),str(port)+'_Seconds_Behind_Master:'+str(Seconds_Behind_Master)

            slave_monitor(str(ip)+'_'+str(port)+'_Seconds_Behind_Master_'+str(Seconds_Behind_Master))

    cur.execute('show status;')

    result_1=cur.fetchall()

    for results in result_1:

        if results['Variable_name'] == 'Threads_running':

            conn1=results['Value']

    cur.execute('show global variables like "%conn%";')

    result_2=cur.fetchall()

    for result_i in result_2:

        if result_i['Variable_name'] == 'max_user_connections':

            max_conn=result_i['Value']

    print str(ip),str(port),str(max_conn)

    if int(conn1) >= 700:

        print str(ip),str(port),str(conn1)+'_error'

        slave_monitor('slave_'+str(ip)+'_'+str(port)+'_threading:'+str(conn1)+'_max_conn:'+str(max_conn))

    cur.close()

    conn.close()

def slave_monitor(content):

    c=pycurl.Curl()

    c.setopt(c.URL,'http://****/new/?service=mysql_slave&checkpoint=mysql_slave_error&title=%s&content=%s&cluster=public&grade=2'%(content,content)) 

    c.perform()

def master_monitor(content):

    c=pycurl.Curl()

    c.setopt(c.URL,'http://*****/new/?service=mysql_master&checkpoint=mysql_master_error&title=%s&content=%s&cluster=public&grade=2'%(content,content))

    c.perform()

conn=MySQLdb.connect(host='****',user='***',passwd='****',db='****',port=***)

cur=conn.cursor()

cur.execute('select distinct port from node order by port;')

res=cur.fetchall()

list_1=[]

dict_1={}

dict_2={}

for i in res:

    cur.execute('select distinct host,port,type from node where port=%s'%str(i[0]))

    res1=cur.fetchall()

    list_1.append(res1)

for ii in list_1:

    for iii in ii:

        ip_1=iii[0]

        port_1=iii[1]

        dict_2[port_1]=ip_1

        if iii[2] == 'master':

            master_ip=iii[0]

            port=iii[1]

            dict_1[port]=master_ip

            master_work(master_ip,port)

cur.close()

conn.close()

for aa in list_1:

    for aaa in aa:

        port_2=aaa[1]

        if aaa[0] == dict_1[port_2]:

            pass

        else:

            slave_work(aaa[0],aaa[1],aaa[2])