首页 > 代码库 > 个人记录3 mysql监控
个人记录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])
本文出自 “expect批量同步数据” 博客,谢绝转载!
个人记录3 mysql监控