def task_tables_info(): MYSQL_IDC = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) Table = 'tableinfo' cmds = ("TRUNCATE TABLE %s;" % Table, "select ip,port,db from mysqldb;") results = map(MYSQL_IDC.Run, cmds) for host, port, dbs in results[1]: try: if '172.16.9.' not in host: MYSQL = Mysql.MYSQL(USER, PASSWORD, host, port, 'mysql') cmd = "show variables like 'version';" version = MYSQL.Run(cmd) version = version[0][1] or 'None' for db in dbs.split('|'): cmd = "show table status from %s;" % db results = MYSQL.Run(cmd) if results: for table_info in results: try: Table_Name = table_info[0] Engine = table_info[1] or 'None' Rows = table_info[4] or 0 Charset = table_info[14] or 'None' cmd = ( "insert into %s (ip,port,database_name,table_name,Engine_name,Rows,Charset,version) VALUES ('%s',%i,'%s','%s','%s',%i,'%s','%s');" % (Table, host, int(port), db, Table_Name, Engine, Rows, Charset, version)) MYSQL_IDC.Run(cmd) except Exception as e: logging.error(e) continue MYSQL.Close() except Exception as e: logging.error(e) continue MYSQL_IDC.Close()
def WAF2(): DB = 'op' tt = time.strftime('%Y%m%d', time.localtime()) th = time.strftime('%Y%m%d%H', time.localtime()) tm = datetime.datetime.now() - datetime.timedelta(minutes=2) tm = tm.strftime('%Y%m%d%H%M') dm = datetime.datetime.now() + datetime.timedelta(hours=1) expire_time = dm.strftime('%Y-%m-%d %H:%M:%S') black_ip_minute = 'black_ip_%s' % tm black_ip_hour = 'black_ip_%s' % th black_ip_day = 'black_ip_%s' % tt try: MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) IP_LIST = RC.lrange(black_ip_minute, 0, -1) IP_LIST_HOUR = RC.lrange(black_ip_hour, 0, -1) if IP_LIST and IP_LIST_HOUR: for ip in set(IP_LIST): if ip in set(IP_LIST_HOUR): try: #小时拦截 RC.lpush(black_ip_day, ip) MYSQL.Run( "update haproxy_blacklist set expire = '%s' where ip = '%s';" % (expire_time, ip)) except Exception as e: logging.error(e) continue RC.expire(black_ip_day, 86400) MYSQL.Close() except Exception as e: logging.error(e)
def haproxy_blacklist(): DB = 'op' MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) file_path = '/tmp/blacklist' def write_ip(): cmd = "SELECT ip FROM haproxy_blacklist;" hosts = [str(host[0]) for host in MYSQL.Run(cmd)] with open(file_path, 'w') as f: for host in hosts: f.write('%s\n' % host) for ip in HA_SERVERS: ssh = SSH.ssh('work', ip) ssh.Scp(file_path, file_path) tm = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) cmd = "SELECT ip FROM haproxy_blacklist where expire <= '%s' and expire !='0000-00-00 00:00:00';" % tm if MYSQL.Run(cmd): cmd = "delete from haproxy_blacklist where expire <= '%s' and expire !='0000-00-00 00:00:00';" % tm MYSQL.Run(cmd) write_ip() cmd = "SELECT ip FROM haproxy_blacklist where stats = '1';" if MYSQL.Run(cmd): cmd = "update haproxy_blacklist set stats = '0';" MYSQL.Run(cmd) write_ip() cmd = "SELECT ip FROM haproxy_blacklist where stats = '2';" if MYSQL.Run(cmd): cmd = "delete from haproxy_blacklist where stats = '2';" MYSQL.Run(cmd) write_ip() MYSQL.Close()
def get_twemproxy_redis(): MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) redis_info = {} for twemproxy_ip in TWEMPROXY_HOSTS: try: sock=socket.socket(socket.AF_INET,socket.SOCK_STREAM) sock.connect((twemproxy_ip,22222)) INFOS = json.loads(sock.recv(86400)) for key in INFOS: if 'redis_' in key: IP_list = [] for ip_key in INFOS[key]: if '172.16.' in ip_key: IP_list.append(ip_key.split(':')[0]) redis_info[key] = IP_list except Exception as e: loging.write(e) continue cmd = "TRUNCATE TABLE twemproxyInfo;" MYSQL.Run(cmd) for key in redis_info: for ip in redis_info[key]: try: Redis = redis.StrictRedis(host=ip,port=6379,db=0,socket_timeout=1) Keys = Redis.info()['db0']['keys'] cmd = "insert into twemproxyInfo (serviceGroup,clientIP,clientKeyItems) VALUES('%s','%s','%s');"%(key,ip,Keys) MYSQL.Run(cmd) except: continue MYSQL.Close()
def clear_kestrel(): MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) cmd = "select kestrel_ip,kestrel_port,kestrel_key from kestrel where kestrel_num ='0';" results = MYSQL.Run(cmd) if results: MYSQL.Close() for ip,port,key in results: loging.write('%s %s %s delete......\n') %(ip,port,key) try: Kestrel = memcache.Client(['%s:%s'%(ip,port)],debug=0,socket_timeout=1) Kestrel.delete(str(key)) except: continue
def check_publish(): try: DB = 'op' td = time.strftime('%Y-%m-%d', time.localtime()) tt = (datetime.datetime.now() - datetime.timedelta(hours=4)).strftime('%H:%M:%S') MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) cmd = "SELECT DISTINCT(project) FROM op_operation WHERE TYPE = '灰度' AND DATE = '{0}' AND TIME <= '{1}';".format( td, tt) result = MYSQL.Run(cmd) if result: for Project in result: os.system( "/bin/tomail [email protected] 灰度发布警告 {0} 项目已经保持灰度状态超过4个时间,请相关开发人员尽快处理!" .format(Project[0])) MYSQL.Close() except Exception as e: logging.error(e)
def vpn_conf(): def create_file(Type): try: crypto = Md5.crypto('1qazxsw23edcvfr4') file_path = '/tmp/chap-secrets' ips = { 'intranet': ('172.16.9.2', '172.16.9.31'), 'internet': ('172.16.16.150', '172.16.16.151', '172.16.16.164', '172.16.16.165') } cmd = "SELECT user,password FROM vpn_users where vpn_type = '%s';" % Type User_list = MYSQL.Run(cmd) if User_list: with open(file_path, 'w') as f: f.write( '####### system-config-network will overwrite this part!!! (begin) ##########\r\n' ) for user, pw in User_list: f.write('%s * %s *\n' % (user, crypto.decrypt(pw))) for ip in ips[Type]: ssh = SSH.ssh('work', ip) ssh.Scp(file_path, file_path) except Exception as e: logging.error(e) DB = 'op' MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) for Type in ('intranet', 'internet'): cmd = "SELECT user,password FROM vpn_users where status = 2 and vpn_type = '%s';" % Type if MYSQL.Run(cmd): cmd = "delete FROM vpn_users where status = 2 and vpn_type = '%s';" % Type MYSQL.Run(cmd) create_file(Type) cmd = "SELECT user,password FROM vpn_users where status = 1 and vpn_type = '%s';" % Type if MYSQL.Run(cmd): cmd = "update vpn_users set status = 0 where status = 1 and vpn_type = '%s';" % Type MYSQL.Run(cmd) create_file(Type) MYSQL.Close()
def kestel_info(): MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) cmd = "truncate table kestrel;" MYSQL.Run(cmd) sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) sock.connect(('172.16.16.50', 22222)) INFOS = json.loads(sock.recv(86400)) for key in INFOS: try: if 'kestrel_' in key: for ip_key in INFOS[key]: if '172.16.' in ip_key: ip = ip_key.split(':')[0] ks = kestrel.Client(['%s:22133' % ip.strip()]) st = ks.stats()[1]['queues'] for k in st: v = st[k] data = (ip, '22133', str(k.replace("'", "")), str(v['items'])) cmd = "insert into kestrel (kestrel_ip,kestrel_port,kestrel_key,kestrel_num) values('%s',%s,'%s',%s);" % data MYSQL.Run(cmd) except: continue
def mysql_scheduler(): t = time.strftime('%Y-%m-%d', time.localtime()) MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) try: def Run_sql(val): id, IP, PORT, DB = val[:4] CMD = val[5] val = Mysql.Query_sql(IP, PORT, DB, CMD) if val: val = str(val).replace("'", '') else: val = 'None' cmd = "update sql_scheduler set status = '已执行' ,results = '%s' where id = '%s';" % ( val, id) MYSQL.Run(cmd) MYSQL.Close() cmd = "select * from sql_scheduler where status = '未执行' and time = '%s';" % t for values in MYSQL.Run(cmd): Run_sql(values) MYSQL.Close() except Exception as e: logging.error(e)
def WAF(): DB = 'op' black_list = {} white_list = app.config.get('WHITE_LIST') tm = datetime.datetime.now() - datetime.timedelta(minutes=1) tm = tm.strftime('%Y%m%d%H%M') tt = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) th = time.strftime('%Y%m%d%H', time.localtime()) dm = datetime.datetime.now() + datetime.timedelta(minutes=5) expire_time = dm.strftime('%Y-%m-%d %H:%M:%S') top_ip_minute = 'top_ip_%s' % tm top_url_minute = 'top_url_%s' % tm black_ip_minute = 'black_ip_%s' % tm black_ip_hour = 'black_ip_%s' % th try: MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) ip_list = MYSQL.Run("select ip from haproxy_blacklist;") ip_list = [str(ip[0]) for ip in ip_list] url_data = MYSQL.Run( "select url,counts from url_blacklist where stats='1';") if url_data: for url, counts in url_data: black_list[str(url)] = counts # 基于ip统计接口 IP_DATAS = [ str(ip) for ip in RC.zrevrange(top_ip_minute, 0, 20) if ip not in white_list if ip not in ip_list ] if IP_DATAS: for ip in IP_DATAS: try: top_ip_domain_minute = 'top_%s_domain_%s' % (ip, tm) URL_DATAS = [[ str(url), int(RC.zscore(top_ip_domain_minute, url)) ] for url in RC.zrevrange(top_ip_domain_minute, 0, -1)] if URL_DATAS: for URL, URL_COUNT in URL_DATAS: try: # 拦截 if URL in black_list.keys(): if URL_COUNT > int(black_list[URL]): #pass RC.lpush(black_ip_minute, ip) RC.lpush(black_ip_hour, ip) MYSQL.Run( "insert into haproxy_blacklist (addtime,ip,stats,expire,rule) VALUES ('%s','%s','1','%s','%s');" % (tt, ip, expire_time, URL)) #学习 urls = [ str(url[0]) for url in MYSQL.Run( "select url from url_blacklist;") ] if URL not in urls and URL_COUNT > 60: MYSQL.Run( "insert into url_blacklist (url,counts,stats) VALUES ('%s',%i,'0');" % (URL, URL_COUNT)) except Exception as e: logging.error(e) continue except Exception as e: logging.error(e) continue RC.expire(black_ip_minute, 300) RC.expire(black_ip_hour, 3600) #基于接口统计ip URLS = [str(url) for url in RC.zrevrange(top_url_minute, 0, -1)] if URLS: for url in URLS: if url in black_list.keys(): top_url_ip_minute = 'top_%s_ip_%s' % (url, tm) IP_DATAS = [ str(ip) for ip in RC.zrevrange(top_url_ip_minute, 0, -1) if int(RC.zscore(top_url_ip_minute, ip)) > int( black_list[url]) if ip not in white_list if ip not in ip_list ] if IP_DATAS: for ip in IP_DATAS: try: #分钟拦截 RC.lpush(black_ip_minute, ip) RC.lpush(black_ip_hour, ip) MYSQL.Run( "insert into haproxy_blacklist (addtime,ip,stats,expire,rule) VALUES ('%s','%s','1','%s','%s');" % (tt, ip, expire_time, url)) except Exception as e: logging.error(e) continue RC.expire(black_ip_minute, 300) RC.expire(black_ip_hour, 3600) MYSQL.Close() except Exception as e: logging.error(e)
def check_publish(): loging.write('check publish......') def rollback_java(Project, warname, ServerList): information = {} information['warname'] = warname information['warTagName'] = Project information['ServerList'] = ServerList information['Action'] = 'publish' information['key'] = 'auto_rollback' information['Gray'] = False information['Type'] = '2' information['Way'] = 'SVN' Redis.delete('auto_rollback') Redis.lpush('publish_java', information) def rollback_php(Project, App, ServerList): Info = {} Info['action'] = 'publish' Info['key'] = 'auto_rollback' Info['path'] = Project Info['app'] = App Info['sip'] = ServerList Info['gray'] = False Info['Type'] = '2' Info['Way'] = 'SVN' Redis.lpush('publish_php', str(Info)) td = time.strftime('%Y-%m-%d',time.localtime()) tt = (datetime.datetime.now()-datetime.timedelta(hours=4)).strftime('%H:%M:%S') MYSQL = Mysql.MYSQL(USER, PASSWORD, HOST, PORT, DB) cmd = "SELECT DISTINCT(project) FROM op_operation WHERE TYPE = '灰度' AND DATE = '{0}' AND TIME <= '{1}';".format(td,tt) result = MYSQL.Run(cmd) if result: for Project in result: os.system("/bin/tomail [email protected] 灰度发布警告 {0} 项目已经保持灰度状态超过4个时间,请相关开发人员尽快处理!".format(Project[0])) cmd = "SELECT DISTINCT(project) FROM op_operation WHERE DATE = '{0}' AND TIME < '{1}' AND TYPE = '测外';".format(td,tt) results = MYSQL.Run(cmd) if results: results = [str(re[0]) for re in results] for Project in results: cmd = "SELECT DISTINCT(project) FROM op_operation WHERE DATE = '{0}' AND TIME > '{1}' AND TYPE = '线上' AND project = '{2}' ;".format(td,tt,Project) if MYSQL.Run(cmd): pass results.remove(Project) if results: for PRoject in results: if '.war.zip' in PRoject: project = PRoject.split('-')[:-1] project = '-'.join(project) cmd = "SELECT ip,USER FROM java_list WHERE project = '{0}' AND TYPE = '2'".format(project + '.war') ServerList = MYSQL.Run(cmd) cmd = "SELECT project FROM op_operation WHERE TYPE = '线上' AND ACTION <> 'restart' and project like '{0}-%.war.zip' ORDER BY id DESC LIMIT 1;".format(project) loging.write('java:{0}\n'.format(ServerList)) elif 'tags' in PRoject: App = PRoject.split('/')[3] project = PRoject.split('tags')[-1] project = project.replace('/','').split('-')[:-1] project = '-'.join(project) cmd = "SELECT ip,USER FROM php_list WHERE project = '{0}' AND TYPE = '2'".format(App) ServerList = MYSQL.Run(cmd) cmd = "SELECT project FROM op_operation WHERE TYPE = '线上' AND ACTION <> 'restart' and project like '%{0}%' ORDER BY id DESC LIMIT 1;".format(project) loging.write('php:{0}\n'.format(ServerList)) else: continue result = MYSQL.Run(cmd) loging.write('project:{0}\n'.format(result)) #os.system("/bin/tomail [email protected] 测外发布警告 {0} 项目已经保持测外状态超过4个时间,请相关开发人员尽快处理!".format(Project[0])) MYSQL.Close()