def send_rds_slowlog_by_id(self, id_desc_list):
log_path_list = []
now = datetime.datetime.now()
diff = now - datetime.timedelta(days=1)
start_time = diff.strftime("%Y-%m-%dT%H:00Z")
end_time = now.strftime("%Y-%m-%dT%H:00Z")
for dbname, dbid in id_desc_list.items():
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
request.set_DBInstanceId(dbid)
request.set_PageSize(50)
request.set_StartTime(start_time)
request.set_EndTime(end_time)
result = self._request(request)
#print json.dumps(result,sort_keys=True,indent=4, separators=(',', ': '))
if result['TotalRecordCount'] > 0:
log_path = "/tmp/%s-slowlog.csv" % dbname
self.save_csv(result['Items']['SQLSlowRecord'], log_path)
log_path_list.append(log_path)
if log_path_list:
self.send_email(u"数据库慢SQL", u"附件为%s ~ %s慢日志，请查收。谢谢！" % (start_time, end_time), log_path_list)
def get_record(startdate, enddate, pagenum):
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
request.set_accept_format('json')
request.set_DBInstanceId('rm-bp196ckqc2wi132y1')
request.set_StartTime(startdate + 'Z')
request.set_EndTime(enddate + 'Z')
request.set_action_name('DescribeSlowLogs')
request.set_PageSize(100)
request.set_PageNumber(pagenum)
result = clt.do_action(request)
with open('MaxExecutionTime%s--%s.txt' % (startdate, enddate),
'a+') as M_w:
for i in eval(result)['Items']['SQLSlowLog']:
# if i['MaxExecutionTime']>1:
M_w.writelines(
str(
str(i['MaxExecutionTime']) + "\t" + i['DBName'] + "\t" +
i['SQLText'] + "\n"))
with open('TotalExecutionTimes%s--%s.txt' % (startdate, enddate),
'a+') as T_w:
for i in eval(result)['Items']['SQLSlowLog']:
#if i['MySQLTotalExecutionTimes']>1:
T_w.writelines(
str(
str(i['MySQLTotalExecutionTimes']) + "\t" + i['DBName'] +
"\t" + i['SQLText'] + "\n"))
def DescribeSlowLogRecords(self, StartTime, EndTime, **kwargs):
"""查看慢日志明细SQLId,DBName、PageSize、PageNumber"""
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
values = {"action_name": "DescribeSlowLogRecords", "DBInstanceId": self.DBInstanceId,
"StartTime": StartTime, "EndTime": EndTime}
values = dict(values, **kwargs)
result = self.request_api(request, values)
return result
def DescribeSlowLogRecords(self, dbInstanceId, startTime, endTime,
pageNumber):
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
request.set_accept_format('json')
request.set_DBInstanceId(dbInstanceId)
request.set_StartTime(startTime)
request.set_EndTime(endTime)
request.set_PageNumber(pageNumber)
return json.loads(self.clt.do_action(request))
def get_pagecount(startdate, enddate):
#传入查询初始时间和结束时间
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
request.set_accept_format('json')
request.set_DBInstanceId('rm-bp196ckqc2wi132y1')
request.set_StartTime(startdate + 'Z')
request.set_EndTime(enddate + 'Z')
request.set_action_name('DescribeSlowLogs')
request.set_PageSize(100)
request.set_PageNumber(1)
result = clt.do_action(request)
return eval(result)['TotalRecordCount']
def DescribeSlowLogRecords(self, DBInstanceId, StartTime, EndTime, DBType, **kwargs):
'''查看慢日志明细
SQLId,DBName、PageSize、PageNumber'''
if DBType == 'mongodb':
request = DdsDescribeSlowLogRecordsRequest()
else:
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
values = {"action_name": "DescribeSlowLogRecords", "DBInstanceId": DBInstanceId,
"StartTime": StartTime, "EndTime": EndTime}
values = dict(values, **kwargs)
result = self.request_api(request, values)
return result
def getSlowLogDetails(client, dbInstanceId, startTime, endTime, dbName):
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
request.set_DBInstanceId(dbInstanceId)
request.set_StartTime(startTime)
request.set_EndTime(endTime)
request.set_DBName(dbName)
try:
response = client.do_action_with_exception(request)
return response
except ServerException as exception:
print("Error while getting slow log details.")
print("HTTP statue = ", exception.get_http_status())
print("HTTP Error code = ", exception.get_error_code())
print("Error message = ", exception.get_error_msg())
return None
def GetSlowlog(DBInstanceId, StartTime, EndTime):
Slowlog = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
Slowlog.set_accept_format('json')
Slowlog.set_DBInstanceId(DBInstanceId)
Slowlog.set_StartTime(StartTime)
# Slowlog.set_StartTime("2017-10-13T15:00Z")
Slowlog.set_EndTime(EndTime)
SlowlogInfo = clt.do_action_with_exception(Slowlog)
Info = (json.loads(SlowlogInfo))
PageRecordCount = Info['PageRecordCount']
# print PageRecordCount
if PageRecordCount == 0:
print 0
else:
i = 0
while i < PageRecordCount:
SQL = Info['Items']['SQLSlowRecord'][i]['SQLText']
DBNAME = Info['Items']['SQLSlowRecord'][i]['DBName']
QueryTimes = Info['Items']['SQLSlowRecord'][i]['QueryTimes']
i = i + 1
print "No: %s ,DBNAME: %s ,QueryTimes: %s ,SLOWSQL: %s" % (
i, DBNAME, QueryTimes, SQL)
def rds_slow_sql(request, db_instance_id, timing):
temp_name = "monitor/monitor-header.html"
if db_instance_id is None:
return HttpResponse("<h3>db_instance_id can not be None!</h3>",
content_type="text/html")
else:
t = time.localtime(time.time() - TIME_SECTOR[int(timing)])
start_time = time.strftime("%Y-%m-%dT%H:%MZ", t)
end_time = time.strftime("%Y-%m-%dT%H:%MZ", time.localtime())
# request = DescribeSlowLogsRequest.DescribeSlowLogsRequest()
req = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
req.set_DBInstanceId(db_instance_id)
req.set_StartTime(start_time)
req.set_EndTime(end_time)
client = AcsClient(ACCESS_KEY_ID, ACCESS_KEY_SECRET, 'cn-shanghai')
resp = json.loads(client.do_action_with_exception(req),
encoding='utf-8')
slow_sql_list = resp.get('Items').get('SQLSlowRecord')
return render(request, "monitor/rds_slow_sql.html", locals())
'cn-hangzhou')
dblist = [
'rr-2zeyvcbluukv1qiwn', 'rm-2zek28989p9k1179v', 'rr-2zeo3es975ai399y',
'rr-2ze0s71v12o8qdtuh', 'rr-2ze9wz8bb610cx7', 'rr-2ze6hc62pa61dx0p4',
'rr-2zela73o7mh0s2p8o', 'rr-2ze6t3cl58ad3w62', 'rr-2ze1t8s3l254v9qqv',
'rr-2ze66z8t4fpd0x51h', 'rm-2zeg270l9534v1a5m', 'rm-2ze6239209hgvuvgq',
'rm-2zey971kf9t3ublde', 'rm-2ze15vy3vb88cp426', 'rm-2zezohz4xdu54263k',
'rm-2ze85u60ll2iw7x46', 'rm-2ze29k1wb187v1796', 'rm-2ze2yq4xg2mz3a5p',
'rr-2ze5ufm1idmn4bn01', 'rr-2ze4ka78ef56b70fq', 'rr-2ze79e35ia84d2dpq',
'rm-2ze509kn6k6177uf5'
]
starttime = time.strftime('%Y-%m-%d') + "T00:00Z"
endtime = time.strftime('%Y-%m-%dT%H:%m') + 'Z'
request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest()
request.set_accept_format('json')
def exc_sql(db):
request.add_query_param('DBInstanceId', db)
request.add_query_param('StartTime', starttime)
request.add_query_param('EndTime', endtime)
request.add_query_param('PageSize', 100)
request.add_query_param('PageNumber', 1)
response = clt.do_action(request)
return json.loads(response)
if __name__ == "__main__":
for db in dblist: