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_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})