def process_binlog(self): stream = BinLogStreamReader(connection_settings=self.conn_setting, server_id=self.server_id, log_file=self.start_file, log_pos=self.start_pos, only_schemas=self.only_schemas, only_tables=self.only_tables, resume_stream=True) flag_last_event = False e_start_pos, last_pos = stream.log_pos, stream.log_pos # to simplify code, we do not use flock for tmp_file. tmp_file = create_unique_file('%s.%s' % (self.conn_setting['host'], self.conn_setting['port'])) with temp_open(tmp_file, "w") as f_tmp, self.connection as cursor: for binlog_event in stream: if not self.stop_never: try: event_time = datetime.datetime.fromtimestamp(binlog_event.timestamp) except OSError: event_time = datetime.datetime(1980, 1, 1, 0, 0) if (stream.log_file == self.end_file and stream.log_pos == self.end_pos) or \ (stream.log_file == self.eof_file and stream.log_pos == self.eof_pos): flag_last_event = True elif event_time < self.start_time: if not (isinstance(binlog_event, RotateEvent) or isinstance(binlog_event, FormatDescriptionEvent)): last_pos = binlog_event.packet.log_pos continue elif (stream.log_file not in self.binlogList) or \ (self.end_pos and stream.log_file == self.end_file and stream.log_pos > self.end_pos) or \ (stream.log_file == self.eof_file and stream.log_pos > self.eof_pos) or \ (event_time >= self.stop_time): break # else: # raise ValueError('unknown binlog file or position') if isinstance(binlog_event, QueryEvent) and binlog_event.query == 'BEGIN': e_start_pos = last_pos if isinstance(binlog_event, QueryEvent) and not self.only_dml: sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event, flashback=self.flashback, no_pk=self.no_pk) if sql: print(sql) elif is_dml_event(binlog_event) and event_type(binlog_event) in self.sql_type: for row in binlog_event.rows: sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event, no_pk=self.no_pk, row=row, flashback=self.flashback, e_start_pos=e_start_pos) if self.flashback: f_tmp.write(sql + '\n') else: print(sql) if not (isinstance(binlog_event, RotateEvent) or isinstance(binlog_event, FormatDescriptionEvent)): last_pos = binlog_event.packet.log_pos if flag_last_event: break stream.close() f_tmp.close() if self.flashback: self.print_rollback_sql(filename=tmp_file) return True
def process_binlog(self): stream = BinLogStreamReader(connection_settings=self.connectionSettings, server_id=self.serverId, log_file=self.startFile, log_pos=self.startPos, only_schemas=self.only_schemas, only_tables=self.only_tables, resume_stream=True) cur = self.connection.cursor() tmpFile = create_unique_file('%s.%s' % (self.connectionSettings['host'],self.connectionSettings['port'])) # to simplify code, we do not use file lock for tmpFile. ftmp = open(tmpFile ,"w") flagLastEvent = False eStartPos, lastPos = stream.log_pos, stream.log_pos try: for binlogevent in stream: if not self.stopnever: if (stream.log_file == self.endFile and stream.log_pos == self.endPos) or (stream.log_file == self.eofFile and stream.log_pos == self.eofPos): flagLastEvent = True elif datetime.datetime.fromtimestamp(binlogevent.timestamp) < self.startTime: if not (isinstance(binlogevent, RotateEvent) or isinstance(binlogevent, FormatDescriptionEvent)): lastPos = binlogevent.packet.log_pos continue elif (stream.log_file not in self.binlogList) or (self.endPos and stream.log_file == self.endFile and stream.log_pos > self.endPos) or (stream.log_file == self.eofFile and stream.log_pos > self.eofPos) or (datetime.datetime.fromtimestamp(binlogevent.timestamp) >= self.stopTime): break # else: # raise ValueError('unknown binlog file or position') if isinstance(binlogevent, QueryEvent) and binlogevent.query == 'BEGIN': eStartPos = lastPos if isinstance(binlogevent, QueryEvent): sql = concat_sql_from_binlogevent(cursor=cur, binlogevent=binlogevent, flashback=self.flashback, nopk=self.nopk) if sql: print sql elif isinstance(binlogevent, WriteRowsEvent) or isinstance(binlogevent, UpdateRowsEvent) or isinstance(binlogevent, DeleteRowsEvent): for row in binlogevent.rows: sql = concat_sql_from_binlogevent(cursor=cur, binlogevent=binlogevent, row=row , flashback=self.flashback, nopk=self.nopk, eStartPos=eStartPos) if self.flashback: ftmp.write(sql + '\n') else: print sql if not (isinstance(binlogevent, RotateEvent) or isinstance(binlogevent, FormatDescriptionEvent)): lastPos = binlogevent.packet.log_pos if flagLastEvent: break ftmp.close() if self.flashback: with open(tmpFile) as ftmp: for line in reversed_lines(ftmp): print line.rstrip() finally: os.remove(tmpFile) cur.close() stream.close() return True
def process_binlog(self): stream = BinLogStreamReader(connection_settings=self.conn_setting, server_id=self.server_id, log_file=self.start_file, log_pos=self.start_pos, only_schemas=self.only_schemas, only_tables=self.only_tables, resume_stream=True, blocking=True) # for binlogevent in stream: # binlogevent.dump() # return flag_last_event = False e_start_pos, last_pos = stream.log_pos, stream.log_pos # logger.log(f"e_start_pos:{e_start_pos}, last_pos:{last_pos}") #4 # to simplify code, we do not use flock for tmp_file. tmp_file = create_unique_file('%s.%s' % (self.conn_setting['host'], self.conn_setting['port'])) with temp_open(tmp_file, "w") as f_tmp, self.connection as cursor: for binlog_event in stream: # logger.log(f"binlog_event:{binlog_event}") if not self.stop_never: #Continuously parse binlog. default: stop at the latest event when you start. # logger.log(f"### not parse binlog continue, binlog_evnet: ###") # # print(f"binlog_event:{binlog_event}") # binlog_event # logger.log(f"### not parse binlog continue, end: ###") try: event_time = datetime.datetime.fromtimestamp(binlog_event.timestamp) except OSError: event_time = datetime.datetime(1980, 1, 1, 0, 0) # logger.log(f"event_time:{event_time}, log_pos:{binlog_event.packet.log_pos}, type:{type(binlog_event)}") # logger.log(f"eof_file:{self.eof_file}") #if to then event end like 1555 if (stream.log_file == self.end_file and stream.log_pos == self.end_pos) or \ (stream.log_file == self.eof_file and stream.log_pos == self.eof_pos): # logger.log(f"flag_last_event = True") flag_last_event = True elif event_time < self.start_time: #if current event time befor start time if not (isinstance(binlog_event, RotateEvent) or isinstance(binlog_event, FormatDescriptionEvent)): last_pos = binlog_event.packet.log_pos continue elif (stream.log_file not in self.binlogList) or \ (self.end_pos and stream.log_file == self.end_file and stream.log_pos > self.end_pos) or \ (stream.log_file == self.eof_file and stream.log_pos > self.eof_pos) or \ (event_time >= self.stop_time): break # else: # raise ValueError('unknown binlog file or position') if isinstance(binlog_event, QueryEvent) and binlog_event.query == 'BEGIN': # logger.log(f"isinstance(binlog_event, QueryEvent) and binlog_event.query == 'BEGIN', query:{binlog_event.query}") e_start_pos = last_pos if isinstance(binlog_event, QueryEvent) and not self.only_dml: #INSERT/UPDATE/DELETE # logger.log(f"QueryEvent and not self.only_dml, exec concat_sql_from_binlog_event, query:\n{binlog_event.query}") sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event, flashback=self.flashback, no_pk=self.no_pk) if sql: print(f"{sql}") elif is_dml_event(binlog_event) and event_type(binlog_event) in self.sql_type: for row in binlog_event.rows: # logger.log(f"row:{row}") sql = concat_sql_from_binlog_event(cursor=cursor, binlog_event=binlog_event, no_pk=self.no_pk, row=row, flashback=self.flashback, e_start_pos=e_start_pos) if self.flashback: f_tmp.write(sql + '\n') else: print(sql) # print(f"sql:\n{sql}") if not (isinstance(binlog_event, RotateEvent) or isinstance(binlog_event, FormatDescriptionEvent)): last_pos = binlog_event.packet.log_pos if flag_last_event: break stream.close() f_tmp.close() if self.flashback: self.print_rollback_sql(filename=tmp_file) return True
def __init__(self, connection_settings, start_file=None, start_pos=None, end_file=None, end_pos=None, start_time=None, stop_time=None, only_schemas=None, only_tables=None, no_pk=False, flashback=False, stop_never=False, back_interval=1.0, only_dml=True, sql_type=None, rollback_with_primary_key=False, rollback_with_changed_value=False, pseudo_thread_id=0): """ conn_setting: {'host': 127.0.0.1, 'port': 3306, 'user': user, 'passwd': passwd, 'charset': 'utf8'} """ if not start_file: raise ValueError('Lack of parameter: start_file') self.conn_setting = connection_settings self.start_file = start_file self.start_pos = start_pos if start_pos else 4 # use binlog v4 self.end_file = end_file if end_file else start_file self.end_pos = end_pos self.pseudo_thread_id = pseudo_thread_id if start_time: self.start_time = datetime.datetime.strptime( start_time, "%Y-%m-%d %H:%M:%S") else: self.start_time = datetime.datetime.strptime( '1980-01-01 00:00:00', "%Y-%m-%d %H:%M:%S") if stop_time: self.stop_time = datetime.datetime.strptime( stop_time, "%Y-%m-%d %H:%M:%S") else: self.stop_time = datetime.datetime.strptime( '2999-12-31 00:00:00', "%Y-%m-%d %H:%M:%S") self.rollback_with_primary_key = rollback_with_primary_key self.rollback_with_changed_value = rollback_with_changed_value self.only_schemas = only_schemas if only_schemas else None self.only_tables = only_tables if only_tables else None self.no_pk, self.flashback, self.stop_never, self.back_interval = ( no_pk, flashback, stop_never, back_interval) self.only_dml = only_dml self.sql_type = [t.upper() for t in sql_type] if sql_type else [] self.binlogList = [] file_name = '%s_%s' % (self.conn_setting['host'], self.conn_setting['port']) self.connection = pymysql.connect(**self.conn_setting) execute_sql_file, rollback_sql_file, tmp_sql_file = create_unique_file( file_name) self.execute_sql_file = execute_sql_file self.rollback_sql_file = rollback_sql_file self.tmp_sql_file = tmp_sql_file self.rollback_sql_files = list() with self.connection as cursor: cursor.execute("SHOW MASTER STATUS") self.eof_file, self.eof_pos = cursor.fetchone()[:2] cursor.execute("SHOW MASTER LOGS") bin_index = [row[0] for row in cursor.fetchall()] if self.start_file not in bin_index: raise ValueError( 'parameter error: start_file %s not in mysql server' % self.start_file) binlog2i = lambda x: x.split('.')[1] for binary in bin_index: if binlog2i(self.start_file) <= binlog2i(binary) <= binlog2i( self.end_file): self.binlogList.append(binary) cursor.execute("SELECT @@server_id") self.server_id = cursor.fetchone()[0] if not self.server_id: raise ValueError( 'missing server_id in %s:%s' % (self.conn_setting['host'], self.conn_setting['port']))
def process_binlog(self): stream = BinLogFileReader( self.filePath, ctl_connection_settings=self.connectionSettings, log_pos=self.startPos, only_schemas=self.only_schemas, only_tables=self.only_tables, resume_stream=True) cur = self.connection.cursor() tmpFile = create_unique_file( '%s.%s' % (self.connectionSettings['host'], self.connectionSettings['port']) ) # to simplify code, we do not use file lock for tmpFile. ftmp = open(tmpFile, "w") flagLastEvent = False eStartPos, lastPos = stream.log_pos, stream.log_pos try: for binlogevent in stream: if not self.stopnever: if datetime.datetime.fromtimestamp( binlogevent.timestamp) < self.startTime: if not (isinstance(binlogevent, RotateEvent) or isinstance(binlogevent, FormatDescriptionEvent)): lastPos = binlogevent.packet.log_pos continue elif datetime.datetime.fromtimestamp( binlogevent.timestamp) >= self.stopTime: break else: pass if isinstance(binlogevent, QueryEvent) and binlogevent.query == 'BEGIN': eStartPos = lastPos if isinstance(binlogevent, QueryEvent): sql = concat_sql_from_binlogevent(cursor=cur, binlogevent=binlogevent, flashback=self.flashback, no_pk=self.nopk) if sql: print sql print "\n" elif isinstance(binlogevent, WriteRowsEvent) or isinstance( binlogevent, UpdateRowsEvent) or isinstance( binlogevent, DeleteRowsEvent): for row in binlogevent.rows: sql = concat_sql_from_binlogevent( cursor=cur, binlogevent=binlogevent, row=row, flashback=self.flashback, no_pk=self.nopk, eStartPos=eStartPos) if self.flashback: ftmp.write(sql + '\n') else: print sql print "\n" if not (isinstance(binlogevent, RotateEvent) or isinstance(binlogevent, FormatDescriptionEvent)): lastPos = binlogevent.packet.log_pos if flagLastEvent: break ftmp.close() if self.flashback: self.print_rollback_sql(tmpFile) finally: os.remove(tmpFile) cur.close() stream.close() return True