def QueryRollbackSQL(taskId, backup_mysql_url):
"""查询任务执行成功后的SQL回滚语句"""
res = dict(code=1, msg=None)
if taskId and isinstance(taskId, int) and backup_mysql_url:
dbbackup = create_mysql_engine(backup_mysql_url)
dblocal = create_mysql_engine()
sql = "SELECT executeResult FROM incetops_task WHERE taskId=%s AND status=0"
data = dblocal.get(sql, taskId)
if data and isinstance(data, dict) and data.get("executeResult"):
result = json.loads(data["executeResult"])
rollback_sqls = []
for row in result:
if row.get('backup_dbname'
) and row.get('backup_dbname') != 'None':
backup_dbname = row.get("backup_dbname")
opid_time = row.get("sequence").replace("'", "")
rowdata = dict(opid_time=opid_time,
backup_dbname=backup_dbname,
sql=None,
rollback=None,
errmsg=None)
# 以下是实际查询操作
sql_table = "select sql_statement,tablename from {}.$_$Inception_backup_information$_$ where opid_time=%s".format(
backup_dbname)
try:
table_data = dbbackup.get(sql_table, opid_time)
except Exception, e:
logger.warning(e)
res.update(msg=str(e))
else:
if not table_data:
rowdata["errmsg"] = "Get table data error"
else:
rowdata["sql"] = table_data["sql_statement"]
sql_back = "select rollback_statement from {}.{} where opid_time=%s".format(
backup_dbname, table_data['tablename'])
back_data = dbbackup.query(sql_back, opid_time)
if not back_data:
rowdata["errmsg"] = "Get rollback data error"
else:
rowdata["rollback"] = "\n".join([
i["rollback_statement"] for i in back_data
if isinstance(i, dict)
and "rollback_statement" in i
])
rollback_sqls.append(rowdata)
else:
continue
if rollback_sqls:
res.update(code=0, data=rollback_sqls)
else:
res.update(code=2, msg=res["msg"] or "No rollback sql")
else:
res.update(msg="Invaild task")
def before_request():
g.startTime = time.time()
g.redis = create_redis_engine()
g.mysql = create_mysql_engine()
g.signin = verify_sessionId(request.cookies.get("sessionId"))
g.sid, g.uid = analysis_sessionId(request.cookies.get("sessionId"),
"tuple") if g.signin else (None, None)
app.logger.debug("uid: {}, sid: {}".format(g.uid, g.sid))
g.api = api
g.ip = request.headers.get('X-Real-Ip', request.remote_addr)
g.agent = request.headers.get("User-Agent")
# 仅是重定向页面快捷定义
g.redirect_uri = get_redirect_url()
# 上下文扩展点之请求后(返回前)
before_request_hook = plugin.get_all_cep.get("before_request_hook")
for cep_func in before_request_hook():
cep_func(request=request, g=g)
before_request_return = plugin.get_all_cep.get("before_request_return")
for cep_func in before_request_return():
resp = cep_func(request=request, g=g)
try:
success = resp.is_before_request_return
except:
logger.warn(
"Plugin returns abnormalities when before_request_return")
else:
if success is True:
return resp
def __init__(self):
#设置全局超时时间(如连接超时)
self.timeout= 2
# 建立redis单机连接
if REDIS:
self.redis = from_url(REDIS)
else:
raise RedisURLError("The value of the REDIS in the config.py file is not valid.")
# 建立mysql连接
self.mysql = create_mysql_engine()
self.asyncQueueHigh = Queue(name='high', connection=self.redis)
self.asyncScheduler = Scheduler(queue=self.asyncQueueHigh, connection=self.redis, interval=1)
def before_request():
g.redis = create_redis_engine()
g.mysql = create_mysql_engine()
g.signin = verify_cookie(request.cookies.get("sessionId"))
g.uid = analysis_cookie(request.cookies.get("sessionId")).get("uid")
g.ref = request.referrer
g.redirect_uri = g.ref or url_for('front.index') if request.endpoint and request.endpoint in ("logout", ) else request.url
#access_logger.debug("referrer: {}, redirect_uri: {}".format(g.ref, g.redirect_uri))
#上下文扩展点之请求后(返回前)
before_request_hook = plugin.get_all_cep.get("before_request_hook")
for cep_func in before_request_hook():
cep_func(request=request, g=g)
def before_request():
sessionId = request.cookies.get("sessionId",
request.headers.get("sessionId"))
g.startTime = time.time()
g.redis = create_redis_engine()
g.mysql = create_mysql_engine()
g.signin = verify_sessionId(sessionId)
g.sid, g.uid = analysis_sessionId(sessionId,
"tuple") if g.signin else (None, None)
logger.debug("uid: {}, sid: {}".format(g.uid, g.sid))
g.api = api
g.ip = request.headers.get('X-Real-Ip', request.remote_addr)
g.agent = request.headers.get("User-Agent")
# 仅是重定向页面快捷定义
g.redirect_uri = get_redirect_url()
def get_statistic(self):
"""统计数据
# 直接mysql计算各类型百分比，返回4位float
select format(sum((usedb)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) usedb_rate, format(sum((deleting)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) deleting_rate, format(sum((inserting)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) inserting_rate, format(sum((updating)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) updating_rate, format(sum((selecting)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) selecting_rate, format(sum((altertable)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) altertable_rate, format(sum((createtable)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) createtable_rate, format(sum((droptable)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) droptable_rate, format(sum((createdb)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) createdb_rate, format(sum((truncating)/(usedb+deleting+inserting+updating+selecting+altertable+createtable+droptable+createdb+truncating))/count(1), 4) truncating_rate from inception.statistic;
# 查询各类型总数量
select format(sum(usedb), 0) usedb,format(sum(deleting), 0) deleting,format(sum(inserting), 0) inserting,format(sum(updating), 0) updating,format(sum(selecting), 0) selecting,format(sum(altertable), 0) altertable,format(sum(createtable), 0) createtable,format(sum(droptable), 0) droptable,format(sum(createdb), 0) createdb,format(sum(truncating), 0) truncating from inception.statistic;
# 暂时只统计了大分类，alter子分类图表参考http://echarts.baidu.com/examples/editor.html?c=pie-nest
"""
res = dict(code=1, msg=None)
sql = 'select format(sum(usedb), 0) usedb,format(sum(deleting), 0) deleting,format(sum(inserting), 0) inserting,format(sum(updating), 0) updating,format(sum(selecting), 0) selecting,format(sum(altertable), 0) altertable,format(sum(createtable), 0) createtable,format(sum(droptable), 0) droptable,format(sum(createdb), 0) createdb,format(sum(truncating), 0) truncating,format(sum(renaming), 0) renaming,format(sum(createindex), 0) createindex,format(sum(dropindex), 0) dropindex,format(sum(addcolumn), 0) addcolumn,format(sum(dropcolumn), 0) dropcolumn,format(sum(changecolumn), 0) changecolumn,format(sum(alteroption), 0) alteroption,format(sum(alterconvert), 0) alterconvert from inception.statistic'
try:
backup_mysql = create_mysql_engine(PLUGINS["IncetOps"]["DefaultBackupDatabase"])
data = backup_mysql.get(sql)
except Exception,e:
res.update(msg=str(e))
logger.warning(e, exc_info=True)
def __init__(self):
#设置全局超时时间(如连接超时)
self.timeout = 2
self.redis = create_redis_engine()
self.mysql = create_mysql_engine()
def __init__(self, mysql=None, redis=None):
self.db = mysql if mysql else create_mysql_engine()
self.rc = redis if redis else create_redis_engine()
def InceptionProxy(Action, sqlContent, dbData, inception, **kwargs):
"""Inception代理器-通过参数使用Inception服务处理SQL
@kwargs:
sd(str), 任务描述
applicant(str)，申请人
autoviewResult(list)，自动审核结果
enableRemoteBackup,enableIgnoreWarnings(int)，启用备份、禁用警告
"""
res = dict(code=1, msg=None)
aes = KeyGenerationClass("XingkaOps.AT.Key")
if True:
#检查参数
if Action in ("Check", "Execute") and sqlContent and inception and check_sql(sqlContent) == True and check_ipport(inception):
sqlContent = sqlContent.rstrip().replace("\n", "")
logger.debug(sqlContent)
#高危sql检测
scc = sql_criticalddl_check(sqlContent)
if scc == "OK":
# 未发现高危sql; 执行预检测
sic = sql_inceptionpre_check(sqlContent)
if sic == "OK":
# 预检测通过; 先通过dbId查出mysql
if dbData and isinstance(dbData, dict) and "host" in dbData and "user" in dbData and "port" in dbData and "passwd" in dbData:
dbData["passwd"] = aes.decrypt(dbData["passwd"])
# 根据不同Action，使用inception执行
idb = IncetDB(host=inception.split(":")[0], port=int(inception.split(":")[-1]))
mysql = create_mysql_engine()
if Action == "Check":
# 通过Inception检查任务中sql
sql = '/*--user=%s;--password=%s;--host=%s;--port=%s;--enable-check;*/\
inception_magic_start;\
%s\
inception_magic_commit;' %(dbData["user"], dbData["passwd"], dbData["host"], dbData["port"], sqlContent)
result = idb.execute(sql)
logger.debug(result)
if result is None or len(result) == 0:
res.update(code=2, msg="The return of Inception is null. May be something wrong with the SQL")
else:
res.update(data=result, code=0)
elif Action == "Execute":
# 通过Inception立即执行任务中sql，请先执行Check后再使用Execute
ebiw, taskId = "", kwargs["taskId"]
if int(kwargs.get("enableRemoteBackup", 1)) == 1:
ebiw += "--enable-remote-backup;"
else:
ebiw += "--disable-remote-backup;"
if int(kwargs.get("enableIgnoreWarnings", 0)) == 1:
ebiw += "--enable-ignore-warnings;"
sql = '/*--user=%s;--password=%s;--host=%s;--port=%s;--enable-execute;%s*/\
inception_magic_start;\
%s\
inception_magic_commit;' %(dbData["user"], dbData["passwd"], dbData["host"], dbData["port"], ebiw, sqlContent)
logger.debug(sql)
if taskId:
# 设置状态为执行中，并设置执行时间
mysql.update("update incetops_task set status=3,ftime=%s where taskId=%s", get_current_timestamp(), taskId)
result = idb.execute(sql)
logger.debug(result)
if result is None or len(result) == 0:
status, statusMsg = 4, "Execution result is empty"
res.update(code=2, msg="The return of Inception is null. May be something wrong with the SQL")
else:
# 分析执行结果
pir = parse_inception_result(result, 2)
statusMsg = "\n".join(pir["errormessage"]) + "\n"
if 2 in pir["errlevel"]:
status = 4
elif 1 in pir["errlevel"]:
statusMsg += "\n".join(pir["stagestatus"])
status = 4 if int(kwargs.get("enableIgnoreWarnings", 0)) == 0 else 0
else:
status, statusMsg = 0, "\n".join(pir["stagestatus"])
res.update(data=result, code=0)
mysql.update("update incetops_task set status=%s,statusMsg=%s,executeResult=%s where taskId=%s", status, statusMsg, json.dumps(result), taskId)
else:
res.update(msg="Invalid taskId")
else:
res.update(msg="Invaild dbId")
else:
# 预检测出sql通过inception执行可能会core dump，拒绝审核通过
res.update(msg=sic)
else:
# 发现高危sql，拒绝审核通过
res.update(msg=scc)
else:
cs = check_sql(sqlContent)
res.update(msg="There are invalid parameters" if cs == False else cs)
return res
Docstring conventions:
http://flask.pocoo.org/docs/0.10/styleguide/#docstrings
Comments:
http://flask.pocoo.org/docs/0.10/styleguide/#comments
:copyright: (c) 2017 by staugur.
:license: MIT, see LICENSE for more details.
"""
import json
from utils.tool import cli_logger, create_redis_engine, create_mysql_engine, getIpArea, parse_userAgent
redis = create_redis_engine()
mysql = create_mysql_engine()
def execute_refresh_loginlog(times=30):
"""从redis读取登录记录刷入mysql"""
key = "passport:loginlog"
while times > 0:
data = redis.lpop(key)
try:
data = json.loads(data)
except:
cli_logger.info("no loginlog")
break
else:
browserType, browserDevice, browserOs, browserFamily = parse_userAgent(
data["user_agent"])
def __init__(self, mysql=None, redis=None):
self.db = mysql or create_mysql_engine()
self.rc = redis or create_redis_engine()
