此脚本可以拉取rds 审计日志 并且插入本地数据中。
#!/usr/bin/env python2.6
#coding=utf-8
import os
from aliyunsdkcore import client
from aliyunsdkrds.request.v20140815 import DescribeSQLLogRecordsRequest
import json
import urllib
import datetime,time
import subprocess
from subprocess import call
import warnings
import MySQLdb
from math import ceil
from retrying import retry
dbserver="192.168.0.94"
dbuser="root"
dbpwd="user@123"
dbport=3306
dbname="audit"
warnings.filterwarnings("ignore")
os.environ["PATH"]="/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin"
dblist = ['rm-1111','rm-2222','rr-333','rm-444','rm-555']
list = {"rm-1111":"beizhu1","rm-2222":"beizhu2", "rr-333":"beizhu3","rm-444":"beizhu4"}
# mysqldb 操作类
class db_operate(object):
def __init__(self,_hostname,_user,_pwd,_port,_db):
self.conn=MySQLdb.connect(host=_hostname,user=_user,passwd=_pwd,port=_port,db=_db)
self.conn.set_character_set('utf8')
self.cur=self.conn.cursor()
def execsql_fetchall(self,sqlcmd):
self.cur.execute(sqlcmd)
result=self.cur.fetchall()
return result
def execsql_fetchone(self,sqlcmd):
self.cur.execute(sqlcmd)
result=self.cur.fetchall()
return result
def execsql_dml(self,sqlcmd):
self.cur.execute(sqlcmd)
def is_connection_usable(self):
try:
self.conn.ping()
except Exception:
return False
else:
return True
# 获得时间需要备份的时间范围
def getdate():
global start_date
global end_date
current_time = datetime.datetime.now()
end_date = localtrfutc(current_time) # 当前时间
start_date = localtrfutc(current_time - datetime.timedelta(minutes=5)) #拉取指定时间范围内的日志
print start_date,end_date
return 0
#本地 时间转utc
def localtrfutc(local_time):
utc_time = local_time - datetime.timedelta(hours=8)
return utc_time.strftime("%Y-%m-%dT%H:%M:%SZ")
@retry(wait_fixed=5000)
def geterrorlog(db_instanceid,pageNum,Flag):
print db_instanceid,pageNum,Flag
clt = client.AcsClient('1234','123456','cn-hangzhou')
request = DescribeSQLLogRecordsRequest.DescribeSQLLogRecordsRequest()
request.set_accept_format('json')
request.set_action_name('DescribeSQLLogRecords')
request.set_DBInstanceId(db_instanceid)
request.set_StartTime(start_date)
request.set_EndTime(end_date)
request.set_PageNumber(int(pageNum))
result = clt.do_action(request)
s=json.loads(result)
if Flag == 0:
PageRecordCount = float(s["PageRecordCount"])
TotalRecordCount = float(s["TotalRecordCount"])
if TotalRecordCount == 0:
return 0
result = int(ceil(TotalRecordCount/PageRecordCount))
else:
result = s['Items']['SQLRecord']
return result
def installlog(dbid,auditlog):
for log in auditlog:
TotalExecutionTimes = log["TotalExecutionTimes"]
ExecuteTime = datetime.datetime.strptime(log["ExecuteTime"],'%Y-%m-%dT%H:%M:%SZ') + datetime.timedelta(hours=8)
AccountName = log["AccountName"]
HostAddress = log["HostAddress"]
ThreadID = log["ThreadID"]
DBName = log["DBName"]
ReturnRowCounts = log["ReturnRowCounts"]
sqltext = '/* '+log["SQLText"].replace("'",'"')+' */'
serverid = dbid
servermark = unicode(list.get(dbid),"utf-8")
sqlcmd=''' insert into yw_business(TotalExecutionTimes,ExecuteTime,AccountName,HostAddress,ThreadID,DBName,ReturnRowCounts,SQLText,serverid,servermark) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ''' % (TotalExecutionTimes,ExecuteTime,AccountName,HostAddress,ThreadID,DBName,ReturnRowCounts,sqltext,serverid,servermark)
# sqlcmd=''' insert into yw_business(TotalExecutionTimes,ExecuteTime,AccountName,HostAddress,ThreadID,DBName,ReturnRowCounts,SQLText) values('%s','%s','%s','%s','%s','%s','%s','%s') ''' % (TotalExecutionTimes,ExecuteTime,AccountName,HostAddress,ThreadID,DBName,ReturnRowCounts,sqltext)
try:
fo.execsql_dml(sqlcmd)
except Exception,e:
print sqlcmd
print e
fo.conn.commit()
# 插入其他页面的数据
def insertdata(dbid,totalpageNum):
for pageNum in range(1,totalpageNum+1):
result = geterrorlog(dbid,pageNum,1)
if result == "0":
continue
installlog(dbid,result)
try:
if __name__ == "__main__":
getdate()
global fo
fo=db_operate(dbserver,dbuser,dbpwd,int(dbport),dbname)
for i in dblist:
print i
totalpages=geterrorlog(i,1,0)
print totalpages
insertdata(i,totalpages)
fo.conn.close()
except Exception,e:
cmd = ''' echo -e '审计日志插入异常' | mail -s ' %s 审计日志异常 ' dengwang@zillionfortune.com ''' % (list.get("%s" % i))
call(cmd,shell=True)
print e
转载于:https://blog.51cto.com/dwchaoyue/1933595