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