def TaskOb(task_name):
'''任务操作'''
with closing(zkHander()) as zkhander:
_task_value = eval(zkhander.GetTaskContent(task_name))
if _task_value is not None:
if 'add' in _task_value:
return TaskClassify().TaskChange(_task_value[0])
elif 'white' in _task_value:
'''白名单操作'''
return TaskClassify().TaskWhite(_task_value)
elif 'down' in _task_value:
'''宕机需判断是否在白名单列表'''
with closing(zkHander()) as zkhander:
if zkhander.GetWhite(_task_value[0]):
Logging(msg='this master {} has been down,but it in whitelist!!'.format(_task_value[0]),level='info')
return True
else:
return TaskClassify().TaskDown(_task_value[0])
elif 'append' in _task_value:
'''附加任务'''
from db_handle import AdditionTask
return AdditionTask.Addition().ChangeRepl(_task_value)
else:
Logging(msg='task failed state: type error',level='error')
return False
def print_packets(**kwargs):
"""Print out information about each packet in a pcap
Args:
pcap: dpkt pcap reader object (dpkt.pcap.Reader)
"""
t = ThreadDump(**dict(kwargs, **{'queue': my_queue}))
t.start()
_pcap = pcap.pcap(name=kwargs['eth'],
promisc=True,
immediate=True,
timeout_ms=5000)
_pcap.setfilter("tcp port {}".format(kwargs['port']))
_logging = Logging()
for timestamp, buf in _pcap:
if sys.version_info < (3, 0):
_time = time.time()
else:
_time = timestamp
if append_data((buf, _time)):
pass
else:
_logging.error('queue is full!!!!!!!')
sys.exit()
def CheckOnline(self, proxy_value, groupname):
slave_list = eval(proxy_value['read'])
for h in slave_list:
if h != proxy_value['write']: #去除master节点的检查，master节点有watch
__host, __port = h.split(':')[0], h.split(':')[1]
status = self.zkhander.Exists('{}/{}'.format(
self.online_node, Replace(__host)))
if status is None:
time.sleep(random.uniform(0, 0.5))
Logging(
msg='This Group Server {} has slave node:{} is down '.
format(groupname, __host),
level='warning')
__status = self.zkhander.Exists('{}/{}'.format(
self.slave_down_path, Replace(__host)))
if __status is None:
self.zkhander.Create(
path='{}/{}'.format(self.slave_down_path,
Replace(__host)),
value=str({
'groupname': groupname,
'port': __port
}),
seq=False) #slave节点不在线创建slavedown节点
else:
Logging(
msg='This host:{} outage task is being '.format(
__host),
level='warning')
def __get_online_host(self, region):
"""获取在线列表"""
reg_path = GetConf().GetAdditionRegion() + '/' + region
with closing(zkHander()) as zkhander:
reg_value_dict = eval(
zkhander.Get(reg_path)
) #region存储格式为{'192-168-212-1':{'port':333,'ssl':0/1}.....}
if reg_value_dict:
_reg_online = [
host for host in reg_value_dict
if zkhander.GetOnlineState(host)
]
if _reg_online:
_to_reg = {
'host': _reg_online[0].replace('-', '.'),
'port': reg_value_dict[_reg_online[0]]['port'],
'ssl': reg_value_dict[_reg_online[0]]['ssl']
}
return _to_reg
else:
Logging(
msg=
'This group has replication task ,But all region not online',
level='warning')
return None
else:
Logging(
msg='This group has replication task ,But not region value',
level='warning')
return None
def CreateWatch(self,
host,
addition=None,
region=None,
region_for_groupname=None):
'''创建watch,触发时写入task节点'''
online_host_path = GetConf().GetOnlinePath()
_group_name = self.GetMeta(type='host', name=host)
group_name = eval(
_group_name)['group'] if _group_name else region_for_groupname
online_state = self.zk.exists('{}/{}'.format(online_host_path, host))
Logging(msg='master watch :{}'.format(host), level='info')
if online_state is not None:
@self.zk.DataWatch('{}/{}'.format(online_host_path, host))
def my_func(data, stat):
if data is None:
self.CreateDownTask(group_name,
addition=addition,
region=region)
Logging(msg='master({}) has been down!'.format(host),
level='error')
self.zk.stop()
sys.exit()
else:
_name = group_name + '_' + region if region else group_name
Logging(msg="this master {} node not exists".format(host),
level='error')
state = self.Exists('{}/{}'.format(GetConf().GetWatchDown(),
_name))
self.Create(path='{}/{}'.format(GetConf().GetWatchDown(), _name),
value="master not online",
seq=False) if state is None else None
self.zk.stop()
def an_packet(self):
self._ip = self.__get_netcard()
self._logging = Logging()
self.command_list = []
self.command_list_len = 0
while 1:
if not self.queue.empty():
buf, _cur_time = self.queue.get()
eth = dpkt.ethernet.Ethernet(buf)
if not isinstance(eth.data, dpkt.ip.IP):
self._logging.error(
msg='Non IP Packet type not supported %s\n' %
eth.data.__class__.__name__)
continue
ip = eth.data
if isinstance(ip.data, dpkt.tcp.TCP):
tcp = ip.data
src_host, dst_host = self.inet_to_str(
ip.src), self.inet_to_str(ip.dst)
session, session_status = self.GetSession(
src_host, tcp.sport, dst_host, tcp.dport)
if session_status:
self.payload = len(tcp.data)
if self.payload <= 8 and self.payload >= 4194304: # 抛弃小于8字节和大于4m的数据包
continue
self.Unpacking(data=tcp.data)
if len(self.command) > 0:
jsons = {
'source_host': session[0],
'source_port': session[1],
'destination_host': session[2],
'destination_port': session[3],
'command': self.command,
'event_date': int(_cur_time),
'cluster_name': self.kwargs['cluster_name']
}
if self.ckhost:
self.command_list.append(jsons)
self.command_list_len += 1
self.insert_ck()
else:
self._logging.info(msg=json.dumps(jsons))
else:
time.sleep(0.01)
def ChangeRepl(self, _content):
try:
groupname, region, type = _content[0], _content[1], _content[-1]
if type == 'dow': #宕机任务，需重新选择节点并监听同步
for i in range(0, 3):
host, port = self.__get_master_for_region(
region, groupname)
with closing(dbHandle(host, port)) as dbhandle:
mysqlstate = dbhandle.RetryConn() # 检测是否能正常连接
time.sleep(1)
if mysqlstate:
zkHander().CreateWatch(
host=host.replace('.', '-'),
addition=True,
region=region,
region_for_groupname=groupname) # 重新创建master检测
else:
return self.__change_new_master(region=region,
groupname=groupname)
elif type == 'up': #只进行监听，用于手动添加了同步任务
self.__up_watch_master(region=region, groupname=groupname)
return True
except:
Logging(msg='addition task failed!', level='error')
return False
def start(self):
_parse_event = ParseEvent(packet=self.packet)
try:
event_code, event_length = _parse_event.read_header()
except Exception, e:
Logging(msg=traceback.format_exc(), level='error')
return None
def TaskFunc(self,taskname):
with closing(zkHander()) as zkhander: #检查其他server是否在执行
task_stat = zkhander.SetLockTask(taskname)
if task_stat:
with closing(zkHander()) as zkhander:
state = TaskOb(taskname)
if state:
zkhander.DeleteTask(taskname) #删除已执行的任务
else:
now_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
Logging(msg=' {} : this task {} failed'.format(now_time,taskname),level='error')
Logging(msg='sleep time 3S ,waiting other server create watch',level='info')
time.sleep(3)
zkhander.DeleteLockTask(taskname)
else:
Logging(msg='task : {} elsewhere in the execution'.format(taskname),level='info')
zkHander().CreateLockWatch(taskname)
def ResetMaster(self,groupname):
try:
'''获取当前binlog读取位置'''
append_stat=None
master_log_file,read_master_log_pos,master_host = self.CheckPos(get_host=True)
'''================'''
#用于mysql宕机，服务器在线追加数据
from zk_handle.zkHandler import zkHander
from Append.AppendValue import Append
from lib.get_conf import GetConf
from contextlib import closing
with closing(zkHander()) as zkhander:
client_stat = zkhander.CheckOnlineClient(master_host)
if client_stat:
__get_content = {'getbinlog': 10010, 'binlog_file': master_log_file, 'start_position': read_master_log_pos}
Logging(msg='gets the unsynchronized data not. info:{}'.format(__get_content),level='info')
append_stat = Append(connection=self.local_conn,cursor=self.mysql_cur,host=master_host,port=GetConf().GetClientPort()).receive(conn_info=str(__get_content))
if append_stat:
Logging(msg='Append OK',level='info')
else:
Logging(msg='Append Failed',level='error')
'''================='''
if master_host:
readbinlog_status = str([groupname,master_log_file,read_master_log_pos])
execute_gtid = str([groupname,self.__CetGtid()])
with closing(zkHander()) as zkhander:
if append_stat:
zkhander.SetExecuteGtid(master_host, execute_gtid)
else:
zkhander.SetReadBinlog(master_host,readbinlog_status)
zkhander.SetExecuteGtid(master_host,execute_gtid)
''''''
#self.mysql_cur.execute('set global read_only=0;')
self.mysql_cur.execute('stop slave')
self.mysql_cur.execute('reset slave all;')
self.__set_variables(type='master')
except MySQLdb.Warning,e:
Logging(msg=traceback.format_exc(),level='warning')
self.mysql_cur.execute('reset slave all;')
self.__set_variables(type='master')
def my_func(data, stat):
if data is None:
self.CreateDownTask(group_name,
addition=addition,
region=region)
Logging(msg='master({}) has been down!'.format(host),
level='error')
self.zk.stop()
sys.exit()
def CreateDownTask(self, groupname, addition=None, region=None):
task_path = '{}/{}'.format(GetConf().GetTaskPath(), groupname)
if self.Exists(task_path) is None:
time.sleep(random.uniform(0, 1))
try:
if addition:
task_path += '_' + region
self.Create(path=task_path,
value=str([groupname, region, 'append',
'dow']),
seq=False) #跨区域同步主机宕机
else:
self.Create(path=task_path,
value=str([groupname, 'down']),
seq=False)
except:
Logging(msg='Existing outage task for {}'.format(groupname),
level='info')
else:
Logging(msg='Existing outage task for {}'.format(groupname),
level='info')
def __init__(self,host,port):
self.host,self.port = host,int(port)
self.mysqluser,self.mysqlpasswd = GetConf().GetMysqlAcount()
ssl_set = {'ca':GetConf().GetUserSSLCa(),'cert':GetConf().GetUserSSLCert(),'key':GetConf().GetUserSSLKey()}
try:
self.local_conn = MySQLdb.connect(host=self.host, user=self.mysqluser, passwd=self.mysqlpasswd, port=self.port, db='',
charset="utf8mb4",ssl=ssl_set)
self.mysql_cur = self.local_conn.cursor()
self.state = True
except MySQLdb.Error,e:
Logging(msg=traceback.format_exc(),level='error')
self.state = False
def ChangeMaster(self,host,port):
'''主从指向'''
repluser,replpassword,ssl_ca,ssl_cert,ssl_key = GetConf().GetReplAcount()
try:
sql = 'reset slave all;'
print self.host
try:
self.mysql_cur.execute(sql)
except:
self.mysql_cur.execute('stop slave')
self.mysql_cur.execute(sql)
change_sql = 'change master to master_host="%s",master_port=%s,master_user="******",master_password="******",master_auto_position=1 for channel "default"' % (host,int(port),repluser,replpassword)
self.mysql_cur.execute(change_sql)
self.__set_variables(type='slave')
return True
except MySQLdb.Warning,e:
start_sql = 'start slave'
self.mysql_cur.execute(start_sql)
self.__set_variables(type='slave')
Logging(msg='Change master to {} state : Warning'.format(host),level='warning')
Logging(msg=traceback.format_exc(),level='warning')
return True
def SetLockTask(self, taskname):
'''创建锁文件，用户多点运行server控制任务'''
path = GetConf().GetLockPath()
if self.Exists(path='{}/{}'.format(path, taskname)) is None:
try:
time.sleep(random.uniform(0, 1))
self.zk.create(path='{}/{}'.format(path, taskname),
value=b'',
ephemeral=False)
return True
except Exception, e:
Logging(msg=traceback.format_exc(), level='error')
return False
def GetMeta(self, **kwargs):
'''获取元数据信息'''
if kwargs['type'] == 'group':
node_path = '{}/{}'.format(GetConf().GetMetaGroup(),
kwargs['name'])
return self.Get(
node_path) if self.Exists(node_path) != None else False
elif kwargs['type'] == 'host':
node_path = '{}/{}'.format(GetConf().GetMetaHost(), kwargs['name'])
return self.Get(
node_path) if self.Exists(node_path) != None else False
else:
Logging(msg="type is error ,only 【group ,host】", level='error')
raise "type is error ,only 【group ,host】"
def Change(self, region, host_content):
repluser, replpassword, ssl_ca, ssl_cert, ssl_key = GetConf(
).GetReplAcount(rg=True)
master_host, master_port = host_content['host'], int(
host_content['port'])
if host_content['ssl']:
sql = 'change master to master_host="%s",master_port=%d,master_user="******",master_password="******",master_ssl=1,' \
'master_ssl_ca="%s",' \
'master_ssl_cert="%s",master_ssl_key="%s",master_auto_position=1 for channel "%s" ' % (
master_host, master_port,
repluser, replpassword, ssl_ca,
ssl_cert, ssl_key, region)
else:
sql = 'change master to master_host="%s",master_port=%d,master_user="******",master_password="******",' \
'master_auto_position=1 for channel "%s" ' % (master_host, master_port,
repluser, replpassword, region)
with closing(self.conn.cursor()) as cur:
try:
cur.execute('stop slave for channel "%s"' % region)
cur.execute('reset slave for channel "%s"' % region)
except:
pass
try:
cur.execute(sql)
self.__set_group_region(region, host_content)
except pymysql.Warning, e:
Logging(msg=traceback.format_exc(), level='error')
cur.execute('start slave;')
self.__set_group_region(region, host_content)
except pymysql.Error, e:
Logging(msg=traceback.format_exc(), level='error')
Logging(
msg=
'addition task for {} failed,master to {} in region {} ! ! !'
.format(self.host, host_content['host'], region),
level='error')
return False
def StaticInfo(self, result, host):
with closing(zkHander()) as zkhander:
lock_state = zkhander.SetLockTask(host)
if lock_state:
online_state = zkhander.Exists('{}/{}'.format(
self.online_node, host))
if online_state is None:
port, groupname = result['port'], result['groupname']
for i in range(0, 3):
with closing(dbHandle(Replace(host),
port)) as dbhandle:
mysqlstate = dbhandle.RetryConn() # 检测mysql是否能正常连接
time.sleep(1)
if mysqlstate:
zkhander.DeleteSlaveDown(host)
Logging(
msg=
'Groupname:{} slave host:{} is online,but python client server is not online!'
.format(groupname, Replace(host)),
level='warning')
else:
alter_state = self.AlterHaproxy(
groupname=groupname,
delete_host=Replace(host),
port=port)
if alter_state:
zkhander.DeleteSlaveDown(host)
zkhander.DeleteLockTask(host)
else:
zkhander.DeleteLockTask(host)
else:
zkhander.DeleteSlaveDown(host)
else:
Logging(msg='slave:{} outage task elsewhere in the execution'.
format(Replace(host)),
level='warning')
def SendRoute(group_name, slavedown=None):
with closing(zkHander()) as zkhander:
route_content = zkhander.GetRouter(group_name) # 传递路由配置修改信息
if route_content:
_route_content = route_content.split(',')
for _content in _route_content:
try:
with closing(TcpClient(_content)) as tcpclient:
send_stat = tcpclient.Send(group_name)
except Exception, e:
Logging(msg=traceback.format_exc(), level='error')
if not send_stat:
if slavedown:
return False
else:
with closing(zkHander()) as zkhander:
zkhander.SetWatchDown(group_name, 'failed')
def TaskDown(self,groupname):
'''master宕机触发任务'''
with closing(zkHander()) as zkhander:
cur_master = zkhander.GetMasterMeta(groupname)
for i in range(0,3):
with closing(zkHander()) as zkhander:
host_meta = zkhander.GetMeta(type='host', name=cur_master)
host_port = eval(host_meta)['port']
with closing(dbHandle(cur_master.replace('-','.'),host_port)) as dbhandle:
mysqlstate = dbhandle.RetryConn() #检测mysql是否能正常连接
time.sleep(1)
if mysqlstate:
zkHander().CreateWatch(cur_master) #重新创建master检测
return True
else: #宕机重选master
now_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
Logging(msg=' {} : group {} the current master {} state: down'.format(now_time,groupname,cur_master),level='error')
return self.TaskChange(groupname,type='change')
def receive(self, conn_info=None):
# self.client.send(str({'getbinlog': 10010, 'binlog_file': 'bin.000001', 'start_position': 154}).encode('utf8'))
self.client.send(conn_info)
while True:
data = self.client.recv(self.BUFSIZ)
try:
if eval(data)['binlogvalue'] == 10010:
Logging(msg='recv OK!', level='info')
self.client.close()
break
except:
pass
if data:
recv_stat = {'recv_stat': 119}
self.client.send(str(recv_stat).encode('utf8'))
self.packet = data
stat = self.start()
if stat is None:
break
return self.__executesql()
def __executesql(self):
Logging(msg='Additional unsynchronized data now', level='info')
for sql in tmepdata.sql_all_list:
Logging(msg='execute sql -- {}'.format(sql), level='info')
try:
self.mysql_cur.execute(sql[0], sql[1])
Logging(msg='state OK!', level='info')
except MySQLdb.Warning:
Logging(msg=traceback.format_exc(), level='warning')
except Exception, e:
Logging(msg='state failed', level='error')
Logging(msg=traceback.format_exc(), level='error')
self.mysql_conn.rollback()
tmepdata.sql_all_list = []
return False
def an_packet(self):
_ip = self.__get_netcard()
_mysql_packet_op = mysql_packet(
**dict({'_type': self._type}, **{'_ip': _ip}))
session_status = {}
self._logging = Logging()
t = threading.Thread(target=self.conn_maintain, args=())
t.start()
while 1:
if not self.queue.empty():
buf, _cur_time = self.queue.get()
eth = dpkt.ethernet.Ethernet(buf)
if not isinstance(eth.data, dpkt.ip.IP):
self._logging.error(
msg='Non IP Packet type not supported %s\n' %
eth.data.__class__.__name__)
continue
ip = eth.data
if isinstance(ip.data, dpkt.tcp.TCP):
tcp = ip.data
src_host, dst_host = self.inet_to_str(
ip.src), self.inet_to_str(ip.dst)
session, packet_response, client_packet_text, packet_header, packet_seq_id, response_type, response_status = _mysql_packet_op.Unpacking(
data=tcp.data,
srchost=src_host,
srcport=tcp.sport,
dsthost=dst_host,
dstport=tcp.dport,
all_session_users=self.all_session_users)
if packet_response and packet_response in (
'COM_PROCESS_KILL', 'COM_QUIT'):
"""close connection"""
if session in self.all_session_users:
del self.all_session_users[session]
if client_packet_text:
if session in self.all_session_users:
self.create_conn(session, client_packet_text,
packet_seq_id, 'client',
response_type, response_status)
if packet_header == 0x16:
session_status[session] = {
'start_time': _cur_time,
'request_text': client_packet_text,
'request_header': packet_header,
'seq_id': packet_seq_id,
'response_type': response_type,
'com_pre': True
}
elif packet_header == 0x17 and session in session_status and 'com_pre' in session_status[
session]:
del session_status[session]['com_pre']
continue
elif packet_header in (0x01, 0x18):
session_status[session] = {
'start_time': _cur_time,
'request_text': client_packet_text,
'request_header': packet_header,
'seq_id': packet_seq_id,
'response_type': response_type,
'end_time': _cur_time,
'status': 1,
'response_status': None
}
elif packet_header == 0x19:
continue
else:
session_status[session] = {
'start_time': _cur_time,
'request_text': client_packet_text,
'request_header': packet_header,
'seq_id': packet_seq_id,
'response_type': response_type
}
if session in self.all_session_users and self.all_session_users[
session]['status']:
session_status[session][
'user_name'] = self.all_session_users[session][
'user']
self.all_session_users[session]['date'] = _cur_time
elif session not in self.all_session_users:
# session_status[session]['user_name'] = self.get_user_info(host=src_host,port=tcp.sport,
# mysql_host=dst_host,
# mysql_port=tcp.dport,
# session=session)
session_status[session]['user_name'] = None
if session not in self.get_user_list and packet_header not in (
0x01, 0x19, 0x18) and any(
[self.mysql_user, self.mysql_passwd]):
self.get_user_list[session] = [
src_host, tcp.sport, dst_host, tcp.dport,
session
]
elif packet_response:
if packet_header and packet_header in (0x09, 0x0a):
"""connection"""
self.all_session_users[session] = {
'pre': True,
'user': None,
'server_version': packet_response,
'seq_id': packet_seq_id,
'status': False,
'date': _cur_time
}
continue
if session in self.all_session_users:
self.create_conn(session, client_packet_text,
packet_seq_id, 'response',
packet_response, response_status)
if session in session_status:
if packet_response in session_status[session][
'response_type']:
if packet_seq_id - 1 == session_status[session][
'seq_id'] and 'com_pre' not in session_status[
session]:
session_status[session][
'end_time'] = _cur_time
session_status[session][
'status'] = self.check_packet_type(
packet_response)
session_status[session][
'response_status'] = response_status
elif packet_seq_id - 1 != session_status[
session]['seq_id']:
del session_status[session]
else:
del session_status[session]
elif session in self.all_session_users and not self.all_session_users[
session]['status'] and packet_seq_id:
if packet_seq_id - 1 == self.all_session_users[
session]['seq_id']:
self.all_session_users[session][
'seq_id'] = packet_seq_id
else:
if session in session_status:
del session_status[session]
del_session = []
for session in session_status:
if 'status' in session_status[session]:
execute_time = float(
'%.4f' % (session_status[session]['end_time'] -
session_status[session]['start_time']))
if session_status[session]['request_header'] == 0x03:
sql, values = self.sql_parser(
session_status[session]['request_text'])
else:
sql, values = session_status[session][
'request_text'], None
_session = eval(session)
try:
self._logging.info(
msg=
'source_host: {} source_port: {} destination_host: {} destination_port: {} user_name: {} sql: {} values: {} '
'execute_time:{} status:{}'.format(
_session[0], _session[1], _session[2],
_session[3], session_status[session]
['user_name'], sql, values, execute_time,
session_status[session]
['response_status']))
except:
pass
del_session.append(session)
for session in del_session:
del session_status[session]
else:
time.sleep(0.01)
except:
self.mysql_cur.execute('stop slave')
self.mysql_cur.execute(sql)
change_sql = 'change master to master_host="%s",master_port=%s,master_user="******",master_password="******",master_auto_position=1 for channel "default"' % (host,int(port),repluser,replpassword)
self.mysql_cur.execute(change_sql)
self.__set_variables(type='slave')
return True
except MySQLdb.Warning,e:
start_sql = 'start slave'
self.mysql_cur.execute(start_sql)
self.__set_variables(type='slave')
Logging(msg='Change master to {} state : Warning'.format(host),level='warning')
Logging(msg=traceback.format_exc(),level='warning')
return True
except MySQLdb.Error,e:
Logging(msg='Change master to {} state : Error'.format(host),level='error')
Logging(msg=traceback.format_exc(),level='error')
return False
def ResetMaster(self,groupname):
try:
'''获取当前binlog读取位置'''
append_stat=None
master_log_file,read_master_log_pos,master_host = self.CheckPos(get_host=True)
'''================'''
#用于mysql宕机，服务器在线追加数据
from zk_handle.zkHandler import zkHander
from Append.AppendValue import Append
from lib.get_conf import GetConf
from contextlib import closing
class Op_packet:
def __init__(self, **kwargs):
self.kwargs = kwargs
self.queue = kwargs['queue']
self._type = kwargs['_type']
self.mysql_user = kwargs['user'] if 'user' in kwargs else None
self.mysql_passwd = kwargs['passwd'] if 'passwd' in kwargs else None
if self.mysql_user:
if self.mysql_passwd:
pass
else:
print(
'Mysql connection information needs to be set at the same time'
)
import sys
sys.exit()
self.all_session_users = {}
self.get_user_list = {}
def __get_netcard(self):
'''get ip address'''
info = psutil.net_if_addrs()
for k, v in info.items():
for item in v:
if item[0] == 2 and not item[1] == '127.0.0.1' and ':' not in k:
netcard_info = item[1]
return netcard_info
def mac_addr(self, address):
"""Convert a MAC address to a readable/printable string
Args:
address (str): a MAC address in hex form (e.g. '\x01\x02\x03\x04\x05\x06')
Returns:
str: Printable/readable MAC address
"""
return ':'.join('%02x' % compat_ord(b) for b in address)
def inet_to_str(self, inet):
"""Convert inet object to a string
Args:
inet (inet struct): inet network address
Returns:
str: Printable/readable IP address
"""
# First try ipv4 and then ipv6
try:
return socket.inet_ntop(socket.AF_INET, inet)
except ValueError:
return socket.inet_ntop(socket.AF_INET6, inet)
def find_str(self, _str):
str_list = []
vv = 0
while 1:
v_i = _str.find(',', vv)
if v_i == -1:
str_list.append('?')
break
else:
str_list.append('?')
vv = v_i + 1
return str_list
def set_str(self, _str):
str_list = _str.strip().split(' ')
set_str = ''
t = None
_tmp_str = ''
for set_value in str_list:
if t:
if set_value == str_list[-1]:
set_str += '?'
else:
set_str += '?,'
t = None
continue
if set_value == '=':
set_str += _tmp_str + set_value
t = True
continue
_tmp_str = set_value
return set_str
def conn_maintain(self):
while 1:
if self.get_user_list:
__get_list = self.get_user_list.copy()
for session in __get_list:
self.get_user_info(*__get_list[session])
del self.get_user_list[session]
_idle_timeout_session = []
if self.all_session_users:
__all_session_users = self.all_session_users.copy()
for session in __all_session_users:
_cur_time = time.time()
if int(_cur_time -
__all_session_users[session]['date']) > 300:
_idle_timeout_session.append(session)
for session in _idle_timeout_session:
del self.all_session_users[session]
time.sleep(0.1)
def sql_parser(self, sql):
"""Format sql
Args:
sql: Captured sql statement
Returns:
list: [sql,[values,]] If it is an insert statement, the returned data is empty.
"""
sql = sql.strip('\n').strip()
if sql.startswith('insert') or sql.startswith('INSERT'):
k = sql.index('(')
v = sql.index(')')
v_str = tuple(self.find_str(sql[k:v + 1]))
try:
index = sql.index('values')
except:
index = sql.index('VALUES')
return sql[:index + 6] + str(v_str), None
elif sql.startswith('update') or sql.startswith('UPDATE'):
try:
set_index = sql.index('set')
except:
set_index = sql.index('SET')
try:
where_index = sql.index('where')
except:
try:
where_index = sql.index('WHERE')
except:
where_index = None
sql_start = sql[:set_index + 4]
if where_index:
sql_end = sql[where_index - 1:]
else:
sql_end = ''
_set_str = self.set_str(sql[set_index + 4:where_index])
return sql_start + _set_str + sql_end, None
else:
return sql, None
def check_packet_type(self, response):
respons_status = {
'Text_Resultest': 1,
'EOF_Packet': 1,
'ERR_Packet': 0,
'OK_Packet': 1,
'Handshake_Packet': 1
}
return respons_status[response]
def create_conn(self, session, client_packet_text, packet_seq_id, type,
response_type, response_status):
"""
:param session:
:param client_packet_text:
:param packet_seq_id:
:param type:
:param response_type:
:param response_status:
:return:
"""
if self.all_session_users[session]['status']:
pass
else:
if type == 'client':
if session in self.all_session_users and self.all_session_users[
session]['pre']:
if packet_seq_id - 1 == self.all_session_users[session][
'seq_id']:
self.all_session_users[session]['pre'] = False
self.all_session_users[session][
'user'] = client_packet_text
self.all_session_users[session][
'seq_id'] = packet_seq_id
else:
del self.all_session_users[session]
# self.create_conn(session,client_packet_text)
elif session in self.all_session_users and not self.all_session_users[
session]['status']:
if packet_seq_id - 1 == self.all_session_users[session][
'seq_id']:
self.all_session_users[session][
'seq_id'] = packet_seq_id
else:
del self.all_session_users[session]
elif type == 'response':
if session in self.all_session_users and response_type in (
'OK_Packet', 'ERR_Packet'):
if packet_seq_id - 1 == self.all_session_users[session][
'seq_id']:
self.all_session_users[session]['status'] = True
self.all_session_users[session]['date'] = time.time()
_session = eval(session)
self._logging.info(
msg=
'source_host: {} source_port: {} destination_host: {} destination_port: {} user_name: {} sql: {} values: {} '
'execute_time:{} status:{}'.format(
_session[0], _session[1], _session[2],
_session[3], self.all_session_users[session]
['user'], 'create connection', None, None,
response_status))
if response_type == 'ERR_Packet':
del self.all_session_users[session]
else:
del self.all_session_users[session]
elif session in self.all_session_users:
if packet_seq_id - 1 == self.all_session_users[session][
'seq_id']:
self.all_session_users[session][
'seq_id'] = packet_seq_id
else:
del self.all_session_users[session]
def get_user_info(self, host, port, mysql_host, mysql_port, session):
"""select user_name from mysql instance"""
if self.mysql_user:
_kwargs = {
'host': mysql_host,
'port': mysql_port,
'user': self.mysql_user,
'passwd': self.mysql_passwd
}
dd = db(**_kwargs)
user_name = dd.get(host, port)
if user_name:
self.all_session_users[session] = {
'status': True,
'user': user_name,
'pre': False,
'date': time.time()
}
dd.close()
return user_name
else:
return None
def an_packet(self):
_ip = self.__get_netcard()
_mysql_packet_op = mysql_packet(
**dict({'_type': self._type}, **{'_ip': _ip}))
session_status = {}
self._logging = Logging()
t = threading.Thread(target=self.conn_maintain, args=())
t.start()
while 1:
if not self.queue.empty():
buf, _cur_time = self.queue.get()
eth = dpkt.ethernet.Ethernet(buf)
if not isinstance(eth.data, dpkt.ip.IP):
self._logging.error(
msg='Non IP Packet type not supported %s\n' %
eth.data.__class__.__name__)
continue
ip = eth.data
if isinstance(ip.data, dpkt.tcp.TCP):
tcp = ip.data
src_host, dst_host = self.inet_to_str(
ip.src), self.inet_to_str(ip.dst)
session, packet_response, client_packet_text, packet_header, packet_seq_id, response_type, response_status = _mysql_packet_op.Unpacking(
data=tcp.data,
srchost=src_host,
srcport=tcp.sport,
dsthost=dst_host,
dstport=tcp.dport,
all_session_users=self.all_session_users)
if packet_response and packet_response in (
'COM_PROCESS_KILL', 'COM_QUIT'):
"""close connection"""
if session in self.all_session_users:
del self.all_session_users[session]
if client_packet_text:
if session in self.all_session_users:
self.create_conn(session, client_packet_text,
packet_seq_id, 'client',
response_type, response_status)
if packet_header == 0x16:
session_status[session] = {
'start_time': _cur_time,
'request_text': client_packet_text,
'request_header': packet_header,
'seq_id': packet_seq_id,
'response_type': response_type,
'com_pre': True
}
elif packet_header == 0x17 and session in session_status and 'com_pre' in session_status[
session]:
del session_status[session]['com_pre']
continue
elif packet_header in (0x01, 0x18):
session_status[session] = {
'start_time': _cur_time,
'request_text': client_packet_text,
'request_header': packet_header,
'seq_id': packet_seq_id,
'response_type': response_type,
'end_time': _cur_time,
'status': 1,
'response_status': None
}
elif packet_header == 0x19:
continue
else:
session_status[session] = {
'start_time': _cur_time,
'request_text': client_packet_text,
'request_header': packet_header,
'seq_id': packet_seq_id,
'response_type': response_type
}
if session in self.all_session_users and self.all_session_users[
session]['status']:
session_status[session][
'user_name'] = self.all_session_users[session][
'user']
self.all_session_users[session]['date'] = _cur_time
elif session not in self.all_session_users:
# session_status[session]['user_name'] = self.get_user_info(host=src_host,port=tcp.sport,
# mysql_host=dst_host,
# mysql_port=tcp.dport,
# session=session)
session_status[session]['user_name'] = None
if session not in self.get_user_list and packet_header not in (
0x01, 0x19, 0x18) and any(
[self.mysql_user, self.mysql_passwd]):
self.get_user_list[session] = [
src_host, tcp.sport, dst_host, tcp.dport,
session
]
elif packet_response:
if packet_header and packet_header in (0x09, 0x0a):
"""connection"""
self.all_session_users[session] = {
'pre': True,
'user': None,
'server_version': packet_response,
'seq_id': packet_seq_id,
'status': False,
'date': _cur_time
}
continue
if session in self.all_session_users:
self.create_conn(session, client_packet_text,
packet_seq_id, 'response',
packet_response, response_status)
if session in session_status:
if packet_response in session_status[session][
'response_type']:
if packet_seq_id - 1 == session_status[session][
'seq_id'] and 'com_pre' not in session_status[
session]:
session_status[session][
'end_time'] = _cur_time
session_status[session][
'status'] = self.check_packet_type(
packet_response)
session_status[session][
'response_status'] = response_status
elif packet_seq_id - 1 != session_status[
session]['seq_id']:
del session_status[session]
else:
del session_status[session]
elif session in self.all_session_users and not self.all_session_users[
session]['status'] and packet_seq_id:
if packet_seq_id - 1 == self.all_session_users[
session]['seq_id']:
self.all_session_users[session][
'seq_id'] = packet_seq_id
else:
if session in session_status:
del session_status[session]
del_session = []
for session in session_status:
if 'status' in session_status[session]:
execute_time = float(
'%.4f' % (session_status[session]['end_time'] -
session_status[session]['start_time']))
if session_status[session]['request_header'] == 0x03:
sql, values = self.sql_parser(
session_status[session]['request_text'])
else:
sql, values = session_status[session][
'request_text'], None
_session = eval(session)
try:
self._logging.info(
msg=
'source_host: {} source_port: {} destination_host: {} destination_port: {} user_name: {} sql: {} values: {} '
'execute_time:{} status:{}'.format(
_session[0], _session[1], _session[2],
_session[3], session_status[session]
['user_name'], sql, values, execute_time,
session_status[session]
['response_status']))
except:
pass
del_session.append(session)
for session in del_session:
del session_status[session]
else:
time.sleep(0.01)
class Op_packet:
def __init__(self, **kwargs):
self.kwargs = kwargs
self.queue = kwargs['queue']
self._type = kwargs['_type']
self.ckhost = kwargs['ckhost'] if 'ckhost' in kwargs else None
self.many = kwargs['many'] if 'many' in kwargs else 1000
self.all_session_users = {}
self.get_user_list = {}
def __get_netcard(self):
'''get ip address'''
info = psutil.net_if_addrs()
for k, v in info.items():
if k == self.kwargs['eth']:
for item in v:
if item[0] == 2 and not item[
1] == '127.0.0.1' and ':' not in k:
netcard_info = item[1]
return netcard_info
def mac_addr(self, address):
"""Convert a MAC address to a readable/printable string
Args:
address (str): a MAC address in hex form (e.g. '\x01\x02\x03\x04\x05\x06')
Returns:
str: Printable/readable MAC address
"""
return ':'.join('%02x' % compat_ord(b) for b in address)
def inet_to_str(self, inet):
"""Convert inet object to a string
Args:
inet (inet struct): inet network address
Returns:
str: Printable/readable IP address
"""
# First try ipv4 and then ipv6
try:
return socket.inet_ntop(socket.AF_INET, inet)
except ValueError:
return socket.inet_ntop(socket.AF_INET6, inet)
def Unpacking(self, data):
"""
unpack packet
:return:
"""
self.offset = 8 #跳过头部的*3\r\n$4\r\n
s_end = self.find_r(data)
self.command = data[self.offset:s_end].decode("utf8", "ignore")
self.offset = s_end + 2
# self.seek_num = 0
while 1:
if self.check_payload():
return
if self.seek_tmp(data):
self.get_string(data)
else:
return
def get_string(self, data):
s_end = self.find_r(data)
self.command = self.command + ' ' + data[self.offset:s_end].decode(
"utf8", "ignore")
self.offset = s_end + 2
def seek_tmp(self, data):
# self.seek_num += 1
# if self.seek_num >= 10:
# print(self.offset, self.payload)
if self.check_payload():
return None
elif self.check_a(data):
self.find_n(data)
return self.seek_tmp(data)
else:
return True
def find_n(self, data):
s_end = data.find(b'\n', self.offset)
self.offset = s_end + 1
def find_r(self, data):
return data.find(b'\r', self.offset)
def check_a(self, data):
if data[self.offset] == 36:
return True
return None
def check_payload(self):
if self.offset + 2 >= self.payload:
return True
elif self.offset < 8:
return True
return None
def GetSession(self, srchost, srcport, dsthost, dstport):
'''
获取session key, 并检查是否为client请求包，如果为server返回包将直接抛弃
:param srchost:
:param srcport:
:param dsthost:
:param dstport:
:return:
'''
if self._type == 'src':
if srchost == self._ip:
'''client packet'''
session = [srchost, srcport, dsthost, dstport]
return session, True
else:
'''server response'''
return None, None
elif self._type == 'des':
if srchost == self._ip:
'''server response'''
return None, None
else:
'''client packet'''
session = [srchost, srcport, dsthost, dstport]
return session, True
def an_packet(self):
self._ip = self.__get_netcard()
self._logging = Logging()
self.command_list = []
self.command_list_len = 0
while 1:
if not self.queue.empty():
buf, _cur_time = self.queue.get()
eth = dpkt.ethernet.Ethernet(buf)
if not isinstance(eth.data, dpkt.ip.IP):
self._logging.error(
msg='Non IP Packet type not supported %s\n' %
eth.data.__class__.__name__)
continue
ip = eth.data
if isinstance(ip.data, dpkt.tcp.TCP):
tcp = ip.data
src_host, dst_host = self.inet_to_str(
ip.src), self.inet_to_str(ip.dst)
session, session_status = self.GetSession(
src_host, tcp.sport, dst_host, tcp.dport)
if session_status:
self.payload = len(tcp.data)
if self.payload <= 8 and self.payload >= 4194304: # 抛弃小于8字节和大于4m的数据包
continue
self.Unpacking(data=tcp.data)
if len(self.command) > 0:
jsons = {
'source_host': session[0],
'source_port': session[1],
'destination_host': session[2],
'destination_port': session[3],
'command': self.command,
'event_date': int(_cur_time),
'cluster_name': self.kwargs['cluster_name']
}
if self.ckhost:
self.command_list.append(jsons)
self.command_list_len += 1
self.insert_ck()
else:
self._logging.info(msg=json.dumps(jsons))
else:
time.sleep(0.01)
def insert_ck(self):
if self.command_list_len >= self.many:
url = 'clickhouse://{}'.format(self.ckhost)
try:
conn = connect(url)
cursor = conn.cursor()
cursor.executemany(
'insert into redis_audit.redis_audit_info(source_host,source_port,destination_host,destination_port,command,cluster_name,event_date) values',
self.command_list)
except:
print(traceback.format_exc())
self.command_list_len = 0
self.command_list = []
def __executesql(self):
Logging(msg='Additional unsynchronized data now', level='info')
for sql in tmepdata.sql_all_list:
Logging(msg='execute sql -- {}'.format(sql), level='info')
try:
self.mysql_cur.execute(sql[0], sql[1])
Logging(msg='state OK!', level='info')
except MySQLdb.Warning:
Logging(msg=traceback.format_exc(), level='warning')
except Exception, e:
Logging(msg='state failed', level='error')
Logging(msg=traceback.format_exc(), level='error')
self.mysql_conn.rollback()
tmepdata.sql_all_list = []
return False
else:
Logging(msg='There is no data to synchronize.', level='info')
self.mysql_conn.commit()
self.__init_tmepdata()
return True
def __init_tmepdata(self):
tmepdata.database_name, tmepdata.table_name, tmepdata.cloums_type_id_list, tmepdata.metadata_dict = None, None, None, None
tmepdata.table_struct_list = {}
tmepdata.table_pk_idex_list = {}
tmepdata.sql_all_list = []
tmepdata.table_struct_type_list = {} # 字段类型列表
tmepdata.table_struce_key = None
