def do(self):
self.set_header("Access-Control-Allow-Origin", "*")
self.set_header("Access-Control-Allow-Headers", "Authorizssation")
self.set_header('Access-Control-Allow-Methods', 'POST, GET, OPTIONS')
try:
_list = []
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "select `ip`, `dbname`, `submint_time`, `status`, `id` from release_system.history_execute_sql order by id desc"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
ip = row[0]
dbname = row[1]
submint_time = row[2].strftime('%c')
status = row[3]
id = row[4]
_list.append({"ip": ip,"dbname": dbname,"submint_time": submint_time,"status":status,"id":id})
data = json.dumps(_list)
except:
print traceback.format_exc()
finally:
cursor.close()
conn.close()
return data
def do(self):
self.set_header("Access-Control-Allow-Origin", "*")
self.set_header("Access-Control-Allow-Headers", "Authorizssation")
self.set_header('Access-Control-Allow-Methods', 'POST, GET, OPTIONS')
try:
_list = []
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "select `ins_name`, `ip`, `port`, `dbname` from release_system.instance"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
instance_name = row[0]
ip = row[1]
#vip = row[2]
port = row[2]
dbname = row[3]
_list.append({"instance_name": instance_name,"ip": ip,"port":port,"dbname":dbname})
data = json.dumps(_list)
except:
print traceback.format_exc()
finally:
cursor.close()
conn.close()
return data
def do(self, args):
#user = '******'
#password = '******'
ip = args.get('ip')
port = args.get('port')
instance = ip + '_' + str(port)
try:
_list = []
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
cursor = conn.cursor()
cursor.execute("SELECT audit_time, JSON_EXTRACT(mysql_audit.audit,'$.QPS'),JSON_EXTRACT(mysql_audit.audit,'$.TPS') FROM release_system.mysql_audit where instance = '%s';"% instance)
rows = cursor.fetchall()
#data = json.dumps(rows, cls=DateEncoder)
for row in rows:
time = row[0].strftime('%Y.%m.%d %H:%M')
qps = row[1]
tps = row[2]
_list.append({"time": time,"qps": qps,"tps": tps})
data = json.dumps(_list)
except:
logging.error(traceback.format_exc())
finally:
cursor.close()
conn.close()
return data
def do(self, args):
try:
id = args.get('id')
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
cursor = conn.cursor()
sql = "select `description` from release_system.history_execute_sql where id = %s" %id
cursor.execute(sql)
rows = cursor.fetchall()
rows = rows[0][0]
_list = literal_eval(rows)
data = json.dumps(_list)
except:
logging.error(traceback.format_exc())
finally:
cursor.close()
conn.close()
return data
def do(self, args):
try:
ins_name = args.get('instance_name')
ip = args.get('ip')
#vip = args.get('vip')
port = args.get('port')
dbname = args.get('dbname')
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "INSERT INTO instance(ins_name, ip, port, dbname) VALUES('%s', '%s', '%s', '%s');" % (ins_name, ip, port,dbname)
cursor.execute(sql)
except:
logging.error(traceback.format_exc())
finally:
cursor.close()
conn.close()
return 'success'
def do(self, args):
try:
ip = args.get('ip')
port = args.get('port')
db = args.get('db')
user = args.get('user')
password = args.get('password')
sql_text = args.get('sql_text')
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "INSERT INTO execute_sql_info(ip, port, db, user, password, sql_text) VALUES('%s', '%s', '%s', '%s', '%s', '%s');" % (ip, port, db, user, password, sql_text)
cursor.execute(sql)
except:
print traceback.format_exc()
finally:
cursor.close()
conn.close()
return 'success'
def do(self):
try:
_list = []
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "select ip,port,db from execute_sql_info;"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
ip = row[0]
port = row[1]
db = row[2]
_list.append({"ip": ip,"port": port,"db": db})
data = json.dumps(_list)
except:
print traceback.format_exc()
finally:
conn.close()
return data
def do(self, args):
try:
username = args.get('username')
password = args.get('password')
_list = []
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "select user_name,user_password,user_id from jwt_auth;"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
if username == row[0] and password == row[1]:
user_name = row[0]
user_password = row[1]
user_id = row[2]
_list.append({"user_name": user_name,"user_password": user_password,"user_id": user_id})
else:
continue
try:
encoded = jwt.encode({
'user_name': _list[0].get('user_name'),
'exp': datetime.datetime.utcnow() + datetime.timedelta(seconds=600)},
SECRET_KEY,
algorithm='HS256'
)
response = {'token':encoded.decode('ascii')}
except:
response = {'message':AUTHORIZATION_ERROR_CODE}
except:
logging.error(traceback.format_exc())
return response
def mysqlaudit(self):
logging.info('do mysqlaudit')
user = '******'
password = '******'
host = '100.73.20.3'
port = 13307
try:
status_name = []
status_value = []
variables_name = []
variables_value = []
sql_user_list = []
sql_status = "show global status"
sql_variables = "show global variables"
sql_user = "******"
table_audit = "SELECT ENGINE,SUM(DATA_LENGTH)+ SUM(index_length),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql', 'performance_schema','sys') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC"
conn = MySQLdb.connect(host=host,
port=port,
user=user,
passwd=password,
charset="utf8")
cursor = conn.cursor()
cursor.execute(sql_status)
sql_status = cursor.fetchall()
for row in sql_status:
status_name.append(row[0])
status_value.append(row[1])
status_dict = dict(zip(status_name, status_value))
cursor.execute(sql_variables)
sql_variables = cursor.fetchall()
for row in sql_variables:
variables_name.append(row[0])
variables_value.append(row[1])
variables_dict = dict(zip(variables_name, variables_value))
cursor.execute(sql_user)
sql_user = cursor.fetchall()
for row in sql_user:
sql_user_list.append(row)
cursor.execute(table_audit)
table_audit = cursor.fetchall()
for row in table_audit:
table_audit_dict = {
"engine": row[0],
"data_size": str(row[1]),
"innodb_table_nu": str(row[2])
}
total_writes = int(status_dict.get('Com_delete')) + int(
status_dict.get('Com_insert')) + int(
status_dict.get('Com_update')) + int(
status_dict.get('Com_replace'))
total_reads = int(status_dict.get('Com_select'))
total_qps = int(total_writes + total_reads)
now_tps = int(status_dict.get('Com_insert')) + int(
status_dict.get('Com_update')) + int(
status_dict.get('Com_delete'))
now_qps = int(status_dict.get('Com_select')) + int(
status_dict.get('Com_insert')) + int(
status_dict.get('Com_update')) + int(
status_dict.get('Com_delete')) + int(
status_dict.get('Com_replace'))
audit_list = {
"Uptime":
status_dict.get('Uptime'),
"QPS":
now_qps - MysqlAudit.first_init_qps,
"TPS":
now_tps - MysqlAudit.first_init_tps,
#"QPS":int(status_dict.get('Com_select')) + int(status_dict.get('Com_insert')) + int(status_dict.get('Com_update')) + int(status_dict.get('Com_delete')) + int(status_dict.get('Com_replace')),
#"TPS":int(status_dict.get('Com_insert')) + int(status_dict.get('Com_update')) + int(status_dict.get('Com_delete')),
"Threads_running":
status_dict.get('Threads_running'),
"pct_slow_queries":
"%.2f%%" % (float(status_dict.get('Slow_queries')) /
float(status_dict.get('Questions')) * 100),
"pct_reads":
"%.2f%%" % (float(total_reads) / float(total_qps) * 100),
"pct_writes":
"%.2f%%" %
((float(1) - float(total_reads) / float(total_qps)) * 100),
"pct_connections_used":
"%.2f%%" %
(float(status_dict.get('Max_used_connections')) /
float(variables_dict.get('max_connections')) * 100),
"pct_aborted_connections":
"%.2f%%" % (float(status_dict.get('Aborted_connects')) /
float(status_dict.get('Connections')) * 100),
"Bytes_received":
str(int(status_dict.get('Bytes_received')) / 1024 / 1024) +
'M',
"Bytes_sent":
str(int(status_dict.get('Bytes_sent')) / 1024 / 1024) + 'M',
"Disk_temporary_tables":
"%.2f%%" %
(float(status_dict.get('Created_tmp_disk_tables')) /
(float(status_dict.get('Created_tmp_tables')) +
float(status_dict.get('Created_tmp_disk_tables'))) * 100),
"Thread_cache_hit_rate":
"%.2f%%" % (float(status_dict.get('Threads_created')) /
float(status_dict.get('Connections')) * 100),
"Table_cache_hit_rate":
"%.2f%%" %
(float(status_dict.get('Table_open_cache_hits')) /
(float(status_dict.get('Table_open_cache_hits')) +
float(status_dict.get('Table_open_cache_misses'))) * 100),
"Open_file_limit_used":
"%.2f%%" %
(float(status_dict.get('Open_files')) /
float(variables_dict.get('open_files_limit')) * 100),
"Sort_temporary_tables":
"%.2f%%" % (float(status_dict.get('Sort_merge_passes')) /
(float(status_dict.get('Sort_scan')) +
float(status_dict.get('Sort_range'))) * 100),
"joins_without_indexes":
int(status_dict.get('Select_range_check')) +
int(status_dict.get('Select_full_join')),
#"binlog_disk_cache":"%.2f%%" % (float(status_dict.get('Binlog_cache_disk_use')) / float(status_dict.get('Binlog_cache_use')) * 100),
"innodb_buffer_pool_size":
int(variables_dict.get('innodb_buffer_pool_size')) / 1024 /
1024 / 1024,
}
MysqlAudit.first_init_qps = now_qps
MysqlAudit.first_init_tps = now_tps
data = json.dumps(audit_list)
conn = dba_opers.get_mysql_connection(options.mysql_host,
options.mysql_user,
options.mysql_passwd,
options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "insert mysql_audit(audit,instance) values('%s','%s');" % (
MySQLdb.escape_string(data), host + '_' + str(port))
cursor.execute(sql)
except:
logging.error(traceback.format_exc())
finally:
cursor.close()
conn.close()
def do(self, args):
host = args.get('ip')
port = args.get('port')
db = args.get('dbname')
sql_text = args.get('sql_text')
sql_text = sql_text.replace('\"', '\'')
sql_text = sql_text.replace('`', '\`')
operate = args.get('operate')
try:
if operate == 1:
try:
cmd = 'ddl_manager -h %s -d %s -P %s -a "%s" -j %s' %(host,db,port,sql_text,operate)
logging.info('sql_check:'+'%s' % cmd)
res = create_process(cmd)
data = list(res)
log_path = res[1]
log_path = ''.join(log_path.split('\n'))
_log = open(log_path, "r")
_list = []
for line in _log.readlines():
line = line.strip()
_list.append(line)
_log.close()
data.append(_list)
if data[0] ==0:
data = {"code":data[0],"log_path":data[1],"log_message":' '.join(data[2][-1].split(' ')[4:])}
else:
data = {"code":data[0],"log_path":data[1],"log_message":' '.join(data[2][-1].split(' ')[4:])}
data = json.dumps(data)
except:
logging.error(traceback.format_exc())
elif operate == 0:
try:
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "INSERT INTO release_system.history_execute_sql(ip, dbname, status) VALUES('%s', '%s','running');" % (host, db)
cursor.execute(sql)
cursor.execute("select last_insert_id();")
last_insert_id = cursor.fetchall()
last_insert_id = last_insert_id[0][0]
cursor.close()
except:
logging.error(traceback.format_exc())
try:
cmd = 'ddl_manager -h %s -d %s -P %s -a "%s" -j %s' %(host,db,port,sql_text,operate)
logging.info('sql_execute:'+'%s' % cmd)
res = create_process(cmd)
data = list(res)
log_path = res[1]
log_path = ''.join(log_path.split('\n'))
_log = open(log_path, "r")
_list = []
for line in _log.readlines():
line = line.strip()
_list.append(line)
_log.close()
_json = json.dumps(_list)
if data[0] ==0:
ddl_sub_status = "ok"
else:
ddl_sub_status = "error"
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "update release_system.history_execute_sql set status = '%s' where id = %s" %(ddl_sub_status,last_insert_id)
cursor.execute(sql)
sql = "update release_system.history_execute_sql set description = '%s' where id = %s" %(MySQLdb.escape_string(_json),last_insert_id)
cursor.execute(sql)
cursor.close()
data.append(_list)
if data[0] ==0:
data = {"code":data[0],"log_path":data[1],"log_message":' '.join(data[2][-1].split(' ')[4:])}
else:
data = {"code":data[0],"log_path":data[1],"log_message":' '.join(data[2][-1].split(' ')[4:])}
data = json.dumps(data)
except:
logging.error(traceback.format_exc())
except:
logging.error(traceback.format_exc())
return data
def do(self, args):
try:
_list = []
_json = []
user = args.get('user')
password = args.get('password')
host = args.get('ip')
operate = args.get('operate')
port = args.get('port')
db = args.get('dbname')
sql_text = args.get('sql_text')
if operate =="execute":
try:
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "INSERT INTO release_system.history_execute_sql(ip, dbname, status) VALUES('%s', '%s','running');" % (host, db)
cursor.execute(sql)
cursor.execute("select last_insert_id();")
last_insert_id = cursor.fetchall()
last_insert_id = last_insert_id[0][0]
cursor.close()
except:
logging.error(traceback.format_exc())
try:
conn = dba_opers.get_mysql_connection(options.inception_host, options.inception_user, options.inception_passwd, options.inception_port, True)
cursor = conn.cursor()
sql='''/*--user=%s;--password=%s;--host=%s;--enable-%s;--port=%s;*/
inception_magic_start;
set names utf8;
use %s;
%s
inception_magic_commit;'''
#cursor.execute('SET NAMES utf8;')
cursor.execute(sql % (user,password,host,operate,port,db,sql_text))
#num_fields = len(cursor.description)
#field_names = [i[0] for i in cursor.description]
result = cursor.fetchall()
cursor.close()
data = json.dumps(result)
try:
for row in result:
if row[2] != 0:
ince_sub_status = "error"
break
else:
ince_sub_status = "ok"
conn = dba_opers.get_mysql_connection(options.mysql_host, options.mysql_user, options.mysql_passwd, options.mysql_port, True)
conn.select_db('release_system')
cursor = conn.cursor()
sql = "update release_system.history_execute_sql set status = '%s' where id = %s" %(ince_sub_status,last_insert_id)
cursor.execute(sql)
try:
for row in result:
_list.append('{"ID":"%s", "stage":"%s", "errlevel":"%s", "stagestatus":"%s", "errormessage":"%s", "SQL":"%s", "Affected_rows":"%s","sequence":"%s", "backup_dbname":"%s", "execute_time":"%s"}' %(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9]))
#for i in _list:
#_json.append(json.loads(i))
#_json = ast.literal_eval(json.dumps(_json))
#_json = simplejson.dumps(_json)
_json = json.dumps(_list)
sql = "update release_system.history_execute_sql set description = '%s' where id = %s" %(MySQLdb.escape_string(_json),last_insert_id)
cursor.execute(sql)
cursor.close()
except:
logging.error(traceback.format_exc())
except:
logging.error(traceback.format_exc())
except:
logging.error(traceback.format_exc())
elif operate =="check":
try:
conn = dba_opers.get_mysql_connection(options.inception_host, options.inception_user, options.inception_passwd, options.inception_port, True)
cursor = conn.cursor()
sql='''/*--user=%s;--password=%s;--host=%s;--enable-%s;--port=%s;*/
inception_magic_start;
set names utf8;
use %s;
%s
inception_magic_commit;'''
#cursor.execute('SET NAMES utf8;')
cursor.execute(sql % (user,password,host,operate,port,db,sql_text))
result = cursor.fetchall()
cursor.close()
data = json.dumps(result)
except:
logging.error(traceback.format_exc())
except:
logging.error(traceback.format_exc())
finally:
cursor.close()
return data
def do(self, args):
try:
ddl_sql_text = []
dml_sql_text = []
init_sql_text = []
user = "******"
password = "******"
operate = "check"
projectversion =args.get('projectversion')
host = args.get('dbip')
port = args.get('dbport')
db = args.get('dbname')
sql_text = args.get('sqls')
for i in sql_text:
if re.search( r'ddl(.*).sql', i.get('name')):
ddl_sql_text.append(i.get('sql'))
elif re.search( r'dml(.*).sql', i.get('name')):
dml_sql_text.append(i.get('sql'))
elif re.search( r'init(.*).sql', i.get('name')):
init_sql_text.append(i.get('sql'))
if len(ddl_sql_text):
for sql in ddl_sql_text:
sql = sql.replace('\"', '\'')
sql = sql.replace('`', '\`')
cmd = 'ddl_manager -h %s -d %s -P %s -a "%s" -j 1' %(host,db,port,sql)
logging.info('sql_check:'+'%s' % cmd)
res = create_process(cmd)
data = list(res)
log_path = res[1]
log_path = ''.join(log_path.split('\n'))
_log = open(log_path, "r")
_list = []
for line in _log.readlines():
line = line.strip()
_list.append(line)
_log.close()
data.append(_list)
if data[0] ==0:
data = {"code":data[0],"log_path":data[1],"log_message":' '.join(data[2][-1].split(' ')[4:])}
else:
data = {"code":data[0],"log_path":data[1],"log_message":' '.join(data[2][-1].split(' ')[4:])}
data = json.dumps(data)
try:
conn = dba_opers.get_mysql_connection(options.inception_host, options.inception_user, options.inception_passwd, options.inception_port, True)
cursor = conn.cursor()
sql='''/*--user=%s;--password=%s;--host=%s;--enable-%s;--port=%s;*/
inception_magic_start;
set names utf8;
use %s;
%s
inception_magic_commit;'''
#cursor.execute('SET NAMES utf8;')
cursor.execute(sql % (user,password,host,operate,port,db,sql_text))
result = cursor.fetchall()
cursor.close()
data = json.dumps(result)
except:
logging.error(traceback.format_exc())
except:
logging.error(traceback.format_exc())
finally:
cursor.close()
return data
