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( "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("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 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 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("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 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(): #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: for row in servers: print row else: print "MySQLDB OK!"
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 main(): #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: for row in servers: print row else: print "MySQLDB OK!"
def main(): #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: check_slow_query(host,port,user,passwd,application,server_id)
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(): #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 mysql_status_history(server_id,application,host,port,connect,uptime,version,connections,active,create_time,YmdHi) select server_id,application,host,port,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,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_status(host,port,user,passwd) sql="insert into mysql_status(server_id,application,host,port,connect,uptime,version,connections,active) values(%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]) func.mysql_exec(sql,param)
def main(): func.mysql_exec("insert into mysql_replication_history(server_id,application,host,port,master,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,host,port,master,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,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_replication(host,port,user,passwd) if result: sql="insert into mysql_replication(server_id,application,host,port,master,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) values(%s,%s,%s,%s,%s,%s,%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],result[6],result[7],result[8],result[9],result[10],result[11]) func.mysql_exec(sql,param)
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(): 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_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 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 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 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(): #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 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("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 send_alarm_mail(idc_type='ts'): sql = "select alarm.application_id,app.display_name application,alarm.server_id,servers.host,servers.port,alarm.create_time,db_type,alarm_type,alarm_value,level,message,alarm.send_mail,servers.alarm_time,servers.threshold_starttime,servers.threshold_endtime from alarm left join servers on alarm.server_id=servers.id left join application app on servers.application_id=app.id where servers.idc_type='" + str( idc_type) + "';" result = func.mysql_query(sql) if result <> 0: send_alarm_mail = func.get_option('send_alarm_mail') mail_to_list = func.get_option('mail_to_list') mailto_list = mail_to_list.split(';') global_alarm_type = func.get_config('global_alarm_type', 'alarm_type') global_alarm_type_list = global_alarm_type.split(',') for line in result: application_id = line[0] application = line[1] server_id = line[2] host = line[3] port = line[4] create_time = line[5] db_type = line[6] alarm_type = line[7] alarm_value = line[8] level = line[9] message = line[10] send_mail = line[11] alarm_time = line[12] threshold_starttime = line[13] threshold_endtime = line[14] app_email = func.get_application_alarm_email(str(application_id)) app_email_list = app_email.split(',') for email_app in app_email_list: if email_app not in mailto_list: mailto_list.append(email_app) else: pass if send_alarm_mail == "1": if send_mail == 1: mail_subject = message + ' 当前值:' + alarm_value + ' 服务器:' + application + '-' + host + ':' + port + ' 时间:' + create_time.strftime( '%Y-%m-%d %H:%M:%S') mail_content = "请检查下!" now_hour = ("0" + str(datetime.now().hour) if datetime.now().hour < 10 else str( datetime.now().hour)) + ":" + ( "0" + str(datetime.now().minute) if datetime.now().minute < 10 else str( datetime.now().minute)) if alarm_type not in global_alarm_type_list: if (int(alarm_time) == 1) and ( threshold_starttime.strip() != "") and (threshold_endtime.strip() != "") and ( threshold_endtime > threshold_starttime ) and (now_hour <= threshold_endtime) and ( now_hour >= threshold_starttime): result_mail = func.send_mail( mailto_list, mail_subject, mail_content) elif int(alarm_time) != 1: result_mail = func.send_mail( mailto_list, mail_subject, mail_content) else: result_mail = False else: result_mail = func.send_mail(mailto_list, mail_subject, mail_content) if result_mail: send_mail_status = 1 else: send_mail_status = 0 else: send_mail_status = 0 else: send_mail_status = 0 if send_mail_status == 1: func.mysql_exec( "insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1,'" + str(idc_type) + "' from alarm;", '') elif send_mail_status == 0: func.mysql_exec( "insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0,'" + str(idc_type) + "' from alarm;", '') func.mysql_exec( "delete from alarm where idc_type='" + str(idc_type) + "'", '') else: pass
else: send_mail_status = 0 else: send_mail_status = 0 else: send_mail_status = 0 if send_mail_status == 1: func.mysql_exec( "insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1,'" + str(idc_type) + "' from alarm;", '') elif send_mail_status == 0: func.mysql_exec( "insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0,'" + str(idc_type) + "' from alarm;", '') func.mysql_exec( "delete from alarm where idc_type='" + str(idc_type) + "'", '') else: pass if __name__ == '__main__': #get idc type #print("%s: alarm_mysql controller started." % (datetime.time.strftime('%Y-%m-%d %H:%M:%S', datetime.time.localtime()),)); idc_type = func.get_config('idc', 'idc_type') get_alarm_mysql_status(idc_type) get_alarm_mysql_replcation(idc_type) send_alarm_mail(idc_type) #print("%s: alarm_mysql controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def check_backup(Y, y, m, d): result = [] err_result = {} is_alias_host_eq = False total_size = 0 dbconfig = func.get_db_config() db = MySQL.MySQL(dbconfig) if func.idc_type != 'all': check_list_sql = "select check_backup.id,check_backup.host_id,check_backup.db,check_backup.path,check_backup.filename,check_backup.send_mail,host.host,host.port,host.alias_host,host.user,host.passwd,host.key_path,check_backup.check_day,host.alias_host from check_backup left join host on check_backup.host_id=host.id where check_backup.is_checking=0 and check_backup.is_delete=0 and host.idc_type='" + str( func.idc_type) + "'" else: check_list_sql = "select check_backup.id,check_backup.host_id,check_backup.db,check_backup.path,check_backup.filename,check_backup.send_mail,host.host,host.port,host.alias_host,host.user,host.passwd,host.key_path,check_backup.check_day,host.alias_host from check_backup left join host on check_backup.host_id=host.id where check_backup.is_checking=0 and check_backup.is_delete=0" db.query(check_list_sql) check_list = db.fetchAllRows() for item in check_list: # 检查主机信息 id = item[1] is_alias_host_eq = False if func.idc_type == 'all': host = str(item[13]) else: host = str(item[6]) if str(item[13]) == str( item[6]) and func.idc_type == 'all' and item[6].startswith( '192.168'): is_alias_host_eq = True port = int(item[7]) user = item[9] passwd = str(item[10]) key = str(item[11]) # 数据库检查项信息 back_id = item[0] backup_name = item[2] backup_dir = item[3] file_name = item[4] file_name = file_name.replace('%y', y).replace('%Y', Y).replace( '%m', m).replace('%d', d) backup_dir = backup_dir.replace('%y', y).replace('%Y', Y).replace( '%m', m).replace('%d', d) check_day = item[12] day = datetime.now() nowday = day.weekday() if str(nowday) in check_day.split(",") and not is_alias_host_eq: tc = NewClient() tc.load_system_host_keys() tc._policy = paramiko.AutoAddPolicy() isDebug = func.get_config('global_value', 'isDebug') try: #print host,port,user,passwd,key,":"+check_day if not os.path.exists(key): key = func.key_path #key = '/opt/scripts/dbbackup_check/ci123dev' if not user: user = func.key_user tc.connect(host, port=port, username=user, password=passwd, key_filename=key, timeout=10) #增加判断,直接ssh跳转,内网地址设置为192.168.0.x:port hostinfo = item[6].split(':') if len(hostinfo) == 2: ip = hostinfo[0] port = hostinfo[1] host = ip stdin, stdout, stderr, status = tc.call( 'ssh ' + str(ip) + ' -p ' + str(port) + ' \'cd ' + str(backup_dir) + ';ls -gGHrtl --full-time ' + str(file_name) + '\'') else: stdin, stdout, stderr, status = tc.call( 'cd ' + str(backup_dir) + ';ls -gGHrtl --full-time ' + str(file_name)) data = stdout.read() data = re.sub(" +", " ", data) res = data.split(' ') try: b_time = res[3] + ' ' + res[4].split('.')[0] b_size = res[2] b_name = res[-1].strip() total_size += string.atof(b_size) b_size_str = func.convertBytes(string.atof(b_size)) if isDebug == 'yes': print b_time, b_size, b_name, b_size_str print host, '\033[1;31;40m', backup_name, '\033[0m', b_time, '\033[1;36;40m', b_size_str, '\033[0m', b_name, '\033[1;34;40m', backup_dir, '\033[0m' except: b_time = '' b_size = '' b_size_str = '' b_name = '' if isDebug == 'yes': print host, '\033[1;31;40m', backup_name, '\033[0m', '\033[1;37;41m', 'warning!!', '\033[0m' if not b_size: #insert_backup_checklog_sql = "insert into check_backup_log (`bid`,`db_name`,`size`,`date`) values ('"+str(id)+"','"+str(b_name)+"','"+str(b_size)+"','"+str(Y)+"')" update_sql = "update `check_backup` set `size`=' ' where id=" + str( back_id) + " limit 1" else: update_sql = "update `check_backup` set `size`='" + b_size_str + "',`last_check_filename`='" + str( b_name) + "',`last_check_time`='" + str( b_time) + "' where id=" + str(back_id) + " limit 1" file_backup_date = str(Y) + "-" + str(m) + "-" + str(d) insert_backup_checklog_sql = "insert into check_backup_log (`bid`,`host_id`,`db_name`,`size`,`date`) values ('" + str( back_id) + "','" + str(id) + "','" + str( b_name) + "','" + str(b_size_str) + "','" + str( file_backup_date) + "')" if not b_name or not b_size: insert_backuplog_alarm = "insert into alarm_checkbackup_history (`idc_type`,`b_id`,`server_id`,`check_type`,`alarm_type`,`alarm_value`,`level`,`message`,`send_mail`,`send_mail_status`) value ('" + func.idc_type + "','" + str( back_id) + "','" + str(id) + "','" + str( "checkbakup") + "','" + str( "check_db") + "','" + str( "数据库备份出错,请检查!") + "','" + str( "warning") + "','" + str( "检查路径和服务器ip是否配置正确。") + "','1','0')" db.insert(insert_backuplog_alarm) #print insert_backup_checklog_sql db.update(update_sql) db.insert(insert_backup_checklog_sql) list = [ host + ',' + backup_name + ',' + b_time + ',' + b_size_str + ',' + b_name + ',' + backup_dir ] result.append(list) stdout.close() stdin.close() except Exception as err: print "error" + str(id) + "###" + str(err) err_result[host] = str(id) + "###" + str(err) continue tc.close() if total_size: total_size_str = func.convertBytes(total_size) else: total_size_str = "0b" return result, err_result, total_size_str
Y = sys.argv[1] y = sys.argv[2] m = sys.argv[3] d = sys.argv[4] else: is_auto = True Y = (datetime.now() - timedelta(days=1)).strftime('%Y') y = (datetime.now() - timedelta(days=1)).strftime('%y') m = (datetime.now() - timedelta(days=1)).strftime('%m') d = (datetime.now() - timedelta(days=1)).strftime('%d') h = (datetime.now() - timedelta(days=1)).strftime('%H') M = (datetime.now() - timedelta(days=1)).strftime('%M') S = (datetime.now() - timedelta(days=1)).strftime('%S') isSendMail = func.get_config('global_value', 'isSendMail') #主题调用函数 result, err_result, total_size_str = check_backup(Y, y, m, d) if len(result) > 1: att_filename = "xls/backuplist_" + str(Y) + "-" + str(m) + "-" + str( d) + "_" + str(h) + str(M) + str(S) + ".xlsx" hdngs = ['服务器IP', '备份昵称', '备份日期', '备份大小', '备份文件名', '备份目录'] exc_ins = Excel.Excel(att_filename, sheet_name='ciSheet', encoding='utf-8') exc_ins.backup_xlsx_write(hdngs, result) if is_auto and isSendMail == 'yes' and len(result) > 1: content = func.toHtml(result, total_size_str) sub = "服务器备份每日统计(" + str(Y) + '-' + str(m) + '-' + str(d) + ")"
if send_mail_status==1: func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1,'"+str(idc_type)+"' from alarm;",'') elif send_mail_status==0: func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0,'"+str(idc_type)+"' from alarm;",'') func.mysql_exec("delete from alarm where idc_type='"+str(idc_type)+"'",'') else: pass if __name__ == '__main__': #get idc type #print("%s: alarm_mysql controller started." % (datetime.time.strftime('%Y-%m-%d %H:%M:%S', datetime.time.localtime()),)); idc_type = func.get_config('idc','idc_type') get_alarm_mysql_status(idc_type) get_alarm_mysql_replcation(idc_type) send_alarm_mail(idc_type) #print("%s: alarm_mysql controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def send_alarm_mail(idc_type='ts'): sql="select alarm.application_id,app.display_name application,alarm.server_id,servers.host,servers.port,alarm.create_time,db_type,alarm_type,alarm_value,level,message,alarm.send_mail,servers.alarm_time,servers.threshold_starttime,servers.threshold_endtime from alarm left join servers on alarm.server_id=servers.id left join application app on servers.application_id=app.id where servers.idc_type='"+str(idc_type)+"';" result=func.mysql_query(sql) if result <> 0: send_alarm_mail = func.get_option('send_alarm_mail') mail_to_list = func.get_option('mail_to_list') mailto_list=mail_to_list.split(';') global_alarm_type=func.get_config('global_alarm_type','alarm_type') global_alarm_type_list=global_alarm_type.split(',') for line in result: application_id=line[0] application=line[1] server_id=line[2] host=line[3] port=line[4] create_time=line[5] db_type=line[6] alarm_type=line[7] alarm_value=line[8] level=line[9] message=line[10] send_mail=line[11] alarm_time=line[12] threshold_starttime=line[13] threshold_endtime=line[14] app_email=func.get_application_alarm_email(str(application_id)) app_email_list=app_email.split(',') for email_app in app_email_list: if email_app not in mailto_list: mailto_list.append(email_app) else: pass if send_alarm_mail=="1": if send_mail==1: mail_subject=message+' 当前值:'+alarm_value+' 服务器:'+application+'-'+host+':'+port+' 时间:'+create_time.strftime('%Y-%m-%d %H:%M:%S') mail_content="请检查下!" now_hour = ("0"+str(datetime.now().hour) if datetime.now().hour<10 else str(datetime.now().hour))+":"+( "0"+str(datetime.now().minute) if datetime.now().minute<10 else str(datetime.now().minute)) if alarm_type not in global_alarm_type_list: if (int(alarm_time) == 1) and (threshold_starttime.strip() != "") and (threshold_endtime.strip() != "") and (threshold_endtime > threshold_starttime) and (now_hour <= threshold_endtime) and (now_hour >= threshold_starttime): result_mail = func.send_mail(mailto_list,mail_subject,mail_content) elif int(alarm_time) != 1: result_mail = func.send_mail(mailto_list,mail_subject,mail_content) else: result_mail = False else: result_mail = func.send_mail(mailto_list,mail_subject,mail_content) if result_mail: send_mail_status=1 else: send_mail_status=0 else: send_mail_status=0 else: send_mail_status=0 if send_mail_status==1: func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1,'"+str(idc_type)+"' from alarm;",'') elif send_mail_status==0: func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0,'"+str(idc_type)+"' from alarm;",'') func.mysql_exec("delete from alarm where idc_type='"+str(idc_type)+"'",'') else: pass
def check_memcache_info(Y,y,m,d): """ 每日检查memcache信息 主体函数 Y:完整年份 如:2015 y:年份缩写 如:15 m:月份 d:天 """ result = [] result_mail = [] list_pidname = [] err_result = {} total_size = 0 check_list = [xxx] for item in check_list: hostport = item.split(":") host = hostport[0] port = int(hostport[1]) # 检查主机信息 key = "" user = "" passwd = "" day=datetime.now() nowday=day.weekday() if str(nowday) : tc = NewClient() tc.load_system_host_keys() tc._policy = paramiko.AutoAddPolicy() isDebug = func.get_config('global_value','isDebug') try: if not os.path.exists(key): key = func.key_path if not user : user = func.key_user tc.connect(host,port=port, username=user, password=passwd,key_filename=key, timeout=10) ip_stdin, ip_stdout, ip_stderr, ip_status = tc.call("ifconfig | awk -F'addr:|Bcast' '/Bcast/{print $2}'|grep '192.168'|head -n 1") host_ip = ip_stdout.read().strip() stdin, stdout, stderr, status = tc.call("netstat -nltp|grep memcached") data = stdout.read() res = data.split("\n") for line in res : if line : tmp_list = re.split("[ ]+",line.strip()) host_port = tmp_list[3] software_name = tmp_list[6] if not str(software_name) in list_pidname : list_pidname.append(str(software_name)) if software_name.split('/')[1] == "memcached" : memcache_stats = {} memcache_stats['host_port'] = host_port memcache_stats['host_ip'] = host_ip memcache_bind_ip = host_port.split(':')[0] if memcache_bind_ip == '0.0.0.0' : memcache_bind_ip = host_ip memcache_bind_port = host_port.split(':')[1] nc_stdin, nc_stdout, nc_stderr, nc_status = tc.call("echo \"stats\"|nc -w 2 "+str(memcache_bind_ip)+" "+str(memcache_bind_port)) memcache_stats_nc = nc_stdout.read() if memcache_stats_nc : memcache_stats_list = memcache_stats_nc.split("\n") for stats in memcache_stats_list: if stats.strip().startswith('STAT') : stat_list = stats.strip().split() memcache_stats[stat_list[1]] = stat_list[2] time_uptime = int(memcache_stats['time']) - int(memcache_stats['uptime']) diff_day,diff_hour,diff_minute,diff_sec = func.get_diff_time(times=time_uptime) runing_start_str = "%d天%d小时%d分钟%d秒" %(diff_day,diff_hour,diff_minute,diff_sec) if int(memcache_stats['get_hits']) > 0 and int(memcache_stats['get_misses']) > 0 : hit_rate_d = float(memcache_stats['get_hits'])/(float(memcache_stats['get_hits']) + float(memcache_stats['get_misses'])) hit_rate = round(hit_rate_d,2)*100 miss_rate_d = float(memcache_stats['get_misses'])/( float(memcache_stats['get_hits']) + float(memcache_stats['get_misses'])) miss_rate = round(miss_rate_d,2)*100 hit_rate_dd = float(memcache_stats['get_hits'])/float(memcache_stats['uptime']) hit_rated = "%.2f"%(hit_rate_dd) miss_rate_dd = float(memcache_stats['get_misses'])/float(memcache_stats['uptime']) miss_rated = "%.2f"%(miss_rate_dd) else : hit_rate = 0 miss_rate = 0 hit_rated = 0 miss_rated = 0 list = [str(host)+","+str(memcache_stats['host_port'])+","+str(memcache_stats['pid'])+","+str(memcache_stats['limit_maxbytes'])+","+str(memcache_stats['bytes'])+","+str(memcache_stats['curr_items'])+","+str(memcache_stats['total_items'])+","+str(memcache_stats['version'])+","+str(memcache_stats['uptime'])+","+str(runing_start_str)+","+str(hit_rate)+"%"+","+str(miss_rate)+"%"+","+str(hit_rated)+" req/s"+","+str(miss_rated)+" req/s"+","+str(memcache_stats['get_hits'])+","+str(memcache_stats['get_misses'])] diff_bytes = int(memcache_stats['limit_maxbytes'])-int(memcache_stats['bytes']) diff_bytes_str = func.convertBytes(float(diff_bytes)) bytes_str = str(memcache_stats['bytes']) stats_list = [str(host)+","+str(memcache_stats['host_port'])+","+str(int(hit_rate))+"%,"+str(int(miss_rate))+"%,"+str(func.convertBytes(float(memcache_stats['limit_maxbytes'])))+","+diff_bytes_str+","+bytes_str] if isDebug : print stats_list result.append(list) result_mail.append(stats_list) stdout.close() stdin.close() except Exception as err: print "error"+str(id)+"###"+str(err) err_result[host] = str(id)+"###"+str(err) continue tc.close() if total_size : total_size_str = func.convertBytes(total_size) else : total_size_str = "0b" return result,result_mail,err_result,total_size_str