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