MySQL Monitor py

Monitor MySQL

Probably logical

There's a library where we get the ports for all the libraries,

There's a table that records the IP address ports and roles for each database.

Some databases are both the main library and the library (not the actual slave, but the read and write are on this, so the table is also recorded as slave)

To get the data from the sun, that is, the main library and from the library as a primary library to come and go to monitor, the rest is from the library

Monitor MySQL with the following information

Monitor the MySQL link is ok

Monitor the master-slave status from the library, from the behind of the library, the number of links, the maximum number of connections set

The maximum number of links that the main library monitors and sets

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

This article is from the "Expect batch Sync data" blog, so be sure to keep this source http://4249964.blog.51cto.com/4239964/1558507

MySQL Monitor py