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)