def log_db_inst(p_instid):
try:
result = {}
result['code'] = '0'
result['message'] = ''
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT
concat(DATE_FORMAT(create_date,'%Y-%m-%d %H:%i:%s'),' => ', a.message) AS log
FROM t_db_inst_log a
WHERE a.inst_id='{0}'
ORDER BY a.create_date""".format(p_instid)
#print(sql)
cr.execute(sql)
rs = cr.fetchall()
v_log = ''
for r in rs:
v_log = v_log + r['log'] + '\n'
cr.close()
db.commit()
result['message'] = v_log[0:-1]
#print('log_db_inst=',v_log)
return result
except:
print(traceback.print_exc())
result['code'] = '-1'
result['message'] = '获取日志失败!'
return result
def query_wtd_detail(p_wtd_no,p_userid):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT
order_no,
order_env,
order_type,
order_status,
creator,
date_format(a.create_date,'%Y-%m-%d') as create_date,
order_handler,
date_format(a.handler_date,'%Y-%m-%d') as handler_date,
(SELECT db_desc FROM t_db_source WHERE id=a.order_env) AS order_env_name,
(SELECT dmmc FROM t_dmmx WHERE dm='17' AND dmm=a.order_type) AS order_type_name,
(SELECT dmmc FROM t_dmmx WHERE dm='19' AND dmm=a.order_status) AS order_status_name,
(SELECT NAME FROM t_user WHERE id=a.creator) AS creator_name,
(SELECT NAME FROM t_user WHERE id=a.order_handler) AS order_handler_name,
order_desc,
attachment_path,
attachment_name,
'{0}' as curr_user
FROM t_wtd a where order_no='{1}'
""".format(p_userid,p_wtd_no)
print(sql)
cr.execute(sql)
rs=cr.fetchone()
cr.close()
db.commit()
print(rs)
return rs
def get_ds_by_instid(p_inst_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.id as dsid,
a.inst_name as db_desc,
CASE WHEN a.is_rds='Y' THEN
a.inst_ip
ELSE
(SELECT server_ip FROM t_server b WHERE b.id=a.server_id)
END AS ip,
CASE when a.inst_mapping_port is null or a.inst_mapping_port ='' then
a.inst_port
ELSE
a.inst_mapping_port
END as port,
a.inst_type as db_type,
a.inst_env as db_env,
a.mgr_user as user,
a.mgr_pass as password,
'' as service,
date_format(created_date,'%Y-%m-%d %H:%i:%s') as created_date
FROM t_db_inst a
WHERE a.id='{0}'""".format(p_inst_id)
print(sql)
cr.execute(sql)
rs = cr.fetchone()
rs['password'] = aes_decrypt(rs['password'], rs['user'])
print("rs->password=", rs['password'])
cr.close()
db.commit()
return rs
def query_slow_log_detail(p_sqlid):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT
GROUP_CONCAT(DISTINCT x.user) AS "user",
GROUP_CONCAT(DISTINCT x.host) AS "host",
GROUP_CONCAT(DISTINCT x.db) AS "db",
CONCAT(GROUP_CONCAT(x.min_query_time SEPARATOR "~"),'s') AS min_query_time,
CONCAT(GROUP_CONCAT(x.max_query_time SEPARATOR "~"),'s') AS max_query_time,
GROUP_CONCAT(x.min_finish_time SEPARATOR "~") AS min_finish_time,
GROUP_CONCAT(x.max_finish_time SEPARATOR "~") AS max_finish_time,
GROUP_CONCAT(x.exec_time) AS exec_time
FROM (
SELECT a.sql_id,
a.user,
a.host,
a.db,
ROUND(MIN(query_time),0) AS min_query_time,
ROUND(MAX(query_time),0) AS max_query_time,
MIN(finish_time) AS min_finish_time,
MAX(finish_time) AS max_finish_time,
COUNT(0) AS exec_time
FROM t_slow_detail a WHERE a.sql_id='{}'
GROUP BY
a.user,
a.host,
a.db ) X GROUP BY x.sql_id""".format(p_sqlid)
print(sql)
cr.execute(sql)
rs=cr.fetchone()
cr.close()
db.commit()
return rs
def get_slow_by_slowid(p_slowid):
db = get_connection_dict()
cr = db.cursor()
sql="select * from t_slow_log where id={0}".format(p_slowid)
print(sql)
cr.execute(sql)
rs = cr.fetchone()
cr.close()
db.commit()
return rs
def get_minio_by_minioid(p_sync_tag):
db = get_connection_dict()
cr = db.cursor()
sql = "select * from t_minio_config where sync_tag='{0}'".format(
p_sync_tag)
cr.execute(sql)
rs = cr.fetchone()
cr.close()
db.commit()
print(rs)
return rs
def query_slow_log_by_id(p_sqlid):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.inst_id,a.db,a.sql_text FROM t_slow_detail a WHERE a.sql_id='{0}' limit 1""".format(p_sqlid)
print(sql)
cr.execute(sql)
rs=cr.fetchone()
cr.close()
db.commit()
rs['sql_text'] = format_sql(rs['sql_text'])['message']
return rs
def get_user_privs_zh(p_user_privs):
db = get_connection_dict()
cr = db.cursor()
st = "SELECT dmmc FROM t_dmmx WHERE dm='31' and instr('{}',dmm)>0".format(p_user_privs)
cr.execute(st)
rs=cr.fetchall()
v =''
for p in rs:
v=v+p['dmmc']+','
cr.close()
return v[0:-1]
def get_sync_by_sync_tag(p_sync_tag):
db = get_connection_dict()
cr = db.cursor()
sql = "select * from t_db_sync_config where sync_tag='{0}'".format(
p_sync_tag)
print('get_sync_by_sync_tag=', sql)
cr.execute(sql)
rs = cr.fetchone()
cr.close()
db.commit()
print('rs=', rs)
return rs
def query_datax_by_id(sync_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT
a.sync_tag,
a.server_id,
a.sour_db_id,
a.sync_schema,
a.sync_table,
a.sync_incr_col,
e.user,
e.password,
a.sync_columns,
a.sync_table,
CONCAT(e.ip,':',e.port,'/',a.sync_schema) AS mysql_url,
a.zk_hosts,
a.sync_hbase_table,
a.sync_hbase_rowkey,
a.sync_hbase_rowkey_sour,
a.sync_hbase_rowkey_separator,
a.sync_hbase_columns,
a.sync_incr_where,
a.sync_ywlx,
a.sync_type,
a.script_path,
a.run_time,
a.comments,
a.datax_home,
a.sync_time_type,
a.sync_gap,
a.api_server,
a.status,
a.python3_home,
a.hbase_thrift,
a.es_service,
a.es_index_name,
a.es_type_name,
a.sync_es_columns
FROM t_datax_sync_config a,t_server b ,t_dmmx c,t_dmmx d,t_db_source e
WHERE a.server_id=b.id AND b.status='1'
AND a.sour_db_id=e.id
AND c.dm='08' AND d.dm='09'
AND a.sync_ywlx=c.dmm
AND a.sync_type=d.dmm
AND a.id='{0}'
""".format(sync_id)
print(sql)
cr.execute(sql)
rs = cr.fetchone()
#v_list=list(rs)
cr.close()
db.commit()
return rs
def get_archive_by_archiveid(p_archiveid):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT id,archive_tag,server_id,comments,archive_db_type,sour_db_id,sour_schema,
sour_table,archive_time_col,archive_rentition,rentition_time,rentition_time_type,dest_db_id,dest_schema,script_path,
script_file,python3_home,api_server,status,batch_size,if_cover,run_time
FROM t_db_archive_config where id={0}
""".format(p_archiveid)
cr.execute(sql)
rs = cr.fetchall()
cr.close()
db.commit()
print('get_archive_by_archiveid->rs=', rs)
return rs[0]
def query_archive_detail(archive_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.archive_tag,
a.comments,
b.server_desc,
e.dmmc AS db_type,
CONCAT(c.ip,':',c.port,'/',a.sour_schema) AS archive_db_sour,
LOWER(a.sour_table) AS sour_table,
a.archive_time_col,
a.archive_rentition,
a.rentition_time,
f.dmmc AS rentition_time_type,
CONCAT(d.ip,':',d.port,'/',a.dest_schema) AS archive_db_dest,
a.`dest_schema`,
a.python3_home,
a.script_path,
a.script_file,
a.batch_size,
a.api_server,
a.status
FROM t_db_archive_config a,t_server b,t_db_source c,t_db_source d,t_dmmx e,t_dmmx f
WHERE a.server_id=b.id
AND a.sour_db_id=c.id
AND a.dest_db_id=d.id
AND a.archive_db_type=e.dmm
AND e.dm='02'
AND f.dm= '20'
AND a.id='{0}'
ORDER BY a.id
""".format(archive_id)
print(sql)
cr.execute(sql)
rs = cr.fetchone()
print('query_archive_detail=>rs=', rs)
# v_list=list(rs)
cr.close()
db.commit()
return rs
def query_user_by_id(p_user_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.id,
a.inst_id,
a.description,
a.db_user,
a.db_pass,
a.user_dbs,
a.user_privs,
a.statement,
a.status,
date_format(a.created_date,'%Y-%m-%d %H:%i:%s') as created_date
FROM t_db_user a WHERE a.id='{0}'""".format(p_user_id)
print(sql)
cr.execute(sql)
rs=cr.fetchone()
rs['db_pass'] = aes_decrypt(rs['db_pass'],rs['db_user'].replace("'", "''"))
rs['user_privs_zh'] = get_user_privs_zh(rs['user_privs'])
print("rs->password=",rs['db_pass'])
cr.close()
db.commit()
return rs
def query_inst_by_id(p_inst_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.id,
a.inst_name,
(SELECT id FROM t_server b WHERE b.id=a.server_id) as server_id,
(SELECT server_desc FROM t_server b WHERE b.id=a.server_id) as server_desc,
CASE WHEN a.is_rds='Y' THEN
a.inst_ip
ELSE
(SELECT server_ip FROM t_server b WHERE b.id=a.server_id)
END AS inst_ip,
a.inst_port,
a.inst_type,
a.inst_env,
a.inst_ver,
a.templete_id,
a.is_rds,
a.mgr_user,
a.mgr_pass,
date_format(a.created_date,'%Y-%m-%d %H:%i:%s') as created_date,
a.api_server,
a.python3_home,
a.script_path,
a.script_file,
inst_mapping_port
FROM t_db_inst a WHERE a.id='{0}'""".format(p_inst_id)
print(sql)
cr.execute(sql)
rs = cr.fetchone()
print('password='******'mgr_pass'], rs['mgr_user']))
rs['mgr_pass'] = aes_decrypt(rs['mgr_pass'], rs['mgr_user'])
print("rs->password=", rs['mgr_pass'])
cr.close()
db.commit()
return rs
def query_slow_by_id(p_slow_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.id,
a.inst_id,
a.server_id,
a.query_time,
a.log_file,
a.python3_home,
a.run_time,
a.exec_time,
a.script_path,
a.script_file,
a.api_server,
a.status,
date_format(a.create_date,'%Y-%m-%d %H:%i:%s') as create_date,
date_format(a.last_update_date,'%Y-%m-%d %H:%i:%s') as last_update_date
FROM t_slow_log a WHERE a.id='{0}'""".format(p_slow_id)
print(sql)
cr.execute(sql)
rs=cr.fetchone()
cr.close()
db.commit()
return rs
def analyze_slow_log(p_inst_id,p_db_name,p_db_user,p_db_host,p_begin_date,p_end_date):
db = get_connection_dict()
db2 = get_connection()
cr = db.cursor()
cr2 = db2.cursor()
vv = ''
v_total = {}
if p_inst_id != '':
vv = " and a.inst_id ='{0}' ".format(p_inst_id)
if p_db_name != '':
vv = vv + " and a.db ='{0}' ".format(p_db_name)
if p_db_user != '':
vv = vv + " and a.user ='******' ".format(p_db_user)
if p_db_host != '':
vv = vv + " and instr(a.host,'{0}')>0".format(p_db_host)
if p_begin_date != '':
vv = vv + " and a.finish_time>='{0}'\n".format(p_begin_date)
if p_end_date != '':
vv = vv + " and a.finish_time<='{0}'\n".format(p_end_date)
sql_host = """SELECT HOST as name ,
COUNT(0) AS value
FROM t_slow_detail a where 1 =1 {}
GROUP BY HOST""".format(vv)
print(sql_host)
cr.execute(sql_host)
v_list_host = []
for r in cr.fetchall():
v_list_host.append(r)
sql_db = """SELECT db as name ,COUNT(0) AS value FROM t_slow_detail a where 1 =1 {} GROUP BY db""".format(vv)
print(sql_db)
cr.execute(sql_db)
v_list_db = []
for r in cr.fetchall():
v_list_db.append(r)
sql_user = """SELECT user as name ,COUNT(0) AS value FROM t_slow_detail a where 1 =1 {} GROUP BY user""".format(vv)
print(sql_user)
cr.execute(sql_user)
v_list_user = []
for r in cr.fetchall():
v_list_user.append(r)
sql_top10 = """SELECT CONCAT((@rowNum:=@rowNum+1),'') AS xh,
sql_id,
query_time,
exec_time
FROM (
SELECT
sql_id,
cast(ROUND(AVG(query_time),0) as char) AS query_time,
count(0) as exec_time
FROM t_slow_detail a ,(SELECT (@rowNum:=0)) b
WHERE 1 =1 {}
GROUP BY inst_id,sql_id
ORDER BY AVG(query_time) DESC LIMIT 10
) X""".format(vv)
print(sql_top10)
cr2.execute(sql_top10)
v_list_top10 = []
for r in cr2.fetchall():
v_list_top10.append(r)
cr.close()
db.commit()
cr2.close()
db2.commit()
v_total['host'] = v_list_host
v_total['db'] = v_list_db
v_total['user'] = v_list_user
v_total['top10'] = v_list_top10
return v_total