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