MySQL主從、字典死鎖、連接數的Python管控腳本

2024-07-24 12:31:27

字體：大中小

來源：轉載

供稿：網友

#!/usr/bin/python

# MySQL主從集中監控腳本
# 請在MySQL上執行如下語句，將跑腳本的機器授權一個db_monitor賬號。
# grant SHOW VIEW,REPLICATION CLIENT,SHOW DATABASES,PROCESS on *.* to db_monitor@'192.168.1.10';
import MySQLdb
import os
import datetime

# 發送一個字符串告警，公司特有告警系統，可以改成其他監控方式，比如短信或郵件
def report_alarm(attr, err_msg):
    os.system('/usr/local/agenttools/agent/agentRepStr %s "%s" >/dev/null' % (attr, err_msg))

# 上報一個數值型告警，也是特有告警系統
def send_repnum(attr, num):
    os.system('/usr/local/agenttools/agent/agentRepNum %s "%s" >/dev/null' % (attr ,num) )

def monitor(db_host):
    try:
        cxn=MySQLdb.connect(db_host, 'db_monitor', '', db='mysql')
    except MySQLdb.Error, e:
        try:
            sqlError = "Error %d:%s" % (e.args[0], e.args[1])
        except IndexError:
            sqlError = "MySQL Error:%s" % str(e)
        report_alarm(36320, sqlError)

    #get mysql version
    cur=cxn.cursor()
    cur.execute('select version()')
    for item in cur.fetchall():
        if '5.0.' in item[0]:
            mysql_version = 'low'
        else:
            mysql_version = 'high'
    #show slave status
    cur=cxn.cursor()
    cur.execute('show slave status')
    for item in cur.fetchall():
        replicate_error = item[19]
        if mysql_version == 'low':
            seconds_behind_master = item[-1]
        else:
            seconds_behind_master = item[32]
    #show processlist
    cur=cxn.cursor()
    cur.execute('show processlist')
    connect_num = len(cur.fetchall())
    meta_lock_num = cur.fetchall().count("meta lock")
    cxn.close()
    return replicate_error,seconds_behind_master,connect_num,meta_lock_num

print datetime.datetime.now()

for host in open('%s/hosts.list'% os.path.dirname(os.path.realpath(__file__))):
    host = host.strip('/n')
    replicate_error = ''
    seconds_behind_master = 0
    connect_num = 0
    meta_lock_num = 0
    replicate_error,seconds_behind_master,connect_num,meta_lock_num = monitor(host)
    try:
        Seconds_behind_master = int(seconds_behind_master)
    except:
        Seconds_behind_master = seconds_behind_master
    print '==============================='
    print 'Infomation of %s' % host
    print "The Num of metalock   : %s" % meta_lock_num
    print "The Num of connection : %s" % connect_num
    # 上報當前連接數
    send = send_repnum(36713, connect_num)
    # 上報字典鎖數量
    send = send_repnum(36715, meta_lock_num)
    if len(replicate_error) > 0:
        print replicate_error
        # 如果主從同步出現問題，就發送告警
        report_alarm(36320, '%s: Replicate error' % host)
    elif Seconds_behind_master > 600:
        print "Seconds behid master: %s" % Seconds_behind_master
        # 如果主從延遲超過600s，就發送告警
        report_alarm(36320, '%s: Seconds behind master ge 600' % host)
    #else:
    #   print host + " is OK!"
print '==============================='
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

原文鏈接：http://blog.itpub.net/14710393/viewspace-2132654/

mysql

（編輯：武林網）

下一篇：linux下面創建關于java與mysql的web平臺