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): prpCryptor = Prpcrypt() try: auth = AliyunAccessKey.objects.get(is_enable=1) ak = prpCryptor.decrypt(auth.ak) secret = prpCryptor.decrypt(auth.secret) region_id = prpCryptor.decrypt(auth.region_id) except Exception: logger.error(traceback.format_exc()) logger.error('没有找到有效的ak信息!') else: self.clt = client.AcsClient(ak=ak, secret=secret, region_id=region_id)
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 sqladvisorcheck(request): sql_content = request.POST.get('sql_content') instance_name = request.POST.get('instance_name') dbName = 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() if sql_content[-1] != ";": result['status'] = 1 result['msg'] = 'SQL语句结尾没有以;结尾,请重新修改并提交!' return HttpResponse(json.dumps(result), content_type='application/json') try: user_instances(request.user, 'master').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(dbName), 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 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 __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 __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 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)