def db_sql_dumps(request):
if request.method == "POST":#执行原生SQL
try:
dbServer = DataBase_Server_Config.objects.get(id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
result = mysql.execute(sql=request.POST.get('sql'),num=1000)
if isinstance(result,str):
return JsonResponse({'msg':"不支持导出功能","code":500,'data':result,'tag':3})
else:
if result[0] == 0:return JsonResponse({'msg':"不支持导出功能","code":200,'data':"SQL执行成功",'tag':3})
file_name = "query_result.csv"
with open(file_name,"w") as csvfile:
writer = csv.writer(csvfile,dialect='excel')
#先写入columns_name
writer.writerow(result[2])
#写入多行用writerows
for ds in result[1]:
writer.writerows([list(ds)])
response = StreamingHttpResponse(base.file_iterator(file_name))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment; filename="{file_name}'.format(file_name=file_name)
return response
def db_ops(request):
if request.method == "GET":
dataBaseList = DataBase_Server_Config.objects.all()
serviceList = Service_Assets.objects.all()
projectList = Project_Assets.objects.all()
return render(request,'database/db_ops.html',{"user":request.user,"dataBaseList":dataBaseList,"serviceList":serviceList,"projectList":projectList})
elif request.method == "POST" and request.POST.get('model') == 'binlog':#通过获取数据库的binlog版本
try:
dbServer = DataBase_Server_Config.objects.get(id=request.POST.get('ops_db'))
except:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
result = mysql.queryAll(sql='show binary logs;')
binLogList = []
if isinstance(result,tuple):
for ds in result[1]:
binLogList.append(ds[0])
return JsonResponse({'msg':"数据查询成功","code":200,'data':binLogList,'count':0})
elif request.method == "POST" and request.POST.get('model') == 'querydb':#根据业务类型查询数据库
dataList = []
dbSerlist = DataBase_Server_Config.objects.filter(db_env=request.POST.get('db_env'),db_service=request.POST.get('db_service'))
for ds in dbSerlist:
data = dict()
data['id'] = ds.id
data['db_name'] = ds.db_name
data['db_host'] = ds.db_host
data['db_mark'] = ds.db_mark
dataList.append(data)
return JsonResponse({'msg':"数据查询成功","code":200,'data':dataList})
elif request.method == "POST" and request.POST.get('opsTag') == '1':#通过binlog获取DML
sqlList = []
try:
dbServer = DataBase_Server_Config.objects.get(id=int(request.POST.get('dbId')))
except Exception, ex:
sqlList.append(ex)
dbServer = None
if dbServer:
conn_setting = {'host': dbServer.db_host, 'port': dbServer.db_port, 'user': dbServer.db_user, 'passwd': dbServer.db_passwd, 'charset': 'utf8'}
#flashback=True获取DML回滚语句
binlog2sql = Binlog2sql(connection_settings=conn_setting,
back_interval=1.0, only_schemas=dbServer.db_name,
end_file='', end_pos=0, start_pos=4,
flashback=True,only_tables='',
no_pk=False, only_dml=True,stop_never=False,
sql_type=['INSERT', 'UPDATE', 'DELETE'],
start_file=request.POST.get('binlog'),
start_time=request.POST.get('startime'),
stop_time=request.POST.get('endtime'),)
sqlList = binlog2sql.process_binlog()
return JsonResponse({'msg':"获取binlog数据成功","code":200,'data':sqlList,'tag':1})
def db_status(request, id,format=None):
try:
dbServer = DataBase_Server_Config.objects.get(id=id)
except DataBase_Server_Config.DoesNotExist:
return Response(status=status.HTTP_404_NOT_FOUND)
if request.method == 'POST':
MySQL_STATUS = {}
MYSQL = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
STATUS = MYSQL.getStatus()
GLOBAL = MYSQL.getGlobalStatus()
MySQL_STATUS['base'] = STATUS[0] + GLOBAL
MySQL_STATUS['pxc'] = STATUS[1]
MySQL_STATUS['master'] = MYSQL.getMasterStatus()
MySQL_STATUS['slave'] = MYSQL.getSlaveStatus()
return JsonResponse({"code":200,"msg":"success","data":MySQL_STATUS})
def db_org(request, id,format=None):
try:
dbServer = DataBase_Server_Config.objects.get(id=id)
except DataBase_Server_Config.DoesNotExist:
return Response(status=status.HTTP_404_NOT_FOUND)
if request.method == 'POST':
MYSQL = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
STATUS = MYSQL.getStatus()
pxcServerList = []
title = dbServer.db_host + dbServer.db_mark
if dbServer.db_mode == 1:name = '单例模式'
elif dbServer.db_mode == 2:name = '主从模式'
else:
name = 'PXC模式'
title = dbServer.db_mark
MYSQLORG = {
'name': name,
'title': title,
'className': 'product-dept',
'children': []
}
if dbServer.db_mode == 3:
for ds in STATUS[1]:
if ds.get('name') == 'Wsrep_incoming_addresses':pxcServerList = ds.get('value').split(',')
slaveData = {}
for ds in MYSQL.getMasterStatus():
if ds.get('name') == 'Slave':slaveData[dbServer.db_host+':'+str(dbServer.db_port)] = ds.get('value')
for s in pxcServerList:
data = {}
host = s.split(':')[0]
port = s.split(':')[1]
data['name'] = host
data['title'] = port
data['children'] = []
if slaveData.has_key(s):
data['name'] = 'master'
data['title'] = host+':'+port
count = 1
for d in slaveData.get(s):
x = {}
host = d.split(':')[0]
port = d.split(':')[1]
x['name'] = 'slave-' + str(count)
x['title'] = host+':'+port
count = count + 1
data['children'].append(x)
MYSQLORG['children'].append(data)
elif dbServer.db_mode == 2:
count = 1
for m in MYSQL.getSlaveStatus():
if m.get('name') == 'Master_Host':
MYSQLORG['children'].append({"name":'Master-' + str(count),"title":m.get('value')})
count = count + 1
for ds in MYSQL.getMasterStatus():
if ds.get('name') == 'Slave':
count = 1
for s in ds.get('value'):
x = {}
host = s.split(':')[0]
port = s.split(':')[1]
x['name'] = 'slave-' + str(count)
x['title'] = host+':'+port
count = count + 1
MYSQLORG['children'].append(x)
return JsonResponse({"code":200,"msg":"success","data":MYSQLORG})
passwd=dbServer.db_passwd, dbname=dbServer.db_name,
sql=request.POST.get('sql'),port=dbServer.db_port)
if status == 0:
return JsonResponse({'msg':"获取SQL优化数据成功","code":200,'data':result,'tag':2})
else:
return JsonResponse({'msg':"获取SQL优化数据失败","code":500,'data':result,'tag':2})
else:return JsonResponse({'msg':"获取SQL优化数据失败","code":500,'data':[],'tag':2})
elif request.method == "POST" and request.POST.get('opsTag') == '3':#执行DQL
if re.match(r"^(\s*)?select(\S+)?(.*)", request.POST.get('sql').lower()):
try:
dbServer = DataBase_Server_Config.objects.get(id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
result = mysql.queryMany(sql=request.POST.get('sql'),num=1000)
if isinstance(result,str):
recordSQL.delay(exe_user=str(request.user),exe_db=dbServer,exe_sql=request.POST.get('sql'),exec_status=0,exe_result=str)
return JsonResponse({'msg':"数据查询失败","code":500,'data':result,'tag':3})
recordSQL.delay(exe_user=str(request.user),exe_db=dbServer,exe_sql=request.POST.get('sql'),exec_status=1,exe_result=None)
return JsonResponse({'msg':"数据查询成功","code":200,'data':{"colName":result[2],"dataList":result[1]},'count':result[0],'tag':3})
else:return JsonResponse({'msg':"数据查询失败","code":500,'data':"不是DQL类型语句",'tag':3})
elif request.method == "POST" and request.POST.get('opsTag') == '4' and request.user.is_superuser:#执行原生SQL
try:
dbServer = DataBase_Server_Config.objects.get(id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,port=dbServer.db_port,user=dbServer.db_user,passwd=dbServer.db_passwd,dbName=dbServer.db_name)
'tag': 2
})
elif request.method == "POST" and request.POST.get(
'opsTag') == '3': #执行DQL
if re.match(r"^(\s*)?select(\S+)?(.*)",
request.POST.get('sql').lower()):
try:
dbServer = DataBase_Server_Config.objects.get(
id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,
port=dbServer.db_port,
user=dbServer.db_user,
passwd=dbServer.db_passwd,
dbName=dbServer.db_name)
result = mysql.queryMany(sql=request.POST.get('sql'), num=1000)
if isinstance(result, str):
recordSQL.delay(exe_user=str(request.user),
exe_db=dbServer,
exe_sql=request.POST.get('sql'),
exec_status=0,
exe_result=str)
return JsonResponse({
'msg': "数据查询失败",
"code": 500,
'data': result,
'tag': 3
})
def db_ops(request):
if request.method == "GET":
dataBaseList = DataBase_Server_Config.objects.all()
serviceList = Service_Assets.objects.all()
return render(
request, 'database/db_ops.html', {
"user": request.user,
"dataBaseList": dataBaseList,
"serviceList": serviceList
})
elif request.method == "POST" and request.POST.get(
'model') == 'binlog': #通过获取数据库的binlog版本
try:
dbServer = DataBase_Server_Config.objects.get(
id=request.POST.get('ops_db'))
except:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,
port=dbServer.db_port,
user=dbServer.db_user,
passwd=dbServer.db_passwd,
dbName=dbServer.db_name)
result = mysql.queryAll(sql='show binary logs;')
binLogList = []
if isinstance(result, tuple):
for ds in result[1]:
binLogList.append(ds[0])
return JsonResponse({
'msg': "数据查询成功",
"code": 200,
'data': binLogList,
'count': 0
})
elif request.method == "POST" and request.POST.get(
'model') == 'querydb': #根据业务类型查询数据库
dataList = []
dbSerlist = DataBase_Server_Config.objects.filter(
db_env=request.POST.get('db_env'),
db_service=request.POST.get('db_service'))
for ds in dbSerlist:
data = dict()
data['id'] = ds.id
data['db_name'] = ds.db_name
data['db_host'] = ds.db_host
dataList.append(data)
return JsonResponse({'msg': "数据查询成功", "code": 200, 'data': dataList})
elif request.method == "POST" and request.POST.get(
'opsTag') == '1': #通过binlog获取DML
sqlList = []
try:
dbServer = DataBase_Server_Config.objects.get(
id=int(request.POST.get('dbId')))
except Exception, ex:
sqlList.append(ex)
dbServer = None
if dbServer:
conn_setting = {
'host': dbServer.db_host,
'port': dbServer.db_port,
'user': dbServer.db_user,
'passwd': dbServer.db_passwd,
'charset': 'utf8'
}
#flashback=True获取DML回滚语句
binlog2sql = Binlog2sql(
connection_settings=conn_setting,
back_interval=1.0,
only_schemas=dbServer.db_name,
end_file='',
end_pos=0,
start_pos=4,
flashback=True,
only_tables='',
no_pk=False,
only_dml=True,
stop_never=False,
sql_type=['INSERT', 'UPDATE', 'DELETE'],
start_file=request.POST.get('binlog'),
start_time=request.POST.get('startime'),
stop_time=request.POST.get('endtime'),
)
sqlList = binlog2sql.process_binlog()
return JsonResponse({
'msg': "获取binlog数据成功",
"code": 200,
'data': sqlList,
'tag': 1
})
'tag': 2
})
elif request.method == "POST" and request.POST.get(
'opsTag') == '3': #执行DQL
if re.match(r"^(\s*)?select(\S+)?(.*)",
request.POST.get('sql').lower()):
try:
dbServer = DataBase_Server_Config.objects.get(
id=int(request.POST.get('dbId')))
except Exception, ex:
dbServer = None
if dbServer:
mysql = MySQLPool(host=dbServer.db_host,
port=dbServer.db_port,
user=dbServer.db_user,
passwd=dbServer.db_passwd,
dbName=dbServer.db_name)
result = mysql.queryMany(sql=request.POST.get('sql'), num=1000)
if isinstance(result, str):
recordSQL.delay(exe_user=str(request.user),
exe_db=dbServer,
exe_sql=request.POST.get('sql'),
exec_status=0,
exe_result=str)
return JsonResponse({
'msg': "数据查询失败",
"code": 500,
'data': result,
'tag': 3
})
def db_org(request, id, format=None):
try:
dbServer = DataBase_Server_Config.objects.get(id=id)
except DataBase_Server_Config.DoesNotExist:
return Response(status=status.HTTP_404_NOT_FOUND)
if request.method == 'POST':
MYSQL = MySQLPool(host=dbServer.db_host,
port=dbServer.db_port,
user=dbServer.db_user,
passwd=dbServer.db_passwd,
dbName=dbServer.db_name)
STATUS = MYSQL.getStatus()
pxcServerList = []
title = dbServer.db_host + dbServer.db_mark
if dbServer.db_mode == 1: name = '单例模式'
elif dbServer.db_mode == 2: name = '主从模式'
else:
name = 'PXC模式'
title = dbServer.db_mark
MYSQLORG = {
'name': name,
'title': title,
'className': 'product-dept',
'children': []
}
if dbServer.db_mode == 3:
for ds in STATUS[1]:
if ds.get('name') == 'Wsrep_incoming_addresses':
pxcServerList = ds.get('value').split(',')
slaveData = {}
for ds in MYSQL.getMasterStatus():
if ds.get('name') == 'Slave':
slaveData[dbServer.db_host + ':' +
str(dbServer.db_port)] = ds.get('value')
for s in pxcServerList:
data = {}
host = s.split(':')[0]
port = s.split(':')[1]
data['name'] = host
data['title'] = port
data['children'] = []
if slaveData.has_key(s):
data['name'] = 'master'
data['title'] = host + ':' + port
count = 1
for d in slaveData.get(s):
x = {}
host = d.split(':')[0]
port = d.split(':')[1]
x['name'] = 'slave-' + str(count)
x['title'] = host + ':' + port
count = count + 1
data['children'].append(x)
MYSQLORG['children'].append(data)
elif dbServer.db_mode == 2:
count = 1
for m in MYSQL.getSlaveStatus():
if m.get('name') == 'Master_Host':
MYSQLORG['children'].append({
"name": 'Master-' + str(count),
"title": m.get('value')
})
count = count + 1
for ds in MYSQL.getMasterStatus():
if ds.get('name') == 'Slave':
count = 1
for s in ds.get('value'):
x = {}
host = s.split(':')[0]
port = s.split(':')[1]
x['name'] = 'slave-' + str(count)
x['title'] = host + ':' + port
count = count + 1
MYSQLORG['children'].append(x)
return JsonResponse({"code": 200, "msg": "success", "data": MYSQLORG})