Personal records 3 MySQL monitoring

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

This article from "Expect volume synchronization data" blog, declined reprint!

Personal records 3 MySQL monitoring