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
