def main(): #get mysql servers list servers = func.mysql_query('select id,host,port,username,password,tags from db_servers_mysql where is_delete=0 and monitor=1;') logger.info("check mysql controller started.") if servers: plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] username=row[3] password=row[4] tags=row[5] #thread.start_new_thread(check_mysql, (host,port,user,passwd,server_id,application_id)) #time.sleep(1) p = Process(target = check_all_indexes, args = (host,port,username,password)) plist.append(p) for p in plist: p.start() time.sleep(10) for p in plist: p.terminate() for p in plist: p.join() else: logger.warning("check mysql: not found any servers") logger.info("check mysql controller finished.")
def main(): #get mysql servers list servers = func.mysql_query('select m.id,m.host,m.port,m.tags,m.bigtable_size from db_servers_mysql as m , mysql_status as s where m.is_delete=0 and m.monitor=1 and m.bigtable_monitor=1 and m.host=s.host and m.port=s.port and s.role="master" order by host;') #++ guoqi exeTimeout = 20 cnfKey = "monitor_mysql" username = func.get_config(cnfKey,'user') password = func.get_config(cnfKey,'passwd') if servers: print("%s: check mysql bigtable controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] no = 1 for row in servers: (server_id, host, port, tags, bigtable_size) = row check_mysql_bigtable(no,host,port,username,password,server_id,tags,bigtable_size) no += 1 ''' p = Process(target = check_mysql_bigtable, args = (no,host,port,username,password,server_id,tags,bigtable_size)) plist.append(p) no += 1 for p in plist: p.start() time.sleep(exeTimeout) for p in plist: p.terminate() for p in plist: p.join() ''' print("%s: check mysql bigtable controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def main(): func.mysql_exec("insert into oracle_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_status;",'') func.mysql_exec('delete from oracle_status;','') func.mysql_exec("insert into oracle_tablespace_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_tablespace;",'') func.mysql_exec('delete from oracle_tablespace;','') #get oracle servers list servers=func.mysql_query("select id,host,port,dsn,username,password,tags from db_servers_oracle where is_delete=0 and monitor=1;") logger.info("check oracle controller start.") if servers: plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] dsn=row[3] username=row[4] password=row[5] tags=row[6] p = Process(target = check_oracle, args = (host,port,dsn,username,password,server_id,tags)) plist.append(p) p.start() #time.sleep(10) #for p in plist: # p.terminate() for p in plist: p.join() else: logger.warning("check oracle: not found any servers") logger.info("check oracle controller finished.")
def mysql_result(indexDict): ## 获取数据库信息 mysqlDB = portTEU.db or mysql_conn.db mysqlTB = portTEU.tb or mysql_conn.tb mysqlHost = portTEU.host or mysql_conn.host mysqlUser = portTEU.user or mysql_conn.user mysqlPass = portTEU.passwd or mysql_conn.passwd mysqlPort = portTEU.port or mysql_conn.port mysqlChrt = portTEU.charset or mysql_conn.charset filterKeys = indexDict.get('wds') sql = "SELECT year,value FROM {db}.{tb} WHERE wds='{fk}' \ AND year=(SELECT MAX(year) FROM {db}.{tb} WHERE wds='{fk}' AND value!=0)".format( db = mysqlDB, tb = mysqlTB, fk = filterKeys) result = mysql_query( cmd = sql, host = mysqlHost, user = mysqlUser, passwd = mysqlPass, port = mysqlPort, charset = mysqlChrt, fetchone = 1 ) return result
def main(): #get os servers list cpus = 12 servers = func.mysql_query( "select host,tags from db_servers_os where is_delete=0 and monitor=1;") logger.info("check os controller started.") if servers: plist = [] proHostsNum = len(servers) / cpus + 1 logger.info("check os sum:%s, cpus:%s, percpu:%s" % (len(servers), cpus, proHostsNum)) for i in range(0, len(servers), proHostsNum): proSrvs = servers[i:i + proHostsNum] p = Process(target=check_hosts, args=(i, proSrvs)) plist.append(p) intervals = exeTimeout / len(plist) if intervals <= 0: intervals = 1 for p in plist: p.start() #time.sleep(intervals) time.sleep(1) for p in plist: p.join(timeout=10) else: logger.warning("check os: not found any servers") logger.info("check os controller finished.")
def main(): func.mysql_exec("insert into redis_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_status;",'') func.mysql_exec('delete from redis_status where server_id not in (select id from db_servers_redis where monitor=1);','') func.mysql_exec("insert into redis_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_replication;",'') func.mysql_exec('delete from redis_replication where server_id not in (select id from db_servers_redis where monitor=1);','') servers = func.mysql_query('select id,host,port,password,tags from db_servers_redis where is_delete=0 and monitor=1;') logger.info("check redis controller started.") if servers: plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] passwd=row[3] tags=row[4] p = Process(target = check_redis, args = (host,port,passwd,server_id,tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check redis: not found any servers") logger.info("check redis controller finished.")
def main(): servers = func.mysql_query( 'select id,host,port,username,password,tags from db_cfg_mongodb where is_delete=0 and monitor=1;' ) logger.info("check mongodb controller started.") if servers: plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] username = row[3] password = row[4] tags = row[5] p = Process(target=check_mongodb, args=(host, port, username, password, server_id, tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check mongodb: not found any servers") logger.info("check mongodb controller finished.")
def main(): func.mysql_exec("insert into redis_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_status;",'') func.mysql_exec('delete from redis_status;','') func.mysql_exec("insert into redis_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_replication;",'') func.mysql_exec('delete from redis_replication;','') servers = func.mysql_query('select id,host,port,password,tags from db_servers_redis where is_delete=0 and monitor=1;') logger.info("check redis controller started.") if servers: plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] passwd=row[3] tags=row[4] p = Process(target = check_redis, args = (host,port,passwd,server_id,tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check redis: not found any servers") logger.info("check redis controller finished.")
def main(): user = func.get_config('mysql_db', 'username') passwd = func.get_config('mysql_db', 'password') servers = func.mysql_query( "select host,port,binlog_store_days from db_cfg_mysql where is_delete=0 and monitor=1 and binlog_auto_purge=1;" ) if servers: print("%s: admin mysql purge binlog controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) plist = [] for row in servers: host = row[0] port = row[1] binlog_store_days = row[2] p = Process(target=admin_mysql_purge_binlog, args=(host, port, user, passwd, binlog_store_days)) plist.append(p) for p in plist: p.start() time.sleep(60) for p in plist: p.terminate() for p in plist: p.join() print("%s: admin mysql purge binlog controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def get_result(_dict): filterKeys = _dict.get('wds') ## 获取数据库信息 mysqlDB = railwaytraveller.db or mysql_conn.db mysqlTB = railwaytraveller.tb or mysql_conn.tb mysqlHost = railwaytraveller.host or mysql_conn.host mysqlUser = railwaytraveller.user or mysql_conn.user mysqlPass = railwaytraveller.passwd or mysql_conn.passwd mysqlPort = railwaytraveller.port or mysql_conn.port mysqlChrt = railwaytraveller.charset or mysql_conn.charset ## 查询语句 sql = "SELECT year,value FROM {db}.{tb} WHERE wds='{fk}' \ AND year=(SELECT MAX(year) FROM {db}.{tb} WHERE wds='{fk}' AND value!=0)".format( db = mysqlDB, tb = mysqlTB, fk = filterKeys) ## 查询结果 resultDict = mysql_query( cmd = sql, host = mysqlHost, user = mysqlUser, passwd = mysqlPass, port = mysqlPort, charset = mysqlChrt, fetchone = 1 ) return resultDict
def main(): func.mysql_exec( "insert into mysql_bigtable_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),8) from mysql_bigtable", '') func.mysql_exec('delete from mysql_bigtable;', '') #get mysql servers list servers = func.mysql_query( 'select id,host,port,username,password,tags,bigtable_size from db_servers_mysql where is_delete=0 and monitor=1 and bigtable_monitor=1;' ) if servers: print("%s: check mysql bigtable 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] username = row[3] password = row[4] tags = row[5] bigtable_size = row[6] p = Process(target=check_mysql_bigtable, args=(host, port, username, password, server_id, tags, bigtable_size)) plist.append(p) for p in plist: p.start() time.sleep(15) for p in plist: p.terminate() for p in plist: p.join() print("%s: check mysql bigtable controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def main(): func.mysql_exec( "insert into mongodb_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mongodb_status;", '') func.mysql_exec('delete from mongodb_status;', '') #get mongodb servers list servers = func.mysql_query( 'select id,host,port,username,password,tags from db_servers_mongodb where is_delete=0 and monitor=1;' ) logger.info("check mongodb controller started.") if servers: plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] username = row[3] password = row[4] tags = row[5] p = Process(target=check_mongodb, args=(host, port, username, password, server_id, tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check mongodb: not found any servers") logger.info("check mongodb controller finished.")
def main(): func.mysql_exec( "replace into memcache_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from memcache_status;", '') func.mysql_exec( 'delete from memcache_status where server_id not in (select id from db_servers_memcache where monitor=1);', '') #func.mysql_exec('delete from memcache_status;','') #get memcache servers list servers = func.mysql_query( 'select id,host,port,tags from db_servers_memcache where is_delete=0 and monitor=1;' ) logger.info("check memcache controller started.") if servers: plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] tags = row[3] p = Process(target=check_memcache, args=(host, port, server_id, tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check memcache: not found any servers") logger.info("check memcache controller finished.")
def main(): #get os servers list servers=func.mysql_query("select host,community,filter_os_disk,tags from db_servers_os where is_delete=0 and monitor=1;") logger.info("check os controller started.") if servers: plist = [] for row in servers: host=row[0] community=row[1] filter_os_disk=row[2] tags=row[3] if host <> '' : thread.start_new_thread(check_os, (host,community,filter_os_disk,tags)) time.sleep(1) #p = Process(target = check_os, args=(host,filter_os_disk)) #plist.append(p) #p.start() #for p in plist: # p.join() else: logger.warning("check os: not found any servers") logger.info("check os controller finished.")
def main(): func.mysql_exec( "insert into sqlserver_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from sqlserver_status;", "", ) func.mysql_exec("delete from sqlserver_status;", "") servers = func.mysql_query( "select id,host,port,username,password,tags from db_servers_sqlserver where is_delete=0 and monitor=1;" ) logger.info("check sqlserver controller started.") if servers: plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] username = row[3] passwd = row[4] tags = row[5] p = Process(target=check_sqlserver, args=(host, port, username, passwd, server_id, tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check sqlserver: not found any servers") logger.info("check sqlserver controller finished.")
def main(): #get os servers list servers = func.mysql_query( "select host,community,filter_os_disk,tags from db_servers_os where is_delete=0 and monitor=1;" ) logger.info("check os controller started.") if servers: plist = [] for row in servers: host = row[0] community = row[1] filter_os_disk = row[2] tags = row[3] if host <> '': thread.start_new_thread( check_os, (host, community, filter_os_disk, tags)) time.sleep(1) #p = Process(target = check_os, args=(host,filter_os_disk)) #plist.append(p) #p.start() #for p in plist: # p.join() else: logger.warning("check os: not found any servers") logger.info("check os controller finished.")
def main(): func.mysql_exec("insert into mysql_bigtable_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),8) from mysql_bigtable",'') func.mysql_exec('delete from mysql_bigtable;','') #get mysql servers list servers = func.mysql_query('select id,host,port,username,password,tags,bigtable_size from db_servers_mysql where is_delete=0 and monitor=1 and bigtable_monitor=1;') if servers: print("%s: check mysql bigtable 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] username=row[3] password=row[4] tags=row[5] bigtable_size=row[6] p = Process(target = check_mysql_bigtable, args = (host,port,username,password,server_id,tags,bigtable_size)) plist.append(p) for p in plist: p.start() time.sleep(15) for p in plist: p.terminate() for p in plist: p.join() print("%s: check mysql bigtable controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def main(): servers = func.mysql_query('select id,host,port,username,password,tags from db_servers_mongodb where is_delete=0 and monitor=1;') logger.info("check mongodb controller started.") if servers: plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] username=row[3] password=row[4] tags=row[5] p = Process(target = check_mongodb, args = (host,port,username,password,server_id,tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check mongodb: not found any servers") logger.info("check mongodb controller finished.")
def main(): #get os servers list zbItems=func.mysql_query("SELECT item_type, stat_item_name, zabbix_item_name, zabbix_item_value_unit, zabbix_server,last_stat_time FROM zabbix_item where item_type='os';") zbSrvItems = {} for zbItem in zbItems: (item_type, stat_item_name, zabbix_item_name, zabbix_item_value_unit, zabbix_server,last_stat_time) = zbItem if not zbSrvItems.has_key(zabbix_server): zbSrvItems[zabbix_server] = [] zbSrvItems.get(zabbix_server).append(zbItem) #print zbSrvItems zbSectors = ["zabbix_dc", "zabbix_dba"] zbApis = {} for zbSector in zbSectors: zbKey = func.get_config(zbSector,'key') zbHost = func.get_config(zbSector,'host') zbUser = func.get_config(zbSector,'user') zbPasswd = func.get_config(zbSector, 'passwd') zbApis[zbKey] = zbCli(zbHost, zbUser, zbPasswd) logger.info("check os controller started.") servers=func.mysql_query("select id, host,tags, create_time from db_servers_os where is_delete=0 and monitor=1;") if servers: plist = [] proHostsNum = len(servers)/cpus+1 logger.info("check os sum:%s, cpus:%s, percpu:%s" % (len(servers), cpus, proHostsNum)) for i in range(0,len(servers), proHostsNum): proSrvs = servers[i:i+proHostsNum] p = Process(target = check_hosts, args=(i, proSrvs, zbSrvItems, zbApis)) plist.append(p) intervals = exeTimeout/len(plist) if intervals <= 0: intervals = 1 for p in plist: p.start() #time.sleep(intervals) time.sleep(1) for p in plist: p.join(timeout=10) else: logger.warning("check os: not found any servers") func.mysql_exec('update os_status set zabbix=0,create_time=now() where create_time<date_sub(now(), interval %s second)' % (min_interval)) logger.info("check os controller finished.")
def main(): #get os servers list servers=func.mysql_query("select host,community,filter_os_disk,tags from db_servers_os where is_delete=0 and monitor=1;") for row in servers: host=row[0] community=row[1] filter_os_disk=row[2] tags=row[3] check_os(host,community,filter_os_disk,tags)
def disk_mod(): label = config.disk.label dataDict = mysql_query(cmd=mysql_conn.sltSQL.format(label), host=mysql_conn.host, user=mysql_conn.user, passwd=mysql_conn.passwd, port=mysql_conn.port, fetchone=1, charset='utf8') return dataDict.get('data')
def data_query(_sql): _resultList = mysql_query(cmd=_sql, host=zbxdb_conn.host, user=zbxdb_conn.user, passwd=zbxdb_conn.passwd, port=zbxdb_conn.port, fetchone=1, charset='utf8') ## 返回结果 _result = _resultList.get('result') return _result
def main(): #func.mysql_exec("insert into mysql_processlist_history SELECT * from mysql_processlist",'') #func.mysql_exec("delete from mysql_processlist",'') func.mysql_exec( "replace into mysql_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status", '') func.mysql_exec( 'delete from mysql_status where server_id not in (select id from db_servers_mysql where monitor=1);', '') func.mysql_exec( "replace into mysql_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication", '') func.mysql_exec( 'delete from mysql_replication where server_id not in (select id from db_servers_mysql where monitor=1);', '') #get mysql servers list servers = func.mysql_query( "select id,host,port,username,password,tags,repl_channel from db_servers_mysql where is_delete=0 and monitor=1 and monitor_proxy='0';" ) logger.info("check mysql controller started.") if servers: plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] username = row[3] password = row[4] tags = row[5] replchannel = row[6] #thread.start_new_thread(check_mysql, (host,port,user,passwd,server_id,application_id)) #time.sleep(1) p = Process(target=check_mysql, args=(host, port, username, password, server_id, tags, replchannel)) plist.append(p) for p in plist: p.start() time.sleep(10) for p in plist: p.terminate() for p in plist: p.join() else: logger.warning("check mysql: not found any servers") logger.info("check mysql controller finished.")
def get_connect(server_id): url = "" host = "" port = "" username = "" password = "" tags = "" server = func.mysql_query( "select host,port,dsn,username,password,tags from db_cfg_oracle where id=%s;" % (server_id)) if server: for row in server: host = row[0] port = row[1] username = row[3] password = row[4] tags = row[5] url = row[0] + ':' + row[1] + '/' + row[2] if host == "": logger.warning("get host failed, exit!") sys.exit(1) try: conn = cx_Oracle.connect(username, password, url, mode=cx_Oracle.SYSDBA) #获取connection对象 return conn except Exception, e: logger_msg = "check oracle %s : %s" % (url, str(e).strip('\n')) logger.warning(logger_msg) try: connect = 0 func.mysql_exec("begin;", '') sql = "delete from oracle_status where server_id = %s; " % ( server_id) func.mysql_exec(sql, '') sql = "insert into oracle_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)" param = (server_id, host, port, tags, connect) func.mysql_exec(sql, param) func.mysql_exec("commit;", '') except Exception, e: func.mysql_exec("rollback;", '') logger.error(str(e).strip('\n'))
def data_query(_sql): _resultList = mysql_query(cmd=_sql, host=zbxdb_conn.host, user=zbxdb_conn.user, passwd=zbxdb_conn.passwd, port=zbxdb_conn.port, charset='utf8') ## 数据处理, 主要是将名称简化并并规范 _dataList = [{ 'name': '_'.join(x.get('name').split()[1:-2]), 'value': x.get('result') } for x in _resultList] return _dataList
def main(): func.mysql_exec("insert into oracle_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_status;",'') func.mysql_exec('delete from oracle_status;','') func.mysql_exec("insert into oracle_tablespace_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_tablespace;",'') func.mysql_exec('delete from oracle_tablespace;','') func.mysql_exec("insert into oracle_dg_p_status_his SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_dg_p_status;",'') func.mysql_exec('delete from oracle_dg_p_status_tmp;','') func.mysql_exec('insert into oracle_dg_p_status_tmp select * from oracle_dg_p_status;','') func.mysql_exec("insert into oracle_dg_s_status_his SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_dg_s_status;",'') func.mysql_exec('delete from oracle_dg_s_status_tmp;','') func.mysql_exec('insert into oracle_dg_s_status_tmp select * from oracle_dg_s_status;','') #get oracle servers list servers=func.mysql_query("select id,host,port,dsn,username,password,tags from db_servers_oracle where is_delete=0 and monitor=1;") logger.info("check oracle controller start.") if servers: plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] dsn=row[3] username=row[4] password=row[5] tags=row[6] p = Process(target = check_oracle, args = (host,port,dsn,username,password,server_id,tags)) plist.append(p) p.start() #time.sleep(10) #for p in plist: # p.terminate() for p in plist: p.join() else: logger.warning("check oracle: not found any servers") func.mysql_exec('DELETE FROM oracle_dg_p_status WHERE id IN (select id from oracle_dg_p_status_tmp);','') func.mysql_exec('DELETE FROM oracle_dg_s_status WHERE id IN (select id from oracle_dg_s_status_tmp);','') logger.info("check oracle controller finished.")
def sub_mysql(args): qSql = "select host, port from db_servers_mysql" qSql = "select host, port from mysql_status where connect=1 and role='master'" qSql = '''SELECT s.host, s.port,t.db_ip, t.db_port FROM mysql_status AS s left join (SELECT db_ip,db_port FROM mysql_databases WHERE upd_time>DATE_SUB(CURDATE(),INTERVAL 3 DAY) GROUP BY db_ip,db_port) AS t on CONCAT_WS(',',s.host,s.`port`) = CONCAT_WS(',',t.db_ip,t.db_port) WHERE s.connect=1 AND s.role='master' having t.db_ip is null''' lines = func.mysql_query(qSql) for line in lines: (ip, port, sip, sport) = line if re.search("^172.30.", ip): continue print datetime.now(), ip, port myCli = mysClient.mysqlClient(ip, port) if myCli == None: print "Error: %s, %s" % (ip, port) else: dbs = collect_tables(myCli) saveTables(ip, port, dbs)
def series_mod(**kwargs): ## 通过参数获取分支名称 _branchName=kwargs.get('branchName') ## 获取对应分支数据在yaml数据中 _textTagList = yamlDict.get(_branchName).get('textTagList') _divUnit = yamlDict.get(_branchName).get('divUnit') or 1 _filterKeyList = [ w.get('wds') for w in _textTagList ] ## 根据元组长度判断sql查询语句 ### python单元素元组写法是(e,), 直接用IN查询会失败 if len(_filterKeyList) == 1: _sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds='{}' ORDER BY year".format( mysqlDB, mysqlTB, _filterKeyList[0]) elif len(_filterKeyList) > 1: _sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds IN {} ORDER BY year".format( mysqlDB, mysqlTB, tuple(_filterKeyList)) ## 调用数据库查询函数查询结果 _resultList = mysql_query( cmd = _sql, host = mysqlHost, user = mysqlUser, passwd = mysqlPass, port = mysqlPort, charset = mysqlChrt ) ## 获取X轴刻度列表 _yearList = [ y.get('year') for y in _resultList ] ## 刻度列表修正(去重) _yearList = list_uniq(_yearList) ## 刻度列表重新排序 list.sort(_yearList) ## 获取series列表 _seriesList = [ series(_resultList, _x, _divUnit, picType, _yearList) for _x in _textTagList ] ## 返回年份列表和series列表 return { 'year':_yearList, 'series':_seriesList }
def main(): user = func.get_config('mysql_db','username') passwd = func.get_config('mysql_db','password') servers=func.mysql_query("select host,port,binlog_store_days from db_servers_mysql where is_delete=0 and monitor=1 and binlog_auto_purge=1;") if servers: print("%s: admin mysql purge binlog controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),)); plist = [] for row in servers: host=row[0] port=row[1] binlog_store_days=row[2] p = Process(target = admin_mysql_purge_binlog, args = (host,port,user,passwd,binlog_store_days)) plist.append(p) for p in plist: p.start() time.sleep(60) for p in plist: p.terminate() for p in plist: p.join() print("%s: admin mysql purge binlog controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def main(): func.mysql_exec("insert into mysql_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status",'') func.mysql_exec('delete from mysql_status;','') func.mysql_exec("insert into mysql_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication",'') func.mysql_exec('delete from mysql_replication;','') #get mysql servers list servers = func.mysql_query('select id,host,port,username,password,tags from db_servers_mysql where is_delete=0 and monitor=1;') logger.info("check mysql controller started.") if servers: plist = [] for row in servers: server_id=row[0] host=row[1] port=row[2] username=row[3] password=row[4] tags=row[5] #thread.start_new_thread(check_mysql, (host,port,user,passwd,server_id,application_id)) #time.sleep(1) p = Process(target = check_mysql, args = (host,port,username,password,server_id,tags)) plist.append(p) for p in plist: p.start() time.sleep(10) for p in plist: p.terminate() for p in plist: p.join() else: logger.warning("check mysql: not found any servers") logger.info("check mysql controller finished.")
def stat_mysql_tablespace(): print("%s: check mysql bigtable controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), )) # get mysql servers list servers = func.mysql_query( 'select m.id,m.host,m.port,m.tags,m.bigtable_size from db_servers_mysql as m , mysql_status as s where m.is_delete=0 and m.monitor=1 and m.bigtable_monitor=1 and m.host=s.host and m.port=s.port and s.role="master" order by host;' ) cnfKey = "monitor_mysql" username = func.get_config(cnfKey, 'user') password = func.get_config(cnfKey, 'passwd') if servers: no = 1 for row in servers: (server_id, host, port, tags, bigtable_size) = row if re.search("^172.30", host): continue print "%s/%s %s, %s" % (no, len(servers), host, port) check_mysql_tablespace(no, host, port, username, password, server_id, tags, bigtable_size) no += 1 print("%s: check mysql bigtable controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def main(): servers = func.mysql_query( 'select id,host,port,username,password,tags from db_cfg_sqlserver where is_delete=0 and monitor=1;' ) logger.info("check sqlserver controller started.") if servers: func.update_check_time('sqlserver') plist = [] for row in servers: server_id = row[0] host = row[1] port = row[2] username = row[3] passwd = row[4] tags = row[5] p = Process(target=check_sqlserver, args=(host, port, username, passwd, server_id, tags)) plist.append(p) p.start() for p in plist: p.join() else: logger.warning("check sqlserver: not found any servers") logger.info("check sqlserver controller finished.") #check for mirror group mirror_list = func.mysql_query( "select id, mirror_name, primary_db_id, standby_db_id, db_name, is_switch from db_cfg_sqlserver_mirror where is_delete=0 and on_process = 0;" ) logger.info("check sqlserver mirror start.") if mirror_list: plist_2 = [] for row in mirror_list: mirror_id = row[0] mirror_name = row[1] pri_id = row[2] sta_id = row[3] db_name = row[4] is_switch = row[5] p2 = Process(target=check_mirror, args=(mirror_id, pri_id, sta_id, db_name, is_switch)) plist_2.append(p2) p2.start() for p2 in plist_2: p2.join() else: logger.warning("check sqlserver mirror: not found any mirror group") logger.info("check sqlserver mirror finished.") # Clean invalid data logger.info("Clean invalid sqlserver status start.") clean_invalid_db_status() logger.info("Clean invalid sqlserver status finished.")
def get_connect(server_id): host = "" port = "" username = "" password = "" tags = "" server = func.mysql_query( "select host,port,username,password,tags from db_cfg_sqlserver where id=%s;" % (server_id)) if server: for row in server: host = row[0] port = row[1] username = row[2] passwd = row[3] tags = row[4] if host == "": logger.warning("get host failed, exit!") sys.exit(1) try: conn = pymssql.connect(host=host, port=int(port), user=username, password=passwd, charset="utf8") return conn except Exception, e: func.mysql_exec("rollback;", '') logger_msg = "check sqlserver %s:%s : %s" % (host, port, e) logger.warning(logger_msg) try: connect = 0 func.mysql_exec("begin;", '') sql = "delete from sqlserver_status where server_id = %s; " % ( server_id) func.mysql_exec(sql, '') # delete for the mirror record sql = "delete from sqlserver_mirror_p where server_id = %s; " % ( server_id) func.mysql_exec(sql, '') sql = "delete from sqlserver_mirror_s where server_id = %s; " % ( server_id) func.mysql_exec(sql, '') sql = "insert into sqlserver_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)" param = (server_id, host, port, tags, connect) func.mysql_exec(sql, param) logger.info( "Generate sqlserver instance alert for server: %s begin:" % (server_id)) alert.gen_alert_sqlserver_status( server_id) # generate oracle instance alert logger.info( "Generate sqlserver instance alert for server: %s end." % (server_id)) func.mysql_exec("commit;", '') except Exception, e: logger.error(e) sys.exit(1)
def main(): ## ## 从yaml文件数组中获取数据 textTagList = yamlDict.get('textTagList') legendList = [x.get('name') for x in textTagList] colorList = yamlDict.get('colorList') titleName = yamlDict.get('titleName') yUnit = yamlDict.get('yUnit') divUnit = yamlDict.get('divUnit') or 1 picType = yamlDict.get('picType') ## 获取数据库信息 mysqlDB = express.db or mysql_conn.db mysqlTB = express.tb or mysql_conn.tb mysqlHost = express.host or mysql_conn.host mysqlUser = express.user or mysql_conn.user mysqlPass = express.passwd or mysql_conn.passwd mysqlPort = express.port or mysql_conn.port mysqlChrt = express.charset or mysql_conn.charset ## 获取数据索引列表 keyList = [e.get('wds') for e in textTagList] ## 将索引列表转元组(方便数据库IN逻辑查询) keyTuple = tuple(keyList) ## 根据元组长度判断sql查询语句 ### python单元素元组写法是(e,), 直接用IN查询会失败 if len(keyTuple) == 1: sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds='{}'".format( mysqlDB, mysqlTB, keyTuple[0]) elif len(keyTuple) > 1: sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds IN {}".format( mysqlDB, mysqlTB, keyTuple) ## 调用数据库查询函数查询结果 resultList = mysql_query(cmd=sql, host=mysqlHost, user=mysqlUser, passwd=mysqlPass, port=mysqlPort, charset=mysqlChrt) ## 获取X轴刻度列表 yearList = [y.get('year') for y in resultList] ## 刻度列表修正(去重) yearList = list_uniq(yearList) ## 刻度列表重新排序 list.sort(yearList) ## 获取series列表 seriesList = [ series(resultList, x, divUnit, picType, yearList) for x in textTagList ] ## 生成输出字典 outputDict = { 'title': titleName, 'legend': legendList, 'color': colorList, 'xlabel': yearList, 'yname': yUnit, 'series': seriesList } ## 转json字符串,不转码 outputStr = json.dumps(outputDict, ensure_ascii=False) ## 返回数据 return outputStr
def gen_alert_sqlserver_mirror(server_id, mirror_role): if g_alert != "1": return -1 sql = """SELECT a.server_id, a.connect, a.create_time, a.host, a.port, b.send_mail, b.send_mail_to_list, b.send_sms, b.send_sms_to_list, b.send_wx, b.tags, 'sqlserver' AS db_type FROM sqlserver_status a, db_cfg_sqlserver b WHERE a.server_id = b.id and a.server_id = %s """ % (server_id) result = func.mysql_query(sql) if result <> 0: for line in result: server_id = line[0] connect = line[1] create_time = line[2] host = line[3] port = line[4] send_mail = line[5] send_mail_to_list = line[6] send_sms = line[7] send_sms_to_list = line[8] send_wx = line[9] tags = line[10] db_type = line[11] if send_mail_to_list is None or send_mail_to_list.strip() == '': send_mail_to_list = mail_to_list_common if send_sms_to_list is None or send_sms_to_list.strip() == '': send_sms_to_list = sms_to_list_common if mirror_role == 1: send_mail = func.update_send_mail_status( server_id, db_type, 'mirroring_role', send_mail, send_mail_max_count) send_sms = func.update_send_sms_status(server_id, db_type, 'mirroring_role', send_sms, send_sms_max_count) send_wx = func.update_send_wx_status(server_id, db_type, 'mirroring_role', send_wx) func.add_alert(server_id, tags, host, port, create_time, db_type, 'mirroring_role', mirror_role, 'critical', 'Database role is NOT match!', send_mail, send_mail_to_list, send_sms, send_sms_to_list, send_wx) func.update_db_status('repl', 3, server_id, host, db_type, create_time, 'mirroring_role', mirror_role, 'critical') else: func.check_if_ok(server_id, tags, host, port, create_time, db_type, 'mirroring_role', mirror_role, 'Database role is OK!', send_mail, send_mail_to_list, send_sms, send_sms_to_list, send_wx) func.update_db_status('repl', 1, server_id, host, db_type, create_time, 'mirroring_role', mirror_role, 'ok') else: pass
def check_redis(host, port, passwd, server_id, tags): try: r = redis.StrictRedis(host=host, port=int(port), password=passwd, db=0, socket_timeout=3, encoding='utf-8') info = r.info() time.sleep(1) info_2 = r.info() # Server redis_version = info['redis_version'] redis_git_sha1 = info['redis_git_sha1'] redis_git_dirty = info['redis_git_dirty'] arch_bits = info['arch_bits'] multiplexing_api = info['multiplexing_api'] gcc_version = info['gcc_version'] process_id = info['process_id'] uptime_in_seconds = info['uptime_in_seconds'] uptime_in_days = info['uptime_in_days'] lru_clock = info['lru_clock'] os = check_value(info, 'os') redis_mode = check_value(info, 'redis_mode') hz = check_value(info, 'hz') run_id = check_value(info, 'run_id') tcp_port = check_value(info, 'tcp_port') # Clients connected_clients = info['connected_clients'] client_longest_output_list = info['client_longest_output_list'] client_biggest_input_buf = info['client_biggest_input_buf'] blocked_clients = info['blocked_clients'] # Memory used_memory = info['used_memory'] used_memory_human = info['used_memory_human'] used_memory_rss = info['used_memory_rss'] used_memory_peak = info['used_memory_peak'] used_memory_peak_human = info['used_memory_peak_human'] used_memory_lua = check_value(info, 'used_memory_lua') mem_fragmentation_ratio = info['mem_fragmentation_ratio'] mem_allocator = info['mem_allocator'] # Persistence loading = info['loading'] rdb_changes_since_last_save = check_value( info, 'rdb_changes_since_last_save') rdb_bgsave_in_progress = check_value(info, 'rdb_bgsave_in_progress') rdb_last_save_time = check_value(info, 'rdb_last_save_time') rdb_last_bgsave_status = check_value(info, 'rdb_last_bgsave_status') rdb_last_bgsave_time_sec = check_value(info, 'rdb_last_bgsave_time_sec') rdb_current_bgsave_time_sec = check_value( info, 'rdb_current_bgsave_time_sec') aof_enabled = check_value(info, 'aof_enabled') aof_rewrite_in_progress = check_value(info, 'aof_rewrite_in_progress') aof_rewrite_scheduled = check_value(info, 'aof_rewrite_scheduled') aof_last_rewrite_time_sec = check_value(info, 'aof_last_rewrite_time_sec') aof_current_rewrite_time_sec = check_value( info, 'aof_current_rewrite_time_sec') aof_last_bgrewrite_status = check_value(info, 'aof_last_bgrewrite_status') # Stats total_connections_received = check_value(info, 'total_connections_received') total_commands_processed = check_value(info, 'total_commands_processed') current_commands_processed = int(info_2['total_commands_processed'] - info['total_commands_processed']) instantaneous_ops_per_sec = check_value(info, 'instantaneous_ops_per_sec') rejected_connections = check_value(info, 'rejected_connections') expired_keys = info['expired_keys'] evicted_keys = info['evicted_keys'] keyspace_hits = info['keyspace_hits'] keyspace_misses = info['keyspace_misses'] pubsub_channels = info['pubsub_channels'] pubsub_patterns = info['pubsub_patterns'] latest_fork_usec = info['latest_fork_usec'] # Replication role = info['role'] connected_slaves = info['connected_slaves'] # CPU used_cpu_sys = info['used_cpu_sys'] used_cpu_user = info['used_cpu_user'] used_cpu_sys_children = info['used_cpu_sys_children'] used_cpu_user_children = info['used_cpu_user_children'] # replication if role == 'slave': #print info master_host = info['master_host'] master_port = info['master_port'] master_link_status = info['master_link_status'] master_last_io_seconds_ago = info['master_last_io_seconds_ago'] master_sync_in_progress = info['master_sync_in_progress'] #slave_repl_offset = info['slave_repl_offset'] slave_priority = check_value(info, 'slave_priority') slave_read_only = check_value(info, 'slave_read_only') master_server_id = func.mysql_query( "SELECT id FROM db_servers_redis WHERE host='%s' AND port='%s' limit 1;" % (master_host, master_port)) master_server_id = master_server_id[0][0] role_new = 's' else: master_host = '-1' master_port = '-1' master_link_status = '-1' master_last_io_seconds_ago = '-1' master_sync_in_progress = '-1' #slave_repl_offset = '---' slave_priority = '-1' slave_read_only = '-1' master_server_id = '-1' role_new = 'm' #add redis_status connect = 1 sql = "insert into redis_status(server_id,host,port,tags,redis_role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param = (server_id, host, port, tags, role, connect, redis_version, redis_git_sha1, redis_git_dirty, redis_mode, os, arch_bits, multiplexing_api, gcc_version, process_id, run_id, tcp_port, uptime_in_seconds, uptime_in_days, hz, lru_clock, connected_clients, client_longest_output_list, client_biggest_input_buf, blocked_clients, used_memory, used_memory_human, used_memory_rss, used_memory_peak, used_memory_peak_human, used_memory_lua, mem_fragmentation_ratio, mem_allocator, loading, rdb_changes_since_last_save, rdb_bgsave_in_progress, rdb_last_save_time, rdb_last_bgsave_status, rdb_last_bgsave_time_sec, rdb_current_bgsave_time_sec, aof_enabled, aof_rewrite_in_progress, aof_rewrite_scheduled, aof_last_rewrite_time_sec, aof_current_rewrite_time_sec, aof_last_bgrewrite_status, total_connections_received, total_commands_processed, current_commands_processed, instantaneous_ops_per_sec, rejected_connections, expired_keys, evicted_keys, keyspace_hits, keyspace_misses, pubsub_channels, pubsub_patterns, latest_fork_usec, used_cpu_sys, used_cpu_user, used_cpu_sys_children, used_cpu_user_children) func.mysql_exec(sql, param) #add redis_replication sql_1 = "insert into redis_replication(server_id,tags,host,port,role,master_server_id,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_sync_in_progress,slave_priority,slave_read_only,connected_slaves) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param_1 = (server_id, tags, host, port, role, master_server_id, master_host, master_port, master_link_status, master_last_io_seconds_ago, master_sync_in_progress, slave_priority, slave_read_only, connected_slaves) func.mysql_exec(sql_1, param_1) func.update_db_status_init(role_new, redis_version, host, port, tags) except Exception, e: logger_msg = "check redis %s:%s : %s" % (host, port, e) logger.warning(logger_msg) try: connect = 0 sql = "insert into redis_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)" param = (server_id, host, port, tags, connect) func.mysql_exec(sql, param) except Exception, e: logger.error(e) sys.exit(1)
def gen_alert_sqlserver_status(server_id): if g_alert != "1": return -1 sql = """SELECT a.server_id, a.connect, a.processes, a.processes_running, a.processes_waits, a.create_time, a.host, a.port, b.alarm_processes, b.alarm_processes_running, alarm_processes_waits, b.threshold_warning_processes, b.threshold_warning_processes_running, b.threshold_warning_processes_waits, b.threshold_critical_processes, threshold_critical_processes_running, threshold_critical_processes_waits, b.send_mail, b.send_mail_to_list, b.send_sms, b.send_sms_to_list, b.send_wx, b.tags, 'sqlserver' AS db_type FROM sqlserver_status a, db_cfg_sqlserver b WHERE a.server_id = b.id and a.server_id = %s """ % (server_id) result = func.mysql_query(sql) if result <> 0: for line in result: server_id = line[0] connect = line[1] processes = line[2] processes_running = line[3] processes_waits = line[4] create_time = line[5] host = line[6] port = line[7] alarm_processes = line[8] alarm_processes_running = line[9] alarm_processes_waits = line[10] threshold_warning_processes = line[11] threshold_warning_processes_running = line[12] threshold_warning_processes_waits = line[13] threshold_critical_processes = line[14] threshold_critical_processes_running = line[15] threshold_critical_processes_waits = line[16] send_mail = line[17] send_mail_to_list = line[18] send_sms = line[19] send_sms_to_list = line[20] send_wx = line[21] tags = line[22] db_type = line[23] if send_mail_to_list is None or send_mail_to_list.strip() == '': send_mail_to_list = mail_to_list_common if send_sms_to_list is None or send_sms_to_list.strip() == '': send_sms_to_list = sms_to_list_common if connect <> 1: send_mail = func.update_send_mail_status( server_id, db_type, 'connect', send_mail, send_mail_max_count) send_sms = func.update_send_sms_status(server_id, db_type, 'connect', send_sms, send_sms_max_count) send_wx = func.update_send_wx_status(server_id, db_type, 'connect', send_wx) func.add_alert(server_id, tags, host, port, create_time, db_type, 'connect', 'down', 'critical', 'sqlserver server down', send_mail, send_mail_to_list, send_sms, send_sms_to_list, send_wx) func.update_db_status('connect', '3', server_id, host, db_type, create_time, 'connect', 'down', 'critical') func.update_db_status('sessions', '-1', server_id, host, db_type, '', '', '', '') func.update_db_status('actives', '-1', server_id, host, db_type, '', '', '', '') func.update_db_status('waits', '-1', server_id, host, db_type, '', '', '', '') func.update_db_status('repl', '-1', server_id, host, db_type, '', '', '', '') func.update_db_status('repl_delay', '-1', server_id, host, db_type, '', '', '', '') else: func.check_if_ok(server_id, tags, host, port, create_time, db_type, 'connect', 'up', 'sqlserver server up', send_mail, send_mail_to_list, send_sms, send_sms_to_list, send_wx) func.update_db_status('connect', '1', server_id, host, db_type, create_time, 'connect', 'up', 'ok') if int(alarm_processes) == 1: if int(processes) >= int(threshold_critical_processes): #send_mail = func.update_send_mail_status(server_id,db_type,'processes',send_mail,send_mail_max_count) #send_sms = func.update_send_sms_status(server_id,db_type,'processes',send_sms,send_sms_max_count) #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes',processes,'critical','too many processes',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx) func.update_db_status('sessions', 3, server_id, host, db_type, create_time, 'processes', processes, 'critical') elif int(processes) >= int(threshold_warning_processes): #send_mail = func.update_send_mail_status(server_id,db_type,'processes',send_mail,send_mail_max_count) #send_sms = func.update_send_sms_status(server_id,db_type,'processes',send_sms,send_sms_max_count) #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes',processes,'warning','too many processes',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx) func.update_db_status('sessions', 2, server_id, host, db_type, create_time, 'processes', processes, 'warning') else: func.update_db_status('sessions', 1, server_id, host, db_type, create_time, 'processes', processes, 'ok') func.check_if_ok(server_id, tags, host, port, create_time, db_type, 'processes', processes, 'processes ok', send_mail, send_mail_to_list, send_sms, send_sms_to_list, send_wx) if int(alarm_processes_running) == 1: if int(processes_running) >= int( threshold_critical_processes_running): #send_mail = func.update_send_mail_status(server_id,db_type,'processes_running',send_mail,send_mail_max_count) #send_sms = func.update_send_sms_status(server_id,db_type,'processes_running',send_sms,send_sms_max_count) #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_running',processes_running,'critical','too many processes running',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx) func.update_db_status('actives', 3, server_id, host, db_type, create_time, 'processes_running', processes_running, 'critical') elif int(processes_running) >= int( threshold_warning_processes_running): #send_mail = func.update_send_mail_status(server_id,db_type,'processes_running',send_mail,send_mail_max_count) #send_sms = func.update_send_sms_status(server_id,db_type,'processes_running',send_sms,send_sms_max_count) #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_running',processes_running,'critical','too many processes running',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx) func.update_db_status('actives', 2, server_id, host, db_type, create_time, 'processes_running', processes_running, 'warning') else: func.update_db_status('actives', 1, server_id, host, db_type, create_time, 'processes_running', processes_running, 'ok') func.check_if_ok(server_id, tags, host, port, create_time, db_type, 'processes_running', processes_running, 'processes running ok', send_mail, send_mail_to_list, send_sms, send_sms_to_list, send_wx) if int(alarm_processes_waits) == 1: if int(processes_waits) >= int( threshold_critical_processes_waits): #send_mail = func.update_send_mail_status(server_id,db_type,'processes_waits',send_mail,send_mail_max_count) #send_sms = func.update_send_sms_status(server_id,db_type,'processes_waits',send_sms,send_sms_max_count) #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_waits',processes_waits,'critical','too many processes waits',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx) func.update_db_status('waits', 3, server_id, host, db_type, create_time, 'processes_waits', processes_waits, 'critical') elif int(processes_waits) >= int( threshold_warning_processes_waits): #send_mail = func.update_send_mail_status(server_id,db_type,'processes_waits',send_mail,send_mail_max_count) #send_sms = func.update_send_sms_status(server_id,db_type,'processes_waits',send_sms,send_sms_max_count) #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_waits',processes_waits,'warning','too many processes waits',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx) func.update_db_status('waits', 2, server_id, host, db_type, create_time, 'processes_waits', processes_waits, 'warning') else: func.update_db_status('waits', 1, server_id, host, db_type, create_time, 'processes_waits', processes_waits, 'ok') func.check_if_ok(server_id, tags, host, port, create_time, db_type, 'processes_waits', processes_waits, 'processes waits ok', send_mail, send_mail_to_list, send_sms, send_sms_to_list, send_wx) else: pass
def check_redis(host,port,passwd,server_id,tags): try: r=redis.StrictRedis(host=host,port=port,password=passwd,db=0,socket_timeout=3,charset='utf-8') info=r.info() time.sleep(1) info_2=r.info() # Server redis_version = info['redis_version'] redis_git_sha1 = info['redis_git_sha1'] redis_git_dirty = info['redis_git_dirty'] arch_bits = info['arch_bits'] multiplexing_api = info['multiplexing_api'] gcc_version = info['gcc_version'] process_id = info['process_id'] uptime_in_seconds = info['uptime_in_seconds'] uptime_in_days = info['uptime_in_days'] lru_clock = info['lru_clock'] os = check_value(info,'os') redis_mode = check_value(info,'redis_mode') hz = check_value(info,'hz') run_id = check_value(info,'run_id') tcp_port = check_value(info,'tcp_port') # Clients connected_clients = info['connected_clients'] client_longest_output_list = info['client_longest_output_list'] client_biggest_input_buf = info['client_biggest_input_buf'] blocked_clients = info['blocked_clients'] # Memory used_memory = info['used_memory'] used_memory_human = info['used_memory_human'] used_memory_rss = info['used_memory_rss'] used_memory_peak = info['used_memory_peak'] used_memory_peak_human = info['used_memory_peak_human'] used_memory_lua = check_value(info,'used_memory_lua') mem_fragmentation_ratio = info['mem_fragmentation_ratio'] mem_allocator = info['mem_allocator'] # Persistence loading = info['loading'] rdb_changes_since_last_save = check_value(info,'rdb_changes_since_last_save') rdb_bgsave_in_progress = check_value(info,'rdb_bgsave_in_progress') rdb_last_save_time = check_value(info,'rdb_last_save_time') rdb_last_bgsave_status = check_value(info,'rdb_last_bgsave_status') rdb_last_bgsave_time_sec = check_value(info,'rdb_last_bgsave_time_sec') rdb_current_bgsave_time_sec = check_value(info,'rdb_current_bgsave_time_sec') aof_enabled = check_value(info,'aof_enabled') aof_rewrite_in_progress = check_value(info,'aof_rewrite_in_progress') aof_rewrite_scheduled = check_value(info,'aof_rewrite_scheduled') aof_last_rewrite_time_sec = check_value(info,'aof_last_rewrite_time_sec') aof_current_rewrite_time_sec = check_value(info,'aof_current_rewrite_time_sec') aof_last_bgrewrite_status = check_value(info,'aof_last_bgrewrite_status') # Stats total_connections_received = check_value(info,'total_connections_received') total_commands_processed = check_value(info,'total_commands_processed') current_commands_processed = int(info_2['total_commands_processed'] - info['total_commands_processed']) instantaneous_ops_per_sec = check_value(info,'instantaneous_ops_per_sec') rejected_connections = check_value(info,'rejected_connections') expired_keys = info['expired_keys'] evicted_keys = info['evicted_keys'] keyspace_hits = info['keyspace_hits'] keyspace_misses = info['keyspace_misses'] pubsub_channels = info['pubsub_channels'] pubsub_patterns = info['pubsub_patterns'] latest_fork_usec = info['latest_fork_usec'] # Replication role = info['role'] connected_slaves = info['connected_slaves'] # CPU used_cpu_sys = info['used_cpu_sys'] used_cpu_user = info['used_cpu_user'] used_cpu_sys_children = info['used_cpu_sys_children'] used_cpu_user_children = info['used_cpu_user_children'] # replication if role == 'slave': #print info master_host = info['master_host'] master_port = info['master_port'] master_link_status = info['master_link_status'] master_last_io_seconds_ago = info['master_last_io_seconds_ago'] master_sync_in_progress = info['master_sync_in_progress'] #slave_repl_offset = info['slave_repl_offset'] slave_priority = check_value(info,'slave_priority') slave_read_only = check_value(info,'slave_read_only') master_server_id = func.mysql_query("SELECT id FROM db_servers_redis WHERE host='%s' AND port='%s' limit 1;" %(master_host,master_port)) master_server_id = master_server_id[0][0] role_new='s' else: master_host = '-1' master_port = '-1' master_link_status= '-1' master_last_io_seconds_ago = '-1' master_sync_in_progress = '-1' #slave_repl_offset = '---' slave_priority = '-1' slave_read_only = '-1' master_server_id = '-1' role_new='m' #add redis_status connect=1 sql = "insert into redis_status(server_id,host,port,tags,redis_role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param = (server_id,host,port,tags,role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) func.mysql_exec(sql,param) #add redis_replication sql_1 = "insert into redis_replication(server_id,tags,host,port,role,master_server_id,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_sync_in_progress,slave_priority,slave_read_only,connected_slaves) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param_1 = (server_id,tags,host,port,role,master_server_id,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_sync_in_progress,slave_priority,slave_read_only,connected_slaves) func.mysql_exec(sql_1,param_1) func.update_db_status_init(role_new,redis_version,host,port,tags) except Exception, e: logger_msg="check redis %s:%s : %s" %(host,port,e) logger.warning(logger_msg) try: connect=0 sql="insert into redis_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)" param=(server_id,host,port,tags,connect) func.mysql_exec(sql,param) except Exception, e: logger.error(e) sys.exit(1)
##################### insert data to mysql server############################# sql = "insert into oracle_status(server_id,host,port,tags,connect,db_name, instance_name,instance_role,instance_status,database_role,open_mode,protection_mode,host_name,database_status,startup_time,uptime,version,archiver,session_total,session_actives,session_waits,dg_stats,dg_delay,processes,session_logical_reads_persecond,physical_reads_persecond,physical_writes_persecond,physical_read_io_requests_persecond,physical_write_io_requests_persecond,db_block_changes_persecond,os_cpu_wait_time,logons_persecond,logons_current,opened_cursors_persecond,opened_cursors_current,user_commits_persecond,user_rollbacks_persecond,user_calls_persecond,db_block_gets_persecond) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param = (server_id,host,port,tags,connect,db_name,instance_name,instance_role,instance_status,database_role,open_mode,protection_mode,host_name,database_status,startup_time,uptime,version,archiver,session_total,session_actives,session_waits,dg_stats,dg_delay,processes,session_logical_reads_persecond,physical_reads_persecond,physical_writes_persecond,physical_read_io_requests_persecond,physical_write_io_requests_persecond,db_block_changes_persecond,os_cpu_wait_time,logons_persecond,logons_current,opened_cursors_persecond,opened_cursors_current,user_commits_persecond,user_rollbacks_persecond,user_calls_persecond,db_block_gets_persecond) func.mysql_exec(sql,param) func.update_db_status_init(database_role_new,version,host,port,tags) #check tablespace tablespace = oracle.get_tablespace(conn) if tablespace: for line in tablespace: sql="insert into oracle_tablespace(server_id,host,port,tags,tablespace_name,total_size,used_size,avail_size,used_rate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)" param=(server_id,host,port,tags,line[0],line[1],line[2],line[3],line[4]) func.mysql_exec(sql,param) #check dataguard status result = func.mysql_query("select count(1) from db_servers_oracle_dg where primary_db_id = '%s' or standby_db_id = '%s'" %(server_id, server_id)) if result: is_dg = result[0][0] if is_dg > 0: if database_role == 'PRIMARY': dg_p_info = oracle.get_dg_p_info(conn, 1) dest_id = -1 thread = -1 sequence = -1 archived = -1 applied = -1 current_scn = -1 curr_db_time = "" if dg_p_info:
def main(): #host = "192.168.1.175" doHosts = ["172.21.100.40", "172.21.100.121", "172.21.100.122"] doHosts = ["172.21.100.88"] qSql = ''' SELECT d.id,d.host,d.port,d.dsn,d.tags,s.id FROM db_servers_oracle d LEFT JOIN oracle_status s ON d.`host`=s.`host`AND d.`port`=s.`port` AND s.`database_role`='PRIMARY' WHERE is_delete=0 AND monitor=1 AND awrreport=1 HAVING s.id IS NOT NULL ''' servers = func.mysql_query(qSql) logger.info("check oracle awsreport started.") cnfKey = "monitor_oracle" username = func.get_config(cnfKey, 'user') password = func.get_config(cnfKey, 'passwd') errSrvs = [] if servers: for row in servers: (server_id, host, port, dsn, tags, stid) = row if doHosts.count(host) <= 0: #continue pass logger.info("AwrReport:%s,%s,%s" % (host, port, dsn)) (isOk, reports) = createReport(host, port, dsn, username, password) if not isOk: logger.error("Err createReport:%s,%s,%s" % (host, port, dsn)) errSrvs.append([server_id, host, port, tags]) continue for report in reports: (host, port, instance_num, statTime, textReportFile, htmlReportFile) = report sectors = parseAwrReport(textReportFile) ## LoadProfile lpVals = parseLoadProfile(sectors[SECTOR_LoadProfile]) logger.info("parseLoadProfile OK") # save db saveLoadProfile(statTime, server_id, host, port, tags, instance_num, lpVals) ## TOP SQL topSqls = parseTopsql(sectors) logger.info("parseTopsql OK") # save db saveTopsql(statTime, server_id, host, port, tags, instance_num, topSqls) # doTopsqlSummary() # check err db_type = "oracle" create_time = datetime.datetime.now() alarm_item = "oracle AwrReport" alarm_value = "Fail" level = "warning" message = "" for errSrv in errSrvs: (server_id, db_host, db_port, tags) = errSrv func.add_alarm(server_id, tags, db_host, db_port, create_time, db_type, alarm_item, alarm_value, level, message)