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