# -*- coding: utf-8 -*-
"""
libs.tool
~~~~~~~~~~~~~~
工具类
:copyright: (c) 2018 by taochengwei.
:license: Apache2.0, see LICENSE for more details.
"""
from __future__ import absolute_import
from utils.tool import logger, get_current_timestamp, create_mysql_engine
from utils.aes_cbc import CBC as KeyGenerationClass
import re
import time
import json
import MySQLdb
import MySQLdb.cursors
class IncetDB(object):
"""封装的Inception操作类"""
def __init__(self, host, port, max_idle_time=8*3600, connect_timeout=3, **kwargs):
#打开数据库连接
self.db = None
self.host = host
self.port = port
self.max_idle_time = float(max_idle_time)
self.last_use_time = time.time()
self.kwargs = dict(host=host, port=port, charset="utf8", connect_timeout=connect_timeout, cursorclass=MySQLdb.cursors.DictCursor, **kwargs)
try:
self.connect()
except Exception:
raise
def __repr__(self):
try:
version = self.db.get_server_info()
except Exception:
return "<Server: %s, Status: disconnected>" % self.host
else:
return "<Server: %s, Status: connected, Version: %s>" % (self.host, version)
def __del__(self):
self.close()
def __ensure_connected(self):
if (self.db is None or (time.time() - self.last_use_time > self.max_idle_time)):
self.connect()
self.last_use_time = time.time()
def connect(self):
self.close()
self.db = MySQLdb.connect(**self.kwargs)
self.db.autocommit = True
def close(self):
"""Closes this database connection."""
if getattr(self, "db", None) is not None:
self.db.close()
self.db = None
def execute(self, sql, *args, **kwargs):
#获取操作游标
self.__ensure_connected()
cur = self.db.cursor()
try:
cur.execute(sql, kwargs or args)
ret = cur.fetchall()
except MySQLdb.MySQLError:
raise
else:
return ret
finally:
cur.close()
def get(self, sql, *args, **kwargs):
rows = self.execute(sql, *args, **kwargs)
if not rows:
return None
elif len(rows) > 1:
raise Exception("Multiple rows returned for Database.get() query")
else:
return rows[0]
def parse_inception_result(result, scene):
"""解析inception的结果集"""
if scene == 1:
# 获取sqlsha1列表, errlevel列表
return dict(sqlsha1=[ i['sqlsha1'] for i in result if i and isinstance(i, dict) and i.get('sqlsha1') ], errlevel=[ i['errlevel'] for i in result if i and isinstance(i, dict) ])
elif scene == 2:
# 获取errlevel列表和errormessage
return dict(errormessage=[ i['errormessage'] for i in result if i and isinstance(i, dict) and i.get('errormessage') and i.get('errormessage') != 'None' ], errlevel=[ i['errlevel'] for i in result if i and isinstance(i, dict) ], stagestatus=[ i['stagestatus'] for i in result if i and isinstance(i, dict) and i.get('stagestatus') and i.get('stagestatus') != 'None' ])
elif scene == 3:
# 获取执行结果中的backup_dbname, sequence, errlevel
return dict(backup_dbname=[ i['backup_dbname'] for i in result if i and isinstance(i, dict) and i.get('backup_dbname') and i.get('backup_dbname') != 'None' ], errlevel=[ i['errlevel'] for i in result if i and isinstance(i, dict) ], sequence=[ i['sequence'].replace("'","").replace('"','') for i in result if i and isinstance(i, dict) and i.get('sequence') and "".join(i.get('sequence').replace("'","").replace('"','').split('_')[:2]) != '00' ])
def check_sql(sqlContent):
"""针对多行情况检测sql语句"""
if sqlContent:
return sqlContent.rstrip().endswith(';')
return False
def check_ipport(connect):
pat = re.compile(r'^(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\:([0-9]|[1-9]\d{1,3}|[1-5]\d{4}|6[0-5]{2}[0-3][0-5])$')
if connect and pat.match(connect):
return True
def sql_criticalddl_check(sql_content):
"""
识别DROP DATABASE, DROP TABLE, TRUNCATE PARTITION, TRUNCATE TABLE等高危DDL操作，因为对于这些操作，inception在备份时只能备份METADATA，而不会备份数据！
如果识别到包含高危操作，则返回“审核不通过”
:return msg str:
OK表示可以通过审核；
其他字符串表示含有高危ddl，审核不通过，为具体拒绝信息。
"""
for row in sql_content.rstrip(';').split(';'):
if re.match(r"([\s\S]*)drop(\s+)database(\s+.*)|([\s\S]*)drop(\s+)table(\s+.*)|([\s\S]*)truncate(\s+)partition(\s+.*)|([\s\S]*)truncate(\s+)table(\s+.*)", row.lower()):
return "Reject High Danger SQL."
return "OK"
def sql_inceptionpre_check(sql_content):
"""
在提交给inception之前，预先识别一些Inception不能正确审核的SQL
比如"alter table t1;"或"alter table test.t1;"
以免导致inception core dump
:param sql_content:
:return: msg str:
OK是预检测通过；
其他信息不通过。
2. msg str: false是提示信息
"""
for row in sql_content.rstrip(';').split(';'):
if re.match(r"(\s*)alter(\s+)table(\s+)(\S+)(\s*);|(\s*)alter(\s+)table(\s+)(\S+)\.(\S+)(\s*);", row.lower() + ";"):
return "Pre-detect sql syntax error."
return "OK"
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("YRRGBRYQqrV1gv5A") #此处key值保持与service.py中DBService一致，否则无法解密
if True:
#检查参数
if Action in ("Check", "Execute") and sqlContent and inception and check_sql(sqlContent) == True and check_ipport(inception):
#高危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"] = unicode(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 = u'''/*--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 = u'''/*--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
def InceptionOSC(Action, sqlsha1, inception):
"""查询pt-osc执行进度"""
res = dict(code=1, msg=None)
if Action in ("Query", "Stop") and sqlsha1 and inception and check_ipport(inception) and len(sqlsha1) == 41 and sqlsha1.startswith('*'):
if Action == "Query":
# 通过Inception查询osc
sql = 'inception get osc_percent %s;'
idb = IncetDB(host=inception.split(":")[0], port=int(inception.split(":")[-1]))
result = idb.get(sql, sqlsha1)
if not result:
res.update(code=2, msg="No query to OSC progress")
else:
res.update(data=result, code=0)
elif Action == "Stop":
# 通过Inception中止OSC执行
sql = 'inception stop alter %s;'
idb = IncetDB(host=inception.split(":")[0], port=int(inception.split(":")[-1]))
try:
result = idb.get(sql, sqlsha1)
logger.debug("stop osc result " + result)
except Exception,e:
res.update(msg=str(e))
else:
if not result:
res.update(code=2, msg="No query to OSC progress")
else:
res.update(data=result, code=0)
else:
res.update(msg="There are invalid parameters")
return res
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")
else:
res.update(msg="Invaild params")
return res
