1、通过阿里sdk获取慢查询列表，格式化。
2、企业微信报警接口
3、deamon
#!/usr/bin/python
#-*- conding:utf-8 -*-
from aliyunsdkcore.client import AcsClient
from aliyunsdkrds.request.v20140815 import DescribeDBInstanceAttributeRequest
from aliyunsdkrds.request.v20140815 import DescribeSlowLogRecordsRequest
import requests
import json
import os
import time
import logging
import subprocess
#ali key
client = AcsClient(
"",
"",
""
);
log_file = "/tmp/rdsslowinfo.log"
logging.basicConfig(filename=log_file,filemode='a',level=logging.DEBUG)
def WARNING(*objs):
print("[%s] : " % time.strftime("%y-%m-%d %H:%M:%S", time.localtime()), *objs, file=sys.stderr)
class rdsInfo(object):
''' ali rds slow log get'''
def __init__(self,dbId,startTime,endTime):
self.dbId = dbId
self.startTime = startTime
self.endTime = endTime
def slowlogGet(self):
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
request.set_accept_format('json')
request.set_DBInstanceId(self.dbId)
request.set_StartTime(self.startTime)
request.set_EndTime(self.endTime)
response = client.do_action_with_exception(request)
data = json.loads(response)
return data
def instanceGet(self):
request = DescribeDBInstanceAttributeRequest.DescribeDBInstanceAttributeRequest()
request.set_accept_format('json')
request.set_DBInstanceId(self.dbId)
response = client.do_action_with_exception(request)
data = json.loads(response)
#print (data['Items'])
return data
class WeChat:
'''微信接口'''
def __init__(self,user):
self.CORPID = '' #企业ID， 登陆企业微信，在我的企业-->企业信息里查看
self.CORPSECRET = '' #自建应用，每个自建应用里都有单独的secret
self.AGENTID = '' #应用代码
self.TOUSER = user # 接收者用户名, @all 全体成员
def _get_access_token(self):
url = 'https://qyapi.weixin.qq.com/cgi-bin/gettoken'
values = {'corpid': self.CORPID,
'corpsecret': self.CORPSECRET,
}
req = requests.post(url, params=values)
data = json.loads(req.text)
# print (data)
return data["access_token"]
def get_access_token(self):
try:
with open('access_token.conf', 'r') as f:
t, access_token = f.read().split()
except:
with open('access_token.conf', 'w') as f:
access_token = self._get_access_token()
cur_time = time.time()
f.write('\t'.join([str(cur_time), access_token]))
return access_token
else:
cur_time = time.time()
if 0 < cur_time - float(t) < 7200: #token的有效时间7200s
return access_token
else:
with open('access_token.conf', 'w') as f:
access_token = self._get_access_token()
f.write('\t'.join([str(cur_time), access_token]))
return access_token
def send_data(self, msg):
send_url = 'https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=' + self.get_access_token()
send_values = {
"touser": self.TOUSER,
"msgtype": "text",
"agentid": self.AGENTID,
"text": {
"content": msg
},
"safe": "0"
}
send_msges=(bytes(json.dumps(send_values), 'utf-8'))
respone = requests.post(send_url, send_msges)
respone = respone.json()#当返回的数据是json串的时候直接用.json即可将respone转换成字典
# print (respone["errmsg"])
return respone["errmsg"]
def dataInit():
''' get data info'''
instanceInfo = {
'rm-阿里域名':'业务线',
'rm-':'',
'rm-':'',
'rm-':'',
'rm-':'',
}
alarmList = ['','','','','']#收报警人列表
for dbUrl in instanceInfo.keys():
nowTime = time.strftime("%Y-%m-%dT%H:%MZ")
second = time.time() - 24*60*60 #查询间隔 自己定义
tago = time.strftime('%Y-%m-%dT%H:%MZ', time.localtime(second))
rdsinfo = rdsInfo(dbUrl,tago,nowTime)
slowlog_data = rdsinfo.slowlogGet()
instance_data = rdsinfo.instanceGet()
try:
for recode in slowlog_data['Items']['SQLSlowRecord']:
#print(recode)
ParseRowCounts = recode['ParseRowCounts']
ReturnRowCounts = recode['ReturnRowCounts']
QueryTimes = recode['QueryTimes']
HostAddress = recode['HostAddress']
LockTimes = recode['LockTimes']
ExecutionStartTime = recode['ExecutionStartTime']
SQLText = recode['SQLText']
DBName = recode['DBName']
content = '''
业务线 :{url}
SQL来源:{HostAddress}
执行时间:{ExecutionStartTime}
数据库名:{DBName}
执行时长:{QueryTimes}
锁定时长:{LockTimes}
解析行数:{ParseRowCounts}
返回行数:{ReturnRowCounts}
SQL详情:{SQLText}
''' .format(url=instanceInfo[dbUrl],HostAddress=HostAddress,ExecutionStartTime\
=ExecutionStartTime,DBName=DBName,QueryTimes=QueryTimes,LockTimes=LockTimes,\
ParseRowCounts=ParseRowCounts,ReturnRowCounts=ReturnRowCounts,SQLText=SQLText)
#print(content)
logging.info(content)
for alarm in alarmList:
wx = WeChat(alarm)
wx.send_data(msg=content)
except Exception as e:
WARNING('[%s]')%(e)
class Daemon:
''' Daemon '''
def createDaemon(self):
try:
if os.fork() > 0: os._exit(0) # exit father…
except OSError as error:
print ('fork #1 failed: %d (%s)' % (error.errno, error.strerror))
os._exit(1)
# it separates the son from the father
os.chdir('/')
os.setsid()
os.umask(0)
# create - fork 2
try:
pid = os.fork()
if pid > 0:
print ('Daemon PID %d' % pid)
logging.info('Daemon PID %d' % pid)
os._exit(0)
except OSError as error:
print ('fork #2 failed: %d (%s)' % (error.errno, error.strerror))
os._exit(1)
self.run() # function demo
def run(self):
while True:
dataInit()
time.sleep(24*60*60) #deamon运行间隔时间
#time.sleep(5)
#except Exception,e:
# traceback.print_exc()
if __name__ == '__main__':
daemon = Daemon()
daemon.createDaemon()
