def check_qps(host,port,user,passwd,db="",charset="utf8"):
"""统计各MySQL实例的QPS
"""
db = DB(host,port,user,passwd,db,charset,cursorclass="")
en = Encrypt()
db.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
rows = db.fetchall()
for row in rows:
db.execute("""SELECT UPTIME,QUESTIONS FROM T_QPS WHERE INSTID = {0}
ORDER BY ID DESC LIMIT 1""".format(row.get('ID')))
insertrow = db.fetchone()
if insertrow == None:
insertrow = dict()
tmpdb = DB(row.get('IPADDR'),row.get('PORT'),row.get('USER'),en.decrypt(row.get('PASSWD')))
tmpdb.execute("show global status like 'Uptime'")
uprow = tmpdb.fetchone()
tmpdb.execute("show global status like 'Questions'")
qurow = tmpdb.fetchone()
db.execute("""INSERT INTO T_QPS(INSTID,UPTIME,QUESTIONS,DIFFUPTIME,DIFFQUESTIONS)
VALUES({0},{1},{2},{3},{4})""".format(
row.get('ID'), uprow[1], qurow[1],
int(uprow[1])-int(insertrow.get('UPTIME',0)),
int(qurow[1])-int(insertrow.get('QUESTIONS', 0))
))
db.commit()
def truncate_slow(host, port, user, passwd):
if host and port and user and passwd:
db = DB(host=host, port=port, user=user, passwd=passwd, db="mysql")
else:
print "You mst give all parmaters as host,port,user,passwd."
exit(1)
try:
db.execute("TRUNCATE mysql.slow_log")
db.commit()
except MySQLdb.OperationalError as e:
print e
def main():
en = Encrypt()
cg = parser_config()
db = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
SQL = "SELECT ID,INET_NTOA(IP),PORT,USER,PASSWD FROM T_INSTANCE"
db.execute(SQL)
rows = db.fetchall()
for row in rows:
tmpdb = DB(row[1],row[2],row[3],en.decrypt(row[4]))
tmpdb.execute("SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST")
cnt = tmpdb.fetchone()
db.execute("""INSERT INTO T_CONNECTION(INSTANCE,CNT)
VALUES({0}, {1})""".format(row[0], cnt[0]))
db.commit()
def main():
# 建议读取外部配置文件以获取监控服务器配置
cg = parser_config()
en = Encrypt()
mon_db = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
mon_db.execute("SELECT ID,INET_NTOA(IP),PORT,USER,PASSWD FROM T_INSTANCE WHERE INSTTYPE='SLAVE'")
rows = mon_db.fetchall()
for row in rows:
replicat_status = check(row[1],row[2],row[3],en.decrypt(row[4]))
if replicat_status:
mon_db.execute("""INSERT INTO T_REPLICAT(INSTANCE,IOTHREAD,SQLTHREAD,BEHIND)
VALUES('{0}','{1}','{2}',{3})""".format(row[0],replicat_status.get('SLAVE_IO_RUNNING'),
replicat_status.get('SLAVE_SQL_RUNNING'),replicat_status.get('SECONDS_BEHIND_MASTER')))
mon_db.commit()
def check_hitrate(host, port, user, passwd, db="", charset="utf8"):
"""
"""
db = DB(host, port, user, passwd, db, charset, cursorclass="")
en = Encrypt()
db.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
rows = db.fetchall()
sql = "INSERT INTO T_HITRATE VALUES(NULL,'%s','%s','%s')"
for row in rows:
tmpdb = DB(row.get("IPADDR"), row.get("PORT"), row.get("USER"), en.decrypt(row.get("PASSWD")))
tmpdb.execute("show global status like 'Qcache_hits'")
hits = tmpdb.fetchone()
tmpdb.execute("show global status like 'Com_select'")
selects = tmpdb.fetchone()
try:
hit_rate = int(hits[1]) / int(hits[1] + selects[1])
except ZeroDivisionError:
hit_rate = 0
# 00.0%
db.execute(sql % (row.get("ID"), datetime.datetime.now(), int(hit_rate)))
db.commit()
def check_slowsql(host,port,user,passwd,db):
size = 1024
en = Encrypt()
db = DB(host,port,user,passwd,db)
db.execute("SELECT ID,INET_NTOA(IP),PORT,SOCKETPORT FROM T_INSTANCE ORDER BY ID DESC")
instrows = db.fetchall()
for inst in instrows:
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
s.connect((inst[1],inst[3]))
print "connected to:{0}\t{1}".format(inst[1],inst[3])
except socket.error as e:
print '\033[91m' + "Socket error:{0}\t{1} for host {2}".format(
e.errno, e.strerror, inst[1]) +\
'\033[0m'
if e.errno == 111:
continue
s.send('GET LaSt SQL')
data_collect = ""
while True:
data = s.recv(size)
if not data: break
data_collect += data
json_collect = json.loads(data_collect)
for row in json_collect:
try:
insert_row = [ i[1] for i in sorted(row.items())]
insert_row.append(inst[0])
db.executemany("""INSERT INTO
T_SLOW(ONDB,LOCKTIME,QUERYTIME,ROWS_EXAMINED,ROWS_SENT,SQL_TEXT,STARTTIME,STATE,USER_HOST,OBJID,INSTID)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
insert_row)
except MySQLdb.IntegrityError as e:
print 'row:{0} occur error:{1}'.format(tuple(insert_row[0:]),e)
state = db.commit()
if not state:
print 'UP ID:{0}'.format(insert_row[9])
tmps = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
tmps.connect((inst[1],inst[3]))
tmps.send('UP ID:{0}'.format(insert_row[9]))
data = tmps.recv(size)
tmps.close()
s.close()