def execute_skipinc_call_back(workflowId, instance_name, db_name, sql_content,
url):
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
try:
# 执行sql
t_start = time.time()
execute_result = Dao(instance_name=instance_name).mysql_execute(
db_name, sql_content)
t_end = time.time()
execute_time = "%5s" % "{:.4f}".format(t_end - t_start)
execute_result['execute_time'] = execute_time + 'sec'
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
if execute_result.get('Warning'):
workflowDetail.status = Const.workflowStatus['exception']
elif execute_result.get('Error'):
workflowDetail.status = Const.workflowStatus['exception']
else:
workflowDetail.status = Const.workflowStatus['finish']
workflowDetail.finish_time = timezone.now()
workflowDetail.execute_result = json.dumps(execute_result)
workflowDetail.is_manual = 1
workflowDetail.audit_remark = ''
workflowDetail.is_backup = '否'
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
except Exception as e:
logger.error(e)
# 发送消息
send_msg(workflowDetail, url)
def __init__(self, instance_name, db_name, sqltext):
self.dao = Dao(instance_name=instance_name, flag=True)
self.db_name = db_name
self.sqltext = sqltext
self.sql_variable = '''
select
lower(variable_name),
variable_value
from performance_schema.global_variables
where upper(variable_name) in ('%s')
order by variable_name;''' % ('\',\''.join(SQLTuning.SYS_PARM_FILTER))
self.sql_optimizer_switch = '''
select variable_value
from performance_schema.global_variables
where upper(variable_name) = 'OPTIMIZER_SWITCH';
'''
self.sql_table_info = '''
select
table_name,
engine,
row_format as format,
table_rows,
avg_row_length as avg_row,
round((data_length + index_length) / 1024 / 1024, 2) as total_mb,
round((data_length) / 1024 / 1024, 2) as data_mb,
round((index_length) / 1024 / 1024, 2) as index_mb
from information_schema.tables
where table_schema = '%s' and table_name = '%s'
'''
self.sql_table_index = '''
def create_kill_session(request):
instance_name = request.POST.get('instance_name')
ThreadIDs = request.POST.get('ThreadIDs')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 判断是RDS还是其他实例
if len(AliyunRdsConfig.objects.filter(instance_name=instance_name)) > 0:
if SysConfig().sys_config.get('aliyun_rds_manage') == 'true':
result = aliyun_create_kill_session(request)
else:
raise Exception('未开启rds管理，无法查看rds数据！')
else:
ThreadIDs = ThreadIDs.replace('[', '').replace(']', '')
sql = "select concat('kill ', id, ';') from information_schema.processlist where id in ({});".format(
ThreadIDs)
all_kill_sql = Dao(instance_name=instance_name).mysql_query(
'information_schema', sql)
kill_sql = ''
for row in all_kill_sql['rows']:
kill_sql = kill_sql + row[0]
result['data'] = kill_sql
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def statistic(self):
sql = '''
select
sum(deleting) deleting,
sum(inserting) inserting,
sum(updating) updating,
sum(selecting) selecting,
sum(altertable) altertable,
sum(renaming) renaming,
sum(createindex) createindex,
sum(dropindex) dropindex,
sum(addcolumn) addcolumn,
sum(dropcolumn) dropcolumn,
sum(changecolumn) changecolumn,
sum(alteroption) alteroption,
sum(alterconvert) alterconvert,
sum(createtable) createtable,
sum(droptable) droptable,
sum(createdb) createdb,
sum(truncating) truncating
from statistic;'''
return Dao().mysql_query(self.inception_remote_backup_host,
self.inception_remote_backup_port,
self.inception_remote_backup_user,
self.inception_remote_backup_password,
'inception',
sql)
def statistic(self):
sql = '''
select
sum(deleting) deleting,
sum(inserting) inserting,
sum(updating) updating,
sum(selecting) selecting,
sum(altertable) altertable,
sum(renaming) renaming,
sum(createindex) createindex,
sum(dropindex) dropindex,
sum(addcolumn) addcolumn,
sum(dropcolumn) dropcolumn,
sum(changecolumn) changecolumn,
sum(alteroption) alteroption,
sum(alterconvert) alterconvert,
sum(createtable) createtable,
sum(droptable) droptable,
sum(createdb) createdb,
sum(truncating) truncating
from statistic;'''
try:
return Dao(
host=self.inception_remote_backup_host,
user=self.inception_remote_backup_user,
port=self.inception_remote_backup_port,
password=self.inception_remote_backup_password).mysql_query(
'inception', sql)
except Exception:
return {'column_list': [], 'rows': [], 'effect_row': 0}
def process(request):
instance_name = request.POST.get('instance_name')
command_type = request.POST.get('command_type')
base_sql = "select id, user, host, db, command, time, state, ifnull(info,'') as info from information_schema.processlist"
# 判断是RDS还是其他实例
if len(
AliyunRdsConfig.objects.filter(instance_name=instance_name,
is_enable=1)) > 0:
result = aliyun_process_status(request)
else:
if command_type == 'All':
sql = base_sql + ";"
elif command_type == 'Not Sleep':
sql = "{} where command<>'Sleep';".format(base_sql)
else:
sql = "{} where command= '{}';".format(base_sql, command_type)
processlist = Dao(instance_name=instance_name).mysql_query(
'information_schema', sql)
column_list = processlist['column_list']
rows = []
for row in processlist['rows']:
row_info = {}
for row_index, row_item in enumerate(row):
row_info[column_list[row_index]] = row_item
rows.append(row_info)
result = {'status': 0, 'msg': 'ok', 'data': rows}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def create_kill_session(request):
instance_name = request.POST.get('instance_name')
thread_ids = request.POST.get('ThreadIDs')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 判断是RDS还是其他实例
if len(
AliyunRdsConfig.objects.filter(instance_name=instance_name,
is_enable=1)) > 0:
result = aliyun_create_kill_session(request)
else:
thread_ids = thread_ids.replace('[', '').replace(']', '')
sql = "select concat('kill ', id, ';') from information_schema.processlist where id in ({});".format(
thread_ids)
all_kill_sql = Dao(instance_name=instance_name).mysql_query(
'information_schema', sql)
kill_sql = ''
for row in all_kill_sql['rows']:
kill_sql = kill_sql + row[0]
result['data'] = kill_sql
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def execute_skipinc_call_back(workflowId, instance_name, db_name, sql_content,
url):
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
try:
# 执行sql
t_start = time.time()
execute_result = Dao(instance_name=instance_name).mysql_execute(
db_name, sql_content)
t_end = time.time()
execute_time = "%5s" % "{:.4f}".format(t_end - t_start)
execute_result['execute_time'] = execute_time + 'sec'
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
if execute_result.get('Warning'):
workflowDetail.status = Const.workflowStatus['exception']
elif execute_result.get('Error'):
workflowDetail.status = Const.workflowStatus['exception']
else:
workflowDetail.status = Const.workflowStatus['finish']
workflowDetail.finish_time = timezone.now()
workflowDetail.execute_result = json.dumps(execute_result)
workflowDetail.is_manual = 1
workflowDetail.audit_remark = ''
workflowDetail.is_backup = '否'
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
except Exception:
logger.error(traceback.format_exc())
# 增加工单日志
# 获取audit_id
audit_id = Workflow.auditinfobyworkflow_id(
workflow_id=workflowId,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Workflow.add_workflow_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(
workflowDetail.status),
operator='',
operator_display='系统')
# 发送消息
send_msg(workflowDetail, url)
def users(request):
instance_name = request.POST.get('instance_name')
sql_get_user = '''select concat("\'", user, "\'", '@', "\'", host,"\'") as query from mysql.user;'''
dao = Dao(instance_name=instance_name)
db_users = dao.mysql_query('mysql', sql_get_user)['rows']
# 获取用户权限信息
data = []
for db_user in db_users:
user_info = {}
user_priv = dao.mysql_query('mysql', 'show grants for {};'.format(
db_user[0]))['rows']
user_info['user'] = db_user[0]
user_info['privileges'] = user_priv
data.append(user_info)
result = {'status': 0, 'msg': 'ok', 'data': data}
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def get_db_name_list(request):
instance_name = request.POST.get('instance_name')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
# 取出该实例的连接方式，为了后面连进去获取所有databases
db_list = Dao(instance_name=instance_name).get_alldb_by_cluster()
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
result['data'] = db_list
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
return HttpResponse(json.dumps(result), content_type='application/json')
def execute_skipinc_call_back(workflowId, clusterName, db_name, sql_content, url):
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
# 获取审核人
reviewMan = workflowDetail.review_man
# 获取实例连接信息
masterInfo = getMasterConnStr(clusterName)
try:
# 执行sql
t_start = time.time()
execute_result = Dao().mysql_execute(masterInfo['masterHost'], masterInfo['masterPort'],
masterInfo['masterUser'],
masterInfo['masterPassword'], db_name, sql_content)
t_end = time.time()
execute_time = "%5s" % "{:.4f}".format(t_end - t_start)
execute_result['execute_time'] = execute_time + 'sec'
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
if execute_result.get('Warning'):
workflowDetail.status = Const.workflowStatus['exception']
elif execute_result.get('Error'):
workflowDetail.status = Const.workflowStatus['exception']
else:
workflowDetail.status = Const.workflowStatus['finish']
workflowDetail.finish_time = timezone.now()
workflowDetail.execute_result = json.dumps(execute_result)
workflowDetail.is_manual = 1
workflowDetail.audit_remark = ''
workflowDetail.is_backup = '否'
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
except Exception as e:
logger.error(e)
# 发送消息
send_msg(workflowDetail, url)
def getTableNameList(request):
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
# 取出该实例从库的连接方式，为了后面连进去获取所有的表
tb_list = Dao(instance_name=instance_name).getAllTableByDb(db_name)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
result['data'] = tb_list
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
return HttpResponse(json.dumps(result), content_type='application/json')
def get_column_name_list(request):
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
tb_name = request.POST.get('tb_name')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
# 取出该实例的连接方式，为了后面连进去获取表的所有字段
col_list = Dao(instance_name=instance_name).get_all_columns_by_tb(db_name, tb_name)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
result['data'] = col_list
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
return HttpResponse(json.dumps(result), content_type='application/json')
def tablesapce(request):
instance_name = request.POST.get('instance_name')
# 判断是RDS还是其他实例
if len(
AliyunRdsConfig.objects.filter(instance_name=instance_name,
is_enable=1)) > 0:
if SysConfig().sys_config.get('aliyun_rds_manage'):
result = aliyun_sapce_status(request)
else:
raise Exception('未开启rds管理，无法查看rds数据！')
else:
sql = '''
SELECT
table_schema,
table_name,
engine,
TRUNCATE((data_length+index_length+data_free)/1024/1024,2) AS total_size,
table_rows,
TRUNCATE(data_length/1024/1024,2) AS data_size,
TRUNCATE(index_length/1024/1024,2) AS index_size,
TRUNCATE(data_free/1024/1024,2) AS data_free,
TRUNCATE(data_free/(data_length+index_length+data_free)*100,2) AS pct_free
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'test', 'sys')
ORDER BY total_size DESC
LIMIT 14;'''.format(instance_name)
table_space = Dao(instance_name=instance_name).mysql_query(
'information_schema', sql)
column_list = table_space['column_list']
rows = []
for row in table_space['rows']:
row_info = {}
for row_index, row_item in enumerate(row):
row_info[column_list[row_index]] = row_item
rows.append(row_info)
result = {'status': 0, 'msg': 'ok', 'data': rows}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def binlog_list(request):
instance_name = request.POST.get('instance_name')
binlog = Dao(instance_name=instance_name).mysql_query(
'information_schema', 'show binary logs;')
column_list = binlog['column_list']
rows = []
for row in binlog['rows']:
row_info = {}
for row_index, row_item in enumerate(row):
row_info[column_list[row_index]] = row_item
rows.append(row_info)
result = {'status': 0, 'msg': 'ok', 'data': rows}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def explain(request):
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if sql_content is None or instance_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = sql_content.strip()
if sql_content[-1] != ";":
result['status'] = 1
result['msg'] = 'SQL语句结尾没有以;结尾，请重新修改并提交！'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 过滤非查询的语句
if re.match(r"^explain", sql_content.lower()):
pass
else:
result['status'] = 1
result['msg'] = '仅支持explain开头的语句，请检查'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 按照分号截取第一条有效sql执行
sql_content = sql_content.strip().split(';')[0]
# 执行获取执行计划语句
sql_result = Dao(instance_name=instance_name).mysql_query(
str(db_name), sql_content)
result['data'] = sql_result
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def instanceusercreate(request):
if request.GET:
instances = [
instance.instance_name
for instance in user_instances(request.user, 'all')
]
exteral_ip = get_out_ip()
context = {'instances': instances, 'user_host': exteral_ip}
return render(request, 'instanceusercreate.html', context)
else:
user_name = request.POST.get('user', '')
password = request.POST.get('password', '')
privileges = request.POST.get('privilegs')
instance_name = request.POST.get('instance_name')
user_host = request.POST.get('user_host')
schema = request.POST.get('schema')
tables = request.POST.get('tables')
if user_name == '' and privileges == '' and user_host == '':
error = "info is not empty"
if password == '':
password = generate_random_password(32)
if schema is None or schema == '':
schema = '*'
data = {}
data["user"] = user_name
data['host'] = user_host
data['schema'] = schema
data['tables'] = tables
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
# 取出该实例实例的连接方式，为了后面连进去获取所有的表
tb_list = Dao(instance_name=instance_name).mysql_createuser(data)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
result['data'] = tb_list
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
return
def kill_session(request):
instance_name = request.POST.get('instance_name')
request_params = request.POST.get('request_params')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 判断是RDS还是其他实例
if len(
AliyunRdsConfig.objects.filter(instance_name=instance_name,
is_enable=1)) > 0:
result = aliyun_kill_session(request)
else:
kill_sql = request_params
Dao(instance_name=instance_name).mysql_execute('information_schema',
kill_sql)
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def kill_session(request):
instance_name = request.POST.get('instance_name')
request_params = request.POST.get('request_params')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 判断是RDS还是其他实例
if len(AliyunRdsConfig.objects.filter(instance_name=instance_name)) > 0:
if SysConfig().sys_config.get('aliyun_rds_manage') == 'true':
result = aliyun_kill_session(request)
else:
raise Exception('未开启rds管理，无法查看rds数据！')
else:
kill_sql = request_params
Dao(instance_name=instance_name).mysql_execute('information_schema',
kill_sql)
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def trxandlocks(request):
instance_name = request.POST.get('instance_name')
sql = '''
SELECT
rtrx.`trx_state` AS "等待的状态",
rtrx.`trx_started` AS "等待事务开始时间",
rtrx.`trx_wait_started` AS "等待事务等待开始时间",
lw.`requesting_trx_id` AS "等待事务ID",
rtrx.trx_mysql_thread_id AS "等待事务线程ID",
rtrx.`trx_query` AS "等待事务的sql",
CONCAT(rl.`lock_mode`, '-', rl.`lock_table`, '(', rl.`lock_index`, ')') AS "等待的表信息",
rl.`lock_id` AS "等待的锁id",
lw.`blocking_trx_id` AS "运行的事务id",
trx.trx_mysql_thread_id AS "运行的事务线程id",
CONCAT(l.`lock_mode`, '-', l.`lock_table`, '(', l.`lock_index`, ')') AS "运行的表信息",
l.lock_id AS "运行的锁id",
trx.`trx_state` AS "运行事务的状态",
trx.`trx_started` AS "运行事务的时间",
trx.`trx_wait_started` AS "运行事务的等待开始时间",
trx.`trx_query` AS "运行事务的sql"
FROM information_schema.`INNODB_LOCKS` rl
, information_schema.`INNODB_LOCKS` l
, information_schema.`INNODB_LOCK_WAITS` lw
, information_schema.`INNODB_TRX` rtrx
, information_schema.`INNODB_TRX` trx
WHERE rl.`lock_id` = lw.`requested_lock_id`
AND l.`lock_id` = lw.`blocking_lock_id`
AND lw.requesting_trx_id = rtrx.trx_id
AND lw.blocking_trx_id = trx.trx_id;'''
trxandlocks = Dao(instance_name=instance_name).mysql_query(
'information_schema', sql)
result = {'status': 0, 'msg': 'ok', 'data': trxandlocks}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def instance(request):
result = {'status': 0, 'msg': 'ok', 'data': []}
instance_id = request.POST.get('instance_id')
instance_name = Instance.objects.get(id=instance_id).instance_name
dao = Dao(instance_name=instance_name)
try:
conn = MySQLdb.connect(host=dao.host,
port=dao.port,
user=dao.user,
passwd=dao.password,
charset='utf8')
cursor = conn.cursor()
sql = "select 1"
cursor.execute(sql)
except Exception as e:
result['status'] = 1
result['msg'] = '无法连接实例{},\n{}'.format(instance_name, str(e))
else:
cursor.close()
conn.close()
# 返回结果
return HttpResponse(json.dumps(result), content_type='application/json')
def query(request):
instance_name = request.POST.get('instance_name')
sqlContent = request.POST.get('sql_content')
dbName = request.POST.get('db_name')
limit_num = request.POST.get('limit_num')
finalResult = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if sqlContent is None or dbName is None or instance_name is None or limit_num is None:
finalResult['status'] = 1
finalResult['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(finalResult), content_type='application/json')
sqlContent = sqlContent.strip()
if sqlContent[-1] != ";":
finalResult['status'] = 1
finalResult['msg'] = 'SQL语句结尾没有以;结尾，请重新修改并提交！'
return HttpResponse(json.dumps(finalResult), content_type='application/json')
# 获取用户信息
user = request.user
# 过滤注释语句和非查询的语句
sqlContent = ''.join(
map(lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
sqlContent.splitlines(1))).strip()
# 去除空行
sqlContent = re.sub('[\r\n\f]{2,}', '\n', sqlContent)
sql_list = sqlContent.strip().split('\n')
for sql in sql_list:
if re.match(r"^select|^show|^explain", sql.lower()):
break
else:
finalResult['status'] = 1
finalResult['msg'] = '仅支持^select|^show|^explain语法，请联系管理员！'
return HttpResponse(json.dumps(finalResult), content_type='application/json')
# 取出该实例的连接方式,查询只读账号,按照分号截取第一条有效sql执行
slave_info = Instance.objects.get(instance_name=instance_name)
sqlContent = sqlContent.strip().split(';')[0]
# 查询权限校验
priv_check_info = query_priv_check(user, instance_name, dbName, sqlContent, limit_num)
if priv_check_info['status'] == 0:
limit_num = priv_check_info['data']
else:
return HttpResponse(json.dumps(priv_check_info), content_type='application/json')
if re.match(r"^explain", sqlContent.lower()):
limit_num = 0
# 对查询sql增加limit限制
if re.match(r"^select", sqlContent.lower()):
if re.search(r"limit\s+(\d+)$", sqlContent.lower()) is None:
if re.search(r"limit\s+\d+\s*,\s*(\d+)$", sqlContent.lower()) is None:
sqlContent = sqlContent + ' limit ' + str(limit_num)
sqlContent = sqlContent + ';'
# 执行查询语句,统计执行时间
t_start = time.time()
sql_result = Dao(instance_name=instance_name).mysql_query(str(dbName), sqlContent, limit_num)
t_end = time.time()
cost_time = "%5s" % "{:.4f}".format(t_end - t_start)
sql_result['cost_time'] = cost_time
# 数据脱敏，同样需要检查配置，是否开启脱敏，语法树解析是否允许出错继续执行
t_start = time.time()
if SysConfig().sys_config.get('data_masking') == 'true':
# 仅对查询语句进行脱敏
if re.match(r"^select", sqlContent.lower()):
try:
masking_result = datamasking.data_masking(instance_name, dbName, sqlContent, sql_result)
except Exception:
if SysConfig().sys_config.get('query_check') == 'true':
finalResult['status'] = 1
finalResult['msg'] = '脱敏数据报错,请联系管理员'
return HttpResponse(json.dumps(finalResult), content_type='application/json')
else:
if masking_result['status'] != 0:
if SysConfig().sys_config.get('query_check') == 'true':
return HttpResponse(json.dumps(masking_result), content_type='application/json')
t_end = time.time()
masking_cost_time = "%5s" % "{:.4f}".format(t_end - t_start)
sql_result['masking_cost_time'] = masking_cost_time
finalResult['data'] = sql_result
# 成功的查询语句记录存入数据库
if sql_result.get('Error'):
pass
else:
query_log = QueryLog()
query_log.username = user.username
query_log.user_display = user.display
query_log.db_name = dbName
query_log.instance_name = instance_name
query_log.sqllog = sqlContent
if int(limit_num) == 0:
limit_num = int(sql_result['effect_row'])
else:
limit_num = min(int(limit_num), int(sql_result['effect_row']))
query_log.effect_row = limit_num
query_log.cost_time = cost_time
# 防止查询超时
try:
query_log.save()
except:
connection.close()
query_log.save()
# 返回查询结果
return HttpResponse(json.dumps(finalResult, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
from django.core import serializers
from django.db import transaction
import datetime
import time
from sql.utils.extend_json_encoder import ExtendJSONEncoder
from sql.utils.aes_decryptor import Prpcrypt
from sql.utils.dao import Dao
from .const import WorkflowDict
from .models import MasterConfig, SlaveConfig, QueryPrivilegesApply, QueryPrivileges, QueryLog, SqlGroup
from sql.utils.data_masking import Masking
from sql.utils.workflow import Workflow
from sql.utils.config import SysConfig
from sql.utils.group import user_slaves
dao = Dao()
prpCryptor = Prpcrypt()
datamasking = Masking()
workflowOb = Workflow()
# 查询权限申请用于工作流审核回调
def query_audit_call_back(workflow_id, workflow_status):
# 更新业务表状态
apply_info = QueryPrivilegesApply()
apply_info.apply_id = workflow_id
apply_info.status = workflow_status
apply_info.save(update_fields=['status'])
# 审核通过插入权限信息，批量插入，减少性能消耗
if workflow_status == WorkflowDict.workflow_status['audit_success']:
apply_queryset = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
class SqlTuning(object):
def __init__(self, instance_name, db_name, sqltext):
self.dao = Dao(instance_name=instance_name, flag=True)
self.db_name = db_name
self.sqltext = sqltext
self.sql_variable = '''
select
lower(variable_name),
variable_value
from performance_schema.global_variables
where upper(variable_name) in ('%s')
order by variable_name;''' % ('\',\''.join(SQLTuning.SYS_PARM_FILTER))
self.sql_optimizer_switch = '''
select variable_value
from performance_schema.global_variables
where upper(variable_name) = 'OPTIMIZER_SWITCH';
'''
self.sql_table_info = '''
select
table_name,
engine,
row_format as format,
table_rows,
avg_row_length as avg_row,
round((data_length + index_length) / 1024 / 1024, 2) as total_mb,
round((data_length) / 1024 / 1024, 2) as data_mb,
round((index_length) / 1024 / 1024, 2) as index_mb
from information_schema.tables
where table_schema = '%s' and table_name = '%s'
'''
self.sql_table_index = '''
select
table_name,
index_name,
non_unique,
seq_in_index,
column_name,
collation,
cardinality,
nullable,
index_type
from information_schema.statistics
where table_schema = '%s' and table_name = '%s'
order by 1, 3;
'''
@staticmethod
def __is_subselect(parsed):
if not parsed.is_group:
return False
for item in parsed.tokens:
if item.ttype is DML and item.value.upper() == 'SELECT':
return True
return False
def __extract_from_part(self, parsed):
from_seen = False
for item in parsed.tokens:
# print item.ttype,item.value
if from_seen:
if self.__is_subselect(item):
for x in self.__extract_from_part(item):
yield x
elif item.ttype is Keyword:
raise StopIteration
else:
yield item
elif item.ttype is Keyword and item.value.upper() == 'FROM':
from_seen = True
@staticmethod
def __extract_table_identifiers(token_stream):
for item in token_stream:
if isinstance(item, IdentifierList):
for identifier in item.get_identifiers():
yield identifier.get_real_name()
elif isinstance(item, Identifier):
yield item.get_real_name()
# It's a bug to check for Keyword here, but in the example
# above some tables names are identified as keywords...
elif item.ttype is Keyword:
yield item.value
def __extract_tables(self, p_sqltext):
stream = self.__extract_from_part(sqlparse.parse(p_sqltext)[0])
return list(self.__extract_table_identifiers(stream))
def basic_information(self):
return self.dao.mysql_query(sql="select @@version")
def sys_parameter(self):
# 获取mysql版本信息
version = self.basic_information()['rows'][0][0]
server_version = tuple(
[numeric_part(n) for n in version.split('.')[:2]])
if server_version < (5, 7):
sql = self.sql_variable.replace('performance_schema',
'information_schema')
else:
sql = self.sql_variable
return self.dao.mysql_query(sql=sql)
def optimizer_switch(self):
# 获取mysql版本信息
version = self.basic_information()['rows'][0][0]
server_version = tuple(
[numeric_part(n) for n in version.split('.')[:2]])
if server_version < (5, 7):
sql = self.sql_optimizer_switch.replace('performance_schema',
'information_schema')
else:
sql = self.sql_optimizer_switch
return self.dao.mysql_query(sql=sql)
def sqlplan(self):
plan = self.dao.mysql_query(self.db_name,
"explain extended " + self.sqltext)
optimizer_rewrite_sql = self.dao.mysql_query(sql="show warnings")
return plan, optimizer_rewrite_sql
# 获取关联表信息存在缺陷，只能获取到一张表
def object_statistics(self):
tableistructure = {'column_list': [], 'rows': []}
tableinfo = {'column_list': [], 'rows': []}
indexinfo = {'column_list': [], 'rows': []}
for index, table_name in enumerate(self.__extract_tables(
self.sqltext)):
tableistructure = self.dao.mysql_query(
db_name=self.db_name,
sql="show create table {};".format(
table_name.replace('`', '').lower()))
tableinfo = self.dao.mysql_query(
sql=self.sql_table_info %
(self.db_name, table_name.replace('`', '').lower()))
indexinfo = self.dao.mysql_query(
sql=self.sql_table_index %
(self.db_name, table_name.replace('`', '').lower()))
return tableistructure, tableinfo, indexinfo
def exec_sql(self):
result = {
"EXECUTE_TIME": 0,
"BEFORE_STATUS": {
'column_list': [],
'rows': []
},
"AFTER_STATUS": {
'column_list': [],
'rows': []
},
"SESSION_STATUS(DIFFERENT)": {
'column_list': ['status_name', 'before', 'after', 'diff'],
'rows': []
},
"PROFILING_DETAIL": {
'column_list': [],
'rows': []
},
"PROFILING_SUMMARY": {
'column_list': [],
'rows': []
}
}
sql_profiling = "select concat(upper(left(variable_name,1)),substring(lower(variable_name),2,(length(variable_name)-1))) var_name,variable_value var_value from performance_schema.session_status order by 1"
# 获取mysql版本信息
version = self.basic_information()['rows'][0][0]
server_version = tuple(
[numeric_part(n) for n in version.split('.')[:2]])
if server_version < (5, 7):
sql = sql_profiling.replace('performance_schema',
'information_schema')
else:
sql = sql_profiling
self.dao.mysql_query(sql="set profiling=1")
records = self.dao.mysql_query(
sql=
"select ifnull(max(query_id),0) from INFORMATION_SCHEMA.PROFILING")
query_id = records['rows'][0][0] + 3 # skip next sql
# 获取执行前信息
result['BEFORE_STATUS'] = self.dao.mysql_query(sql=sql)
# 执行查询语句,统计执行时间
t_start = time.time()
self.dao.mysql_query(sql=self.sqltext)
t_end = time.time()
cost_time = "%5s" % "{:.4f}".format(t_end - t_start)
result['EXECUTE_TIME'] = cost_time
# 获取执行后信息
result['AFTER_STATUS'] = self.dao.mysql_query(sql=sql)
# 获取PROFILING_DETAIL信息
result['PROFILING_DETAIL'] = self.dao.mysql_query(
sql=
"select STATE,DURATION,CPU_USER,CPU_SYSTEM,BLOCK_OPS_IN,BLOCK_OPS_OUT ,MESSAGES_SENT ,MESSAGES_RECEIVED ,PAGE_FAULTS_MAJOR ,PAGE_FAULTS_MINOR ,SWAPS from INFORMATION_SCHEMA.PROFILING where query_id="
+ str(query_id) + " order by seq")
result['PROFILING_SUMMARY'] = self.dao.mysql_query(
sql=
"SELECT STATE,SUM(DURATION) AS Total_R,ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID="
+ str(query_id) +
"),2) AS Pct_R,COUNT(*) AS Calls,SUM(DURATION)/COUNT(*) AS R_Call FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID="
+ str(query_id) + " GROUP BY STATE ORDER BY Total_R DESC")
# 处理执行前后对比信息
before_status_rows = [
list(item) for item in result['BEFORE_STATUS']['rows']
]
after_status_rows = [
list(item) for item in result['AFTER_STATUS']['rows']
]
for index, item in enumerate(before_status_rows):
if before_status_rows[index][1] != after_status_rows[index][1]:
before_status_rows[index].append(after_status_rows[index][1])
before_status_rows[index].append(
str(
float(after_status_rows[index][1]) -
float(before_status_rows[index][1])))
diff_rows = [item for item in before_status_rows if len(item) == 4]
result['SESSION_STATUS(DIFFERENT)']['rows'] = diff_rows
return result
def query(request):
instance_name = request.POST.get('instance_name')
sql_content = request.POST.get('sql_content')
db_name = request.POST.get('db_name')
limit_num = request.POST.get('limit_num')
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if sql_content is None or db_name is None or instance_name is None or limit_num is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = sql_content.strip()
# 获取用户信息
user = request.user
# 过滤注释语句和非查询的语句
sql_content = ''.join(
map(
lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub(
'', x, count=1), sql_content.splitlines(1))).strip()
# 去除空行
sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_list = sql_content.strip().split('\n')
for sql in sql_list:
if re.match(r"^select|^show|^explain", sql.lower()):
break
else:
result['status'] = 1
result['msg'] = '仅支持^select|^show|^explain语法，请联系管理员！'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 按照分号截取第一条有效sql执行
sql_content = sql_content.strip().split(';')[0]
try:
# 查询权限校验
priv_check_info = query_priv_check(user, instance_name, db_name,
sql_content, limit_num)
if priv_check_info['status'] == 0:
limit_num = priv_check_info['data']['limit_num']
priv_check = priv_check_info['data']['priv_check']
else:
return HttpResponse(json.dumps(priv_check_info),
content_type='application/json')
if re.match(r"^explain", sql_content.lower()):
limit_num = 0
# 对查询sql增加limit限制
if re.match(r"^select", sql_content.lower()):
if re.search(r"limit\s+(\d+)$", sql_content.lower()) is None:
if re.search(r"limit\s+\d+\s*,\s*(\d+)$",
sql_content.lower()) is None:
sql_content = sql_content + ' limit ' + str(limit_num)
sql_content = sql_content + ';'
# 执行查询语句,统计执行时间
t_start = time.time()
sql_result = Dao(instance_name=instance_name).mysql_query(
str(db_name), sql_content, limit_num)
t_end = time.time()
cost_time = "%5s" % "{:.4f}".format(t_end - t_start)
sql_result['cost_time'] = cost_time
# 数据脱敏，同样需要检查配置，是否开启脱敏，语法树解析是否允许出错继续执行
hit_rule = 0 if re.match(r"^select", sql_content.lower(
)) else 2 # 查询是否命中脱敏规则，0, '未知', 1, '命中', 2, '未命中'
masking = 2 # 查询结果是否正常脱敏，1, '是', 2, '否'
t_start = time.time()
# 仅对查询语句进行脱敏
if SysConfig().sys_config.get('data_masking') and re.match(
r"^select", sql_content.lower()):
try:
masking_result = datamasking.data_masking(
instance_name, db_name, sql_content, sql_result)
if masking_result['status'] != 0 and SysConfig(
).sys_config.get('query_check'):
return HttpResponse(json.dumps(masking_result),
content_type='application/json')
else:
hit_rule = masking_result['data']['hit_rule']
masking = 1 if hit_rule == 1 else 2
except Exception:
logger.error(traceback.format_exc())
hit_rule = 0
masking = 2
if SysConfig().sys_config.get('query_check'):
result['status'] = 1
result['msg'] = '脱敏数据报错,请联系管理员'
return HttpResponse(json.dumps(result),
content_type='application/json')
t_end = time.time()
masking_cost_time = "%5s" % "{:.4f}".format(t_end - t_start)
sql_result['masking_cost_time'] = masking_cost_time
result['data'] = sql_result
# 成功的查询语句记录存入数据库
if sql_result.get('Error'):
pass
else:
query_log = QueryLog()
query_log.username = user.username
query_log.user_display = user.display
query_log.db_name = db_name
query_log.instance_name = instance_name
query_log.sqllog = sql_content
if int(limit_num) == 0:
limit_num = int(sql_result['effect_row'])
else:
limit_num = min(int(limit_num), int(sql_result['effect_row']))
query_log.effect_row = limit_num
query_log.cost_time = cost_time
query_log.priv_check = priv_check
query_log.hit_rule = hit_rule
query_log.masking = masking
# 防止查询超时
try:
query_log.save()
except:
connection.close()
query_log.save()
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = str(e)
# 返回查询结果
try:
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
except Exception:
return HttpResponse(json.dumps(result,
default=str,
bigint_as_string=True,
encoding='latin1'),
content_type='application/json')
