def delete_old_data(log): sql="select count(1),a.server_id,a.back_path,a.save_number,a.back_IP,a.back_ssh_port,a.back_ssh_user,a.back_ssh_passwd,b.db_name from redis_coldback_config a,redis_coldback_info b where a.server_id=b.server_id and b.suc_flag='y' and b.del_flag ='n' group by b.server_id,b.db_name;" result=func.mysql_query(sql) if result: for row in result: count=int(row[0]) server_id=str(row[1]) back_path=row[2] save_number=int(row[3]) back_IP=row[4] back_ssh_port=row[5] back_ssh_user=row[6] back_ssh_passwd=row[7] db_name=row[8] if count > save_number: del_num=count-save_number sql="select file_name from redis_coldback_info where server_id='"+server_id+"' and db_name='"+db_name+"' and del_flag='n' order by date limit "+str(del_num)+";" file_name_del=func.mysql_query(sql) if file_name_del: if back_ssh_passwd == "": log.error("delete old data:passwd not found,server_id:%s,back_IP:%s"%(server_id,back_IP)) continue for file in file_name_del: file_name=file[0] remote_file=back_path+"/"+file_name tCmd="/usr/bin/expect exec_user_cmd.exp "+back_IP+" "+back_ssh_port+" "+back_ssh_user+" '"+back_ssh_passwd+"' 'rm -f "+remote_file+";ls -l "+remote_file+";' 120" log.debug("tCmd:%s"%(tCmd)) (status, output) = commands.getstatusoutput(tCmd) log.debug("output:%s"%(output)) if output.find(remote_file)>=0 and (output.find("No such file or directory")>=0 or output.find("cannot access")>=0): sql="update redis_coldback_info set del_flag='y' where server_id='"+server_id+"' and db_name='"+db_name+"' and file_name='"+file_name+"';" log.debug("sql:%s"%(sql)) func.mysql_exec(sql,'') return
def main(): func.mysql_exec( "insert into mysql_status_history(server_id,application_id,connect,uptime,version,connections,active,create_time,YmdHi) select server_id,application_id,connect,uptime,version,connections,active,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status;", '') func.mysql_exec("delete from mysql_status", '') #get mysql servers list user = func.get_config('mysql_db', 'username') passwd = func.get_config('mysql_db', 'password') servers = func.mysql_query( "select id,host,port,application_id,status from servers where is_delete=0;" ) if servers: print("%s: check_mysql_status controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] application_id = row[3] status = row[4] if status <> 0: p = Process(target=check_mysql_status, args=(host, port, user, passwd, server_id, application_id)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_status controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def check_redis_status(host, port, passwd, server_id, application_id, frequency_monitor, log): #print host redis_client = redis.StrictRedis(host=host, port=port, db=0, password=passwd, socket_timeout=3) try: redis_info = redis_client.info() current_config = redis_client.config_get() current_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) check_redis_server_status(server_id, application_id, redis_info, current_config, current_time, log) check_redis_persistence_status(server_id, application_id, redis_info, current_config, current_time, log) check_redis_resource_status(server_id, application_id, redis_info, current_config, current_time, log) check_redis_run_status(server_id, application_id, redis_info, current_config, current_time, frequency_monitor, log) check_redis_keyspace(server_id, application_id, redis_info, current_time, log) check_redis_replication(server_id, application_id, redis_info, current_time, log) except Exception, e: redis_client = redis.StrictRedis(host=host, port=port, db=0, password=passwd, socket_timeout=3) try: redis_info = redis_client.info() current_config = redis_client.config_get() except Exception, e: connect = "fail" redis_version = "---" redis_mode = "---" multiplexing_api = "---" process_id = "0" run_id = "---" uptime = "0" config_file = "---" current_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) info = e.args[0] #print("info:%s"%(e.args[0])) sql = "insert into redis_server_status(server_id,application_id,connect,redis_version,redis_mode,multiplexing_api,process_id,run_id,uptime,config_file,info,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, connect, redis_version, redis_mode, multiplexing_api, process_id, run_id, uptime, config_file, info, current_time) func.mysql_exec(sql, param) log.error( "ERROR: Get redis into failed,host:%s port:%s passwd:%s error:%s" % (host, port, passwd, traceback.format_exc())) return
def check_mysql_process(host, port, user, passwd, server_id, application_id): try: datalist = [] connect = MySQLdb.connect(host=host, user=user, passwd=passwd, port=int(port), connect_timeout=2, charset='utf8') cur = connect.cursor() connect.select_db('information_schema') processlist = cur.execute( 'select * from information_schema.processlist where Command !="" and DB !="information_schema";' ) if processlist: for row in cur.fetchall(): result = row sql = "insert into mysql_process(server_id,application_id,pid,user,host,db,command,time,status,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]) func.mysql_exec(sql, param) except MySQLdb.Error, e: pass print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def main(): func.mysql_exec("delete from mysql_process", '') #get mysql servers list user = func.get_config('mysql_db', 'username') passwd = func.get_config('mysql_db', 'password') servers = func.mysql_query( "select id,host,port,application_id,status from servers where is_delete=0;" ) if servers: print("%s: check_mysql_process controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] application_id = row[3] status = row[4] if status <> 0: p = Process(target=check_mysql_process, args=(host, port, user, passwd, server_id, application_id)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_process controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def get_alarm_mysql_replcation(): sql = "select a.application_id,a.server_id,a.create_time,a.slave_io_run,a.slave_sql_run,a.delay,b.send_mail,b.alarm_repl_status,b.alarm_repl_delay,b.threshold_repl_delay from mysql_replication a,servers b where a.server_id=b.id and a.is_slave='1';" result = func.mysql_query(sql) if result <> 0: for line in result: application = line[0] server_id = line[1] create_time = line[2] slave_io_run = line[3] slave_sql_run = line[4] delay = line[5] send_mail = line[6] alarm_repl_status = line[7] alarm_repl_delay = line[8] threshold_repl_delay = line[9] if alarm_repl_status == 1: if (slave_io_run == "Yes") and (slave_sql_run == "Yes"): if alarm_repl_delay == "yes": if int(delay) >= int(threshold_repl_delay): sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" param = (application, server_id, create_time, 'mysql', 'delay', delay, 'warning', '数据库备库延时', send_mail) func.mysql_exec(sql, param) else: sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" param = (application, server_id, create_time, 'mysql', 'replication', 'IO Thread:' + slave_io_run + ',SQL Thread:' + slave_sql_run, 'error', '数据库同步进程停止', send_mail) func.mysql_exec(sql, param) else: pass
def check_mysql_widget(host,port,user,passwd,server_id): try: connect=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=2,charset='utf8') cur=connect.cursor() connect.select_db('information_schema') try: bigtable=cur.execute("SELECT TABLE_SCHEMA as 'DB',TABLE_NAME as 'TABLE',CONCAT(ROUND(( DATA_LENGTH + INDEX_LENGTH ) / ( 1024 * 1024 * 1024 ), 2), '') 'TOTAL' , TABLE_COMMENT as COMMENT FROM information_schema.TABLES ORDER BY DATA_LENGTH + INDEX_LENGTH DESC ;"); big_table_size = func.get_option('big_table_size') if bigtable: for row in cur.fetchall(): datalist=[] for r in row: datalist.append(r) result=datalist if result: table_size = float(string.atof(result[2])) if table_size >= int(big_table_size): sql="insert into mysql_widget_bigtable(server_id,db_name,table_name,table_size,table_comment) values(%s,%s,%s,%s,%s);" param=(server_id,result[0],result[1],result[2],result[3]) func.mysql_exec(sql,param) except : pass cur.close() connect.close() exit except MySQLdb.Error,e: pass print "Mysql Error %d: %s" %(e.args[0],e.args[1])
def check_redis_run_status(server_id,application_id,redis_info,current_config,current_time,frequency_monitor,log): try: max_clients=current_config.get("maxclients","10000") connected_clients=redis_info.get("connected_clients","0") blocked_clients=redis_info.get("blocked_clients","0") last_total_connections_received=redis_info.get("total_connections_received","0") last_total_commands_processed=redis_info.get("total_commands_processed","0") last_total_net_input_bytes=redis_info.get("total_net_input_bytes",'0') last_total_net_output_bytes=redis_info.get("total_net_output_bytes",'0') last_rejected_connections=redis_info.get("rejected_connections","0") last_expired_keys=redis_info.get("expired_keys","0") last_evicted_keys=redis_info.get("evicted_keys","0") last_keyspace_hits=redis_info.get("keyspace_hits","0") last_keyspace_misses=redis_info.get("keyspace_misses","0") sql="insert into redis_run_status(server_id,application_id,max_clients,connected_clients,blocked_clients,last_total_connections_received,last_total_commands_processed,last_total_net_input_bytes,last_total_net_output_bytes,last_rejected_connections,last_expired_keys,last_evicted_keys,last_keyspace_hits,last_keyspace_misses,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,max_clients,connected_clients,blocked_clients,last_total_connections_received,last_total_commands_processed,last_total_net_input_bytes,last_total_net_output_bytes,last_rejected_connections,last_expired_keys,last_evicted_keys,last_keyspace_hits,last_keyspace_misses,current_time) func.mysql_exec(sql,param) #change time from s to min --- /min /min k/s k/s /min /min /min /min frequency_monitor=int(frequency_monitor) frequency_monitor_min=int(frequency_monitor/60) sql="update redis_run_status a,redis_run_status_last b set a.total_connections_received=(a.last_total_connections_received-b.last_total_connections_received)/%s,a.total_commands_processed=(a.last_total_commands_processed-b.last_total_commands_processed)/%s,a.total_net_input_bytes=(a.last_total_net_input_bytes-b.last_total_net_input_bytes)/1024/%s,a.total_net_output_bytes=(a.last_total_net_output_bytes-b.last_total_net_output_bytes)/1024/%s,a.rejected_connections=(a.last_rejected_connections-b.last_rejected_connections)/%s,a.expired_keys=(a.last_expired_keys-b.last_expired_keys)/%s,a.evicted_keys=(a.last_evicted_keys-b.last_evicted_keys)/%s,a.keyspace_hits=(a.last_keyspace_hits-b.last_keyspace_hits)/%s,a.keyspace_misses=(a.last_keyspace_misses-b.last_keyspace_misses)/%s,a.keyspace_hit_rate=ifnull(100*a.keyspace_hits/(a.keyspace_hits+a.keyspace_misses),100) where a.server_id=b.server_id and a.application_id=b.application_id" #print frequency_monitor #print frequency_monitor_min param=(frequency_monitor_min,frequency_monitor_min,frequency_monitor,frequency_monitor,frequency_monitor_min,frequency_monitor_min,frequency_monitor_min,frequency_monitor_min,frequency_monitor_min) func.mysql_exec(sql,param) except Exception,e: log.error("ERROR: check_redis_run_status failed %s" % (traceback.format_exc())) return
def get_alarm_redis_run_status(): sql = "select a.application_id,a.server_id,a.create_time,a.max_clients,a.connected_clients,ifnull(round(100*a.connected_clients/a.max_clients,2),0),c.threshold_connections from redis_run_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_connections=1;" result = func.mysql_query(sql) if result <> 0: for line in result: application_id = line[0] server_id = line[1] create_time = line[2] max_clients = line[3] connected_clients = line[4] connection_use_rate = line[5] threshold_connections = line[6] if int(connection_use_rate) >= int(threshold_connections): sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message = "WARN:Redis连接数过多,已用数" + str( connected_clients) + ",使用率" + str( connection_use_rate ) + "%>=" + threshold_connections + "%" param = (application_id, server_id, create_time, 'redis', 'connections', connected_clients, 'warning', message, '1') func.mysql_exec(sql, param) else: pass
def check_redis_persistence_status(server_id,application_id,redis_info,current_config,current_time,log): try: tmp_rdb_enabled=current_config.get("save","---") if tmp_rdb_enabled=="---" or tmp_rdb_enabled=="": rdb_enabled="0" else: rdb_enabled="1" rdb_dbfilename=current_config.get("dbfilename","---") rdb_changes_since_last_save=redis_info.get("rdb_changes_since_last_save","0") rdb_last_save_time=redis_info.get("rdb_last_save_time","0") rdb_last_bgsave_status=redis_info.get("rdb_last_bgsave_status","---") rdb_last_bgsave_time_sec=redis_info.get("rdb_last_bgsave_time_sec","0") aof_enabled=redis_info.get("aof_enabled","---") aof_last_rewrite_time_sec=redis_info.get("aof_last_rewrite_time_sec","0") aof_last_bgrewrite_status=redis_info.get("aof_last_bgrewrite_status","---") aof_last_write_status=redis_info.get("aof_last_write_status","---") if int(aof_enabled)==0 or aof_enabled=="---": aof_current_size="0" else: aof_current_size=redis_info.get("aof_current_size","0") sql="insert into redis_persistence_status(server_id,application_id,rdb_enabled,rdb_dbfilename,rdb_changes_since_last_save,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,aof_enabled,aof_last_rewrite_time_sec,aof_last_bgrewrite_status,aof_last_write_status,aof_current_size,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,rdb_enabled,rdb_dbfilename,rdb_changes_since_last_save,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,aof_enabled,aof_last_rewrite_time_sec,aof_last_bgrewrite_status,aof_last_write_status,aof_current_size,current_time) func.mysql_exec(sql,param) except Exception,e: log.error("ERROR: check_redis_persistence_status failed %s" % (traceback.format_exc())) return
def check_mysql_widget(host,port,user,passwd,server_id): try: connect=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=2,charset='utf8') cur=connect.cursor() connect.select_db('information_schema') try: bigtable=cur.execute("SELECT table_schema as 'DB',table_name as 'TABLE',CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), '') 'TOTAL' , table_comment as COMMENT FROM information_schema.TABLES ORDER BY data_length + index_length DESC ;"); big_table_size = func.get_option('big_table_size') if bigtable: for row in cur.fetchall(): datalist=[] for r in row: datalist.append(r) result=datalist if result: table_size = float(string.atof(result[2])) if table_size >= int(big_table_size): sql="insert into mysql_widget_bigtable(server_id,db_name,table_name,table_size,table_comment) values(%s,%s,%s,%s,%s);" param=(server_id,result[0],result[1],result[2],result[3]) func.mysql_exec(sql,param) except : pass cur.close() connect.close() exit except MySQLdb.Error,e: pass print "Mysql Error %d: %s" %(e.args[0],e.args[1])
def main(): user = func.get_config("mysql_db", "username") passwd = func.get_config("mysql_db", "password") killed_pids = func.mysql_query( "select p.pid,s.host,s.port from mysql_process_killed p left join servers s on p.server_id=s.id;" ) if killed_pids: print ( "%s: admin_mysql_kill_process controller started." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),) ) plist = [] for row in killed_pids: pid = row[0] host = row[1] port = row[2] p = Process(target=admin_mysql_kill_process, args=(host, port, user, passwd, pid)) plist.append(p) p.start() for p in plist: p.join() print ( "%s: admin_mysql_kill_process controller finished." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),) ) func.mysql_exec("delete from mysql_process_killed", "")
def main(): func.mysql_exec("truncate table mysql_widget_connect", "") # get mysql servers list user = func.get_config("mysql_db", "username") passwd = func.get_config("mysql_db", "password") servers = func.mysql_query("select id,host,port from servers where is_delete=0;") if servers: print ( "%s: check_mysql_widget_connect controller started." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),) ) plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] p = Process(target=check_mysql_widget, args=(host, port, user, passwd, server_id)) plist.append(p) p.start() for p in plist: p.join() print ( "%s: check_mysql_widget_connect controller finished." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),) )
def get_alarm_mysql_replcation(): sql="select a.application,a.server_id,a.host,a.port,a.create_time,a.slave_io_run,a.slave_sql_run,a.delay,b.send_mail,b.alarm_repl_status,b.alarm_repl_delay,b.threshold_repl_delay from mysql_replication a,servers b where a.server_id=b.id and a.slave='1';" result=func.mysql_query(sql) if result <> 0: for line in result: application=line[0] server_id=line[1] host=line[2] port=line[3] create_time=line[4] slave_io_run=line[5] slave_sql_run=line[6] delay=line[7] send_mail=line[8] alarm_repl_status=line[9] alarm_repl_delay=line[10] threshold_repl_delay=line[11] if alarm_repl_status==1: if (slave_io_run== "Yes") and (slave_sql_run== "Yes"): if alarm_repl_delay=="yes": if int(delay)>=int(threshold_repl_delay): sql="insert into alarm(application,server_id,host,port,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param=(application,server_id,host,port,create_time,'mysql','delay',delay,'warning','数据库备库延时',send_mail) func.mysql_exec(sql,param) else: sql="insert into alarm(application,server_id,host,port,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param=(application,server_id,host,port,create_time,'mysql','replication','IO Thread:'+slave_io_run+',SQL Thread:'+slave_sql_run,'error','数据库同步进程停止',send_mail) func.mysql_exec(sql,param) else: pass
def get_alarm_mysql_slowquerys(): sql = "select b.application_id,a.server_id,a.modify_time,(ts_cnt_sum-ts_cnt_sum_last) as ts_cnt,if((ts_cnt_sum-ts_cnt_sum_last)>0,(Query_time_sum-Query_time_sum_last)/(ts_cnt_sum-ts_cnt_sum_last),'0') as query_time_avg,b.send_mail,b.alarm_slow_querys,b.threshold_slow_querys from mysql_slow_query_status a,servers b where a.server_id=b.id and b.send_mail='1';" result = func.mysql_query(sql) if result <> 0: for line in result: application_id = line[0] server_id = line[1] create_time = line[2] ts_cnt = line[3] query_time_avg = line[4] send_mail = line[5] alarm_slow_querys = line[6] threshold_slow_querys = line[7] if int(alarm_slow_querys) == 1: if int(ts_cnt) >= int(threshold_slow_querys): sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message = "WARN:慢查询语句过多 " + str(ts_cnt) + ">=" + str( threshold_slow_querys) param = (application_id, server_id, create_time, 'mysql', 'slow_query', ts_cnt, 'warning', message, send_mail) func.mysql_exec(sql, param) else: pass
def main(): func.mysql_exec( "insert into mysql_replication_history(server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication;", '') func.mysql_exec("delete from mysql_replication", '') #get mysql servers list user = func.get_config('mysql_db', 'username') passwd = func.get_config('mysql_db', 'password') servers = func.mysql_query( "select id,host,port,application_id,status from servers where is_delete=0;" ) if servers: print("%s: check_mysql_replication controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] application_id = row[3] status = row[4] if status <> 0: p = Process(target=check_mysql_replication, args=(host, port, user, passwd, server_id, application_id)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_replication controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def main(): func.mysql_exec("insert into mysql_status_history(server_id,application_id,connect,uptime,version,connections,active,create_time,YmdHi) select server_id,application_id,connect,uptime,version,connections,active,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status;",'') func.mysql_exec("delete from mysql_status",'') #get mysql servers list user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') servers=func.mysql_query("select id,host,port,application_id,status from servers where is_delete=0;") if servers: print("%s: check_mysql_status controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] application_id=row[3] status=row[4] if status <> 0: p = Process(target = check_mysql_status, args = (host,port,user,passwd,server_id,application_id)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_status controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def main(): func.mysql_exec( "insert into mysql_replication_history(server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication;", "", ) func.mysql_exec("delete from mysql_replication", "") # get mysql servers list user = func.get_config("mysql_db", "username") passwd = func.get_config("mysql_db", "password") servers = func.mysql_query("select id,host,port,application_id,status from servers where is_delete=0;") if servers: print ( "%s: check_mysql_replication controller started." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),) ) plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] application_id = row[3] status = row[4] if status <> 0: p = Process(target=check_mysql_replication, args=(host, port, user, passwd, server_id, application_id)) plist.append(p) p.start() for p in plist: p.join() print ( "%s: check_mysql_replication controller finished." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),) )
def main(): #get idc type idc_type = func.get_config('idc', 'idc_type') func.mysql_exec( "insert into linux_resource_history select * from linux_resource where idc_type='" + str(idc_type) + "'", '') func.mysql_exec( "delete from linux_resource where idc_type='" + str(idc_type) + "'", '') linux_servers_ip = func.get_config('linux_server', 'server_ip') servers = linux_servers_ip.split("|") if servers: print("%s: check_server_resource controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for ip in servers: if ip <> '': print ip p = Process(target=check_server_resource, args=(ip, )) plist.append(p) p.start() for p in plist: p.join() print("%s: check_server_resource controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def get_alarm_redis_replication(): sql = "select a.application_id,a.server_id,a.create_time,a.master_link_status,a.delay,c.alarm_repl_delay,c.threshold_repl_delay from redis_replication a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and a.is_slave=1 and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_repl_status=1;" result = func.mysql_query(sql) if result <> 0: for line in result: application_id = line[0] server_id = line[1] create_time = line[2] master_link_status = line[3] delay = line[4] alarm_repl_delay = line[5] threshold_repl_delay = line[6] if master_link_status != "up": sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message = "ERROR:Redis复制停止 master_link_status:" + master_link_status param = (application_id, server_id, create_time, 'redis', 'replication', master_link_status, 'error', message, '1') func.mysql_exec(sql, param) else: if (alarm_repl_delay == "1") and (int(delay) >= int(threshold_repl_delay)): sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message = "WARN:Redis复制延迟过大 " + delay + ">=" + threshold_repl_delay param = (application_id, server_id, create_time, 'redis', 'delay', delay, 'warning', message, '1') func.mysql_exec(sql, param) else: pass
def get_alarm_redis_replication(): sql="select a.application_id,a.server_id,a.create_time,a.master_link_status,a.delay,c.alarm_repl_delay,c.threshold_repl_delay from redis_replication a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and a.is_slave=1 and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_repl_status=1;" result=func.mysql_query(sql) if result <> 0: for line in result: application_id=line[0] server_id=line[1] create_time=line[2] master_link_status=line[3] delay=line[4] alarm_repl_delay=line[5] threshold_repl_delay=line[6] if master_link_status!="up": sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message="ERROR:Redis复制停止 master_link_status:"+master_link_status param=(application_id,server_id,create_time,'redis','replication',master_link_status,'error',message,'1') func.mysql_exec(sql,param) else: if (alarm_repl_delay=="1") and (int(delay)>=int(threshold_repl_delay)): sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message="WARN:Redis复制延迟过大 "+delay+">="+threshold_repl_delay param=(application_id,server_id,create_time,'redis','delay',delay,'warning',message,'1') func.mysql_exec(sql,param) else: pass
def check_mysql_widget(host,port,user,passwd,server_id,idc_type='ts'): try: connect=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=2,charset='utf8') cur=connect.cursor() connect.select_db('information_schema') totalresult=cur.execute("select SUBSTRING_INDEX(host,':',1) as connect_server, user connect_user,db connect_db, count(SUBSTRING_INDEX(host,':',1)) as connect_count from information_schema.processlist where db is not null and db!='information_schema' and db !='performance_schema' group by connect_server ;"); if totalresult: for row in cur.fetchall(): datalist=[] for r in row: datalist.append(r) result=datalist if result: connect_server=result[0] connect_user=result[1] connect_db=result[2] connect_count=result[3] sql="insert into mysql_widget_connect(server_id,connect_server,connect_user,connect_db,connect_count,idc_type) values(%s,%s,%s,%s,%s,%s);" param =(server_id,connect_server,connect_user,connect_db,connect_count,idc_type) func.mysql_exec(sql,param) cur.close() connect.close() exit except MySQLdb.Error,e: pass print "Mysql Error %d: %s" %(e.args[0],e.args[1])
def job_task(ip,port,user,keyfile,passwd): commands = open('./servers/linux_commands.list') result = [ip] for cmd in commands: r=ssh_cmd(ip,port,user,keyfile,passwd,cmd) result.append(r.strip('\r').strip('\n').strip('\r')) #insert data to mysql database sql="insert into linux_resource(ip,hostname,kernel,digit,load1,load5,load15,disk_use_root,disk_use_home,disk_use_data,mem_total,mem_use) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7],result[8],result[9],result[10],result[11]) func.mysql_exec(sql,param)
def check_mysql_status(host, port, user, passwd, server_id, application_id): datalist = [] try: connect = MySQLdb.connect(host=host, user=user, passwd=passwd, port=int(port), connect_timeout=2, charset='utf8') datalist.append('success') cur = connect.cursor() connect.select_db('mysql') #get uptime uptime = cur.execute('SHOW GLOBAL STATUS LIKE "Uptime";') uptime_data = cur.fetchone() datalist.insert(4, int(uptime_data[1])) #get version version = cur.execute('select version();') version_data = cur.fetchone() datalist.insert(5, version_data[0]) connections = cur.execute( 'select id from information_schema.processlist;') datalist.append(connections) active = cur.execute( 'select id from information_schema.processlist where command !="Sleep";' ) datalist.append(int(active)) cur.close() connect.close() result = datalist if result: sql = "insert into mysql_status(server_id,application_id,connect,uptime,version,connections,active) values(%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, result[0], result[1], result[2], result[3], result[4]) func.mysql_exec(sql, param) except MySQLdb.Error, e: pass print "Mysql Error %d: %s" % (e.args[0], e.args[1]) datalist.append('fail') datalist.append('0') datalist.append('---') datalist.append('---') datalist.append('---') result = datalist if result: sql = "insert into mysql_status(server_id,application_id,connect,uptime,version,connections,active) values(%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, result[0], result[1], result[2], result[3], result[4]) func.mysql_exec(sql, param)
def main(): #get idc type idc_type = func.get_config('idc', 'idc_type') if idc_type != "yf": print "ide_type error" return #get mysql servers list user = func.get_config('mysql_db', 'username') passwd = func.get_config('mysql_db', 'password') #get allow host server_list = func.get_config('mysql_db', 'allow_server_list') func.mysql_exec( "insert into mysql_replication_history(idc_type,server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select '" + str(idc_type) + "',server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication where idc_type='" + str(idc_type) + "';", '') func.mysql_exec( "delete from mysql_replication where idc_type='" + str(idc_type) + "'", '') servers = func.mysql_query( "select id,host,port,application_id,status,idc_type from servers where idc_type='" + str(idc_type) + "' and is_delete=0;") if servers: print("%s: check_mysql_replication controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] application_id = row[3] status = row[4] idc_type = row[5] if host not in server_list: print "Deny host:" + str(host) continue if status <> 0: p = Process(target=check_mysql_replication, args=(host, port, user, passwd, server_id, application_id, idc_type)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_replication controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def check_redis_replication(server_id, application_id, redis_info, current_time, log): try: role = redis_info.get("role", "---") if (role == "master"): is_master = "1" is_slave = "0" master_host = "---" master_port = "---" master_link_status = "---" master_last_io_seconds_ago = "---" master_repl_offset = redis_info.get("master_repl_offset", '0') connected_slaves = redis_info.get("connected_slaves", '0') slave_repl_offset = "---" slave_read_only = "0" slave_priority = "---" master_link_down_since_seconds = "---" delay = "---" else: is_slave = "1" master_host = redis_info.get("master_host", "---") master_port = redis_info.get("master_port", "---") master_link_status = redis_info.get("master_link_status", "---") master_last_io_seconds_ago = redis_info.get( "master_last_io_seconds_ago", "---") master_repl_offset = redis_info.get("master_repl_offset", '0') connected_slaves = redis_info.get("connected_slaves", '0') is_master = "0" if int(connected_slaves) > 0: is_master = "1" slave_repl_offset = redis_info.get("slave_repl_offset", "---") slave_read_only = redis_info.get("slave_read_only", '0') slave_priority = redis_info.get("slave_priority", "---") if master_link_status == "down": master_link_down_since_seconds = redis_info.get( "master_link_down_since_seconds", "---") else: master_link_down_since_seconds = "0" delay = "0" sql = "insert into redis_replication(server_id,application_id,is_master,is_slave,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_repl_offset,connected_slaves,slave_repl_offset,slave_read_only,slave_priority,master_link_down_since_seconds,delay,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, is_master, is_slave, master_host, master_port, master_link_status, master_last_io_seconds_ago, master_repl_offset, connected_slaves, slave_repl_offset, slave_read_only, slave_priority, master_link_down_since_seconds, delay, current_time) func.mysql_exec(sql, param) except Exception, e: log.error("ERROR: check_redis_replication failed %s" % (traceback.format_exc())) return
def main(): #get idc type idc_type = func.get_config('idc', 'idc_type') if idc_type != "yf": print "ide_type error" return #get mysql servers list user = func.get_config('mysql_db', 'username') passwd = func.get_config('mysql_db', 'password') #get allow host server_list = func.get_config('mysql_db', 'allow_server_list') #print "delete from mysql_widget_hit_rate where idc_type='"+str(idc_type)+"'" #func.mysql_exec("truncate table mysql_widget_hit_rate",'') func.mysql_exec( "delete from mysql_widget_hit_rate where idc_type='" + str(idc_type) + "'", '') #print "select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;" servers = func.mysql_query( "select id,host,port,status,idc_type from servers where idc_type='" + str(idc_type) + "' and is_delete=0;") if servers: print("%s: check_mysql_widget_hit_rate controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] status = row[3] idc_type = row[4] if host not in server_list: print "Deny host:" + str(host) continue if status <> 0: p = Process(target=check_mysql_widget, args=(host, port, user, passwd, server_id, idc_type)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_widget_hit_rate controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def main(): print("%s: controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); func.mysql_exec("delete from linux_resource",'') hosts = open('./servers/linux_servers.list'); plist = [] for host in hosts: if host: ip,port,user,keyfile,passwd = host.split(":") datalist = [ip] p = Process(target = job_task, args = (ip,port,user,keyfile,passwd)) plist.append(p) #print plist p.start(); for p in plist: p.join(); print("%s: controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def check_mysql_process(host,port,user,passwd,server_id,application_id): try: datalist=[] connect=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=2,charset='utf8') cur=connect.cursor() connect.select_db('information_schema') processlist=cur.execute('select * from information_schema.processlist where Command !="" and DB !="information_schema";') if processlist: for row in cur.fetchall(): result=row sql="insert into mysql_process(server_id,application_id,pid,user,host,db,command,time,status,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7]) func.mysql_exec(sql,param) except MySQLdb.Error,e: pass print "Mysql Error %d: %s" %(e.args[0],e.args[1])
def check_redis_resource_status(server_id,application_id,redis_info,current_config,current_time,log): try: max_memory=current_config.get("maxmemory","0") used_memory=redis_info.get("used_memory","0") used_memory_rss=redis_info.get("used_memory_rss","0") used_memory_peak=redis_info.get("used_memory_peak","0") mem_fragmentation_ratio=redis_info.get("mem_fragmentation_ratio","0") used_cpu_sys=redis_info.get("used_cpu_sys","0") used_cpu_user=redis_info.get("used_cpu_user","0") used_cpu_sys_children=redis_info.get("used_cpu_sys_children","0") used_cpu_user_children=redis_info.get("used_cpu_user_children","0") sql="insert into redis_resource_status(server_id,application_id,max_memory,used_memory,used_memory_rss,used_memory_peak,mem_fragmentation_ratio,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,max_memory,used_memory,used_memory_rss,used_memory_peak,mem_fragmentation_ratio,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children,current_time) func.mysql_exec(sql,param) except Exception,e: log.error("ERROR: check_redis_resource_status failed %s" % (traceback.format_exc())) return
def check_redis_keyspace(server_id, application_id, redis_info, current_time, log): try: #eg: db0:keys=315,expires=0,avg_ttl=0 #databases = [] for key in sorted(redis_info.keys()): if key.startswith("db"): database = redis_info.get(key) # database['name'] = key # databases.append(database) keys = database.get("keys") expires = database.get("expires") avg_ttl = database.get("avg_ttl") persists = database.get("keys") - database.get("expires") sql = "insert into redis_keyspace(server_id,application_id,db_name,db_keys,expires,persists,avg_ttl,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, key, keys, expires, persists, avg_ttl, current_time) func.mysql_exec(sql, param) ##sum #expires = 0 #persists = 0 #for database in databases: # expires += database.get("expires") # persists += database.get("keys") - database.get("expires") # for i in range(0,15): # db_name="db"+str(i) # try: # db_info=redis_info.get(db_name) # if db_info <> "": # keys=db_info["keys"] # expires=db_info["expires"] # avg_ttl=db_info["avg_ttl"] # # keys=db_info[db_info.find("keys=")+5 : db_info.find(",expires=")] # # expires=db_info[db_info.find("expires=")+8 : db_info.find(",avg_ttl=")] # # avg_ttl=db_info[db_info.find("avg_ttl=")+8 :] # sql="insert into redis_keyspace(server_id,application_id,db_name,db_keys,expires,avg_ttl,create_time) values(%s,%s,%s,%s,%s,%s,%s)" # param=(server_id,application_id,db_name,keys,expires,avg_ttl,current_time) # func.mysql_exec(sql,param) # except Exception,e: # pass except Exception, e: log.error("ERROR: check_redis_keyspace failed %s" % (traceback.format_exc())) return
def check_slow_query(host,port,user,passwd,application,server_id): try: datalist=[server_id,application,host,port] connect=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),charset='utf8') cur=connect.cursor() connect.select_db('information_schema') processlist=cur.execute('select * from information_schema.processlist where db!="information_schema" and user!="system user" and command !="Sleep" and info like "select%" and TIME>="2" ;') if processlist: for row in cur.fetchall(): for r in row: datalist.append(r) result=datalist sql="insert into mysql_slow_query(server_id,application,host,port,query_id,query_user,query_host,query_db,query_command,query_time,query_status,query_sql) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7],result[8],result[9],result[10],result[11]) func.mysql_exec(sql,param) except MySQLdb.Error,e: pass
def check_redis_server_status(server_id,application_id,redis_info,current_config,current_time,log): try: connect="success" dir=current_config.get("dir","---") redis_version=redis_info.get("redis_version","---") redis_mode=redis_info.get("redis_mode","---") multiplexing_api=redis_info.get("multiplexing_api","---") process_id=redis_info.get("process_id","0") run_id=redis_info.get("run_id","---") uptime=redis_info.get("uptime_in_seconds","0") config_file=redis_info.get("config_file","---") sql="insert into redis_server_status(server_id,application_id,connect,redis_version,redis_mode,multiplexing_api,process_id,run_id,uptime,config_file,dir,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,connect,redis_version,redis_mode,multiplexing_api,process_id,run_id,uptime,config_file,dir,current_time) func.mysql_exec(sql,param) except Exception,e: log.error("ERROR: check_redis_server_status failed %s" % (traceback.format_exc())) return
def main(): #get idc type idc_type = func.get_config('idc', 'idc_type') if idc_type != "yf": print "ide_type error" return #get mysql servers list user = func.get_config('mysql_db', 'username') passwd = func.get_config('mysql_db', 'password') #get allow host server_list = func.get_config('mysql_db', 'allow_server_list') killed_pids = func.mysql_query( "select p.pid,s.host,s.port,s.status,s.idc_type from mysql_process_killed p left join servers s on p.server_id=s.id where s.idc_type='" + str(idc_type) + "';") if killed_pids: print("%s: admin_mysql_kill_process controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for row in killed_pids: print row pid = row[0] host = row[1] port = row[2] status = row[3] idc_type = row[4] if host not in server_list: print "Deny host:" + str(host) continue if status <> 0: p = Process(target=admin_mysql_kill_process, args=(host, port, user, passwd, pid, idc_type)) plist.append(p) p.start() for p in plist: p.join() print("%s: admin_mysql_kill_process controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) func.mysql_exec( "delete from mysql_process_killed where idc_type='" + str(idc_type) + "'", '')
def main(): func.mysql_exec("insert into linux_resource_history select * from linux_resource",'') func.mysql_exec("delete from linux_resource",'') linux_servers_ip = func.get_config('linux_server','server_ip') servers=linux_servers_ip.split("|") if servers: print("%s: check_server_resource controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for ip in servers: if ip <> '': print ip p = Process(target = check_server_resource, args=(ip,)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_server_resource controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def check_redis_keyspace(server_id,application_id,redis_info,current_time,log): try: #eg: db0:keys=315,expires=0,avg_ttl=0 #databases = [] for key in sorted(redis_info.keys()): if key.startswith("db"): database = redis_info.get(key) # database['name'] = key # databases.append(database) keys=database.get("keys") expires=database.get("expires") avg_ttl=database.get("avg_ttl") persists=database.get("keys") - database.get("expires") sql="insert into redis_keyspace(server_id,application_id,db_name,db_keys,expires,persists,avg_ttl,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,key,keys,expires,persists,avg_ttl,current_time) func.mysql_exec(sql,param) ##sum #expires = 0 #persists = 0 #for database in databases: # expires += database.get("expires") # persists += database.get("keys") - database.get("expires") # for i in range(0,15): # db_name="db"+str(i) # try: # db_info=redis_info.get(db_name) # if db_info <> "": # keys=db_info["keys"] # expires=db_info["expires"] # avg_ttl=db_info["avg_ttl"] # # keys=db_info[db_info.find("keys=")+5 : db_info.find(",expires=")] # # expires=db_info[db_info.find("expires=")+8 : db_info.find(",avg_ttl=")] # # avg_ttl=db_info[db_info.find("avg_ttl=")+8 :] # sql="insert into redis_keyspace(server_id,application_id,db_name,db_keys,expires,avg_ttl,create_time) values(%s,%s,%s,%s,%s,%s,%s)" # param=(server_id,application_id,db_name,keys,expires,avg_ttl,current_time) # func.mysql_exec(sql,param) # except Exception,e: # pass except Exception,e: log.error("ERROR: check_redis_keyspace failed %s" % (traceback.format_exc())) return
def check_redis_run_status(server_id, application_id, redis_info, current_config, current_time, frequency_monitor, log): try: max_clients = current_config.get("maxclients", "10000") connected_clients = redis_info.get("connected_clients", "0") blocked_clients = redis_info.get("blocked_clients", "0") last_total_connections_received = redis_info.get( "total_connections_received", "0") last_total_commands_processed = redis_info.get( "total_commands_processed", "0") last_total_net_input_bytes = redis_info.get("total_net_input_bytes", '0') last_total_net_output_bytes = redis_info.get("total_net_output_bytes", '0') last_rejected_connections = redis_info.get("rejected_connections", "0") last_expired_keys = redis_info.get("expired_keys", "0") last_evicted_keys = redis_info.get("evicted_keys", "0") last_keyspace_hits = redis_info.get("keyspace_hits", "0") last_keyspace_misses = redis_info.get("keyspace_misses", "0") sql = "insert into redis_run_status(server_id,application_id,max_clients,connected_clients,blocked_clients,last_total_connections_received,last_total_commands_processed,last_total_net_input_bytes,last_total_net_output_bytes,last_rejected_connections,last_expired_keys,last_evicted_keys,last_keyspace_hits,last_keyspace_misses,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, max_clients, connected_clients, blocked_clients, last_total_connections_received, last_total_commands_processed, last_total_net_input_bytes, last_total_net_output_bytes, last_rejected_connections, last_expired_keys, last_evicted_keys, last_keyspace_hits, last_keyspace_misses, current_time) func.mysql_exec(sql, param) #change time from s to min --- /min /min k/s k/s /min /min /min /min frequency_monitor = int(frequency_monitor) frequency_monitor_min = int(frequency_monitor / 60) sql = "update redis_run_status a,redis_run_status_last b set a.total_connections_received=(a.last_total_connections_received-b.last_total_connections_received)/%s,a.total_commands_processed=(a.last_total_commands_processed-b.last_total_commands_processed)/%s,a.total_net_input_bytes=(a.last_total_net_input_bytes-b.last_total_net_input_bytes)/1024/%s,a.total_net_output_bytes=(a.last_total_net_output_bytes-b.last_total_net_output_bytes)/1024/%s,a.rejected_connections=(a.last_rejected_connections-b.last_rejected_connections)/%s,a.expired_keys=(a.last_expired_keys-b.last_expired_keys)/%s,a.evicted_keys=(a.last_evicted_keys-b.last_evicted_keys)/%s,a.keyspace_hits=(a.last_keyspace_hits-b.last_keyspace_hits)/%s,a.keyspace_misses=(a.last_keyspace_misses-b.last_keyspace_misses)/%s,a.keyspace_hit_rate=ifnull(100*a.keyspace_hits/(a.keyspace_hits+a.keyspace_misses),100) where a.server_id=b.server_id and a.application_id=b.application_id" #print frequency_monitor #print frequency_monitor_min param = (frequency_monitor_min, frequency_monitor_min, frequency_monitor, frequency_monitor, frequency_monitor_min, frequency_monitor_min, frequency_monitor_min, frequency_monitor_min, frequency_monitor_min) func.mysql_exec(sql, param) except Exception, e: log.error("ERROR: check_redis_run_status failed %s" % (traceback.format_exc())) return
def check_mysql_status(host,port,user,passwd,server_id,application_id): datalist=[] try: connect=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=2,charset='utf8') datalist.append('success') cur=connect.cursor() connect.select_db('mysql') #get uptime uptime=cur.execute('SHOW GLOBAL STATUS LIKE "Uptime";'); uptime_data = cur.fetchone() datalist.insert(4,int(uptime_data[1])) #get version version=cur.execute('select version();') version_data=cur.fetchone() datalist.insert(5,version_data[0]) connections=cur.execute('select id from information_schema.processlist;') datalist.append(connections) active=cur.execute('select id from information_schema.processlist where command !="Sleep";') datalist.append(int(active)) cur.close() connect.close() result=datalist if result: sql="insert into mysql_status(server_id,application_id,connect,uptime,version,connections,active) values(%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,result[0],result[1],result[2],result[3],result[4]) func.mysql_exec(sql,param) except MySQLdb.Error,e: pass print "Mysql Error %d: %s" %(e.args[0],e.args[1]) datalist.append('fail') datalist.append('0') datalist.append('---') datalist.append('---') datalist.append('---') result=datalist if result: sql="insert into mysql_status(server_id,application_id,connect,uptime,version,connections,active) values(%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,result[0],result[1],result[2],result[3],result[4]) func.mysql_exec(sql,param)
def check_redis_replication(server_id,application_id,redis_info,current_time,log): try: role = redis_info.get("role","---") if(role == "master"): is_master="1" is_slave="0" master_host="---" master_port="---" master_link_status="---" master_last_io_seconds_ago="---" master_repl_offset=redis_info.get("master_repl_offset",'0') connected_slaves=redis_info.get("connected_slaves",'0') slave_repl_offset="---" slave_read_only="0" slave_priority="---" master_link_down_since_seconds="---" delay="---" else: is_slave="1" master_host=redis_info.get("master_host","---") master_port=redis_info.get("master_port","---") master_link_status=redis_info.get("master_link_status","---") master_last_io_seconds_ago=redis_info.get("master_last_io_seconds_ago","---") master_repl_offset=redis_info.get("master_repl_offset",'0') connected_slaves=redis_info.get("connected_slaves",'0') is_master="0" if int(connected_slaves)>0: is_master="1" slave_repl_offset=redis_info.get("slave_repl_offset","---") slave_read_only=redis_info.get("slave_read_only",'0') slave_priority=redis_info.get("slave_priority","---") if master_link_status=="down": master_link_down_since_seconds=redis_info.get("master_link_down_since_seconds","---") else: master_link_down_since_seconds="0" delay="0" sql="insert into redis_replication(server_id,application_id,is_master,is_slave,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_repl_offset,connected_slaves,slave_repl_offset,slave_read_only,slave_priority,master_link_down_since_seconds,delay,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,is_master,is_slave,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_repl_offset,connected_slaves,slave_repl_offset,slave_read_only,slave_priority,master_link_down_since_seconds,delay,current_time) func.mysql_exec(sql,param) except Exception,e: log.error("ERROR: check_redis_replication failed %s" % (traceback.format_exc())) return
def main(): func.mysql_exec("delete from mysql_performance",'') #get mysql servers list user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') servers=func.mysql_query("select id,host,port,application,status from servers where is_delete=0;") if servers: for row in servers: server_id=row[0] host=row[1] port=row[2] application=row[3] status=row[4] if status <> 0: result=check_mysql_performance(host,port,user,passwd) #print result if result: sql="insert into mysql_performance(server_id,application,host,port,Query_cache_hits,Key_buffer_read_hits,Key_buffer_write_hits,Thread_cache_hits,Key_blocks_used_rate,Created_tmp_disk_tables_rate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application,host,port,result[0],result[1],result[2],result[3],result[4],result[5]) func.mysql_exec(sql,param)
def main(): func.mysql_exec("truncate table mysql_widget_hit_rate",'') #get mysql servers list user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') servers=func.mysql_query("select id,host,port from servers where is_delete=0;") if servers: print("%s: check_mysql_widget_hit_rate controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] p = Process(target = check_mysql_widget, args = (host,port,user,passwd,server_id)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_widget_hit_rate controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def get_alarm_redis_run_status(): sql="select a.application_id,a.server_id,a.create_time,a.max_clients,a.connected_clients,ifnull(round(100*a.connected_clients/a.max_clients,2),0),c.threshold_connections from redis_run_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_connections=1;" result=func.mysql_query(sql) if result <> 0: for line in result: application_id=line[0] server_id=line[1] create_time=line[2] max_clients=line[3] connected_clients=line[4] connection_use_rate=line[5] threshold_connections=line[6] if int(connection_use_rate)>=int(threshold_connections): sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message="WARN:Redis连接数过多,已用数"+str(connected_clients)+",使用率" + str(connection_use_rate) + "%>=" + threshold_connections +"%" param=(application_id,server_id,create_time,'redis','connections',connected_clients,'warning',message,'1') func.mysql_exec(sql,param) else: pass
def main(): user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') killed_pids=func.mysql_query("select p.pid,s.host,s.port from mysql_process_killed p left join servers s on p.server_id=s.id;") if killed_pids: print("%s: admin_mysql_kill_process controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for row in killed_pids: pid=row[0] host=row[1] port=row[2] p = Process(target = admin_mysql_kill_process, args = (host,port,user,passwd,pid)) plist.append(p) p.start() for p in plist: p.join() print("%s: admin_mysql_kill_process controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)) func.mysql_exec("delete from mysql_process_killed",'')
def get_alarm_redis_resource_status(): sql="select a.application_id,a.server_id,a.create_time,a.max_memory,a.used_memory,ifnull(round(100*a.used_memory/a.max_memory,2),0),c.threshold_used_memory from redis_resource_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_used_memory=1;" result=func.mysql_query(sql) if result <> 0: for line in result: application_id=line[0] server_id=line[1] create_time=line[2] max_memory=line[3] used_memory=line[4] memory_use_rate=line[5] threshold_used_memory=line[6] if int(memory_use_rate)>=int(threshold_used_memory): sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message="WARN:Redis内存使用过多,已用"+str(int(used_memory/1024/1024))+"M,使用率" + str(memory_use_rate) + "%>=" + threshold_used_memory +"%" param=(application_id,server_id,create_time,'redis','memory',used_memory,'warning',message,'1') func.mysql_exec(sql,param) else: pass
def get_alarm_redis_server_status(): sql="select a.application_id,a.server_id,a.create_time,a.connect,a.info from redis_server_status a, servers b where a.server_id=b.id and b.send_mail='1' and b.is_delete=0 and b.status=1;" result=func.mysql_query(sql) if result <> 0: for line in result: application_id=line[0] server_id=line[1] create_time=line[2] connect=line[3] info=line[4] if connect != "success": sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message="ERROR:Redis连接失败 "+info param=(application_id,server_id,create_time,'redis','connect',connect,'error',message,'1') func.mysql_exec(sql,param) else: pass else: #log.error("ERROR:exe sql failed, %s"%(sql)) pass
def main(): #get idc type idc_type = func.get_config('idc','idc_type') if idc_type != "yf": print "ide_type error" return #get mysql servers list user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') #get allow host server_list = func.get_config('mysql_db','allow_server_list') func.mysql_exec("insert into mysql_replication_history(idc_type,server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select '"+str(idc_type)+"',server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication where idc_type='"+str(idc_type)+"';",'') func.mysql_exec("delete from mysql_replication where idc_type='"+str(idc_type)+"'",'') servers=func.mysql_query("select id,host,port,application_id,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;") if servers: print("%s: check_mysql_replication controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] application_id=row[3] status=row[4] idc_type=row[5] if host not in server_list: print "Deny host:"+str(host) continue if status <> 0: p = Process(target = check_mysql_replication, args = (host,port,user,passwd,server_id,application_id,idc_type)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_replication controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def get_alarm_last_status(): sql = "delete from alarm_last_status where server_id not in (select distinct server_id from alarm);" func.mysql_exec(sql, '') sql = "update alarm_last_status set connect='',status_connections='',status_used_memory='',status_replication_relay='';" func.mysql_exec(sql, '') #insert new warn sql = "insert into alarm_last_status(application_id,server_id) select distinct application_id,server_id from alarm where server_id not in (select distinct server_id from alarm_last_status);" func.mysql_exec(sql, '') #old warn, update sql = "select application_id,server_id,alarm_type,alarm_value,level,message,send_mail from alarm where server_id in (select distinct server_id from alarm_last_status);" result = func.mysql_query(sql) if result <> 0: for line in result: update_sql = "update alarm_last_status set " application_id = line[0] server_id = line[1] alarm_type = line[2] alarm_value = line[3] level = line[4] message = line[5] send_mail = line[6] if alarm_type == "connect": update_sql = update_sql + "connect=\"" + message + "\"," elif alarm_type == "connections": update_sql = update_sql + "connections='" + alarm_value + "',status_connections='" + message + "'," elif alarm_type == "memory": update_sql = update_sql + "used_memory='" + alarm_value + "',status_used_memory='" + message + "'," elif alarm_type == "delay": update_sql = update_sql + "replication_relay='" + alarm_value + "',status_replication_relay='" + message + "'," elif alarm_type == "replication": update_sql = update_sql + "replication_relay='-1',status_replication_relay='" + message + "'," update_sql = update_sql + "send_alarm='" + str( send_mail) + "' where application_id='" + str( application_id) + "' and server_id='" + str( server_id) + "';" log.debug("update_sql:%s" % (update_sql)) func.mysql_exec(update_sql, '') #should not appear, send alarm to me sql = "select application_id,server_id,alarm_type,alarm_value,level,message,send_mail from alarm where server_id not in (select distinct server_id from alarm_last_status);" result = func.mysql_query(sql) if result <> 0: mailto_list = func.get_option('mail_to_list') mail_subject = "redis monitor error" mail_content = "this should not appear:" + sql func.send_alarm(0, mailto_list, mail_subject, mail_content.encode('utf-8'))
def get_alarm_redis_server_status(): sql = "select a.application_id,a.server_id,a.create_time,a.connect,a.info from redis_server_status a, servers b where a.server_id=b.id and b.send_mail='1' and b.is_delete=0 and b.status=1;" result = func.mysql_query(sql) if result <> 0: for line in result: application_id = line[0] server_id = line[1] create_time = line[2] connect = line[3] info = line[4] if connect != "success": sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message = "ERROR:Redis连接失败 " + info param = (application_id, server_id, create_time, 'redis', 'connect', connect, 'error', message, '1') func.mysql_exec(sql, param) else: pass else: #log.error("ERROR:exe sql failed, %s"%(sql)) pass
def check_redis_server_status(server_id, application_id, redis_info, current_config, current_time, log): try: connect = "success" dir = current_config.get("dir", "---") redis_version = redis_info.get("redis_version", "---") redis_mode = redis_info.get("redis_mode", "---") multiplexing_api = redis_info.get("multiplexing_api", "---") process_id = redis_info.get("process_id", "0") run_id = redis_info.get("run_id", "---") uptime = redis_info.get("uptime_in_seconds", "0") config_file = redis_info.get("config_file", "---") sql = "insert into redis_server_status(server_id,application_id,connect,redis_version,redis_mode,multiplexing_api,process_id,run_id,uptime,config_file,dir,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, connect, redis_version, redis_mode, multiplexing_api, process_id, run_id, uptime, config_file, dir, current_time) func.mysql_exec(sql, param) except Exception, e: log.error("ERROR: check_redis_server_status failed %s" % (traceback.format_exc())) return
def get_alarm_mysql_status(): sql="select a.application_id,a.server_id,a.create_time,a.connect,a.connections,a.active,b.send_mail,b.alarm_connections,b.alarm_active,b.threshold_connections,b.threshold_active from mysql_status a, servers b where a.server_id=b.id;" result=func.mysql_query(sql) if result <> 0: for line in result: application_id=line[0] server_id=line[1] create_time=line[2] connect=line[3] connections=line[4] active=line[5] send_mail=line[6] alarm_connections=line[7] alarm_active=line[8] threshold_connections=line[9] threshold_active=line[10] if connect <> "success": sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" param=(application_id,server_id,create_time,'mysql','connect',connect,'error','数据库服务器连接失败',send_mail) func.mysql_exec(sql,param) else: if int(alarm_connections)==1: if int(connections)>=int(threshold_connections): sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" param=(application,server_id,create_time,'mysql','connections',connections,'warning','数据库总连接数过多',send_mail) func.mysql_exec(sql,param) if int(alarm_active)==1: if int(active)>=int(threshold_active): sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" param=(application,server_id,create_time,'mysql','active',active,'warning','数据库活动连接过多',send_mail) func.mysql_exec(sql,param) else: pass
def check_redis_status(host,port,passwd,server_id,application_id,frequency_monitor,log): #print host redis_client = redis.StrictRedis(host=host, port=port, db=0, password=passwd,socket_timeout=3) try: redis_info=redis_client.info() current_config=redis_client.config_get() current_time=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) check_redis_server_status(server_id,application_id,redis_info,current_config,current_time,log) check_redis_persistence_status(server_id,application_id,redis_info,current_config,current_time,log) check_redis_resource_status(server_id,application_id,redis_info,current_config,current_time,log) check_redis_run_status(server_id,application_id,redis_info,current_config,current_time,frequency_monitor,log) check_redis_keyspace(server_id,application_id,redis_info,current_time,log) check_redis_replication(server_id,application_id,redis_info,current_time,log) except Exception,e: redis_client = redis.StrictRedis(host=host, port=port, db=0, password=passwd,socket_timeout=3) try: redis_info=redis_client.info() current_config=redis_client.config_get() except Exception,e: connect="fail" redis_version="---" redis_mode="---" multiplexing_api="---" process_id="0" run_id="---" uptime="0" config_file="---" current_time=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) info=e.args[0] #print("info:%s"%(e.args[0])) sql="insert into redis_server_status(server_id,application_id,connect,redis_version,redis_mode,multiplexing_api,process_id,run_id,uptime,config_file,info,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,application_id,connect,redis_version,redis_mode,multiplexing_api,process_id,run_id,uptime,config_file,info,current_time) func.mysql_exec(sql,param) log.error("ERROR: Get redis into failed,host:%s port:%s passwd:%s error:%s" % (host,port,passwd,traceback.format_exc())) return
def main(): #get idc type idc_type = func.get_config('idc','idc_type') if idc_type != "yf": print "ide_type error" return #get mysql servers list user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') #get allow host server_list = func.get_config('mysql_db','allow_server_list') #print "delete from mysql_widget_hit_rate where idc_type='"+str(idc_type)+"'" #func.mysql_exec("truncate table mysql_widget_hit_rate",'') func.mysql_exec("delete from mysql_widget_hit_rate where idc_type='"+str(idc_type)+"'",'') #print "select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;" servers=func.mysql_query("select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;") if servers: print("%s: check_mysql_widget_hit_rate controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] status=row[3] idc_type=row[4] if host not in server_list: print "Deny host:"+str(host) continue if status <> 0: p = Process(target = check_mysql_widget, args = (host,port,user,passwd,server_id,idc_type)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_widget_hit_rate controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def send_backup_alarm(alarm_flag,mailto_list,warn_content,application_id,server_id,db_name,current_date,log): alarm_type=0 mail_subject="REDIS BACKUP WARN" create_time=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) db_type="redis" alarm_type="backup" alarm_value="fail" level="warning" if alarm_flag== "y": result=func.send_alarm(alarm_type,mailto_list,mail_subject,warn_content.encode('utf-8')) if result: send_mail_status=1 else: send_mail_status=0 log.error("send alarm:alarm_type:%s mailto_list:%s mail_subject:%s warn_content:%s"%(str(alarm_type),mailto_list,mail_subject,warn_content.encode('utf-8'))) #print("send alarm:alarm_type:%s mailto_list:%s mail_subject:%s warn_content:%s"%(str(alarm_type),mailto_list,mail_subject,warn_content.encode('utf-8'))) sql="insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,warn_content,'1',send_mail_status) func.mysql_exec(sql,param) sql="replace into redis_coldback_info(date,server_id,db_name,file_name,suc_flag) values(%s,%s,%s,%s,%s)" param=(current_date,server_id,db_name,'---','n') func.mysql_exec(sql,param)
def get_alarm_mysql_slowquerys(): sql="select b.application_id,a.server_id,a.modify_time,(ts_cnt_sum-ts_cnt_sum_last) as ts_cnt,if((ts_cnt_sum-ts_cnt_sum_last)>0,(Query_time_sum-Query_time_sum_last)/(ts_cnt_sum-ts_cnt_sum_last),'0') as query_time_avg,b.send_mail,b.alarm_slow_querys,b.threshold_slow_querys from mysql_slow_query_status a,servers b where a.server_id=b.id and b.send_mail='1';" result=func.mysql_query(sql) if result <> 0: for line in result: application_id=line[0] server_id=line[1] create_time=line[2] ts_cnt=line[3] query_time_avg=line[4] send_mail=line[5] alarm_slow_querys=line[6] threshold_slow_querys=line[7] if int(alarm_slow_querys)==1: if int(ts_cnt)>=int(threshold_slow_querys): sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message="WARN:慢查询语句过多 " + str(ts_cnt) + ">=" + str(threshold_slow_querys) param=(application_id,server_id,create_time,'mysql','slow_query',ts_cnt,'warning',message,send_mail) func.mysql_exec(sql,param) else: pass
def main(): #get idc type idc_type = func.get_config('idc','idc_type') if idc_type != "yf": print "ide_type error" return #get mysql servers list user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') #get allow host server_list = func.get_config('mysql_db','allow_server_list') func.mysql_exec("insert into mysql_status_ext_history(server_id,idc_type,QPS,TPS,Bytes_received,Bytes_sent,create_time,YmdHi) select server_id,'"+str(idc_type)+"',QPS,TPS,Bytes_received,Bytes_sent,create_time,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status_ext where idc_type='"+str(idc_type)+"';",'') func.mysql_exec("delete from mysql_status_ext where idc_type='"+str(idc_type)+"'",'') servers=func.mysql_query("select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;") if servers: print("%s: check_mysql_status_ext controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] status=row[3] idc_type=row[4] if host not in server_list: print "Deny host:"+str(host) continue if status <> 0: p = Process(target = check_mysql_status_ext, args = (host,port,user,passwd,server_id,idc_type)) plist.append(p) p.start() for p in plist: p.join() print("%s: check_mysql_status_ext controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def get_alarm_redis_resource_status(): sql = "select a.application_id,a.server_id,a.create_time,a.max_memory,a.used_memory,ifnull(round(100*a.used_memory/a.max_memory,2),0),c.threshold_used_memory from redis_resource_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_used_memory=1;" result = func.mysql_query(sql) if result <> 0: for line in result: application_id = line[0] server_id = line[1] create_time = line[2] max_memory = line[3] used_memory = line[4] memory_use_rate = line[5] threshold_used_memory = line[6] if int(memory_use_rate) >= int(threshold_used_memory): sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" message = "WARN:Redis内存使用过多,已用" + str( int(used_memory / 1024 / 1024)) + "M,使用率" + str( memory_use_rate) + "%>=" + threshold_used_memory + "%" param = (application_id, server_id, create_time, 'redis', 'memory', used_memory, 'warning', message, '1') func.mysql_exec(sql, param) else: pass
def check_redis_resource_status(server_id, application_id, redis_info, current_config, current_time, log): try: max_memory = current_config.get("maxmemory", "0") used_memory = redis_info.get("used_memory", "0") used_memory_rss = redis_info.get("used_memory_rss", "0") used_memory_peak = redis_info.get("used_memory_peak", "0") mem_fragmentation_ratio = redis_info.get("mem_fragmentation_ratio", "0") used_cpu_sys = redis_info.get("used_cpu_sys", "0") used_cpu_user = redis_info.get("used_cpu_user", "0") used_cpu_sys_children = redis_info.get("used_cpu_sys_children", "0") used_cpu_user_children = redis_info.get("used_cpu_user_children", "0") sql = "insert into redis_resource_status(server_id,application_id,max_memory,used_memory,used_memory_rss,used_memory_peak,mem_fragmentation_ratio,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, max_memory, used_memory, used_memory_rss, used_memory_peak, mem_fragmentation_ratio, used_cpu_sys, used_cpu_user, used_cpu_sys_children, used_cpu_user_children, current_time) func.mysql_exec(sql, param) except Exception, e: log.error("ERROR: check_redis_resource_status failed %s" % (traceback.format_exc())) return
def check_redis_persistence_status(server_id, application_id, redis_info, current_config, current_time, log): try: tmp_rdb_enabled = current_config.get("save", "---") if tmp_rdb_enabled == "---" or tmp_rdb_enabled == "": rdb_enabled = "0" else: rdb_enabled = "1" rdb_dbfilename = current_config.get("dbfilename", "---") rdb_changes_since_last_save = redis_info.get( "rdb_changes_since_last_save", "0") rdb_last_save_time = redis_info.get("rdb_last_save_time", "0") rdb_last_bgsave_status = redis_info.get("rdb_last_bgsave_status", "---") rdb_last_bgsave_time_sec = redis_info.get("rdb_last_bgsave_time_sec", "0") aof_enabled = redis_info.get("aof_enabled", "---") aof_last_rewrite_time_sec = redis_info.get("aof_last_rewrite_time_sec", "0") aof_last_bgrewrite_status = redis_info.get("aof_last_bgrewrite_status", "---") aof_last_write_status = redis_info.get("aof_last_write_status", "---") if int(aof_enabled) == 0 or aof_enabled == "---": aof_current_size = "0" else: aof_current_size = redis_info.get("aof_current_size", "0") sql = "insert into redis_persistence_status(server_id,application_id,rdb_enabled,rdb_dbfilename,rdb_changes_since_last_save,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,aof_enabled,aof_last_rewrite_time_sec,aof_last_bgrewrite_status,aof_last_write_status,aof_current_size,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" param = (server_id, application_id, rdb_enabled, rdb_dbfilename, rdb_changes_since_last_save, rdb_last_save_time, rdb_last_bgsave_status, rdb_last_bgsave_time_sec, aof_enabled, aof_last_rewrite_time_sec, aof_last_bgrewrite_status, aof_last_write_status, aof_current_size, current_time) func.mysql_exec(sql, param) except Exception, e: log.error("ERROR: check_redis_persistence_status failed %s" % (traceback.format_exc())) return