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__)
