def delete_old_data(log):
sql="select count(1),a.server_id,a.back_path,a.save_number,a.back_IP,a.back_ssh_port,a.back_ssh_user,a.back_ssh_passwd,b.db_name from redis_coldback_config a,redis_coldback_info b where a.server_id=b.server_id and b.suc_flag='y' and b.del_flag ='n' group by b.server_id,b.db_name;"
result=func.mysql_query(sql)
if result:
for row in result:
count=int(row[0])
server_id=str(row[1])
back_path=row[2]
save_number=int(row[3])
back_IP=row[4]
back_ssh_port=row[5]
back_ssh_user=row[6]
back_ssh_passwd=row[7]
db_name=row[8]
if count > save_number:
del_num=count-save_number
sql="select file_name from redis_coldback_info where server_id='"+server_id+"' and db_name='"+db_name+"' and del_flag='n' order by date limit "+str(del_num)+";"
file_name_del=func.mysql_query(sql)
if file_name_del:
if back_ssh_passwd == "":
log.error("delete old data:passwd not found,server_id:%s,back_IP:%s"%(server_id,back_IP))
continue
for file in file_name_del:
file_name=file[0]
remote_file=back_path+"/"+file_name
tCmd="/usr/bin/expect exec_user_cmd.exp "+back_IP+" "+back_ssh_port+" "+back_ssh_user+" '"+back_ssh_passwd+"' 'rm -f "+remote_file+";ls -l "+remote_file+";' 120"
log.debug("tCmd:%s"%(tCmd))
(status, output) = commands.getstatusoutput(tCmd)
log.debug("output:%s"%(output))
if output.find(remote_file)>=0 and (output.find("No such file or directory")>=0 or output.find("cannot access")>=0):
sql="update redis_coldback_info set del_flag='y' where server_id='"+server_id+"' and db_name='"+db_name+"' and file_name='"+file_name+"';"
log.debug("sql:%s"%(sql))
func.mysql_exec(sql,'')
return
def get_alarm_last_status():
sql = "delete from alarm_last_status where server_id not in (select distinct server_id from alarm);"
func.mysql_exec(sql, '')
sql = "update alarm_last_status set connect='',status_connections='',status_used_memory='',status_replication_relay='';"
func.mysql_exec(sql, '')
#insert new warn
sql = "insert into alarm_last_status(application_id,server_id) select distinct application_id,server_id from alarm where server_id not in (select distinct server_id from alarm_last_status);"
func.mysql_exec(sql, '')
#old warn, update
sql = "select application_id,server_id,alarm_type,alarm_value,level,message,send_mail from alarm where server_id in (select distinct server_id from alarm_last_status);"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
update_sql = "update alarm_last_status set "
application_id = line[0]
server_id = line[1]
alarm_type = line[2]
alarm_value = line[3]
level = line[4]
message = line[5]
send_mail = line[6]
if alarm_type == "connect":
update_sql = update_sql + "connect=\"" + message + "\","
elif alarm_type == "connections":
update_sql = update_sql + "connections='" + alarm_value + "',status_connections='" + message + "',"
elif alarm_type == "memory":
update_sql = update_sql + "used_memory='" + alarm_value + "',status_used_memory='" + message + "',"
elif alarm_type == "delay":
update_sql = update_sql + "replication_relay='" + alarm_value + "',status_replication_relay='" + message + "',"
elif alarm_type == "replication":
update_sql = update_sql + "replication_relay='-1',status_replication_relay='" + message + "',"
update_sql = update_sql + "send_alarm='" + str(
send_mail) + "' where application_id='" + str(
application_id) + "' and server_id='" + str(
server_id) + "';"
log.debug("update_sql:%s" % (update_sql))
func.mysql_exec(update_sql, '')
#should not appear, send alarm to me
sql = "select application_id,server_id,alarm_type,alarm_value,level,message,send_mail from alarm where server_id not in (select distinct server_id from alarm_last_status);"
result = func.mysql_query(sql)
if result <> 0:
mailto_list = func.get_option('mail_to_list')
mail_subject = "redis monitor error"
mail_content = "this should not appear:" + sql
func.send_alarm(0, mailto_list, mail_subject,
mail_content.encode('utf-8'))
def main():
func.mysql_exec("delete from mysql_process", '')
#get mysql servers list
user = func.get_config('mysql_db', 'username')
passwd = func.get_config('mysql_db', 'password')
servers = func.mysql_query(
"select id,host,port,application_id,status from servers where is_delete=0;"
)
if servers:
print("%s: check_mysql_process controller started." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
application_id = row[3]
status = row[4]
if status <> 0:
p = Process(target=check_mysql_process,
args=(host, port, user, passwd, server_id,
application_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_process controller finished." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def get_alarm_mysql_replcation():
sql = "select a.application_id,a.server_id,a.create_time,a.slave_io_run,a.slave_sql_run,a.delay,b.send_mail,b.alarm_repl_status,b.alarm_repl_delay,b.threshold_repl_delay from mysql_replication a,servers b where a.server_id=b.id and a.is_slave='1';"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
application = line[0]
server_id = line[1]
create_time = line[2]
slave_io_run = line[3]
slave_sql_run = line[4]
delay = line[5]
send_mail = line[6]
alarm_repl_status = line[7]
alarm_repl_delay = line[8]
threshold_repl_delay = line[9]
if alarm_repl_status == 1:
if (slave_io_run == "Yes") and (slave_sql_run == "Yes"):
if alarm_repl_delay == "yes":
if int(delay) >= int(threshold_repl_delay):
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (application, server_id, create_time,
'mysql', 'delay', delay, 'warning',
'数据库备库延时', send_mail)
func.mysql_exec(sql, param)
else:
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (application, server_id, create_time, 'mysql',
'replication', 'IO Thread:' + slave_io_run +
',SQL Thread:' + slave_sql_run, 'error',
'数据库同步进程停止', send_mail)
func.mysql_exec(sql, param)
else:
pass
def main():
func.mysql_exec(
"insert into mysql_status_history(server_id,application_id,connect,uptime,version,connections,active,create_time,YmdHi) select server_id,application_id,connect,uptime,version,connections,active,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status;",
'')
func.mysql_exec("delete from mysql_status", '')
#get mysql servers list
user = func.get_config('mysql_db', 'username')
passwd = func.get_config('mysql_db', 'password')
servers = func.mysql_query(
"select id,host,port,application_id,status from servers where is_delete=0;"
)
if servers:
print("%s: check_mysql_status controller started." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
application_id = row[3]
status = row[4]
if status <> 0:
p = Process(target=check_mysql_status,
args=(host, port, user, passwd, server_id,
application_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_status controller finished." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def get_alarm_redis_replication():
sql="select a.application_id,a.server_id,a.create_time,a.master_link_status,a.delay,c.alarm_repl_delay,c.threshold_repl_delay from redis_replication a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and a.is_slave=1 and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_repl_status=1;"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
application_id=line[0]
server_id=line[1]
create_time=line[2]
master_link_status=line[3]
delay=line[4]
alarm_repl_delay=line[5]
threshold_repl_delay=line[6]
if master_link_status!="up":
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message="ERROR:Redis复制停止 master_link_status:"+master_link_status
param=(application_id,server_id,create_time,'redis','replication',master_link_status,'error',message,'1')
func.mysql_exec(sql,param)
else:
if (alarm_repl_delay=="1") and (int(delay)>=int(threshold_repl_delay)):
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message="WARN:Redis复制延迟过大 "+delay+">="+threshold_repl_delay
param=(application_id,server_id,create_time,'redis','delay',delay,'warning',message,'1')
func.mysql_exec(sql,param)
else:
pass
def main():
func.mysql_exec(
"insert into mysql_replication_history(server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication;",
"",
)
func.mysql_exec("delete from mysql_replication", "")
# get mysql servers list
user = func.get_config("mysql_db", "username")
passwd = func.get_config("mysql_db", "password")
servers = func.mysql_query("select id,host,port,application_id,status from servers where is_delete=0;")
if servers:
print (
"%s: check_mysql_replication controller started." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),)
)
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
application_id = row[3]
status = row[4]
if status <> 0:
p = Process(target=check_mysql_replication, args=(host, port, user, passwd, server_id, application_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print (
"%s: check_mysql_replication controller finished." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),)
)
def get_alarm_redis_replication():
sql = "select a.application_id,a.server_id,a.create_time,a.master_link_status,a.delay,c.alarm_repl_delay,c.threshold_repl_delay from redis_replication a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and a.is_slave=1 and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_repl_status=1;"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
application_id = line[0]
server_id = line[1]
create_time = line[2]
master_link_status = line[3]
delay = line[4]
alarm_repl_delay = line[5]
threshold_repl_delay = line[6]
if master_link_status != "up":
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message = "ERROR:Redis复制停止 master_link_status:" + master_link_status
param = (application_id, server_id, create_time, 'redis',
'replication', master_link_status, 'error', message,
'1')
func.mysql_exec(sql, param)
else:
if (alarm_repl_delay
== "1") and (int(delay) >= int(threshold_repl_delay)):
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message = "WARN:Redis复制延迟过大 " + delay + ">=" + threshold_repl_delay
param = (application_id, server_id, create_time, 'redis',
'delay', delay, 'warning', message, '1')
func.mysql_exec(sql, param)
else:
pass
def get_alarm_mysql_slowquerys():
sql = "select b.application_id,a.server_id,a.modify_time,(ts_cnt_sum-ts_cnt_sum_last) as ts_cnt,if((ts_cnt_sum-ts_cnt_sum_last)>0,(Query_time_sum-Query_time_sum_last)/(ts_cnt_sum-ts_cnt_sum_last),'0') as query_time_avg,b.send_mail,b.alarm_slow_querys,b.threshold_slow_querys from mysql_slow_query_status a,servers b where a.server_id=b.id and b.send_mail='1';"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
application_id = line[0]
server_id = line[1]
create_time = line[2]
ts_cnt = line[3]
query_time_avg = line[4]
send_mail = line[5]
alarm_slow_querys = line[6]
threshold_slow_querys = line[7]
if int(alarm_slow_querys) == 1:
if int(ts_cnt) >= int(threshold_slow_querys):
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message = "WARN:慢查询语句过多 " + str(ts_cnt) + ">=" + str(
threshold_slow_querys)
param = (application_id, server_id, create_time, 'mysql',
'slow_query', ts_cnt, 'warning', message,
send_mail)
func.mysql_exec(sql, param)
else:
pass
def get_alarm_mysql_replcation():
sql="select a.application,a.server_id,a.host,a.port,a.create_time,a.slave_io_run,a.slave_sql_run,a.delay,b.send_mail,b.alarm_repl_status,b.alarm_repl_delay,b.threshold_repl_delay from mysql_replication a,servers b where a.server_id=b.id and a.slave='1';"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
application=line[0]
server_id=line[1]
host=line[2]
port=line[3]
create_time=line[4]
slave_io_run=line[5]
slave_sql_run=line[6]
delay=line[7]
send_mail=line[8]
alarm_repl_status=line[9]
alarm_repl_delay=line[10]
threshold_repl_delay=line[11]
if alarm_repl_status==1:
if (slave_io_run== "Yes") and (slave_sql_run== "Yes"):
if alarm_repl_delay=="yes":
if int(delay)>=int(threshold_repl_delay):
sql="insert into alarm(application,server_id,host,port,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param=(application,server_id,host,port,create_time,'mysql','delay',delay,'warning','数据库备库延时',send_mail)
func.mysql_exec(sql,param)
else:
sql="insert into alarm(application,server_id,host,port,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param=(application,server_id,host,port,create_time,'mysql','replication','IO Thread:'+slave_io_run+',SQL Thread:'+slave_sql_run,'error','数据库同步进程停止',send_mail)
func.mysql_exec(sql,param)
else:
pass
def main():
user = func.get_config("mysql_db", "username")
passwd = func.get_config("mysql_db", "password")
killed_pids = func.mysql_query(
"select p.pid,s.host,s.port from mysql_process_killed p left join servers s on p.server_id=s.id;"
)
if killed_pids:
print (
"%s: admin_mysql_kill_process controller started." % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),)
)
plist = []
for row in killed_pids:
pid = row[0]
host = row[1]
port = row[2]
p = Process(target=admin_mysql_kill_process, args=(host, port, user, passwd, pid))
plist.append(p)
p.start()
for p in plist:
p.join()
print (
"%s: admin_mysql_kill_process controller finished."
% (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),)
)
func.mysql_exec("delete from mysql_process_killed", "")
def get_alarm_redis_run_status():
sql = "select a.application_id,a.server_id,a.create_time,a.max_clients,a.connected_clients,ifnull(round(100*a.connected_clients/a.max_clients,2),0),c.threshold_connections from redis_run_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_connections=1;"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
application_id = line[0]
server_id = line[1]
create_time = line[2]
max_clients = line[3]
connected_clients = line[4]
connection_use_rate = line[5]
threshold_connections = line[6]
if int(connection_use_rate) >= int(threshold_connections):
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message = "WARN:Redis连接数过多,已用数" + str(
connected_clients) + ",使用率" + str(
connection_use_rate
) + "%>=" + threshold_connections + "%"
param = (application_id, server_id, create_time, 'redis',
'connections', connected_clients, 'warning', message,
'1')
func.mysql_exec(sql, param)
else:
pass
def get_alarm_mysql_status():
sql="select a.application_id,a.server_id,a.create_time,a.connect,a.connections,a.active,b.send_mail,b.alarm_connections,b.alarm_active,b.threshold_connections,b.threshold_active from mysql_status a, servers b where a.server_id=b.id;"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
application_id=line[0]
server_id=line[1]
create_time=line[2]
connect=line[3]
connections=line[4]
active=line[5]
send_mail=line[6]
alarm_connections=line[7]
alarm_active=line[8]
threshold_connections=line[9]
threshold_active=line[10]
if connect <> "success":
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param=(application_id,server_id,create_time,'mysql','connect',connect,'error','数据库服务器连接失败',send_mail)
func.mysql_exec(sql,param)
else:
if int(alarm_connections)==1:
if int(connections)>=int(threshold_connections):
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param=(application,server_id,create_time,'mysql','connections',connections,'warning','数据库总连接数过多',send_mail)
func.mysql_exec(sql,param)
if int(alarm_active)==1:
if int(active)>=int(threshold_active):
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param=(application,server_id,create_time,'mysql','active',active,'warning','数据库活动连接过多',send_mail)
func.mysql_exec(sql,param)
else:
pass
def main():
func.mysql_exec("truncate table mysql_widget_connect", "")
# get mysql servers list
user = func.get_config("mysql_db", "username")
passwd = func.get_config("mysql_db", "password")
servers = func.mysql_query("select id,host,port from servers where is_delete=0;")
if servers:
print (
"%s: check_mysql_widget_connect controller started."
% (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),)
)
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
p = Process(target=check_mysql_widget, args=(host, port, user, passwd, server_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print (
"%s: check_mysql_widget_connect controller finished."
% (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),)
)
def main():
func.mysql_exec(
"insert into mysql_replication_history(server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication;",
'')
func.mysql_exec("delete from mysql_replication", '')
#get mysql servers list
user = func.get_config('mysql_db', 'username')
passwd = func.get_config('mysql_db', 'password')
servers = func.mysql_query(
"select id,host,port,application_id,status from servers where is_delete=0;"
)
if servers:
print("%s: check_mysql_replication controller started." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
application_id = row[3]
status = row[4]
if status <> 0:
p = Process(target=check_mysql_replication,
args=(host, port, user, passwd, server_id,
application_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_replication controller finished." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def main():
func.mysql_exec("insert into mysql_status_history(server_id,application_id,connect,uptime,version,connections,active,create_time,YmdHi) select server_id,application_id,connect,uptime,version,connections,active,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status;",'')
func.mysql_exec("delete from mysql_status",'')
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port,application_id,status from servers where is_delete=0;")
if servers:
print("%s: check_mysql_status controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
application_id=row[3]
status=row[4]
if status <> 0:
p = Process(target = check_mysql_status, args = (host,port,user,passwd,server_id,application_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_status controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def get_alarm_last_status():
sql="delete from alarm_last_status where server_id not in (select distinct server_id from alarm);"
func.mysql_exec(sql,'')
sql="update alarm_last_status set connect='',status_connections='',status_used_memory='',status_replication_relay='';"
func.mysql_exec(sql,'')
#insert new warn
sql="insert into alarm_last_status(application_id,server_id) select distinct application_id,server_id from alarm where server_id not in (select distinct server_id from alarm_last_status);"
func.mysql_exec(sql,'')
#old warn, update
sql="select application_id,server_id,alarm_type,alarm_value,level,message,send_mail from alarm where server_id in (select distinct server_id from alarm_last_status);"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
update_sql="update alarm_last_status set "
application_id=line[0]
server_id=line[1]
alarm_type=line[2]
alarm_value=line[3]
level=line[4]
message=line[5]
send_mail=line[6]
if alarm_type=="connect":
update_sql=update_sql + "connect=\"" + message + "\","
elif alarm_type=="connections":
update_sql=update_sql + "connections='" + alarm_value + "',status_connections='" + message + "',"
elif alarm_type=="memory":
update_sql=update_sql + "used_memory='" + alarm_value + "',status_used_memory='" + message + "',"
elif alarm_type=="delay":
update_sql=update_sql + "replication_relay='" + alarm_value + "',status_replication_relay='" + message + "',"
elif alarm_type=="replication":
update_sql=update_sql + "replication_relay='-1',status_replication_relay='" + message + "',"
update_sql=update_sql + "send_alarm='" + str(send_mail) + "' where application_id='" + str(application_id) + "' and server_id='" + str(server_id) + "';"
log.debug("update_sql:%s"%(update_sql))
func.mysql_exec(update_sql,'')
#should not appear, send alarm to me
sql="select application_id,server_id,alarm_type,alarm_value,level,message,send_mail from alarm where server_id not in (select distinct server_id from alarm_last_status);"
result=func.mysql_query(sql)
if result <> 0:
mailto_list = func.get_option('mail_to_list')
mail_subject="redis monitor error"
mail_content="this should not appear:"+sql
func.send_alarm(0,mailto_list,mail_subject,mail_content.encode('utf-8'))
def main():
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port,application_id,status from servers where is_delete=0;")
if servers:
for row in servers:
print row
else:
print "MySQLDB OK!"
def main():
#get idc type
idc_type = func.get_config('idc', 'idc_type')
if idc_type != "yf":
print "ide_type error"
return
#get mysql servers list
user = func.get_config('mysql_db', 'username')
passwd = func.get_config('mysql_db', 'password')
#get allow host
server_list = func.get_config('mysql_db', 'allow_server_list')
func.mysql_exec(
"insert into mysql_replication_history(idc_type,server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select '"
+ str(idc_type) +
"',server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication where idc_type='"
+ str(idc_type) + "';", '')
func.mysql_exec(
"delete from mysql_replication where idc_type='" + str(idc_type) +
"'", '')
servers = func.mysql_query(
"select id,host,port,application_id,status,idc_type from servers where idc_type='"
+ str(idc_type) + "' and is_delete=0;")
if servers:
print("%s: check_mysql_replication controller started." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
application_id = row[3]
status = row[4]
idc_type = row[5]
if host not in server_list:
print "Deny host:" + str(host)
continue
if status <> 0:
p = Process(target=check_mysql_replication,
args=(host, port, user, passwd, server_id,
application_id, idc_type))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_replication controller finished." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def main():
#get mysql servers list
user = func.get_config('mysql_db', 'username')
passwd = func.get_config('mysql_db', 'password')
servers = func.mysql_query(
"select id,host,port,application_id,status from servers where is_delete=0;"
)
if servers:
for row in servers:
print row
else:
print "MySQLDB OK!"
def main():
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port,application,status from servers where is_delete=0;")
if servers:
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
application=row[3]
status=row[4]
if status <> 0:
check_slow_query(host,port,user,passwd,application,server_id)
def main():
#get idc type
idc_type = func.get_config('idc', 'idc_type')
if idc_type != "yf":
print "ide_type error"
return
#get mysql servers list
user = func.get_config('mysql_db', 'username')
passwd = func.get_config('mysql_db', 'password')
#get allow host
server_list = func.get_config('mysql_db', 'allow_server_list')
#print "delete from mysql_widget_hit_rate where idc_type='"+str(idc_type)+"'"
#func.mysql_exec("truncate table mysql_widget_hit_rate",'')
func.mysql_exec(
"delete from mysql_widget_hit_rate where idc_type='" + str(idc_type) +
"'", '')
#print "select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;"
servers = func.mysql_query(
"select id,host,port,status,idc_type from servers where idc_type='" +
str(idc_type) + "' and is_delete=0;")
if servers:
print("%s: check_mysql_widget_hit_rate controller started." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
status = row[3]
idc_type = row[4]
if host not in server_list:
print "Deny host:" + str(host)
continue
if status <> 0:
p = Process(target=check_mysql_widget,
args=(host, port, user, passwd, server_id,
idc_type))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_widget_hit_rate controller finished." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
def send_alarm_mail():
sql = "select alarm.application_id,app.display_name application,alarm.server_id,servers.host,servers.port,alarm.create_time,db_type,alarm_type,alarm_value,level,message,alarm.send_mail from alarm left join servers on alarm.server_id=servers.id left join application app on servers.application_id=app.id;"
result = func.mysql_query(sql)
if result <> 0:
send_alarm_mail = func.get_option('send_alarm_mail')
mail_to_list = func.get_option('mail_to_list')
mailto_list = mail_to_list.split(';')
for line in result:
application_id = line[0]
application = line[1]
server_id = [2]
host = line[3]
port = line[4]
create_time = line[5]
db_type = line[6]
alarm_type = line[7]
alarm_value = line[8]
level = line[9]
message = line[10]
send_mail = line[11]
if send_alarm_mail == "1":
if send_mail == 1:
mail_subject = message + ' 当前值:' + alarm_value + ' 服务器:' + application + '-' + host + ':' + port + ' 时间:' + create_time.strftime(
'%Y-%m-%d %H:%M:%S')
mail_content = "please check!"
result = func.send_mail(mailto_list, mail_subject,
mail_content)
if result:
send_mail_status = 1
else:
send_mail_status = 0
else:
send_mail_status = 0
else:
send_mail_status = 0
if send_mail_status == 1:
func.mysql_exec(
"insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1 from alarm;",
'')
elif send_mail_status == 0:
func.mysql_exec(
"insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0 from alarm;",
'')
func.mysql_exec("delete from alarm", '')
else:
pass
def main():
#get idc type
idc_type = func.get_config('idc', 'idc_type')
if idc_type != "yf":
print "ide_type error"
return
#get mysql servers list
user = func.get_config('mysql_db', 'username')
passwd = func.get_config('mysql_db', 'password')
#get allow host
server_list = func.get_config('mysql_db', 'allow_server_list')
killed_pids = func.mysql_query(
"select p.pid,s.host,s.port,s.status,s.idc_type from mysql_process_killed p left join servers s on p.server_id=s.id where s.idc_type='"
+ str(idc_type) + "';")
if killed_pids:
print("%s: admin_mysql_kill_process controller started." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
plist = []
for row in killed_pids:
print row
pid = row[0]
host = row[1]
port = row[2]
status = row[3]
idc_type = row[4]
if host not in server_list:
print "Deny host:" + str(host)
continue
if status <> 0:
p = Process(target=admin_mysql_kill_process,
args=(host, port, user, passwd, pid, idc_type))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: admin_mysql_kill_process controller finished." %
(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
func.mysql_exec(
"delete from mysql_process_killed where idc_type='" +
str(idc_type) + "'", '')
def get_alarm_mysql_status():
sql = "select a.application_id,a.server_id,a.create_time,a.connect,a.connections,a.active,b.send_mail,b.alarm_connections,b.alarm_active,b.threshold_connections,b.threshold_active,b.send_message from mysql_status a, servers b where a.server_id=b.id;"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
application_id = line[0]
server_id = line[1]
create_time = line[2]
connect = line[3]
connections = line[4]
active = line[5]
send_mail = line[6]
alarm_connections = line[7]
alarm_active = line[8]
threshold_connections = line[9]
threshold_active = line[10],
send_message = line[11]
if isinstance(threshold_active, tuple):
threshold_active = threshold_active[0]
if connect <> "success":
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_message) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (application_id, server_id, create_time, 'mysql',
'connect', connect, 'error', '数据库服务器连接失败', send_mail,
send_message)
func.mysql_exec(sql, param)
else:
if int(alarm_connections) == 1:
if int(connections) >= int(threshold_connections):
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_message) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (application_id, server_id, create_time,
'mysql', 'connections', connections,
'warning', '数据库总连接数过多', send_mail,
send_message)
func.mysql_exec(sql, param)
if int(alarm_active) == 1:
if int(active) >= int(threshold_active):
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_message) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (application_id, server_id, create_time,
'mysql', 'active', active, 'warning',
'数据库活动连接过多', send_mail, send_message)
func.mysql_exec(sql, param)
else:
pass
def send_alarm_mail():
sql="select alarm.application_id,app.display_name application,alarm.server_id,servers.host,servers.port,alarm.create_time,db_type,alarm_type,alarm_value,level,message,alarm.send_mail from alarm left join servers on alarm.server_id=servers.id left join application app on servers.application_id=app.id;"
result=func.mysql_query(sql)
if result <> 0:
send_alarm_mail = func.get_option('send_alarm_mail')
mail_to_list = func.get_option('mail_to_list')
mailto_list=mail_to_list.split(';')
for line in result:
application_id=line[0]
application=line[1]
server_id=[2]
host=line[3]
port=line[4]
create_time=line[5]
db_type=line[6]
alarm_type=line[7]
alarm_value=line[8]
level=line[9]
message=line[10]
send_mail=line[11]
if send_alarm_mail=="1":
if send_mail==1:
mail_subject=message+' 当前值:'+alarm_value+' 服务器:'+application+'-'+host+':'+port+' 时间:'+create_time.strftime('%Y-%m-%d %H:%M:%S')
mail_content="please check!"
result = func.send_mail(mailto_list,mail_subject,mail_content)
if result:
send_mail_status=1
else:
send_mail_status=0
else:
send_mail_status=0
else:
send_mail_status=0
if send_mail_status==1:
func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1 from alarm;",'')
elif send_mail_status==0:
func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0 from alarm;",'')
func.mysql_exec("delete from alarm",'')
else:
pass
def main():
log_level = "info"
log_config = global_logs.CLogConfig(level=log_level)
log_name = "./log/check_redis_status.log"
log_config._path = "%s.%s" % (log_name, func_time.today())
log = global_logs.initLog(log_config, logmodule="status")
if log == None:
print "init log error!"
servers = func.mysql_query(
"select id,host,port,passwd,application_id,status from servers where is_delete=0;"
)
frequency_monitor = func.get_option('frequency_monitor')
if servers:
#print("servers %s" %(servers,));
log.info("check_mysql_status controller started...")
init_table()
plist = []
for row in servers:
server_id = row[0]
host = row[1]
port = row[2]
passwd = row[3]
application_id = row[4]
status = row[5]
if status <> 0:
p = Process(target=check_redis_status,
args=(host, port, passwd, server_id,
application_id, frequency_monitor, log))
plist.append(p)
p.start()
for p in plist:
p.join()
#set replication delay
sql = "delete from redis_replication_last;"
func.mysql_exec(sql, '')
sql = "insert into redis_replication_last select * from redis_replication;"
func.mysql_exec(sql, '')
sql = "update redis_replication a,redis_replication_last b,servers c set a.delay=b.master_repl_offset-a.slave_repl_offset where a.master_host=c.host and a.master_port=c.port and b.server_id=c.id and a.is_slave=1 and b.is_master=1;"
func.mysql_exec(sql, '')
log.info("check_mysql_status controller finished...")
def main():
func.mysql_exec("insert into mysql_replication_history(server_id,application,host,port,master,slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select server_id,application,host,port,master,slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication;",'')
func.mysql_exec("delete from mysql_replication",'')
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port,application,status from servers where is_delete=0;")
if servers:
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
application=row[3]
status=row[4]
if status <> 0:
result=check_mysql_replication(host,port,user,passwd)
if result:
sql="insert into mysql_replication(server_id,application,host,port,master,slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param=(server_id,application,host,port,result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7],result[8],result[9],result[10],result[11])
func.mysql_exec(sql,param)
def main():
func.mysql_exec("delete from mysql_performance",'')
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port,application,status from servers where is_delete=0;")
if servers:
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
application=row[3]
status=row[4]
if status <> 0:
result=check_mysql_performance(host,port,user,passwd)
#print result
if result:
sql="insert into mysql_performance(server_id,application,host,port,Query_cache_hits,Key_buffer_read_hits,Key_buffer_write_hits,Thread_cache_hits,Key_blocks_used_rate,Created_tmp_disk_tables_rate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param=(server_id,application,host,port,result[0],result[1],result[2],result[3],result[4],result[5])
func.mysql_exec(sql,param)
def main():
func.mysql_exec("insert into mysql_status_history(server_id,application,host,port,connect,uptime,version,connections,active,create_time,YmdHi) select server_id,application,host,port,connect,uptime,version,connections,active,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status;",'')
func.mysql_exec("delete from mysql_status",'')
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port,application,status from servers where is_delete=0;")
if servers:
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
application=row[3]
status=row[4]
if status <> 0:
result=check_mysql_status(host,port,user,passwd)
sql="insert into mysql_status(server_id,application,host,port,connect,uptime,version,connections,active) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param=(server_id,application,host,port,result[0],result[1],result[2],result[3],result[4])
func.mysql_exec(sql,param)
def main():
func.mysql_exec("truncate table mysql_widget_hit_rate",'')
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port from servers where is_delete=0;")
if servers:
print("%s: check_mysql_widget_hit_rate controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
p = Process(target = check_mysql_widget, args = (host,port,user,passwd,server_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_widget_hit_rate controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def main():
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
killed_pids=func.mysql_query("select p.pid,s.host,s.port from mysql_process_killed p left join servers s on p.server_id=s.id;")
if killed_pids:
print("%s: admin_mysql_kill_process controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in killed_pids:
pid=row[0]
host=row[1]
port=row[2]
p = Process(target = admin_mysql_kill_process, args = (host,port,user,passwd,pid))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: admin_mysql_kill_process controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
func.mysql_exec("delete from mysql_process_killed",'')
def get_alarm_redis_run_status():
sql="select a.application_id,a.server_id,a.create_time,a.max_clients,a.connected_clients,ifnull(round(100*a.connected_clients/a.max_clients,2),0),c.threshold_connections from redis_run_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_connections=1;"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
application_id=line[0]
server_id=line[1]
create_time=line[2]
max_clients=line[3]
connected_clients=line[4]
connection_use_rate=line[5]
threshold_connections=line[6]
if int(connection_use_rate)>=int(threshold_connections):
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message="WARN:Redis连接数过多,已用数"+str(connected_clients)+",使用率" + str(connection_use_rate) + "%>=" + threshold_connections +"%"
param=(application_id,server_id,create_time,'redis','connections',connected_clients,'warning',message,'1')
func.mysql_exec(sql,param)
else:
pass
def get_alarm_redis_server_status():
sql="select a.application_id,a.server_id,a.create_time,a.connect,a.info from redis_server_status a, servers b where a.server_id=b.id and b.send_mail='1' and b.is_delete=0 and b.status=1;"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
application_id=line[0]
server_id=line[1]
create_time=line[2]
connect=line[3]
info=line[4]
if connect != "success":
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message="ERROR:Redis连接失败 "+info
param=(application_id,server_id,create_time,'redis','connect',connect,'error',message,'1')
func.mysql_exec(sql,param)
else:
pass
else:
#log.error("ERROR:exe sql failed, %s"%(sql))
pass
def get_alarm_redis_resource_status():
sql="select a.application_id,a.server_id,a.create_time,a.max_memory,a.used_memory,ifnull(round(100*a.used_memory/a.max_memory,2),0),c.threshold_used_memory from redis_resource_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_used_memory=1;"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
application_id=line[0]
server_id=line[1]
create_time=line[2]
max_memory=line[3]
used_memory=line[4]
memory_use_rate=line[5]
threshold_used_memory=line[6]
if int(memory_use_rate)>=int(threshold_used_memory):
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message="WARN:Redis内存使用过多,已用"+str(int(used_memory/1024/1024))+"M,使用率" + str(memory_use_rate) + "%>=" + threshold_used_memory +"%"
param=(application_id,server_id,create_time,'redis','memory',used_memory,'warning',message,'1')
func.mysql_exec(sql,param)
else:
pass
def main():
#get idc type
idc_type = func.get_config('idc','idc_type')
if idc_type != "yf":
print "ide_type error"
return
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
#get allow host
server_list = func.get_config('mysql_db','allow_server_list')
func.mysql_exec("insert into mysql_replication_history(idc_type,server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time,YmdHi) select '"+str(idc_type)+"',server_id,application_id,is_master,is_slave,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,create_time, LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication where idc_type='"+str(idc_type)+"';",'')
func.mysql_exec("delete from mysql_replication where idc_type='"+str(idc_type)+"'",'')
servers=func.mysql_query("select id,host,port,application_id,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;")
if servers:
print("%s: check_mysql_replication controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
application_id=row[3]
status=row[4]
idc_type=row[5]
if host not in server_list:
print "Deny host:"+str(host)
continue
if status <> 0:
p = Process(target = check_mysql_replication, args = (host,port,user,passwd,server_id,application_id,idc_type))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_replication controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def get_alarm_redis_server_status():
sql = "select a.application_id,a.server_id,a.create_time,a.connect,a.info from redis_server_status a, servers b where a.server_id=b.id and b.send_mail='1' and b.is_delete=0 and b.status=1;"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
application_id = line[0]
server_id = line[1]
create_time = line[2]
connect = line[3]
info = line[4]
if connect != "success":
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message = "ERROR:Redis连接失败 " + info
param = (application_id, server_id, create_time, 'redis',
'connect', connect, 'error', message, '1')
func.mysql_exec(sql, param)
else:
pass
else:
#log.error("ERROR:exe sql failed, %s"%(sql))
pass
def main():
#get idc type
idc_type = func.get_config('idc','idc_type')
if idc_type != "yf":
print "ide_type error"
return
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
#get allow host
server_list = func.get_config('mysql_db','allow_server_list')
#print "delete from mysql_widget_hit_rate where idc_type='"+str(idc_type)+"'"
#func.mysql_exec("truncate table mysql_widget_hit_rate",'')
func.mysql_exec("delete from mysql_widget_hit_rate where idc_type='"+str(idc_type)+"'",'')
#print "select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;"
servers=func.mysql_query("select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;")
if servers:
print("%s: check_mysql_widget_hit_rate controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
status=row[3]
idc_type=row[4]
if host not in server_list:
print "Deny host:"+str(host)
continue
if status <> 0:
p = Process(target = check_mysql_widget, args = (host,port,user,passwd,server_id,idc_type))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_widget_hit_rate controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def get_alarm_mysql_slowquerys():
sql="select b.application_id,a.server_id,a.modify_time,(ts_cnt_sum-ts_cnt_sum_last) as ts_cnt,if((ts_cnt_sum-ts_cnt_sum_last)>0,(Query_time_sum-Query_time_sum_last)/(ts_cnt_sum-ts_cnt_sum_last),'0') as query_time_avg,b.send_mail,b.alarm_slow_querys,b.threshold_slow_querys from mysql_slow_query_status a,servers b where a.server_id=b.id and b.send_mail='1';"
result=func.mysql_query(sql)
if result <> 0:
for line in result:
application_id=line[0]
server_id=line[1]
create_time=line[2]
ts_cnt=line[3]
query_time_avg=line[4]
send_mail=line[5]
alarm_slow_querys=line[6]
threshold_slow_querys=line[7]
if int(alarm_slow_querys)==1:
if int(ts_cnt)>=int(threshold_slow_querys):
sql="insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message="WARN:慢查询语句过多 " + str(ts_cnt) + ">=" + str(threshold_slow_querys)
param=(application_id,server_id,create_time,'mysql','slow_query',ts_cnt,'warning',message,send_mail)
func.mysql_exec(sql,param)
else:
pass
def main():
#get idc type
idc_type = func.get_config('idc','idc_type')
if idc_type != "yf":
print "ide_type error"
return
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
#get allow host
server_list = func.get_config('mysql_db','allow_server_list')
func.mysql_exec("insert into mysql_status_ext_history(server_id,idc_type,QPS,TPS,Bytes_received,Bytes_sent,create_time,YmdHi) select server_id,'"+str(idc_type)+"',QPS,TPS,Bytes_received,Bytes_sent,create_time,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status_ext where idc_type='"+str(idc_type)+"';",'')
func.mysql_exec("delete from mysql_status_ext where idc_type='"+str(idc_type)+"'",'')
servers=func.mysql_query("select id,host,port,status,idc_type from servers where idc_type='"+str(idc_type)+"' and is_delete=0;")
if servers:
print("%s: check_mysql_status_ext controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
status=row[3]
idc_type=row[4]
if host not in server_list:
print "Deny host:"+str(host)
continue
if status <> 0:
p = Process(target = check_mysql_status_ext, args = (host,port,user,passwd,server_id,idc_type))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_status_ext controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def main():
#get idc type
idc_type = func.get_config('idc','idc_type')
if idc_type != "yf":
print "ide_type error"
return
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
#get allow host
server_list = func.get_config('mysql_db','allow_server_list')
killed_pids=func.mysql_query("select p.pid,s.host,s.port,s.status,s.idc_type from mysql_process_killed p left join servers s on p.server_id=s.id where s.idc_type='"+str(idc_type)+"';")
if killed_pids:
print("%s: admin_mysql_kill_process controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in killed_pids:
print row
pid=row[0]
host=row[1]
port=row[2]
status=row[3]
idc_type=row[4]
if host not in server_list:
print "Deny host:"+str(host)
continue
if status <> 0:
p = Process(target = admin_mysql_kill_process, args = (host,port,user,passwd,pid,idc_type))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: admin_mysql_kill_process controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
func.mysql_exec("delete from mysql_process_killed where idc_type='"+str(idc_type)+"'",'')
def main():
log_level="info"
log_config=global_logs.CLogConfig(level=log_level)
log_name="./log/check_redis_status.log"
log_config._path="%s.%s"%(log_name,func_time.today())
log=global_logs.initLog(log_config,logmodule="status")
if log == None:
print "init log error!"
servers=func.mysql_query("select id,host,port,passwd,application_id,status from servers where is_delete=0;")
frequency_monitor = func.get_option('frequency_monitor')
if servers:
#print("servers %s" %(servers,));
log.info("check_mysql_status controller started..." );
init_table()
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
passwd=row[3]
application_id=row[4]
status=row[5]
if status <> 0:
p = Process(target = check_redis_status, args = (host,port,passwd,server_id,application_id,frequency_monitor,log))
plist.append(p)
p.start()
for p in plist:
p.join()
#set replication delay
sql="delete from redis_replication_last;"
func.mysql_exec(sql,'')
sql="insert into redis_replication_last select * from redis_replication;"
func.mysql_exec(sql,'')
sql="update redis_replication a,redis_replication_last b,servers c set a.delay=b.master_repl_offset-a.slave_repl_offset where a.master_host=c.host and a.master_port=c.port and b.server_id=c.id and a.is_slave=1 and b.is_master=1;"
func.mysql_exec(sql,'')
log.info("check_mysql_status controller finished..." )
def get_alarm_redis_resource_status():
sql = "select a.application_id,a.server_id,a.create_time,a.max_memory,a.used_memory,ifnull(round(100*a.used_memory/a.max_memory,2),0),c.threshold_used_memory from redis_resource_status a,redis_server_status b,servers c where a.server_id=b.server_id and a.server_id=c.id and b.connect='success' and c.send_mail='1' and c.is_delete=0 and c.status=1 and c.alarm_used_memory=1;"
result = func.mysql_query(sql)
if result <> 0:
for line in result:
application_id = line[0]
server_id = line[1]
create_time = line[2]
max_memory = line[3]
used_memory = line[4]
memory_use_rate = line[5]
threshold_used_memory = line[6]
if int(memory_use_rate) >= int(threshold_used_memory):
sql = "insert into alarm(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);"
message = "WARN:Redis内存使用过多,已用" + str(
int(used_memory / 1024 / 1024)) + "M,使用率" + str(
memory_use_rate) + "%>=" + threshold_used_memory + "%"
param = (application_id, server_id, create_time, 'redis',
'memory', used_memory, 'warning', message, '1')
func.mysql_exec(sql, param)
else:
pass
def main():
func.mysql_exec("delete from mysql_process",'')
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port,application_id,status from servers where is_delete=0;")
if servers:
print("%s: check_mysql_process controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
application_id=row[3]
status=row[4]
if status <> 0:
p = Process(target = check_mysql_process, args = (host,port,user,passwd,server_id,application_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_process controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
def send_alarm_message():
sql = "select alarm.application_id,app.display_name application,alarm.server_id,servers.host,servers.port,alarm.create_time,db_type,alarm_type,alarm_value,level,message,alarm.send_message from alarm left join servers on alarm.server_id=servers.id left join application app on servers.application_id=app.id;"
result = func.mysql_query(sql)
if result <> 0:
send_alarm_message = func.get_option('send_alarm_message')
phone = func.get_option('message_to_list')
for line in result:
application_id = line[0]
application = line[1]
server_id = [2]
host = line[3]
port = line[4]
create_time = line[5]
db_type = line[6]
alarm_type = line[7]
alarm_value = line[8]
level = line[9]
message = line[10]
send_message = line[11]
send_message_status = 0
if send_alarm_message == "1":
if send_message == 1:
phone_list = phone.split(';')
all_send_result = []
for i in phone_list:
values = {}
values['phone'] = i
values['type'] = 7
values['signname'] = '小店监控'
values['server'] = host
values['errmsg'] = message
result = func.send_message(values)
all_send_result.append(result)
all_flag = True
# 有一人收到 成功发送
for index, res in enumerate(all_send_result):
if res == True:
all_flag = True
if all_flag:
send_message_status = 1
else:
send_message_status = 0
else:
send_message_status = 0
else:
send_message_status = 0
if send_message_status == 1:
func.mysql_exec(
"insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_message,send_message_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_message,1 from alarm;",
'')
elif send_message_status == 0:
func.mysql_exec(
"insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_message,send_message_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_message,0 from alarm;",
'')
func.mysql_exec("delete from alarm", '')
else:
pass
def send_alarm_mail(idc_type='ts'):
sql = "select alarm.application_id,app.display_name application,alarm.server_id,servers.host,servers.port,alarm.create_time,db_type,alarm_type,alarm_value,level,message,alarm.send_mail,servers.alarm_time,servers.threshold_starttime,servers.threshold_endtime from alarm left join servers on alarm.server_id=servers.id left join application app on servers.application_id=app.id where servers.idc_type='" + str(
idc_type) + "';"
result = func.mysql_query(sql)
if result <> 0:
send_alarm_mail = func.get_option('send_alarm_mail')
mail_to_list = func.get_option('mail_to_list')
mailto_list = mail_to_list.split(';')
global_alarm_type = func.get_config('global_alarm_type', 'alarm_type')
global_alarm_type_list = global_alarm_type.split(',')
for line in result:
application_id = line[0]
application = line[1]
server_id = line[2]
host = line[3]
port = line[4]
create_time = line[5]
db_type = line[6]
alarm_type = line[7]
alarm_value = line[8]
level = line[9]
message = line[10]
send_mail = line[11]
alarm_time = line[12]
threshold_starttime = line[13]
threshold_endtime = line[14]
app_email = func.get_application_alarm_email(str(application_id))
app_email_list = app_email.split(',')
for email_app in app_email_list:
if email_app not in mailto_list:
mailto_list.append(email_app)
else:
pass
if send_alarm_mail == "1":
if send_mail == 1:
mail_subject = message + ' 当前值:' + alarm_value + ' 服务器:' + application + '-' + host + ':' + port + ' 时间:' + create_time.strftime(
'%Y-%m-%d %H:%M:%S')
mail_content = "请检查下!"
now_hour = ("0" + str(datetime.now().hour)
if datetime.now().hour < 10 else str(
datetime.now().hour)) + ":" + (
"0" + str(datetime.now().minute)
if datetime.now().minute < 10 else str(
datetime.now().minute))
if alarm_type not in global_alarm_type_list:
if (int(alarm_time) == 1) and (
threshold_starttime.strip() !=
"") and (threshold_endtime.strip() != "") and (
threshold_endtime > threshold_starttime
) and (now_hour <= threshold_endtime) and (
now_hour >= threshold_starttime):
result_mail = func.send_mail(
mailto_list, mail_subject, mail_content)
elif int(alarm_time) != 1:
result_mail = func.send_mail(
mailto_list, mail_subject, mail_content)
else:
result_mail = False
else:
result_mail = func.send_mail(mailto_list, mail_subject,
mail_content)
if result_mail:
send_mail_status = 1
else:
send_mail_status = 0
else:
send_mail_status = 0
else:
send_mail_status = 0
if send_mail_status == 1:
func.mysql_exec(
"insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1,'"
+ str(idc_type) + "' from alarm;", '')
elif send_mail_status == 0:
func.mysql_exec(
"insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0,'"
+ str(idc_type) + "' from alarm;", '')
func.mysql_exec(
"delete from alarm where idc_type='" + str(idc_type) + "'", '')
else:
pass
def send_alarm_mail(idc_type='ts'):
sql="select alarm.application_id,app.display_name application,alarm.server_id,servers.host,servers.port,alarm.create_time,db_type,alarm_type,alarm_value,level,message,alarm.send_mail,servers.alarm_time,servers.threshold_starttime,servers.threshold_endtime from alarm left join servers on alarm.server_id=servers.id left join application app on servers.application_id=app.id where servers.idc_type='"+str(idc_type)+"';"
result=func.mysql_query(sql)
if result <> 0:
send_alarm_mail = func.get_option('send_alarm_mail')
mail_to_list = func.get_option('mail_to_list')
mailto_list=mail_to_list.split(';')
global_alarm_type=func.get_config('global_alarm_type','alarm_type')
global_alarm_type_list=global_alarm_type.split(',')
for line in result:
application_id=line[0]
application=line[1]
server_id=line[2]
host=line[3]
port=line[4]
create_time=line[5]
db_type=line[6]
alarm_type=line[7]
alarm_value=line[8]
level=line[9]
message=line[10]
send_mail=line[11]
alarm_time=line[12]
threshold_starttime=line[13]
threshold_endtime=line[14]
app_email=func.get_application_alarm_email(str(application_id))
app_email_list=app_email.split(',')
for email_app in app_email_list:
if email_app not in mailto_list:
mailto_list.append(email_app)
else:
pass
if send_alarm_mail=="1":
if send_mail==1:
mail_subject=message+' 当前值:'+alarm_value+' 服务器:'+application+'-'+host+':'+port+' 时间:'+create_time.strftime('%Y-%m-%d %H:%M:%S')
mail_content="请检查下!"
now_hour = ("0"+str(datetime.now().hour) if datetime.now().hour<10 else str(datetime.now().hour))+":"+( "0"+str(datetime.now().minute) if datetime.now().minute<10 else str(datetime.now().minute))
if alarm_type not in global_alarm_type_list:
if (int(alarm_time) == 1) and (threshold_starttime.strip() != "") and (threshold_endtime.strip() != "") and (threshold_endtime > threshold_starttime) and (now_hour <= threshold_endtime) and (now_hour >= threshold_starttime):
result_mail = func.send_mail(mailto_list,mail_subject,mail_content)
elif int(alarm_time) != 1:
result_mail = func.send_mail(mailto_list,mail_subject,mail_content)
else:
result_mail = False
else:
result_mail = func.send_mail(mailto_list,mail_subject,mail_content)
if result_mail:
send_mail_status=1
else:
send_mail_status=0
else:
send_mail_status=0
else:
send_mail_status=0
if send_mail_status==1:
func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1,'"+str(idc_type)+"' from alarm;",'')
elif send_mail_status==0:
func.mysql_exec("insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status,idc_type) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0,'"+str(idc_type)+"' from alarm;",'')
func.mysql_exec("delete from alarm where idc_type='"+str(idc_type)+"'",'')
else:
pass
def check_mysql_slow_query(host, port, user, passwd, server_id,
application_id):
try:
connect = MySQLdb.connect(host=host,
user=user,
passwd=passwd,
port=int(port),
connect_timeout=2,
charset='utf8')
cur = connect.cursor()
connect.select_db('mysqlmtop')
sql = "select * from mysqlmtop.mysql_status where server_id = %d" % server_id
cur.execute(sql)
host_status_data = cur.fetchone()
slave_server_id = host_status_data[len(host_status_data) - 1]
# mysqlmtop slow log start
# SELECT table_name FROM information_schema.TABLES WHERE table_name ='mysql_slow_query_review_1';
table_name = "mysql_slow_query_review_%d" % server_id
is_exist_table = func.check_table_exist(table_name)
# print(is_exist_table)
if is_exist_table == 0:
# craete table
func.create_slow_table_by_name(table_name)
# sure select where
get_slow_sql = "select * from mysqlmtop.mysql_slow_query_review_%d order by start_time desc limit 1" % server_id
cur.execute(get_slow_sql)
mysqlmtop_slow_query = cur.fetchone()
# mysqlmtop slow log end
where_log = "db != 'mysqlmtop' and `server_id` = %d" % slave_server_id
if mysqlmtop_slow_query:
where_log = "db != 'mysqlmtop' and `server_id` = %d and `start_time` > '%s'" % (
slave_server_id, '2018-01-01 00:00:00')
msql = "select * from mysql.slow_log where %s order by start_time desc " % where_log
cur.execute(msql)
one_slow_query = cur.fetchone()
#print(one_slow_query)
while one_slow_query:
# insert db
# insert_sql = "insert into mysqlmtop.mysql_slow_query_review_%d values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" % (int(server_id),
# one_slow_query[0],
# one_slow_query[1],
# one_slow_query[2],
# one_slow_query[3],
# one_slow_query[4],
# one_slow_query[5],
# one_slow_query[6],
# one_slow_query[7],
# one_slow_query[8],
# one_slow_query[9],
# cur2.escape_string(one_slow_query[10]),
# one_slow_query[11])
# print(insert_sql)
# cur2.execute(insert_sql)
param = (one_slow_query[0], one_slow_query[1], one_slow_query[2],
one_slow_query[3], one_slow_query[4], one_slow_query[5],
one_slow_query[6], one_slow_query[7], one_slow_query[8],
one_slow_query[9], one_slow_query[10], one_slow_query[11])
insert_sql = "insert into mysqlmtop.test_slow " \
"(`start_time`,`user_host`,`query_time`,`lock_time`,`rows_sent`,`rows_examined`,`db`,`last_insert_id`,`insert_id`,`server_id`,`sql_text`,`thread_id`) " \
"values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\"%s\",%s);" % param
print(insert_sql)
# print insert_sql % param
# func.mysql_exec(insert_sql,param)
func.mysql_query(insert_sql)
one_slow_query = cur.fetchone()
one_slow_query = None
except MySQLdb.Error, e:
print(e)
pass
def send_warn():
#server_id not delete???
sql="select alarm_last_status.application_id,app.display_name application,alarm_last_status.server_id,servers.host,servers.port,alarm_last_status.modify_time,servers.alarm_interval,servers.alarm_type,servers.converge_type,servers.alarm_person,servers.send_mail,alarm_last_status.connect,alarm_last_status.status_connections,alarm_last_status.status_used_memory,alarm_last_status.status_binlog_count,alarm_last_status.status_innodb_space_free,alarm_last_status.status_replication_relay,alarm_last_status.alarm_num,alarm_last_status.time_error_continue,alarm_last_status.slow_querys,alarm_last_status.status_slow_querys,alarm_last_status.error_log,alarm_last_status.status_error_log from alarm_last_status left join servers on alarm_last_status.server_id=servers.id left join application app on servers.application_id=app.id;"
result=func.mysql_query(sql)
if result <> 0:
domain="www.oa.com"
if(pingServerCall(domain)==False):
#系统告警接收人
mailto_list = func.get_option('mail_to_list')
mail_subject="redis monitor error"
mail_content="ping failed:"+domain +",do not send warn..."
func.send_alarm(0,mailto_list,mail_subject,mail_content.encode('utf-8'))
return None
#全局配置--发送告警
send_alarm_mail = func.get_option('send_alarm_mail')
#全局配置--报警检测
frequency_alarm = func.get_option('frequency_alarm')
#mail_to_list = func.get_option('mail_to_list')
#mailto_list=mail_to_list.split(';')
for line in result:
application_id=line[0]
application=line[1]
server_id=line[2]
host=line[3]
port=line[4]
create_time=line[5]
alarm_interval=line[6]
alarm_type=line[7]
converge_type=line[8]
alarm_person=line[9]
send_alarm=line[10]
connect=line[11]
status_connections=line[12]
status_used_memory=line[13]
status_binlog_count=line[14]
status_innodb_space_free=line[15]
status_replication_relay=line[16]
alarm_num=line[17]
time_error_continue=line[18]
slow_querys=line[19]
status_slow_querys=line[20]
error_log=line[21]
status_error_log=line[22]
warn_content=application+'-'+host+':'+port
eStr="ERROR:"
wStr="WARN:"
if (eStr in connect) or (wStr in connect):
warn_content=warn_content+' '+connect
else:
if (eStr in status_connections) or (wStr in status_connections):
warn_content=warn_content+' '+status_connections
if (eStr in status_used_memory) or (wStr in status_used_memory):
warn_content=warn_content+' '+status_used_memory
if (eStr in status_binlog_count) or (wStr in status_binlog_count):
warn_content=warn_content+' '+status_binlog_count
if (eStr in status_innodb_space_free) or (wStr in status_innodb_space_free):
warn_content=warn_content+' '+status_innodb_space_free
if (eStr in status_replication_relay) or (wStr in status_replication_relay):
warn_content=warn_content+' '+status_replication_relay
if (eStr in status_slow_querys) or (wStr in status_slow_querys):
warn_content=warn_content+' '+status_slow_querys
if (eStr in status_error_log) or (wStr in status_error_log):
warn_content=warn_content+' '+status_error_log
warn_content=warn_content+' ['+create_time.strftime('%Y-%m-%d %H:%M:%S')+']'
#warn_content=warn_content.encode('utf-8')
#print send_alarm_mail
#print send_alarm
#print warn_content
#print("WARN:%s send alarm:%s alarm:%s" %(warn_content,send_alarm_mail,send_alarm))
mailto_list=alarm_person
mail_subject="REDIS WARNING"
send_mail_status=0
if send_alarm_mail=='1':
if send_alarm==1:
next_alarm_time=getTimeInterVal(converge_type,alarm_interval,alarm_num,0);
if(func.test_flag):
log.debug("time_error_continue:%s next_alarm_time:%s converge_type:%s alarm_interval:%s alarm_num:%s"%(str(time_error_continue),str(next_alarm_time),converge_type,str(alarm_interval),str(alarm_num)))
if time_error_continue >= next_alarm_time:
result=func.send_alarm(alarm_type,mailto_list,mail_subject,warn_content.encode('utf-8'))
if result:
send_mail_status=1
else:
send_mail_status=0
sql="update alarm_last_status set alarm_num=alarm_num+1 where application_id='"+str(application_id)+"' and server_id='"+str(server_id)+"'"
func.mysql_exec(sql,'')
log.warning("send alarm:alarm_type:%s mailto_list:%s mail_subject:%s warn_content:%s"%(str(alarm_type),mailto_list,mail_subject,warn_content.encode('utf-8')))
else:
sql="delete from alarm where application_id='"+str(application_id)+"' and server_id='"+str(server_id)+"'"
func.mysql_exec(sql,'')
else:
send_mail_status=0
else:
send_mail_status=0
sql="update alarm_last_status set time_error_continue=time_error_continue+"+str(frequency_alarm)+" where application_id='"+str(application_id)+"' and server_id='"+str(server_id)+"'"
func.mysql_exec(sql,'')
#param=(str(application_id),str(server_id))
if send_mail_status==1:
sql="insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1 from alarm where application_id='"+str(application_id)+"' and server_id='"+str(server_id)+"';"
func.mysql_exec(sql,'')
elif send_mail_status==0:
sql="insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0 from alarm where application_id='"+str(application_id)+"' and server_id='"+str(server_id)+"';"
func.mysql_exec(sql,'')
sql="delete from alarm where application_id='"+str(application_id)+"' and server_id='"+str(server_id)+"';"
func.mysql_exec(sql,'')
else:
pass
def send_warn():
#server_id not delete???
sql = "select alarm_last_status.application_id,app.display_name application,alarm_last_status.server_id,servers.host,servers.port,alarm_last_status.modify_time,servers.alarm_interval,servers.alarm_type,servers.converge_type,servers.alarm_person,servers.send_mail,alarm_last_status.connect,alarm_last_status.status_connections,alarm_last_status.status_used_memory,alarm_last_status.status_binlog_count,alarm_last_status.status_innodb_space_free,alarm_last_status.status_replication_relay,alarm_last_status.alarm_num,alarm_last_status.time_error_continue,alarm_last_status.slow_querys,alarm_last_status.status_slow_querys,alarm_last_status.error_log,alarm_last_status.status_error_log from alarm_last_status left join servers on alarm_last_status.server_id=servers.id left join application app on servers.application_id=app.id;"
result = func.mysql_query(sql)
if result <> 0:
domain = "www.oa.com"
if (pingServerCall(domain) == False):
#系统告警接收人
mailto_list = func.get_option('mail_to_list')
mail_subject = "redis monitor error"
mail_content = "ping failed:" + domain + ",do not send warn..."
func.send_alarm(0, mailto_list, mail_subject,
mail_content.encode('utf-8'))
return None
#全局配置--发送告警
send_alarm_mail = func.get_option('send_alarm_mail')
#全局配置--报警检测
frequency_alarm = func.get_option('frequency_alarm')
#mail_to_list = func.get_option('mail_to_list')
#mailto_list=mail_to_list.split(';')
for line in result:
application_id = line[0]
application = line[1]
server_id = line[2]
host = line[3]
port = line[4]
create_time = line[5]
alarm_interval = line[6]
alarm_type = line[7]
converge_type = line[8]
alarm_person = line[9]
send_alarm = line[10]
connect = line[11]
status_connections = line[12]
status_used_memory = line[13]
status_binlog_count = line[14]
status_innodb_space_free = line[15]
status_replication_relay = line[16]
alarm_num = line[17]
time_error_continue = line[18]
slow_querys = line[19]
status_slow_querys = line[20]
error_log = line[21]
status_error_log = line[22]
warn_content = application + '-' + host + ':' + port
eStr = "ERROR:"
wStr = "WARN:"
if (eStr in connect) or (wStr in connect):
warn_content = warn_content + ' ' + connect
else:
if (eStr in status_connections) or (wStr
in status_connections):
warn_content = warn_content + ' ' + status_connections
if (eStr in status_used_memory) or (wStr
in status_used_memory):
warn_content = warn_content + ' ' + status_used_memory
if (eStr in status_binlog_count) or (wStr
in status_binlog_count):
warn_content = warn_content + ' ' + status_binlog_count
if (eStr in status_innodb_space_free) or (
wStr in status_innodb_space_free):
warn_content = warn_content + ' ' + status_innodb_space_free
if (eStr in status_replication_relay) or (
wStr in status_replication_relay):
warn_content = warn_content + ' ' + status_replication_relay
if (eStr in status_slow_querys) or (wStr
in status_slow_querys):
warn_content = warn_content + ' ' + status_slow_querys
if (eStr in status_error_log) or (wStr in status_error_log):
warn_content = warn_content + ' ' + status_error_log
warn_content = warn_content + ' [' + create_time.strftime(
'%Y-%m-%d %H:%M:%S') + ']'
#warn_content=warn_content.encode('utf-8')
#print send_alarm_mail
#print send_alarm
#print warn_content
#print("WARN:%s send alarm:%s alarm:%s" %(warn_content,send_alarm_mail,send_alarm))
mailto_list = alarm_person
mail_subject = "REDIS WARNING"
send_mail_status = 0
if send_alarm_mail == '1':
if send_alarm == 1:
next_alarm_time = getTimeInterVal(converge_type,
alarm_interval,
alarm_num, 0)
if (func.test_flag):
log.debug(
"time_error_continue:%s next_alarm_time:%s converge_type:%s alarm_interval:%s alarm_num:%s"
% (str(time_error_continue), str(next_alarm_time),
converge_type, str(alarm_interval),
str(alarm_num)))
if time_error_continue >= next_alarm_time:
result = func.send_alarm(alarm_type, mailto_list,
mail_subject,
warn_content.encode('utf-8'))
if result:
send_mail_status = 1
else:
send_mail_status = 0
sql = "update alarm_last_status set alarm_num=alarm_num+1 where application_id='" + str(
application_id) + "' and server_id='" + str(
server_id) + "'"
func.mysql_exec(sql, '')
log.warning(
"send alarm:alarm_type:%s mailto_list:%s mail_subject:%s warn_content:%s"
% (str(alarm_type), mailto_list, mail_subject,
warn_content.encode('utf-8')))
else:
sql = "delete from alarm where application_id='" + str(
application_id) + "' and server_id='" + str(
server_id) + "'"
func.mysql_exec(sql, '')
else:
send_mail_status = 0
else:
send_mail_status = 0
sql = "update alarm_last_status set time_error_continue=time_error_continue+" + str(
frequency_alarm) + " where application_id='" + str(
application_id) + "' and server_id='" + str(
server_id) + "'"
func.mysql_exec(sql, '')
#param=(str(application_id),str(server_id))
if send_mail_status == 1:
sql = "insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,1 from alarm where application_id='" + str(
application_id) + "' and server_id='" + str(
server_id) + "';"
func.mysql_exec(sql, '')
elif send_mail_status == 0:
sql = "insert into alarm_history(application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,send_mail_status) select application_id,server_id,create_time,db_type,alarm_type,alarm_value,level,message,send_mail,0 from alarm where application_id='" + str(
application_id) + "' and server_id='" + str(
server_id) + "';"
func.mysql_exec(sql, '')
sql = "delete from alarm where application_id='" + str(
application_id) + "' and server_id='" + str(server_id) + "';"
func.mysql_exec(sql, '')
else:
pass
def backup_data(log):
current_minute=time.strftime('%H:%M', time.localtime())
current_date=time.strftime('%Y-%m-%d', time.localtime())
#test
#sql="delete from redis_coldback_info where server_id='3';"
#func.mysql_exec(sql,'')
sql="select server_id,IP,ssh_port,ssh_user,ssh_passwd,back_IP,back_ssh_port,back_ssh_user,back_ssh_passwd,back_path,db_name,back_cycle,save_number,alarm_flag,charge_person from redis_coldback_config where back_flag='y' and back_time<='"+current_minute+"' and server_id not in (select distinct server_id from redis_coldback_info where suc_flag='y' and date='"+current_date+"');"
servers=func.mysql_query(sql)
#print sql
#frequency_monitor = func.get_option('frequency_monitor')
if servers:
log.info("need backup servers: %s" %(servers,));
#print("%s: check_mysql_status controller started..." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=str(row[0])
IP=row[1]
ssh_port=row[2]
ssh_user=row[3]
ssh_passwd=row[4]
back_IP=row[5]
back_ssh_port=row[6]
back_ssh_user=row[7]
back_ssh_passwd=row[8]
back_path=row[9]
db_name=row[10]
back_cycle=int(row[11])
save_number=row[12]
alarm_flag=row[13]
charge_person=row[14]
#check if need to back
if back_cycle > 1:
back_date=str(func_time.get_day_of_day(back_cycle*-1))
sql="select count(1) as num from redis_coldback_info where server_id='"+server_id+"' and suc_flag='y' and date>='"+back_date+"'"
result=func.mysql_query(sql)
if result:
num=result[0]
if num >=1:
continue
#get host:port of server
mail_content="RedisBackup:"
sql="select a.display_name,b.host,b.port,b.application_id from application a,servers b where a.id=b.application_id and b.id='"+server_id+"';"
#sql="set names utf8;select a.display_name,b.host,b.port,b.application_id from application a,servers b where a.id=b.application_id and b.id='"+server_id+"';"
result=func.mysql_query(sql)
if result:
display_name=result[0][0]
##host equal with IP
host=result[0][1]
port=result[0][2]
application_id=result[0][3]
else:
mail_content=mail_content+"not found server_id:"+server_id
#send warn to system charge person
application_id=0
send_backup_alarm(alarm_flag,charge_person,mail_content,application_id,server_id,db_name,current_date,log)
continue
mail_content="RedisBackup_"+IP+"_"+port+"("+display_name+"):"
#get redis dir
sql="select dir from redis_server_status_history where server_id='"+server_id+"' order by id desc limit 1;"
result=func.mysql_query(sql)
if result and result[0][0]!="---":
dir=result[0][0]
#print("dir:%s"%(dir))
else:
mail_content=mail_content+"redis dir not found"
#send warn
send_backup_alarm(alarm_flag,charge_person,mail_content,application_id,server_id,db_name,current_date,log)
continue
#get config of rdb/aof
sql="select rdb_enabled,rdb_dbfilename,rdb_last_bgsave_status,aof_enabled,aof_last_bgrewrite_status,aof_last_write_status,aof_current_size from redis_persistence_status_history where server_id='"+server_id+"' order by YmdHi desc limit 1;"
result=func.mysql_query(sql)
if result:
rdb_enabled=result[0][0]
rdb_dbfilename=result[0][1]
rdb_last_bgsave_status=result[0][2]
aof_enabled=result[0][3]
aof_last_bgrewrite_status=result[0][4]
aof_last_write_status=result[0][5]
aof_current_size=result[0][6]
else:
mail_content=mail_content+"redis persistence info not found"
#send warn
send_backup_alarm(alarm_flag,charge_person,mail_content,application_id,server_id,db_name,current_date)
continue
#check passwd
if ssh_passwd == "":
log.error("passwd not found,IP:%s:"%(IP))
continue
if back_ssh_passwd == "":
log.error("passwd not found,IP:%s"%(back_IP))
continue
#back data
if db_name.find("rdb") >=0:
back_rdb_file(server_id,display_name,port,IP,ssh_port,ssh_user,ssh_passwd,back_IP,back_ssh_port,back_ssh_user,back_ssh_passwd,rdb_enabled,rdb_last_bgsave_status,dir,rdb_dbfilename,back_path,current_date,alarm_flag,charge_person,application_id,log)
if db_name.find("aof") >=0:
back_aof_file(server_id,display_name,port,IP,ssh_port,ssh_user,ssh_passwd,back_IP,back_ssh_port,back_ssh_user,back_ssh_passwd,aof_enabled,aof_last_bgrewrite_status,aof_last_write_status,aof_current_size,dir,back_path,current_date,alarm_flag,charge_person,application_id,log)