def get_host_info(obj): sql = "select ip, port, `user`, `password`, host_name from mysql_audit.mysql_hosts where host_id = {0}".format( obj.host_id) info = common_util.get_object(db_util.DBUtil().fetchone( settings.MySQL_HOST, sql)) info.user = custom_algorithm.decrypt(settings.MY_KEY, info.user) return json.dumps(info, default=lambda o: o.__dict__)
def load_group_infos(self): rows = db_util.DBUtil().fetchall( settings.MySQL_HOST, "select * from mysql_audit.group_info where is_deleted = 0;") self.__group_infos.clear() for row in rows: self.__group_infos[row["group_id"]] = common_util.get_object(row)
def get_rollback_sql(sql_id): aa = time.time() result = common_util.Entity() sql_info = get_sql_info_by_id(sql_id) result.rollback_sql = [] result.rollback_sql_value = "" result.is_backup = sql_info.is_backup result.host_id = sql_info.mysql_host_id if (sql_info.is_backup): if (sql_info.rollback_sql is not None): result.rollback_sql_value = sql_info.rollback_sql else: for info in json.loads(sql_info.return_value): info = common_util.get_object(info) if (info.backup_dbname is None): continue sql = "select schema_name from information_schema.SCHEMATA where schema_name = '{0}';".format(info.backup_dbname) db_name = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql) if (db_name is None): continue sql = "select tablename from {0}.$_$Inception_backup_information$_$ where opid_time = {1}".format(info.backup_dbname, info.sequence) table_name_dict = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql) if (table_name_dict is None): continue sql = "select rollback_statement from {0}.{1} where opid_time = {2}".format(info.backup_dbname, table_name_dict["tablename"], info.sequence) for list_dict in db_util.DBUtil().fetchall(settings.MySQL_HOST, sql): result.rollback_sql.append(list_dict.values()[0]) bb = time.time() print(">>>>>>>>>>>>>>get rollback sql time:{0}<<<<<<<<<<<<<<<<<<<".format(bb - aa)) if (len(result.rollback_sql) > 0): result.rollback_sql_value = "\n".join(result.rollback_sql) result.rollback_sql = [] db_util.DBUtil().execute(settings.MySQL_HOST, "update mysql_audit.sql_work set `rollback_sql` = '{0}' where id = {1};".format(db_util.DBUtil().escape(result.rollback_sql_value), sql_id)) return result
def get_sql_info_by_id(id): sql = """select t1.sql_value, t1.title, t1.jira_url, t1.execute_user_id, t1.is_backup, t1.sleep, t1.audit_user_id, t1.ignore_warnings, rollback_sql, execute_date_time, t2.host_name, t1.mysql_host_id, t1.id, t1.status, t1.return_value, t1.execute_db_name, t1.audit_result_value, t1.execute_user_id, t1.created_time, create_user_name, audit_user_name, execute_user_name, real_execute_user_name, create_user_id from `mysql_audit`.`sql_work` t1 left join `mysql_audit`.mysql_hosts t2 on t1.mysql_host_id = t2.host_id where t1.id = {0};""".format(id) return get_sql_work_status_name(common_util.get_object(db_util.DBUtil().fetchone(settings.MySQL_HOST, sql)))
def check_sql_audit_result_has_warnings(sql_id): result = common_util.Entity() sql_info = get_sql_info_by_id(sql_id) for info in json.loads(sql_info.audit_result_value): obj = common_util.get_object(info) if (obj.errlevel == settings.INCETION_SQL_WARNING): result.has_warnings = True break else: result.has_warnings = False return json.dumps(result, default=lambda o: o.__dict__)
def load_mysql_host_infos(self): rows = db_util.DBUtil().fetchall( settings.MySQL_HOST, "select * from mysql_audit.mysql_hosts WHERE is_deleted = 0;") for row in rows: info = common_util.get_object(row) if (info.host_id not in self.__mysql_host_infos.keys()): info.host = info.ip info.key = info.host_id info.user = custom_algorithm.decrypt(settings.MY_KEY, info.user) info.password = custom_algorithm.decrypt( settings.MY_KEY, info.password) info.host_ip = info.host info.host_port = info.port info.host_user = info.user info.host_password = info.password info.is_alive = host_manager.test_connection_new(info) self.__mysql_host_infos[info.host_id] = info
def load_role_infos(self): rows = db_util.DBUtil().fetchall( settings.MySQL_HOST, "select * from mysql_audit.role_info") self.__role_infos.clear() for row in rows: self.__role_infos[row["role_id"]] = common_util.get_object(row)
def update(obj): sql = "select ip, port, `user`, `password`, host_name from mysql_audit.mysql_hosts where host_id = {0}".format( obj.host_id) info = common_util.get_object(db_util.DBUtil().fetchone( settings.MySQL_HOST, sql)) return json.dumps(info, default=lambda o: o.__dict__)