监控mysql
大概逻辑
有个库里得到我们先上所有库的端口，进行去重
有个表里记录了这个每个数据库的ip地址端口以及角色
有的数据库既是主库也是从库(不是实际上的slave，而是读写都在这上面所以，表里也记录为slave)
要先从晒出数据，即为主库又为从库的只当主库来去监控，剩下的都是从库
监控mysql如下信息
监控mysql的链接是否正常
从库监控 主从状态是否正常，从库的behind，链接数，设置的最大连接数
主库监控 链接数，设置的最大链接数
#!/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:
pass
print ‘master_‘+str(ip)+str(port)+str(conn1)
master_monitor(str(ip)+‘_‘+str(port)+‘_‘+‘threading:‘+conn1)
cur.close()
conn.close()
def slave_work(ip,port):
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‘)
pass
if Seconds_Behind_Master >= 86400:
print str(ip),str(port)+‘_Seconds_Behind_Master:‘+str(Seconds_Behind_Master)
mysql_test1.py 38,5 Top
:set nonu
conn.close()
def slave_monitor(content):
c=pycurl.Curl()
c.setopt(c.URL,‘http://alert.sae.sina.com.cn/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://alert.sae.sina.com.cn/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=‘mysql_status‘,port=3306)
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:
print aaa[0],aaa[1]
slave_work(aaa[0],aaa[1])
本文出自 “expect批量同步数据” 博客，请务必保留此出处http://4249964.blog.51cto.com/4239964/1558507