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 insert_host_table_total(host_info, table_total_info):
yesterday_data = db_util.DBUtil().fetchone(
settings.MySQL_Host,
"select * from mysql_web.host_table_total WHERE host_id = {0} AND `date` = date(date_sub(now(), interval 1 day));"
.format(host_info.host_id))
yesterday_data = yesterday_data if (yesterday_data
is not None) else table_total_info
sql = """INSERT INTO mysql_web.host_table_total
(host_id, data_size, index_size, total_size, rows, file_size, free_size, `date`, diff_data_size, diff_index_size, diff_total_size, diff_rows, diff_file_size, diff_free_size)
VALUES
({0}, {1}, {2}, {3}, {4}, {5}, {6}, DATE(NOW()), {7}, {8}, {9}, {10}, {11}, {12})""" \
.format(host_info.host_id,
table_total_info.data_total,
table_total_info.index_total,
table_total_info.data_total + table_total_info.index_total,
table_total_info.rows_total,
table_total_info.file_total,
table_total_info.free_total,
table_total_info.data_total - yesterday_data.data_size,
table_total_info.index_total - yesterday_data.index_size,
table_total_info.total - yesterday_data.total_size,
table_total_info.rows_total - yesterday_data.rows,
table_total_info.file_total - yesterday_data.file_size,
table_total_info.free_total - yesterday_data.free_size)
db_util.DBUtil().execute(settings.MySQL_Host, sql)
def update_sql_work(obj):
sql_info = get_sql_info_by_id(obj.sql_id)
if (obj.current_user_id != sql_info.create_user_id):
return "你不能编辑此工单，你不是工单创建者！"
audit_result = inception_util.sql_audit(get_use_db_sql(obj.sql_value, obj.db_name), cache.MyCache().get_mysql_host_info(obj.host_id))
if (get_sql_execute_status(audit_result) == False):
return "提交的SQL有错误，请仔细检查！"
sql = """update `mysql_audit`.`sql_work`
set `title` = '{0}', `jira_url` = '{1}', `execute_user_id` = {2},
`is_backup` = {3}, sleep = {4}, `execute_user_name` = '{5}', audit_user_id = {6}, audit_user_name = '{7}',
`sql_value` = '{8}', audit_result_value = '{9}'
where id = {10};""".format(db_util.DBUtil().escape(str(obj.title)),
db_util.DBUtil().escape(str(obj.jira_url)),
obj.dba_user_id,
obj.is_backup,
obj.sleep_time,
cache.MyCache().get_user_chinese_name(obj.dba_user_id),
obj.audit_user_tmp,
cache.MyCache().get_user_chinese_name(obj.audit_user_tmp),
db_util.DBUtil().escape(obj.sql_value),
db_util.DBUtil().escape(json.dumps(audit_result, default=lambda o: o.__dict__)),
obj.sql_id)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
return "更新成功！"
def get_table_infos(host_info):
table_infos = {}
db_names = db_util.DBUtil().fetchall(host_info, "show databases;")
for db_name in db_names:
sql = """select table_schema, table_name, DATA_LENGTH, INDEX_LENGTH, TABLE_ROWS, AUTO_INCREMENT, create_time, engine, update_time
from information_schema.tables
where table_schema = '{0}'
and table_schema != 'mysql' and table_schema != 'information_schema' and table_schema != 'performance_schema' and table_schema != 'sys'""".format(
db_name["Database"])
for row in db_util.DBUtil().fetchall(host_info, sql):
table_info = entitys.Entity()
table_info.schema = row["table_schema"]
table_info.t_name = row["table_name"]
table_info.rows = row["TABLE_ROWS"]
table_info.data_size = row["DATA_LENGTH"] if row[
"DATA_LENGTH"] else 0
table_info.index_size = row["INDEX_LENGTH"] if row[
"INDEX_LENGTH"] else 0
table_info.auto_increment = row["AUTO_INCREMENT"] if row[
"AUTO_INCREMENT"] else 0
table_info.total_size = long(table_info.data_size) + long(
table_info.index_size)
table_info.create_time = row["create_time"]
table_info.update_time = row["update_time"] if row[
"update_time"] else ''
table_info.engine = row["engine"]
table_name = row["table_schema"] + "." + row["table_name"]
table_infos[table_name] = table_info
return table_infos
def sql_execute(obj):
try:
return_info = custom_entity.Entity()
return_info.message = ""
return_info.execute_result = None
sql_info = get_sql_info_by_id(obj.sql_id)
user_info = cache.MyCache().get_user_info(obj.current_user_id)
if (user_info.group_id != settings.ADMIN_GROUP_ID):
# 如果审核没通过，或者审核失败，也不允许执行
if (sql_info.status == settings.SQL_NO_AUDIT or sql_info.status == settings.SQL_AUDIT_FAIL):
return_info.message = "审核不通过，不允许执行！"
return return_info
# 如果工单指定执行的用户跟实际执行的用户不一样，那不允许通过
elif (sql_info.execute_user_id != user_info.user_id):
return_info.message = "你不能执行此工单，该工单指定执行用户不是你！"
return return_info
if (sql_info.status == settings.SQL_EXECUTE_ING):
# 如果工单正在执行中，不允许重复执行SQL
return_info.message = "SQL工单正在执行中，请耐心等待..."
return return_info
if (sql_info.status == settings.SQL_EXECUTE_SUCCESS):
# 如果已经执行成功，直接返回执行结果
return_info.execute_result = json.loads(sql_info.return_value)
else:
# 更新工单状态为执行中
sql = "update mysql_audit.sql_work set `status` = {0}, `execute_start_date_time` = NOW(), `execute_date_time` = NOW() where id = {1};".format(settings.SQL_EXECUTE_ING, sql_info.id)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
if (len(sql_info.execute_db_name.strip()) > 0):
sql_info.sql_value = "use {0};{1}".format(sql_info.execute_db_name, sql_info.sql_value)
result_obj = inception_util.sql_execute(sql_info.sql_value,
cache.MyCache().get_mysql_host_info(sql_info.mysql_host_id),
is_backup=sql_info.is_backup,
ignore_warnings=True if (obj.ignore_warnings.upper() == "TRUE") else False,
sleep_time=sql_info.sleep)
sql = """update mysql_audit.sql_work
set
return_value = '{0}',
`status` = {1},
`ignore_warnings` = {2},
`execute_finish_date_time` = NOW(),
`real_execute_user_id` = {3},
`real_execute_user_name` = '{4}' where id = {5};""".format(db_util.DBUtil().escape(json.dumps(result_obj, default=lambda o: o.__dict__)),
settings.SQL_EXECUTE_SUCCESS if (get_sql_execute_status(result_obj)) else settings.SQL_EXECUTE_FAIL,
obj.ignore_warnings,
obj.current_user_id,
cache.MyCache().get_user_info(obj.current_user_id).chinese_name,
sql_info.id)
print(sql)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
send_mail_for_execute_success(sql_info.id)
return_info.execute_result = result_obj
except Exception, e:
# 出现异常要更新状态，直接把状态变为fail
sql = "update mysql_audit.sql_work set `status` = {0} where id = {1};".format(settings.SQL_EXECUTE_FAIL, sql_info.id)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
traceback.print_exc()
return_info.message = "执行时出现异常，请联系管理员！"
def execute_rollback_sql(sql_id):
sql_info = get_sql_info_by_id(sql_id)
rollback_host = cache.MyCache().get_mysql_host_info(int(sql_info.mysql_host_id))
rollback_sql = "start transaction; " + get_rollback_sql(sql_id).rollback_sql_value + " commit;"
if (db_util.DBUtil().execute(rollback_host, rollback_sql)):
db_util.DBUtil().execute(settings.MySQL_HOST, "update mysql_audit.sql_work set `status` = {0} where id = {1};".format(settings.SQL_WORK_ROLLBACK, sql_id))
return "回滚成功"
return "回滚失败"
def delete_user_group_info(group_id):
# 删除用户组必须要判断组内是否还有用户
sql = "SELECT user_count FROM mysql_audit.group_info where group_id = {0};".format(group_id)
result = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql)
if (int(result["user_count"]) > 0):
return "该组内还有用户，请先删除用户再删除组!"
else:
sql = "update mysql_audit.group_info set is_deleted = 1 where group_id = {0};".format(group_id)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
cache.MyCache().load_group_infos()
return "用户组删除成功!"
def check_master_and_slave_relation(self):
"""for key, value in self.__repl_infos.items():
result = db_util.DBUtil().fetchone(self.__host_infos[key], "show slave status;")
if (result != None and int(result["Read_Master_Log_Pos"]) > 0):
value.is_slave = 1
value.master_host_id = 0
value.host_info.role = "S"
value.host_info.is_slave = True
master_ip = result["Master_Host"]
master_port = result["Master_Port"]
for host_info in self.__host_infos.values():
if (host_info.host == master_ip and host_info.port == master_port):
value.master_host_id = host_info.host_id
break
else:
value.host_info.role = "M"
value.host_info.is_master = True"""
#有的mysql既是主库也是从库，这个要理清逻辑
uuid_key = "Slave_UUID"
for key, value in self.__repl_infos.items():
result = db_util.DBUtil().fetchall(value.host_info,
"show slave hosts;")
if (len(result) <= 0):
#解决集群中只有一个mysql的情况
slave_status = db_util.DBUtil().fetchone(
value.host_info, "show slave status;")
if (slave_status != None):
value.host_info.is_slave = True
else:
value.host_info.is_master = True
continue
number = 0
value.master_name = ""
for row in result:
if (uuid_key not in row.keys()):
continue
number += 1
for host_info in self.__host_infos.values():
if (host_info.server_uuid == row[uuid_key]):
host_info.is_slave = True
repl_info = self.__repl_infos[host_info.host_id]
host_info.master_name = value.host_info.remark
repl_info.master_host_id = value.host_info.host_id
host_info.master_host_id = value.host_info.host_id
#解决"show slave status"有数据但UUID为空的情况
if (number >= 1 and len(result) > 0):
value.host_info.is_master = True
else:
value.host_info.is_slave = True
def insert_table_size_log(host_info, table_info):
increase_size = 0
sql = "select total_size from mysql_web.table_size_log where host_id = {0} and `date` = date(date_sub(now(), interval 1 day))".format(
host_info.host_id)
result = db_util.DBUtil().fetchone(settings.MySQL_Host, sql)
if (result != None):
increase_size = table_info.total_size - result["total_size"]
sql = """insert into mysql_web.mysql_data_size_log
(host_id, `db_name`, table_name, data_size, index_size, total_size, rows, auto_increment, file_size, free_size, increase_size, `date`)
values
({0}, '{1}', '{2}', {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, date(now()))"""\
.format(host_info.host_id, table_info.schema, table_info.t_name,
table_info.data_size_o, table_info.index_size_o, table_info.total_size_o, table_info.rows_o, table_info.auto_increment, table_info.file_size_o, table_info.free_size, increase_size)
db_util.DBUtil().execute(settings.MySQL_Host, sql)
def get_table_columns(host_id, table_schema, table_name):
sql = """select column_name, ordinal_position, column_default, is_nullable, column_type, column_key, extra
from information_schema.COLUMNS
where table_schema = '{0}' and table_name = '{1}';""".format(
table_schema, table_name)
return db_util.DBUtil().get_list_infos(
cache.Cache().get_host_info(host_id), sql)
def get_table_indexs(host_id, table_schema, table_name):
sql = """select index_name, non_unique, seq_in_index, column_name, collation, cardinality, nullable, index_type
from information_schema.STATISTICS
where table_schema = '{0}' and table_name = '{1}';""".format(
table_schema, table_name)
return db_util.DBUtil().get_list_infos(
cache.Cache().get_host_info(host_id), sql)
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_show_index(host_info, table_name):
index_infos = []
rows = db_util.DBUtil().fetchall(host_info,
"show index from {0};".format(table_name))
for row_info in rows:
index_infos.append(common.get_object(row_info))
return index_infos
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 skip_slave_error(host_id):
slave_info = get_show_slave_status(host_id)
if (slave_info["Slave_SQL_Running"] == "No"):
sql = "stop slave sql_thread; set global sql_slave_skip_counter=1; start slave sql_thread;"
db_util.DBUtil().execute(cache.Cache().get_host_info(host_id), sql)
return "repl error skip ok."
return "repl status is ok."
def get_sql_work_list_by_where(obj, sql_where):
if (obj.tab_type == settings.NOT_AUDIT_SQL_WORK_TAB):
sql_where += " and status = {0}".format(settings.SQL_NO_AUDIT)
elif (obj.tab_type == settings.AUDIT_OK_SQL_WORK_TAB):
sql_where += " and status = {0}".format(settings.SQL_AUDIT_OK)
elif (obj.tab_type == settings.AUDIT_FAIL_SQL_WORK_TAB):
sql_where += " and status = {0}".format(settings.SQL_AUDIT_FAIL)
elif (obj.tab_type == settings.NOT_EXECUTE_SQL_WORK_TAB):
sql_where += " and status = {0}".format(settings.SQL_AUDIT_OK)
elif (obj.tab_type == settings.EXECUTE_OK_SQL_WROK_TAB):
sql_where += " and status = {0}".format(settings.SQL_EXECUTE_SUCCESS)
elif (obj.tab_type == settings.EXECUTE_FAIL_SQL_WROK_TAB):
sql_where += " and status = {0}".format(settings.SQL_EXECUTE_FAIL)
sql = """select t1.*, t2.host_name
from
(
select id, title, create_user_id, audit_user_id, execute_user_id, audit_date_time,
execute_date_time, mysql_host_id, is_backup, execute_db_name,
status, is_deleted, created_time, execute_finish_date_time,
create_user_name, audit_user_name, execute_user_name, real_execute_user_name
from mysql_audit.sql_work
where is_deleted = 0 {0} order by id desc limit {1}, {2}
) t1
left join mysql_audit.mysql_hosts t2 on t1.mysql_host_id = t2.host_id"""
sql = sql.format(sql_where, (obj.page_number - 1) * settings.SQL_LIST_PAGE_SIZE, settings.SQL_LIST_PAGE_SIZE)
result_list = db_util.DBUtil().get_list_infos(settings.MySQL_HOST, sql)
for info in result_list:
get_sql_work_status_name(info)
return result_list
def delete_mysql_host_info(host_id):
db_util.DBUtil().execute(
settings.MySQL_Host,
"update mysql_web.host_infos set is_deleted = 1 where host_id = {0}".
format(host_id))
cache.Cache().load_all_host_infos()
return "删除成功"
def get_mysql_info(host_id):
sql = "select host_id, host, port, user, password, remark, ssh_user, ssh_port, ssh_password from mysql_web.host_infos where host_id = {0};".format(
host_id)
result = common.get_object(db_util.DBUtil().fetchone(
settings.MySQL_Host, sql))
result.user = custom_algorithm.decrypt(settings.MY_KEY, result.user)
return common.convert_obj_to_json_str(result)
def check_mysql_server_version_and_branch(self):
for host_info in self.__host_infos.values():
result = db_util.DBUtil().fetchall(
host_info, server.show_global_variables_sql)
data = {}
for row in result:
data[row.get("Variable_name")] = row.get("Value")
host_info.version = data["version"]
str_branch = data["version_comment"]
host_info.mysql_data_dir = data["datadir"]
host_info.server_uuid = data["server_uuid"]
host_info.innodb_log_file_size = int(data["innodb_log_file_size"])
host_info.innodb_log_buffer_size = int(
data["innodb_log_buffer_size"])
host_info.innodb_buffer_pool_size = tablespace.get_data_length(
long(data["innodb_buffer_pool_size"]))
if (str_branch.find(MySQLBranch.Percona.name) >= 0):
host_info.branch = MySQLBranch.Percona
elif (str_branch.find(MySQLBranch.Mariadb.name) >= 0):
host_info.branch = MySQLBranch.Mariadb
else:
host_info.branch = MySQLBranch.MySQL
host_info.mysql_pid_file = data["pid_file"]
if (os.path.exists(host_info.mysql_pid_file) == False):
host_info.mysql_pid_file = os.path.join(
host_info.mysql_data_dir, host_info.mysql_pid_file)
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 test_mysql_connection_is_ok(obj):
try:
db_util.DBUtil().execute_sql(obj.host_ip, obj.host_port, obj.host_user,
obj.host_password, "select 1;")
except Exception:
traceback.print_exc()
return False
return True
def load_all_host_infos(self):
if (self.__thread_pool == None):
self.__thread_pool = threadpool.ThreadPool(
settings.THREAD_POOL_SIZE)
sql = "select * from mysql_web.host_infos;"
for row in db_util.DBUtil().fetchall(settings.MySQL_Host, sql):
host_info_temp = common.get_object(row)
host_info_temp.master_host_id = 0
host_info_temp.key = host_info_temp.host_id
host_info_temp.is_slave = bool(host_info_temp.is_slave)
host_info_temp.is_master = bool(host_info_temp.is_master)
host_info_temp.user = custom_algorithm.decrypt(
settings.MY_KEY, host_info_temp.user)
host_info_temp.password = custom_algorithm.decrypt(
settings.MY_KEY, host_info_temp.password)
host_info_temp.ssh_password = custom_algorithm.decrypt(
settings.MY_KEY, host_info_temp.ssh_password) if (
len(host_info_temp.ssh_password) > 0) else None
host_id = host_info_temp.host_id
self.__host_infos[host_info_temp.host_id] = host_info_temp
if (host_info_temp.is_deleted == 1):
self.remove_key(self.__tablespace, host_id)
self.remove_key(self.__host_infos, host_id)
self.remove_key(self.__repl_infos, host_id)
self.remove_key(self.__status_infos, host_id)
self.remove_key(self.__host_infos, host_id)
self.remove_key(self.__innodb_infos, host_id)
self.remove_key(self.__linux_infos, host_id)
self.remove_key(self.__innodb_status_infos, host_id)
self.remove_key(self.__analyze_infos, host_id)
else:
if (self.__tablespace.has_key(host_id) == False):
self.__tablespace[host_id] = BaseClass(host_info_temp)
if (self.__repl_infos.has_key(host_id) == False):
self.__repl_infos[host_id] = BaseClass(host_info_temp)
if (self.__linux_infos.has_key(host_id) == False):
self.__linux_infos[host_id] = BaseClass(host_info_temp)
if (self.__status_infos.has_key(host_id) == False):
self.__status_infos[host_id] = BaseClass(host_info_temp)
if (self.__innodb_infos.has_key(host_id) == False):
self.__innodb_infos[host_id] = self.init_innodb_info(
BaseClass(host_info_temp))
if (self.__analyze_infos.has_key(host_id) == False):
self.__analyze_infos[host_id] = self.init_analyze_info(
BaseClass(None))
if (self.__innodb_status_infos.has_key(host_id) == False):
self.__innodb_status_infos[host_id] = BaseClass(
host_info_temp)
self.__innodb_status_infos[
host_id].buffer_pool_infos = collections.OrderedDict()
self.load_mysql_web_user_infos()
self.check_mysql_server_version_and_branch()
self.check_master_and_slave_relation()
result = "load all host infos ok."
print(result)
return result
def load_mysql_web_user_infos(self):
sql = "select id, user_name, user_password, is_deleted from mysql_web.mysql_web_user_info;"
for row in db_util.DBUtil().fetchall(settings.MySQL_Host, sql):
user_info = BaseClass(None)
user_info.user_id = row["id"]
user_info.user_name = row["user_name"]
user_info.user_password = row["user_password"]
user_info.is_deleted = row["is_deleted"]
self.add_user_info(user_info)
def check_table_has_primary_key(table_schema, table_name):
sql = "select count(1) as row_count from information_schema.COLUMNS where table_schema='{0}' and table_name='{1}' and column_key='PRI'".format(
table_schema, table_name)
result = db_util.DBUtil().fetchone(settings.MySQL_Host, sql)
if (result == None):
return False
elif (int(result["row_count"]) == 1):
return True
return False
def insert_host_tablespace_data(info):
sql = "insert into mysql_web.mysql_data_total_size_log " \
"(host_id, rows_t, data_t, index_t, all_t, file_t, free_t, table_count, `date`) " \
"values " \
"({0},{1},{2},{3},{4},{5},{6},{7},date(now()));"
sql = sql.format(info.host_info.key, info.rows_total, info.data_total_o,
info.index_total_o, info.total_o, info.file_total_o,
info.free_total_o, info.table_count)
db_util.DBUtil().execute(settings.MySQL_Host, sql)
def add_user(obj):
result_json = Entity()
result_json.flag = False
if (len(str(obj.user_name)) <= 0):
result_json.message = "请输入用户名!"
elif (len(str(obj.user_password)) <= 0):
result_json.message = "请输入密码!"
elif (len(str(obj.chinese_name)) <= 0):
result_json.message = "请输入中文名!"
elif (len(str(obj.email)) <= 0):
result_json.message = "请输入用户邮箱!"
elif (obj.group_id <= 0):
result_json.message = "请选择用户组!"
elif (obj.role_id <= 0):
result_json.message = "请选择用户角色!"
else:
result_json.flag = True
if (result_json.flag):
# 这边要判断下用户名是否已经存在
sql = "select user_id from mysql_audit.work_user where user_name = '{0}' limit 1;".format(
obj.user_name)
result = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql)
if (result is not None):
result_json.flag = False
result_json.message = "用户名已存在!"
else:
# 对用户能够访问哪些数据库进行设置
if (len(obj.user_hosts) > 1):
pass
else:
pass
sql = """insert into mysql_audit.work_user
(user_name, user_password, chinese_name, group_id, role_id, email)
VALUES
('{0}', md5('{1}'), '{2}', {3}, {4}, '{5}');
update mysql_audit.group_info set user_count = user_count + 1 where group_id = {6};""" \
.format(obj.user_name, obj.user_password, obj.chinese_name, obj.group_id, obj.role_id, obj.email, obj.group_id)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
cache.MyCache().load_user_infos()
cache.MyCache().load_group_infos()
result_json.message = "添加用户成功!"
return json.dumps(result_json, default=lambda o: o.__dict__)
def get_show_table_status(host_info, db_name, table_name):
result = db_util.DBUtil().fetchone(
host_info,
"show table status from {0} like '{1}';".format(db_name, table_name))
entity = common.get_object(result)
entity.free_size = tablespace.get_data_length(entity.data_free)
entity.date_size = tablespace.get_data_length(entity.data_length)
entity.index_size = tablespace.get_data_length(entity.index_length)
entity.avg_row_size = tablespace.get_data_length(entity.avg_row_length)
return entity
def start_user(user_id):
sql = """update mysql_audit.work_user set is_deleted = 0 where user_id = {0};
update mysql_audit.group_info t1
left join mysql_audit.work_user t2 on t1.group_id = t2.group_id
set t1.user_count = t1.user_count + 1
where t2.user_id = {0};""".format(user_id)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
cache.MyCache().load_user_infos()
cache.MyCache().load_group_infos()
return "启用用户成功"
def audit_sql_work(obj):
user_info = cache.MyCache().get_user_info(obj.current_user_id)
if (user_info.group_id != settings.ADMIN_GROUP_ID):
sql_work = get_sql_info_by_id(obj.sql_id)
if (sql_work.audit_user_id != obj.current_user_id):
return "此工单审核人不是你，你无法审核！"
status = settings.SQL_AUDIT_OK if (obj.status) else settings.SQL_AUDIT_FAIL
sql = """update `mysql_audit`.`sql_work` set `status` = {0}, remark = '{1}' where id = {2};""".format(status, obj.remark, obj.sql_id)
db_util.DBUtil().execute(settings.MySQL_HOST, sql)
send_mail_for_audit_success(obj.sql_id)
return "操作成功！"
def add(obj):
sql = "select host_id from mysql_audit.mysql_hosts where ip = '{0}' and port = {1};".format(
obj.host_ip, obj.host_port)
result = db_util.DBUtil().fetchone(settings.MySQL_HOST, sql)
if (result is not None):
return "1"
is_alive = test_connection_new(obj)
sql = """insert into mysql_audit.mysql_hosts
(ip, port, `user`, `password`, host_name, is_alive)
VALUES
('{0}', {1}, '{2}', '{3}', '{4}', {5})""" \
.format(obj.host_ip,
obj.host_port,
custom_algorithm.encrypt(settings.MY_KEY, obj.host_user),
custom_algorithm.encrypt(settings.MY_KEY, obj.host_password),
obj.host_name, is_alive)
db_util.DBUtil().fetchone(settings.MySQL_HOST, sql)
cache.MyCache().load_mysql_host_infos()
return "2"