def save(self, *args, **kwargs):
pc = Prpcrypt() # 初始化
if self.password:
if self.id:
old_password = Instance.objects.get(id=self.id).password
else:
old_password = ''
# 密码有变动才再次加密保存
self.password = pc.encrypt(self.password) if old_password != self.password else self.password
super(Instance, self).save(*args, **kwargs)
def __init__(self):
prpCryptor = Prpcrypt()
try:
auth = AliyunAccessKey.objects.filter(is_enable=1)
ak = prpCryptor.decrypt(auth[0].ak)
secret = prpCryptor.decrypt(auth[0].secret)
except Exception:
logger.error(traceback.format_exc())
logger.error('没有找到有效的ak信息！')
else:
self.clt = client.AcsClient(ak=ak, secret=secret)
def save(self, *args, **kwargs):
pc = Prpcrypt() # 初始化
if self.id:
old_info = AliyunAccessKey.objects.get(id=self.id)
old_ak = old_info.ak
old_secret = old_info.secret
else:
old_ak = ''
old_secret = ''
# 加密信息有变动才再次加密保存
self.ak = pc.encrypt(self.ak) if old_ak != self.ak else self.ak
self.secret = pc.encrypt(self.secret) if old_secret != self.secret else self.secret
super(AliyunAccessKey, self).save(*args, **kwargs)
def __init__(self, instance_name=None, flag=False, **kwargs):
self.flag = flag
if instance_name:
try:
instance_info = Instance.objects.get(
instance_name=instance_name)
self.host = instance_info.host
self.port = int(instance_info.port)
self.user = instance_info.user
self.password = Prpcrypt().decrypt(instance_info.password)
if self.flag:
self.conn = MySQLdb.connect(host=self.host,
port=self.port,
user=self.user,
passwd=self.password,
charset='utf8')
self.cursor = self.conn.cursor()
except Exception:
raise Exception('找不到对应的实例配置信息，请配置')
else:
self.host = kwargs.get('host', '')
self.port = kwargs.get('port', 0)
self.user = kwargs.get('user', '')
self.password = kwargs.get('password', '')
def __init__(self, instance_name=None):
self.sys_config = SysConfig().sys_config
self.inception_host = self.sys_config.get('inception_host')
self.inception_port = int(
self.sys_config.get('inception_port')) if self.sys_config.get(
'inception_port') else 6669
self.inception_remote_backup_host = self.sys_config.get(
'inception_remote_backup_host')
self.inception_remote_backup_port = int(
self.sys_config.get('inception_remote_backup_port')
) if self.sys_config.get('inception_remote_backup_port') else 3306
self.inception_remote_backup_user = self.sys_config.get(
'inception_remote_backup_user')
self.inception_remote_backup_password = self.sys_config.get(
'inception_remote_backup_password')
if instance_name:
try:
instance_info = Instance.objects.get(
instance_name=instance_name)
self.host = instance_info.host
self.port = int(instance_info.port)
self.user = instance_info.user
self.password = Prpcrypt().decrypt(instance_info.password)
except Exception:
raise Exception('找不到对应的实例配置信息，请配置')
def soar(request):
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
sql = request.POST.get('sql')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if not (instance_name and db_name and sql):
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
user_instances(request.user, 'all').get(instance_name=instance_name)
except Exception:
result['status'] = 1
result['msg'] = '你所在组未关联该实例'
return HttpResponse(json.dumps(result),
content_type='application/json')
sql = sql.strip().replace('"', '\\"').replace('`', '\`').replace('\n', ' ')
# 目标实例的连接信息
instance_info = Instance.objects.get(instance_name=instance_name)
online_dsn = "{user}:{pwd}@{host}:{port}/{db}".format(
user=instance_info.user,
pwd=Prpcrypt().decrypt(instance_info.password),
host=instance_info.host,
port=instance_info.port,
db=db_name)
# 获取测试实例的连接信息和soar程序路径
soar_cfg = Soar()
test_dsn = soar_cfg.soar_test_dsn
soar_path = soar_cfg.soar_path
if not (soar_path and test_dsn):
result['status'] = 1
result['msg'] = '请配置soar_path和test_dsn！'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 提交给soar获取分析报告
try:
p = subprocess.Popen(
soar_path + ' -allow-online-as-test=false -report-type=markdown' +
' -query "{}" -online-dsn "{}" -test-dsn "{}" '.format(
sql.strip(), online_dsn, test_dsn),
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
shell=True,
universal_newlines=True)
stdout, stderr = p.communicate()
result['data'] = stdout
except Exception:
logger.error(traceback.format_exc())
result['data'] = 'soar运行报错，请检查相关日志'
return HttpResponse(json.dumps(result), content_type='application/json')
def mirage(request):
"""迁移加密的Instance数据，保留一定版本后删除"""
try:
pc = Prpcrypt()
mg_user = Migrator(app="sql", model="Instance", field="user")
mg_password = Migrator(app="sql", model="Instance", field="password")
# 还原密码
for ins in Instance.objects.all():
# 忽略解密错误的数据(本身为异常数据)
try:
Instance(pk=ins.pk, password=pc.decrypt(ins.password)).save(update_fields=['password'])
except:
pass
# 使用django-mirage-field重新加密
mg_user.encrypt()
mg_password.encrypt()
return JsonResponse({"msg": "ok"})
except Exception as msg:
return JsonResponse({"msg": f"{msg}"})
def sqladvisor(request):
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
verbose = request.POST.get('verbose')
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()
try:
user_instances(request.user, 'all').get(instance_name=instance_name)
except Exception:
result['status'] = 1
result['msg'] = '你所在组未关联该实例！'
return HttpResponse(json.dumps(result),
content_type='application/json')
if verbose is None or verbose == '':
verbose = 1
# 取出实例的连接信息
instance_info = Instance.objects.get(instance_name=instance_name)
# 提交给sqladvisor获取审核结果
sqladvisor_path = SysConfig().sys_config.get('sqladvisor')
sql_content = sql_content.strip().replace('"',
'\\"').replace('`', '').replace(
'\n', ' ')
try:
p = subprocess.Popen(
sqladvisor_path +
' -h "%s" -P "%s" -u "%s" -p "%s\" -d "%s" -v %s -q "%s"' %
(str(instance_info.host), str(
instance_info.port), str(instance_info.user),
str(Prpcrypt().decrypt(instance_info.password), ), str(db_name),
verbose, sql_content),
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
shell=True,
universal_newlines=True)
stdout, stderr = p.communicate()
result['data'] = stdout
except Exception:
logger.error(traceback.format_exc())
result['data'] = 'sqladvisor运行报错，请检查日志'
return HttpResponse(json.dumps(result), content_type='application/json')
def raw_secret(self):
""" 返回明文secret str """
pc = Prpcrypt() # 初始化
return pc.decrypt(self.secret)
def raw_ak(self):
""" 返回明文ak str """
pc = Prpcrypt() # 初始化
return pc.decrypt(self.ak)
def raw_password(self):
""" 返回明文密码 str """
pc = Prpcrypt() # 初始化
return pc.decrypt(self.password)
def save(self, *args, **kwargs):
pc = Prpcrypt() # 初始化
self.ak = pc.encrypt(self.ak)
self.secret = pc.encrypt(self.secret)
super(AliyunAccessKey, self).save(*args, **kwargs)
def save(self, *args, **kwargs):
pc = Prpcrypt() # 初始化
if self.password:
self.password = pc.encrypt(self.password)
super(Instance, self).save(*args, **kwargs)
def binlog2sql(request):
instance_name = request.POST.get('instance_name')
instance = Instance.objects.get(instance_name=instance_name)
conn_setting = {
'host': instance.host,
'port': int(instance.port),
'user': instance.user,
'passwd': Prpcrypt().decrypt(instance.password),
'charset': 'utf8'
}
no_pk = True if request.POST.get('no_pk') == 'true' else False
flashback = True if request.POST.get('flashback') == 'true' else False
start_file = request.POST.get('start_file')
start_pos = request.POST.get('start_pos') if request.POST.get(
'start_pos') == '' else int(request.POST.get('start_pos'))
end_file = request.POST.get('end_file')
end_pos = request.POST.get('end_pos') if request.POST.get(
'end_pos') == '' else int(request.POST.get('end_pos'))
stop_time = request.POST.get('stop_time')
start_time = request.POST.get('start_time')
only_schemas = request.POST.getlist('only_schemas')
only_tables = request.POST.getlist('only_tables[]')
only_dml = True if request.POST.get('only_dml') == 'true' else False
sql_type = ['INSERT', 'UPDATE', 'DELETE'] if request.POST.getlist(
'sql_type[]') == [] else request.POST.getlist('sql_type[]')
# flashback=True获取DML回滚语句
result = {'status': 0, 'msg': 'ok', 'data': ''}
try:
binlog2sql = Binlog2sql(connection_settings=conn_setting,
start_file=start_file,
start_pos=start_pos,
end_file=end_file,
end_pos=end_pos,
start_time=start_time,
stop_time=stop_time,
only_schemas=' '.join(only_schemas),
only_tables=' '.join(only_tables),
no_pk=no_pk,
flashback=flashback,
stop_never=False,
back_interval=1.0,
only_dml=only_dml,
sql_type=sql_type)
sql_list = binlog2sql.process_binlog()
rows = []
for row in sql_list:
row_info = {}
try:
row_info['sql'] = row.split('; #')[0] + ";"
row_info['binlog_info'] = row.split('; #')[1].rstrip('\"')
except Exception:
logger.error(traceback.format_exc())
row_info['sql'] = row
row_info['binlog_info'] = None
rows.append(row_info)
result['data'] = rows[0:5000]
# 保存文件
if len(sql_list) > 0:
timestamp = int(time.time())
path = os.path.join(settings.BASE_DIR, 'downloads/binlog2sql/')
if flashback:
with open(
os.path.join(path,
'rollback_{}.sql'.format(timestamp)),
'w') as f:
for sql in sql_list:
f.write(sql + '\n')
else:
with open(os.path.join(path, 'do_{}.sql'.format(timestamp)),
'w') as f:
for sql in sql_list:
f.write(sql + '\n')
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = str(e)
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def schemasync(request):
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
target_instance_name = request.POST.get('target_instance_name')
target_db_name = request.POST.get('target_db_name')
sync_auto_inc = '--sync-auto-inc' if request.POST.get(
'sync_auto_inc') == 'true' else ''
sync_comments = '--sync-comments' if request.POST.get(
'sync_comments') == 'true' else ''
result = {'status': 0, 'msg': 'ok', 'data': []}
# diff 选项
options = sync_auto_inc + ' ' + sync_comments
# 循环对比全部数据库
if db_name == 'all' or target_db_name == 'all':
db_name = '*'
target_db_name = '*'
# 取出该实例的连接方式
instance_info = Instance.objects.get(instance_name=instance_name)
target_instance_info = Instance.objects.get(
instance_name=target_instance_name)
# 获取对比结果文件
path = SysConfig().sys_config.get('schemasync', '')
timestamp = int(time.time())
output_directory = os.path.join(settings.BASE_DIR, 'downloads/schemasync/')
command = path + ' %s --output-directory=%s --tag=%s \
mysql://%s:%s@%s:%d/%s mysql://%s:%s@%s:%d/%s' % (
options, output_directory, timestamp, instance_info.user,
Prpcrypt().decrypt(instance_info.password), instance_info.host,
instance_info.port, db_name, target_instance_info.user,
Prpcrypt().decrypt(target_instance_info.password),
target_instance_info.host, target_instance_info.port, target_db_name)
diff = subprocess.Popen(command,
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
shell=True,
universal_newlines=True)
diff_stdout, diff_stderr = diff.communicate()
# 非全部数据库对比可以读取对比结果并在前端展示
if db_name != '*':
date = time.strftime("%Y%m%d", time.localtime())
patch_sql_file = '%s%s_%s.%s.patch.sql' % (
output_directory, target_db_name, timestamp, date)
revert_sql_file = '%s%s_%s.%s.revert.sql' % (
output_directory, target_db_name, timestamp, date)
cat_patch_sql = subprocess.Popen(['cat', patch_sql_file],
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
universal_newlines=True)
cat_revert_sql = subprocess.Popen(['cat', revert_sql_file],
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
universal_newlines=True)
patch_stdout, patch_stderr = cat_patch_sql.communicate()
revert_stdout, revert_stderr = cat_revert_sql.communicate()
result['data'] = {
'diff_stdout': diff_stdout,
'patch_stdout': patch_stdout,
'revert_stdout': revert_stdout
}
else:
result['data'] = {
'diff_stdout': diff_stdout,
'patch_stdout': '',
'revert_stdout': ''
}
# 删除对比文件
# subprocess.call(['rm', '-rf', patch_sql_file, revert_sql_file, 'schemasync.log'])
return HttpResponse(json.dumps(result), content_type='application/json')
# -*- coding: utf-8 -*-
import MySQLdb
from django.db import connection
from common.utils.aes_decryptor import Prpcrypt
from sql.models import Instance
prpCryptor = Prpcrypt()
class DbOperat(object):
"""
数据库连接模块，支持mysql的连接
"""
def __init__(self, instance_name, db_name, flag=True, charset="utf8"):
try:
instance_info = Instance.objects.get(instance_name=instance_name)
except Exception:
connection.close()
instance_info = Instance.objects.get(instance_name=instance_name)
self.host = instance_info.host
self.port = int(instance_info.port)
self.user = instance_info.user
self.password = prpCryptor.decrypt(instance_info.password)
if flag:
self.conn = MySQLdb.connect(host=self.host,
port=int(self.port),
user=self.user,
passwd=self.password,
db=db_name,
charset=charset)
