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()
