def main():
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
server_id=3,
blocking=True,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
s3 = boto3.resource('s3')
previous_events = []
for binlogevent in stream:
prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
vals = row["values"]
print vals
elif isinstance(binlogevent, UpdateRowsEvent):
vals = row["after_values"]
print vals
elif isinstance(binlogevent, WriteRowsEvent):
s3.Bucket(S3_SETTINGS['bucket_name']).put_object(
Key="awef" + str(random.random()) + ".txt",
Body=str(row["values"]))
stream.close()
def test_binlog_info():
binlog_conf = {
"host": "127.0.0.1",
"port": 3306,
"user": "******",
"passwd": "ekpapi",
"charset": 'utf8',
"db": "xserver"
}
stream = BinLogStreamReader(
connection_settings=binlog_conf,
server_id=2,
only_tables=["test"],
only_schemas=["xserver"],
# only_events=[QueryEvent],
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
blocking=True)
count = 0
for binlog_event in stream:
print stream.log_file
print stream.log_pos
binlog_event.dump()
print "rows: %s" % binlog_event.rows
count += 1
print count
stream.close()
class MySQL(Listener):
@classmethod
def new(cls, node: DBNode, dsn: DSN, server_id: int) -> 'Listener':
return cls(node, dsn, server_id)
def __init__(self, node: DBNode, dsn: DSN, server_id: int):
self.node = node
self.dsn = dsn
self.server_id = server_id
def get_stream(self, log_file: Optional[str],
log_pos: int) -> BinLogStreamReader:
blocking = False
if log_file is None:
self.stream = BinLogStreamReader(
connection_settings=self.dsn.get_args(),
server_id=self.server_id,
blocking=blocking)
else:
self.stream = BinLogStreamReader(
connection_settings=self.dsn.get_args(),
server_id=self.server_id,
blocking=blocking,
log_file=log_file,
log_pos=log_pos)
return self.stream
def close(self):
self.stream.close()
def main():
# connect rethinkdb
rethinkdb.connect("localhost", 28015, "mysql")
try:
rethinkdb.db_drop("mysql").run()
except:
pass
rethinkdb.db_create("mysql").run()
tables = [
"dept_emp", "dept_manager", "titles", "salaries", "employees",
"departments"
]
for table in tables:
rethinkdb.db("mysql").table_create(table).run()
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
blocking=True,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
)
# process Feed
for binlogevent in stream:
if not isinstance(binlogevent, WriteRowsEvent):
continue
for row in binlogevent.rows:
if not binlogevent.schema == "employees":
continue
vals = dict((str(k), str(v)) for k, v in row["values"].iteritems())
rethinkdb.table(binlogevent.table).insert(vals).run()
stream.close()
def transfer(self):
"""
sync mysql binlog data to redis
:return:
"""
log_file, log_pos = self._get_log_pos()
log_pos = int(log_pos) if log_pos else log_pos
stream = BinLogStreamReader(
connection_settings=self.MYSQL_SETTINGS,
server_id=int(self.SELF['server_id']),
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
resume_stream=True,
log_file=log_file,
log_pos=log_pos,
blocking=True)
for binlog_event in stream:
prefix = f'{binlog_event.schema}:{binlog_event.table}:'
self._set_log_pos(stream.log_file, stream.log_pos)
for row in binlog_event.rows:
if isinstance(binlog_event, DeleteRowsEvent):
self._delete_handler(prefix=prefix, row=row)
if isinstance(binlog_event, UpdateRowsEvent):
self._update_handler(prefix=prefix, row=row)
if isinstance(binlog_event, WriteRowsEvent):
self._write_handler(prefix=prefix, row=row)
stream.close()
def main():
producer = Kafka_producer("10.40.58.114", 9092, "tmysql")
mysql_settings = {'host': '10.40.63.52', 'port': 3306, 'user': '******', 'passwd': 'zaq1xsw2'}
stream = BinLogStreamReader(connection_settings=mysql_settings, server_id=7765,
only_schemas=["test"],
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
resume_stream=True,
blocking=True)
for binlogevent in stream:
for row in binlogevent.rows:
event = {"schema": binlogevent.schema, "table": binlogevent.table,
"log_pos": binlogevent.packet.log_pos}
if isinstance(binlogevent, DeleteRowsEvent):
event["action"] = "delete"
event["values"] = dict(row["values"].items())
event = dict(event.items())
elif isinstance(binlogevent, UpdateRowsEvent):
event["action"] = "update"
event["before_values"] = dict(row["before_values"].items())
event["after_values"] = dict(row["after_values"].items())
event = dict(event.items())
elif isinstance(binlogevent, WriteRowsEvent):
event["action"] = "insert"
event["values"] = dict(row["values"].items())
event = dict(event.items())
print(json.dumps(event, cls=DateEncoder))
producer.sendjsondata(json.dumps(event, cls=DateEncoder))
sys.stdout.flush()
stream.close()
def main():
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
server_id=3,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
s3 = boto3.resource('s3')
for binlogevent in stream:
dump = []
for row in binlogevent.rows:
event = {"schema": binlogevent.schema, "table": binlogevent.table}
#if isinstance(binlogevent, DeleteRowsEvent):
#event["action"] = "delete"
#event = dict(event.items() + row["values"].items())
if isinstance(binlogevent, WriteRowsEvent):
event["action"] = "insert"
event = dict(event.items())
event["values"] = row["values"]
if "created_at" in event["values"].keys():
dump.append( event )
elif isinstance(binlogevent, UpdateRowsEvent):
event["action"] = "update"
event = dict(event.items() + row["after_values"].items())
if len(dump) > MAX_DUMP_SIZE:
s3.Bucket(S3_SETTINGS['bucket_name']).put_object(Key=event["table"] + "/"+ partition(event["values"]) + "/0" ".json", Body=str(event["values"]))
stream.close()
class MySQLConnector:
"""
The Elastic Replicator create a stream to the MySQL database
and allow to send the specific row to the elastic.
"""
def __init__(self,
mysql_settings,
events,
blocking,
id_label="_id",
skip_to_timestamp=None):
self.stream = BinLogStreamReader(connection_settings=mysql_settings,
server_id=3,
only_events=events,
blocking=blocking,
skip_to_timestamp=skip_to_timestamp)
self.id_label = id_label
def get_id_label(self):
return self.id_label
def get_stream(self):
return self.stream
def get_id(self, row):
return row[self.id_label]
def __del__(self):
self.stream.close()
def proc_binlog(self):
stream = BinLogStreamReader(
connection_settings = self.config['mysql'],
server_id = self.config['slave']['server_id'],
log_file = self.log_file,
log_pos = self.log_pos,
only_schemas = self.config['slave']['schemas'],
blocking = True,
resume_stream = bool(self.log_file and self.log_pos),
only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent]
)
for binlogevent in stream:
self.log_file = stream.log_file
self.log_pos = stream.log_pos
for row in binlogevent.rows:
pk = binlogevent.primary_key
table = binlogevent.table
schema = binlogevent.schema
if isinstance(binlogevent, WriteRowsEvent):
yield self.es.index_op(self._format(row['values']), doc_type=table, index=schema, id=row['values'][pk])
elif isinstance(binlogevent, UpdateRowsEvent):
yield self.es.update_op(self._format(row['after_values']), doc_type=table, index=schema, id=row['after_values'][pk])
elif isinstance(binlogevent, DeleteRowsEvent):
yield self.es.delete_op(doc_type=table, index=schema, id=row['values'][pk])
else:
logging.warning("unsupport event type")
continue
stream.close()
def main():
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
server_id=1,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
only_tables=ONLY_Tables)
for binlogevent in stream:
prefix = "%s-%s-" % (binlogevent.schema, binlogevent.table)
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
vals = row["values"]
print "[Delete] SQLBinLog Row :" + str(row)
updateRediSCache(False, prefix, vals.keys(), vals)
elif isinstance(binlogevent, UpdateRowsEvent):
before_values = row["before_values"]
after_values = row["after_values"]
print "[Update] SQLBinLog Row :" + str(row)
updateRediSCache(True, prefix,
diff(before_values, after_values).keys(),
after_values)
elif isinstance(binlogevent, WriteRowsEvent):
vals = row["values"]
print "[Insert] SQLBinLog Row :" + str(row)
updateRediSCache(True, prefix, vals.keys(), vals)
stream.close()
def main():
# connect rethinkdb
rethinkdb.connect("localhost", 28015, "mysql")
try:
rethinkdb.db_drop("mysql").run()
except:
pass
rethinkdb.db_create("mysql").run()
tables = ["dept_emp", "dept_manager", "titles",
"salaries", "employees", "departments"]
for table in tables:
rethinkdb.db("mysql").table_create(table).run()
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
blocking=True,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
)
# process Feed
for binlogevent in stream:
if not isinstance(binlogevent, WriteRowsEvent):
continue
for row in binlogevent.rows:
if not binlogevent.schema == "employees":
continue
vals = {}
vals = {str(k): str(v) for k, v in row["values"].iteritems()}
rethinkdb.table(binlogevent.table).insert(vals).run()
stream.close()
def transfer(self):
log_file, log_pos = self.get_log_pos()
stream = BinLogStreamReader(
connection_settings=self.MYSQL_SETTINGS,
server_id=int(self.SELF["server_id"]),
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
log_file=log_file,
log_pos=log_pos,
blocking=True)
print "sync"
for binlogevent in stream:
prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
self.set_log_pos(stream.log_file, stream.log_pos)
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
self.delete_handler(prefix, row)
elif isinstance(binlogevent, UpdateRowsEvent):
self.update_handler(prefix, row)
elif isinstance(binlogevent, WriteRowsEvent):
self.insert_handler(prefix, row)
stream.close()
def main():
log_file, log_pos = get_log_pos()
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
server_id=11,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
#log_file = "mysql-bin.000001",
#log_pos = "12812",
blocking=True)
print "sync"
for binlogevent in stream:
prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
set_log_pos(stream.log_file, stream.log_pos)
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
vals = row["values"]
r.delete(prefix + str(vals["id"]))
elif isinstance(binlogevent, UpdateRowsEvent):
vals = row["after_values"]
r.hmset(prefix + str(vals["id"]), vals)
elif isinstance(binlogevent, WriteRowsEvent):
vals = row["values"]
r.hmset(prefix + str(vals["id"]), vals)
stream.close()
def main():
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
for binlogevent in stream:
for row in binlogevent.rows:
event = {}
event["schema"] = binlogevent.schema
event["table"] = binlogevent.table
if isinstance(binlogevent, DeleteRowsEvent):
event["action"] = "delete"
event = dict(event.items() + row["values"].items())
elif isinstance(binlogevent, UpdateRowsEvent):
event["action"] = "update"
event = dict(event.items() + row["after_values"].items())
elif isinstance(binlogevent, WriteRowsEvent):
event["action"] = "insert"
event = dict(event.items() + row["values"].items())
print json.dumps(event)
sys.stdout.flush()
stream.close()
def readBinLog():
stream = BinLogStreamReader(
connection_settings=mysql_settings,
server_id=3,
blocking=True,
only_tables=['order_info','log_info'],
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
for binlogevent in stream:
for row in binlogevent.rows:
event = { "schema": binlogevent.schema, "table": binlogevent.table, "log_pos": binlogevent.packet.log_pos }
if isinstance(binlogevent, DeleteRowsEvent):
event["action"] = "delete"
event["origin"] = dict(row["values"].items())
event["current"] = None
event = dict(event.items())
elif isinstance(binlogevent, UpdateRowsEvent):
event["action"] = "update"
event["origin"] = dict(row["before_values"].items())
event["current"] = dict(row["after_values"].items())
event = dict(event.items())
elif isinstance(binlogevent, WriteRowsEvent):
event["action"] = "insert"
event["origin"] = None
event["current"] = dict(row["values"].items())
event = dict(event.items())
sendBinLog(event)
stream.close()
def main():
stream = BinLogStreamReader(connection_settings=SMYSQL_SETTINGS,
server_id=server_id,
resume_stream=True,
auto_position=gtid_next,
only_schemas=only_schemas,
ignored_schemas=ignored_schemas,
only_tables=only_tables,
ignored_tables=ignored_tables,
only_events=[
DeleteRowsEvent, WriteRowsEvent,
UpdateRowsEvent, GtidEvent
])
con = pymysql.connect(**SMYSQL_SETTINGS)
template = ''
values = []
cursor = con.cursor()
redis_con = redis_pool(REDIS_SETTINGS)
for binlogevent in stream:
if isinstance(binlogevent, GtidEvent):
pre_gtid_next = redis_con.hget('gtid_next', redis_chanel)
redis_con.hset('pre_gtid_next', redis_chanel, pre_gtid_next)
redis_con.hset('gtid_next', redis_chanel, binlogevent.gtid)
else:
#schema = binlogevent.schema
if target_schemas.has_key(binlogevent.schema):
schema = target_schemas[binlogevent.schema]
else:
schema = binlogevent.schema
table = binlogevent.table
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
template = 'DELETE FROM `{0}`.`{1}` WHERE {2} LIMIT 1;'.format(
schema, table,
' AND '.join(map(compare_items,
row['values'].items())))
values = map(fix_object, row['values'].values())
elif isinstance(binlogevent, UpdateRowsEvent):
template = 'UPDATE `{0}`.`{1}` SET {2} WHERE {3} LIMIT 1;'.format(
schema, table, ', '.join([
'`%s`=%%s' % k for k in row['after_values'].keys()
]), ' AND '.join(
map(compare_items, row['before_values'].items())))
values = map(
fix_object,
list(row['after_values'].values()) +
list(row['before_values'].values()))
elif isinstance(binlogevent, WriteRowsEvent):
template = 'INSERT INTO `{0}`.`{1}`({2}) VALUES({3});'.format(
schema, table, ', '.join(
map(lambda key: '`%s`' % key,
row['values'].keys())),
', '.join(['%s'] * len(row['values'])))
values = map(fix_object, row['values'].values())
sql = cursor.mogrify(template, values)
#redis_con.publish(redis_chanel, sql)
con, cursor = con_mysql(DMYSQL_SETTINGS)
sync_binlog(con, cursor, sql)
stream.close()
def process_binlog(self):
"""
:parameter last_events 标志是否读取到最后一个事件
:parameter 允许读取的MySQL语句
"""
last_events = False
allow_sql = ['update', 'delete', 'insert', 'CREATE', 'DROP']
stream = BinLogStreamReader(connection_settings=self.conn_setting,
server_id=1)
for binlog_events in stream:
if binlog_events.event_type == 2:
# 读取query语句
query = binlog_events.query
# 空格分割 获取命令query[0]
query_ = query.split(' ')
# print(query_)
if query_[0] in allow_sql:
self.current.append(binlog_events.query)
#print(self.current)
# 读取到最后一个事件
#if stream.log_file == self.end_file and stream.log_pos == self.eof_pos:
#last_events = True
# 不退出循环表示获取实时mysql语句
if last_events == True: break
stream.close()
print(self.current)
def process_binlog(self):
sqlList = []
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:
sqlList.append(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:
sqlList.append(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:
return self.get_rollback_sql(filename=tmp_file)
return sqlList
def main():
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
server_id=3,
blocking=True,
only_schemas=["jintui"],
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
producer = KafkaProducer(kafka_setting, topic_setting)
for binlogevent in stream:
for row in binlogevent.rows:
event = {"schema": binlogevent.schema, "table": binlogevent.table}
if isinstance(binlogevent, DeleteRowsEvent):
event["action"] = "delete"
event["values"] = dict(row["values"].items())
event = dict(event.items())
elif isinstance(binlogevent, UpdateRowsEvent):
event["action"] = "update"
event["before_values"] = dict(row["before_values"].items())
event["after_values"] = dict(row["after_values"].items())
event = dict(event.items())
elif isinstance(binlogevent, WriteRowsEvent):
event["action"] = "insert"
event["values"] = dict(row["values"].items())
event = dict(event.items())
print json.dumps(event)
sys.stdout.flush()
stream.close()
def main():
rclient = redis.from_url(redis_url)
cache = rcache.Rcache(cache_url, server_id)
log_file = rclient.get("log_file")
log_pos = rclient.get("log_pos")
log_pos = int(log_pos) if log_pos else None
only_events = _trans_events(events)
only_events.append(RotateEvent)
stream = BinLogStreamReader(
connection_settings=mysql_settings,
server_id=server_id,
blocking=blocking,
only_events=only_events,
only_tables=tables,
only_schemas=schemas,
resume_stream=True, # for resuming
freeze_schema=False, # do not support alter table event for faster
log_file=log_file,
log_pos=log_pos)
row_count = 0
for binlogevent in stream:
if int(time.time()) - binlogevent.timestamp > binlog_max_latency:
logger.warn("latency[{}] too large".format(
int(time.time()) - binlogevent.timestamp))
logger.debug("catch {}".format(binlogevent.__class__.__name__))
if isinstance(binlogevent, RotateEvent): #listen log_file changed event
rclient.set("log_file", binlogevent.next_binlog)
rclient.set("log_pos", binlogevent.position)
logger.info("log_file:{}, log_position:{}".format(
binlogevent.next_binlog, binlogevent.position))
else:
row_count += 1
table = "%s.%s" % (binlogevent.schema, binlogevent.table)
vals_lst = _get_row_values(binlogevent)
if not binlogevent.primary_key:
tables_without_primary_key.get(table, None)
try:
cache.save(table, binlogevent.primary_key, vals_lst)
logger.debug("save {} {} rows to cache".format(
table, len(vals_lst)))
except rcache.SaveIgnore as err:
logger.warning(str(err))
except rcache.FullError as err:
logger.info("cache OOM occured: {}.trigger dump command".format(
str(err)))
dump_code = _trigger_dumping()
cache.save(table, binlogevent.primary_key, vals_lst)
if cache_max_rows and cache.size > cache_max_rows:
logger.info("cache size:{} >= {}, trigger dumping".format(
cache.size, cache_max_rows))
_trigger_dumping()
rclient.set("log_pos", binlogevent.packet.log_pos)
if row_count % 1000 == 0:
logger.info("save {} changed rows".format(row_count))
stream.close()
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)
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 open(tmp_file, "w") as f_tmp, self.connection as cursor:
for binlog_event in stream:
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=False, 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=False, e_start_pos=e_start_pos)
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()
return True
def _binlog_info(self):
if self._is_binlog_data():
resume_stream = True
self.logger.info(
"Resume from binlog_file: {file} binlog_pos: {pos}".format(
file=self.log_file, pos=self.log_pos))
else:
resume_stream = False
stream = BinLogStreamReader(
connection_settings=self._mysql_config,
server_id=config.mysql_server_id,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
only_tables=[config.table],
resume_stream=resume_stream,
blocking=True,
log_file=self.binlog_file,
log_pos=self.binlog_pos)
for binlog_event in stream:
self.binlog_file = stream.log_file
self.binlog_pos = stream.log_pos
for row in binlog_event.rows:
# row is a list, every element is a map.
result = {}
if isinstance(binlog_event, DeleteRowsEvent):
result['delete'] = {"delete": self._load_delete_data(row)}
elif isinstance(binlog_event, UpdateRowsEvent) or isinstance(
binlog_event, WriteRowsEvent):
result['update'] = self._load_index_update_data(row)
else:
self.logger.error("not handle event type.")
raise TypeError('not handle event type.')
yield result
stream.close()
raise IOError('mysql connection closed')
class Listener(object):
def __init__(self, connection_settings, server_id, blocking=True, resume_stream=True):
self._stream = BinLogStreamReader(
connection_settings=connection_settings,
server_id=server_id,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
blocking=blocking,
resume_stream=resume_stream
)
def __del__(self):
self._stream.close()
def accept(self, callback):
for log in self._stream:
for row in log.rows:
fields = {}
method = ''
if isinstance(log, DeleteRowsEvent):
fields = row["values"]
method = 'DELETE'
elif isinstance(log, UpdateRowsEvent):
fields = row["after_values"]
method = 'UPDATE'
elif isinstance(log, WriteRowsEvent):
method = 'INSERT'
fields = row["values"]
logger.debug(
"捕获mysql %r事件, 值为: %r",
method, json.dumps(fields)
)
callback(log.schema, log.table, method, fields)
def proc_binlog(self): stream = BinLogStreamReader( connection_settings = self.config['mysql'], server_id = self.config['slave']['server_id'], log_file = self.log_file, log_pos = self.log_pos, only_schemas = self.config['slave']['schemas'], blocking = True, resume_stream = bool(self.log_file and self.log_pos), only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent] ) for binlogevent in stream: #binlogevent.dump() self.log_file = stream.log_file self.log_pos = stream.log_pos for row in binlogevent.rows: pk = binlogevent.primary_key table = binlogevent.table schema = binlogevent.schema if isinstance(binlogevent, WriteRowsEvent): yield self.es.index_op(self._format(row['values']), doc_type=table, index=schema, id=row['values'][pk]) elif isinstance(binlogevent, UpdateRowsEvent): yield self.es.update_op(self._format(row['after_values']), doc_type=table, index=schema, id=row['after_values'][pk]) elif isinstance(binlogevent, DeleteRowsEvent): yield self.es.delete_op(doc_type=table, index=schema, id=row['values'][pk]) else: continue stream.close()
class Listener(object):
def __init__(self,
connection_settings,
server_id,
blocking=True,
resume_stream=True):
self._stream = BinLogStreamReader(
connection_settings=connection_settings,
server_id=server_id,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
blocking=blocking,
resume_stream=resume_stream)
def __del__(self):
self._stream.close()
def accept(self, callback):
for log in self._stream:
for row in log.rows:
fields = {}
method = ''
if isinstance(log, DeleteRowsEvent):
fields = row["values"]
method = 'DELETE'
elif isinstance(log, UpdateRowsEvent):
fields = row["after_values"]
method = 'UPDATE'
elif isinstance(log, WriteRowsEvent):
method = 'INSERT'
fields = row["values"]
logger.debug("捕获mysql %r事件, 值为: %r", method,
json.dumps(fields))
callback(log.schema, log.table, method, fields)
def main():
utils.drop_privileges()
if BinLogStreamReader is None:
utils.err("error: Python module `pymysqlreplication' is missing")
return 1
settings = zabbix_bridge_conf.get_settings()
# Set blocking to True if you want to block and wait for the next event at
# the end of the stream
stream = BinLogStreamReader(connection_settings=settings['mysql'],
server_id=settings['slaveid'],
only_events=[WriteRowsEvent],
resume_stream=True,
blocking=True)
db_filename = settings['sqlitedb']
dbcache = sqlite3.connect(':memory:')
cachecur = dbcache.cursor()
cachecur.execute("ATTACH DATABASE '%s' as 'dbfile'" % (db_filename,))
cachecur.execute('CREATE TABLE zabbix_cache AS SELECT * FROM dbfile.zabbix_cache')
cachecur.execute('CREATE UNIQUE INDEX uniq_zid on zabbix_cache (id)')
# tcollector.zabbix_bridge namespace for internal Zabbix bridge metrics.
log_pos = 0
key_lookup_miss = 0
sample_last_ts = int(time.time())
last_key_lookup_miss = 0
for binlogevent in stream:
if binlogevent.schema == settings['mysql']['db']:
table = binlogevent.table
log_pos = binlogevent.packet.log_pos
if table == 'history' or table == 'history_uint':
for row in binlogevent.rows:
r = row['values']
itemid = r['itemid']
cachecur.execute('SELECT id, key, host, proxy FROM zabbix_cache WHERE id=?', (itemid,))
row = cachecur.fetchone()
if (row is not None):
print("zbx.%s %d %s host=%s proxy=%s" % (row[1], r['clock'], r['value'], row[2], row[3]))
if ((int(time.time()) - sample_last_ts) > settings['internal_metric_interval']): # Sample internal metrics @ 10s intervals
sample_last_ts = int(time.time())
print("tcollector.zabbix_bridge.log_pos %d %s" % (sample_last_ts, log_pos))
print("tcollector.zabbix_bridge.key_lookup_miss %d %s" % (sample_last_ts, key_lookup_miss))
print("tcollector.zabbix_bridge.timestamp_drift %d %s" % (sample_last_ts, (sample_last_ts - r['clock'])))
if ((key_lookup_miss - last_key_lookup_miss) > settings['dbrefresh']):
print("tcollector.zabbix_bridge.key_lookup_miss_reload %d %s" % (sample_last_ts, (key_lookup_miss - last_key_lookup_miss)))
cachecur.execute('DROP TABLE zabbix_cache')
cachecur.execute('CREATE TABLE zabbix_cache AS SELECT * FROM dbfile.zabbix_cache')
cachecur.execute('CREATE UNIQUE INDEX uniq_zid on zabbix_cache (id)')
last_key_lookup_miss = key_lookup_miss
else:
# TODO: Consider https://wiki.python.org/moin/PythonDecoratorLibrary#Retry
utils.err("error: Key lookup miss for %s" % (itemid))
key_lookup_miss += 1
sys.stdout.flush()
dbcache.close()
stream.close()
def run_by_rows(self):
try:
stream = BinLogStreamReader(connection_settings=self.mysql_setting,
server_id=101213112,
only_events=[
DeleteRowsEvent, WriteRowsEvent,
UpdateRowsEvent, GtidEvent
],
resume_stream=True,
blocking=False,
log_file=self.binlog_file,
log_pos=self.start_pos,
only_schemas=self.only_schemas,
only_tables=self.only_tables)
rows = []
for binlogevent in stream:
log_pos = stream.log_pos
if log_pos >= self.end_pos:
print('binlog syncer exit...')
stream.close()
break
else:
if binlogevent.event_type == GTID_LOG_EVENT:
# 此处获取每个事务的GTID
# 不做处理
gtid = binlogevent.gtid
rows.append(gtid)
else:
# 判断当前事务的GTID的影响行数是否等于传入的影响行数
# 由于pymysql执行无法获取到当前事务的GTID以及pymysqlreplication无法获取到binlog的thread_id
# 所以无法实现精确定位，只能通过该方式实现，可能存在多备份数据的情况
for row in binlogevent.rows:
binlog = {
'database': binlogevent.schema,
'table': binlogevent.table,
'primary_key': binlogevent.primary_key
}
if self.sql_type == 'DELETE':
if isinstance(binlogevent, DeleteRowsEvent):
binlog['values'] = row["values"]
binlog['type'] = 'DELETE'
rows.append(binlog)
if self.sql_type == 'UPDATE':
if isinstance(binlogevent, UpdateRowsEvent):
binlog["before"] = row["before_values"]
binlog["after"] = row["after_values"]
binlog['type'] = 'UPDATE'
rows.append(binlog)
if self.sql_type == 'INSERT':
if isinstance(binlogevent, WriteRowsEvent):
binlog['values'] = row["values"]
binlog['type'] = 'INSERT'
rows.append(binlog)
stream.close()
result = {'status': 'success', 'data': self._filter_gtid(rows)}
except Exception as err:
result = {'status': 'fail', 'msg': str(err)}
return result
def mysql_stream(conf, mongo, queue_out):
logger = logging.getLogger(__name__)
# server_id is your slave identifier, it should be unique.
# set blocking to True if you want to block and wait for the next event at
# the end of the stream
mysql_settings = {
"host": conf['host'],
"port": conf.getint('port'),
"user": conf['user'],
"passwd": conf['password']
}
last_log = mongo.get_log_pos()
if last_log['log_file'] == 'NA':
log_file = None
log_pos = None
resume_stream = False
else:
log_file = last_log['log_file']
log_pos = int(last_log['log_pos'])
resume_stream = True
stream = BinLogStreamReader(
connection_settings=mysql_settings,
server_id=conf.getint('slaveid'),
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
blocking=True,
resume_stream=resume_stream,
log_file=log_file,
log_pos=log_pos,
only_schemas=conf['databases'].split(','))
for binlogevent in stream:
binlogevent.dump()
schema = "%s" % binlogevent.schema
table = "%s" % binlogevent.table
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
vals = row["values"]
event_type = 'delete'
elif isinstance(binlogevent, UpdateRowsEvent):
vals = dict()
vals["before"] = row["before_values"]
vals["after"] = row["after_values"]
event_type = 'update'
elif isinstance(binlogevent, WriteRowsEvent):
vals = row["values"]
event_type = 'insert'
seqnum = mongo.write_to_queue(event_type, vals, schema, table)
mongo.write_log_pos(stream.log_file, stream.log_pos)
queue_out.put({'seqnum': seqnum})
logger.debug(row)
logger.debug(stream.log_pos)
logger.debug(stream.log_file)
stream.close()
def mysql_stream(conf, mongo, queue_out):
logger = logging.getLogger(__name__)
# server_id is your slave identifier, it should be unique.
# set blocking to True if you want to block and wait for the next event at
# the end of the stream
mysql_settings = {
"host": conf['host'],
"port": conf.getint('port'),
"user": conf['user'],
"passwd": conf['password']
}
last_log = mongo.get_log_pos()
if last_log['log_file'] == 'NA':
log_file = None
log_pos = None
resume_stream = False
else:
log_file = last_log['log_file']
log_pos = int(last_log['log_pos'])
resume_stream = True
stream = BinLogStreamReader(connection_settings=mysql_settings,
server_id=conf.getint('slaveid'),
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
blocking=True,
resume_stream=resume_stream,
log_file=log_file,
log_pos=log_pos,
only_schemas=conf['databases'].split(','))
for binlogevent in stream:
binlogevent.dump()
schema = "%s" % binlogevent.schema
table = "%s" % binlogevent.table
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
vals = row["values"]
event_type = 'delete'
elif isinstance(binlogevent, UpdateRowsEvent):
vals = dict()
vals["before"] = row["before_values"]
vals["after"] = row["after_values"]
event_type = 'update'
elif isinstance(binlogevent, WriteRowsEvent):
vals = row["values"]
event_type = 'insert'
seqnum = mongo.write_to_queue(event_type, vals, schema, table)
mongo.write_log_pos(stream.log_file, stream.log_pos)
queue_out.put({'seqnum': seqnum})
logger.debug(row)
logger.debug(stream.log_pos)
logger.debug(stream.log_file)
stream.close()
def main():
global repLogFile
global repLogPosition
global repLogConfig
graphiteConfig = readGraphiteConfig()
try:
print "Start"
sock = socket.socket()
sock.connect((CARBON_SERVER, CARBON_PORT))
print 'Carbon socket opened.'
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
server_id=2, #server id needs to be unique
only_events=[WriteRowsEvent,DeleteRowsEvent,UpdateRowsEvent],
blocking=True,
log_file=repLogFile,
log_pos=repLogPosition,
resume_stream=False if repLogPosition==None else True)
print "Binlog stream opened"
for binlogevent in stream:
#put replication log file and position in variables so we can save them later
repLogFile = stream.log_file
repLogPosition = stream.log_pos
#also check for changes in graphite configuration and read again if needed
if binlogevent.schema == "weather" and binlogevent.table == "graphite":
graphiteConfig = readGraphiteConfig()
#this is the data we are interested in
if binlogevent.schema == "weather" and binlogevent.table == "data":
for row in binlogevent.rows:
#we only care about inserts
if isinstance(binlogevent, WriteRowsEvent):
vals = row["values"]
#check if the sensor is one that we have configuration for
if vals["sensorid"] in graphiteConfig:
conf = graphiteConfig[vals["sensorid"]]
value = float(vals["value"])
#do a conversion if needed
if conf["formula"]!=None and conf["formula"]!="":
value=eval(conf["formula"], {"__builtins__": {}}, {"value":value,"round":round})
#construc the message and send it to carbon
message = '%s %f %d\n' % (conf["graphitepath"], value, round((vals["time"] - _EPOCH).total_seconds()))
sock.sendall(message)
print str(vals["sensorid"]), str(vals["time"]), str(value)
print message
except KeyboardInterrupt:
#close open connections
stream.close()
sock.close()
#save replication log position
repLogConfig.set('replicationlog','file',repLogFile)
repLogConfig.set('replicationlog','position',str(repLogPosition))
with open('replogposition.ini', 'w') as f:
repLogConfig.write(f)
def main():
utils.drop_privileges()
if BinLogStreamReader is None:
utils.err("error: Python module `pymysqlreplication' is missing")
return 1
settings = zabbix_bridge_conf.get_settings()
# Set blocking to True if you want to block and wait for the next event at
# the end of the stream
stream = BinLogStreamReader(connection_settings=settings['mysql'],
server_id=settings['slaveid'],
only_events=[WriteRowsEvent],
resume_stream=True,
blocking=True)
db_filename = settings['sqlitedb']
dbcache = sqlite3.connect(':memory:')
cachecur = dbcache.cursor()
cachecur.execute("ATTACH DATABASE '%s' as 'dbfile'" % (db_filename,))
cachecur.execute('CREATE TABLE zabbix_cache AS SELECT * FROM dbfile.zabbix_cache')
cachecur.execute('CREATE UNIQUE INDEX uniq_zid on zabbix_cache (id)')
# tcollector.zabbix_bridge namespace for internal Zabbix bridge metrics.
log_pos = 0
key_lookup_miss = 0
sample_last_ts = int(time.time())
last_key_lookup_miss = 0
for binlogevent in stream:
if binlogevent.schema == settings['mysql']['db']:
table = binlogevent.table
log_pos = binlogevent.packet.log_pos
if table == 'history' or table == 'history_uint':
for row in binlogevent.rows:
r = row['values']
itemid = r['itemid']
cachecur.execute('SELECT id, key, host, proxy FROM zabbix_cache WHERE id=?', (itemid,))
row = cachecur.fetchone()
if (row is not None):
print "zbx.%s %d %s host=%s proxy=%s" % (row[1], r['clock'], r['value'], row[2], row[3])
if ((int(time.time()) - sample_last_ts) > settings['internal_metric_interval']): # Sample internal metrics @ 10s intervals
sample_last_ts = int(time.time())
print "tcollector.zabbix_bridge.log_pos %d %s" % (sample_last_ts, log_pos)
print "tcollector.zabbix_bridge.key_lookup_miss %d %s" % (sample_last_ts, key_lookup_miss)
print "tcollector.zabbix_bridge.timestamp_drift %d %s" % (sample_last_ts, (sample_last_ts - r['clock']))
if ((key_lookup_miss - last_key_lookup_miss) > settings['dbrefresh']):
print "tcollector.zabbix_bridge.key_lookup_miss_reload %d %s" % (sample_last_ts, (key_lookup_miss - last_key_lookup_miss))
cachecur.execute('DROP TABLE zabbix_cache')
cachecur.execute('CREATE TABLE zabbix_cache AS SELECT * FROM dbfile.zabbix_cache')
last_key_lookup_miss = key_lookup_miss
else:
# TODO: Consider https://wiki.python.org/moin/PythonDecoratorLibrary#Retry
utils.err("error: Key lookup miss for %s" % (itemid))
key_lookup_miss += 1
sys.stdout.flush()
dbcache.close()
stream.close()
def _binlog_loader(self):
"""
read row from binlog
"""
if self.is_binlog_sync:
resume_stream = True
logging.info(
"Resume from binlog_file: {file} binlog_pos: {pos}".format(
file=self.log_file, pos=self.log_pos))
else:
resume_stream = False
stream = BinLogStreamReader(
connection_settings=self.binlog_conf,
server_id=self.config['mysql']['server_id'],
only_events=[
DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent, RotateEvent
],
only_tables=self.tables,
resume_stream=resume_stream,
blocking=True,
log_file=self.log_file,
log_pos=self.log_pos)
for binlogevent in stream:
self.log_file = stream.log_file
self.log_pos = stream.log_pos
# RotateEvent to update binlog record when no related table changed
if isinstance(binlogevent, RotateEvent):
self._save_binlog_record()
continue
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
if binlogevent.table == self.master:
rv = {'action': 'delete', 'doc': row['values']}
else:
rv = {
'action': 'update',
'doc': {
k: row['values'][k]
if self.id_key and self.id_key == k else None
for k in row['values']
}
}
elif isinstance(binlogevent, UpdateRowsEvent):
rv = {'action': 'update', 'doc': row['after_values']}
elif isinstance(binlogevent, WriteRowsEvent):
if binlogevent.table == self.master:
rv = {'action': 'create', 'doc': row['values']}
else:
rv = {'action': 'update', 'doc': row['values']}
else:
logging.error('unknown action type in binlog')
raise TypeError('unknown action type in binlog')
yield rv
# print(rv)
stream.close()
raise IOError('mysql connection closed')
class DB_fetcher(multiprocessing.Process):
def __init__(self, share_image_queue, MYSQL_SETTINGS ,target_schema = "capstone",
target_table = "crack_detection_result",
start_time = datetime.datetime(1970,1,2,0,1,0)): # 1970,1,1 is almost 0 second
multiprocessing.Process.__init__(self)
self.share_image_queue = share_image_queue
self.target_schema = target_schema
self.target_table = target_table
self.skip_to_timestamp = convert_to_second_int(start_time)
print(" >>>>> start time is {}".format(self.skip_to_timestamp))
def run(self):
while True:
self.stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
only_events=[WriteRowsEvent], # [DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
server_id=3,
slave_heartbeat=1
,skip_to_timestamp = self.skip_to_timestamp)
# self.idx = 0
# self.show_slave_status()
for binlogevent in self.stream:
prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
if prefix == self.target_schema + ":" + self.target_table + ":":
# print(binlogevent.rows)
for new_update_row in binlogevent.rows:
logging.info(" >>> find new row {} time: {}".format(new_update_row, binlogevent.timestamp))
# format for a row: {'values':
# {'video_id': 1, 'frame_id': 4, 'insert_time': datetime.datetime(2008, 6, 19, 0, 0),
# 'frame_loc': 'RANDOM_LL', 'detect_flag': boolean, 'result_loc': None}}
# if new_update_row["values"]["detect_flag"] == 0 and \
# (new_update_row["values"]['insert_time'] is not None or new_update_row["values"]['insert_time'] != '' )\
# and ((new_update_row["values"]['frame_id'] > 170 and new_update_row["values"]['frame_id' ] < 200)
# or (new_update_row["values"]['frame_id'] > 1010 and new_update_row["values"]['frame_id' ] < 1035)
# or (new_update_row["values"]['frame_id'] > 1155 and new_update_row["values"]['frame_id' ] < 1177)
# or (new_update_row["values"]['frame_id'] > 1312 and new_update_row["values"]['frame_id' ] < 1316)
# or (new_update_row["values"]['frame_id'] > 2127 and new_update_row["values"]['frame_id' ] < 2150)):
if new_update_row["values"]["detect_flag"] == 0 and \
(new_update_row["values"]['insert_time'] is not None or new_update_row["values"]['insert_time'] != ''
):
#logging.info(" >>> for this row, the flag is {}".format(new_update_row['detect_flag']))
try:
self.share_image_queue.put(new_update_row["values"],True,1)
logging.info(" >>> adding 1 image to queue {}".format(new_update_row))
except Exception as e:
logging.error(e)
self.skip_to_timestamp = convert_to_second_int(datetime.datetime.now())
time.sleep(2)
def close_stream(self):
self.stream.close()
def binlog_process(args):
file = None
stream = None
sql_list = []
try:
file = open(args.out_file, "w+")
stream = BinLogStreamReader(connection_settings=connection_settings,
log_file=args.log_file,
log_pos=args.start_pos,
resume_stream=True,
only_schemas=args.databases,
only_tables=args.tables,
server_id=args.server_id)
for binlogevent in stream:
if (args.log_file != stream.log_file):
break
if (args.end_pos != None):
if (binlogevent.packet.log_pos > args.end_pos):
break
if (args.start_datetime != None):
if (datetime.datetime.fromtimestamp(binlogevent.timestamp) <
args.start_datetime):
continue
if (args.end_datetime != None):
if (datetime.datetime.fromtimestamp(binlogevent.timestamp) >
args.end_datetime):
break
if (isinstance(binlogevent, WriteRowsEvent)):
for row in binlogevent.rows:
if (args.flashback):
sql_list.append(delete_to_sql(row, binlogevent) + "\n")
else:
sql_list.append(insert_to_sql(row, binlogevent) + "\n")
elif (isinstance(binlogevent, DeleteRowsEvent)):
for row in binlogevent.rows:
if (args.flashback):
sql_list.append(insert_to_sql(row, binlogevent) + "\n")
else:
sql_list.append(delete_to_sql(row, binlogevent) + "\n")
elif (isinstance(binlogevent, UpdateRowsEvent)):
for row in binlogevent.rows:
sql_list.append(
update_to_sql(row, binlogevent, args.flashback) + "\n")
file.writelines(sql_list)
finally:
if (stream != None):
stream.close()
if (file != None):
file.close()
def main():
# server_id is your slave identifier, it should be unique.
# set blocking to True if you want to block and wait for the next event at
# the end of the stream
stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS,
server_id=3,
blocking=True)
for binlogevent in stream:
binlogevent.dump()
stream.close()
def main():
# server_id is your slave identifier, it should be unique.
# set blocking to True if you want to block and wait for the next event at
# the end of the stream
stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS,
server_id=3,
blocking=True)
for binlogevent in stream:
binlogevent.dump()
stream.close()
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 doRep(logPosConObj, MYSQL_SETTINGS):
key = MYSQL_SETTINGS["host"] + ":" + str(MYSQL_SETTINGS["port"])
try:
stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS,
server_id=100,
only_events=[
DeleteRowsEvent, WriteRowsEvent,
UpdateRowsEvent, RotateEvent,
QueryEvent
],
blocking=True,
log_file=logPosConObj["log_file"],
log_pos=logPosConObj["log_pos"])
for binlogevent in stream:
#prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
if isinstance(binlogevent, RotateEvent):
#pprint (vars(binlogevent.packet))
logPosConObj["log_file"] = binlogevent.packet.event.next_binlog
logPosConObj["log_pos"] = binlogevent.packet.log_pos
#logPosObject.setData(logPosConObj)
continue
if isinstance(binlogevent, QueryEvent):
#pprint (vars(binlogevent.packet))
sendMsg(key, binlogevent.query, binlogevent.timestamp)
#logPosObject.setData(logPosConObj)
continue
for row in binlogevent.rows:
#dbtable = binlogevent.schema+"_"+binlogevent.table
if isinstance(binlogevent, DeleteRowsEvent):
#print 'DeleteRowsEvent'
sendMsg(key, row.get("values", object),
binlogevent.timestamp)
#func(row.get("values",object))
elif isinstance(binlogevent, UpdateRowsEvent):
#print 'UpdateRowsEvent'
#print row
sendMsg(key, row, binlogevent.timestamp)
#func(row.get("after_values",object))
elif isinstance(binlogevent, WriteRowsEvent):
#print 'WriteRowsEvent'
#print row
sendMsg(key, row.get("values", object),
binlogevent.timestamp)
#func(row.get("values",object))
#logPosConObj["log_pos"]=binlogevent.packet.log_pos
#logPosObject.setData(logPosConObj)
stream.close()
except BaseException, e:
print(e)
return
def main():
configuration = {'log_file': 'mysql-bin-changelog.004416', 'log_pos': 0}
for config in open('configuration').read().split('\n'):
config_k_v = config.split('=')
configuration[config_k_v[0]] = config_k_v[1]
MYSQL_SETTINGS = {
"host": configuration['hostname'],
"port": 3306,
"user": configuration['username'],
"password": configuration['password'],
"database": configuration['database']
}
print configuration
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
server_id=1234,
log_file=configuration['log_file'],
log_pos=configuration['log_pos'],
blocking=True,
only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent])
s3 = boto3.resource('s3')
buffer_events = []
buffer_max_size = 1
cur_log_file = ''
cur_log_pos = ''
for binlog_event in stream:
cur_log_file = stream.log_file
cur_log_pos = stream.log_pos
print "Processing " + cur_log_file + " at " + str(cur_log_pos)
buffer_events.append(binlog_event)
if len(buffer_events) == buffer_max_size:
upload_to_s3(
s3, s3_file_name(cur_log_file, cur_log_pos),
map(lambda binlog_event: binlog_event_to_json(binlog_event),
buffer_events))
buffer_events = []
stream.close()
if len(buffer) > 0:
upload_to_s3(
s3, s3_file_name(cur_log_file, cur_log_pos),
map(lambda binlog_event: binlog_event_to_json(binlog_event, table),
buffer_events))
def main():
# server_id is your slave identifier, it should be unique.
# set blocking to True if you want to block and wait for the next event at
# the end of the stream
stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS,
server_id=3,
log_file="mysql-bin.000002",
blocking=True,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
for binlogevent in stream:
binlogevent.dump()
stream.close()
def consume_events():
stream = BinLogStreamReader(connection_settings=database,
server_id=3,
resume_stream=False,
blocking=True,
only_events = [UpdateRowsEvent],
only_tables = ['test'] )
start = time.clock()
i = 0.0
for binlogevent in stream:
i += 1.0
if i % 1000 == 0:
print("%d event by seconds (%d total)" % (i / (time.clock() - start), i))
stream.close()
def binlog_process(args):
file = None
stream = None
sql_list = []
try:
file = open(args.out_file, "w+")
stream = BinLogStreamReader(connection_settings=connection_settings, log_file=args.log_file, log_pos=args.start_pos,
resume_stream=True, only_schemas=args.databases, only_tables=args.tables, server_id=args.server_id)
for binlogevent in stream:
if(args.log_file != stream.log_file):
break
if(args.end_pos != None):
if(binlogevent.packet.log_pos > args.end_pos):
break
if(args.start_datetime != None):
if(datetime.datetime.fromtimestamp(binlogevent.timestamp) < args.start_datetime):
continue
if(args.end_datetime != None):
if(datetime.datetime.fromtimestamp(binlogevent.timestamp) > args.end_datetime):
break
if (isinstance(binlogevent, WriteRowsEvent)):
for row in binlogevent.rows:
if(args.flashback):
sql_list.append(delete_to_sql(row, binlogevent) + "\n")
else:
sql_list.append(insert_to_sql(row, binlogevent) + "\n")
elif (isinstance(binlogevent, DeleteRowsEvent)):
for row in binlogevent.rows:
if(args.flashback):
sql_list.append(insert_to_sql(row, binlogevent) + "\n")
else:
sql_list.append(delete_to_sql(row, binlogevent) + "\n")
elif (isinstance(binlogevent, UpdateRowsEvent)):
for row in binlogevent.rows:
sql_list.append(update_to_sql(row, binlogevent, args.flashback) + "\n")
file.writelines(sql_list)
finally:
if(stream != None):
stream.close()
if(file != None):
file.close()
def _binlog_loader(self):
"""
read row from binlog
"""
if self.is_binlog_sync:
resume_stream = True
logging.info("Resume from binlog_file: {file} binlog_pos: {pos}".format(file=self.log_file,
pos=self.log_pos))
else:
resume_stream = False
stream = BinLogStreamReader(connection_settings=self.binlog_conf,
server_id=self.config['mysql']['server_id'],
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent],
only_tables=[self.config['mysql']['table']],
resume_stream=resume_stream,
blocking=True,
log_file=self.log_file,
log_pos=self.log_pos)
for binlogevent in stream:
self.log_file = stream.log_file
self.log_pos = stream.log_pos
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
rv = {
'action': 'delete',
'doc': row['values']
}
elif isinstance(binlogevent, UpdateRowsEvent):
rv = {
'action': 'update',
'doc': row['after_values']
}
elif isinstance(binlogevent, WriteRowsEvent):
rv = {
'action': 'index',
'doc': row['values']
}
else:
logging.error('unknown action type in binlog')
raise TypeError('unknown action type in binlog')
yield rv
# print(rv)
stream.close()
raise IOError('mysql connection closed')
def doRep(logPosConObj,MYSQL_SETTINGS):
key = MYSQL_SETTINGS["host"]+":"+str(MYSQL_SETTINGS["port"])
try:
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,server_id=100,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent, RotateEvent,QueryEvent],blocking=True,
log_file=logPosConObj["log_file"],log_pos=logPosConObj["log_pos"])
for binlogevent in stream:
#prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
if isinstance(binlogevent, RotateEvent):
#pprint (vars(binlogevent.packet))
logPosConObj["log_file"]=binlogevent.packet.event.next_binlog
logPosConObj["log_pos"]=binlogevent.packet.log_pos
#logPosObject.setData(logPosConObj)
continue
if isinstance(binlogevent, QueryEvent):
#pprint (vars(binlogevent.packet))
sendMsg(key,binlogevent.query,binlogevent.timestamp)
#logPosObject.setData(logPosConObj)
continue
for row in binlogevent.rows:
#dbtable = binlogevent.schema+"_"+binlogevent.table
if isinstance(binlogevent, DeleteRowsEvent):
#print 'DeleteRowsEvent'
sendMsg(key,row.get("values",object),binlogevent.timestamp)
#func(row.get("values",object))
elif isinstance(binlogevent, UpdateRowsEvent):
#print 'UpdateRowsEvent'
#print row
sendMsg(key,row,binlogevent.timestamp)
#func(row.get("after_values",object))
elif isinstance(binlogevent, WriteRowsEvent):
#print 'WriteRowsEvent'
#print row
sendMsg(key,row.get("values",object),binlogevent.timestamp)
#func(row.get("values",object))
#logPosConObj["log_pos"]=binlogevent.packet.log_pos
#logPosObject.setData(logPosConObj)
stream.close()
except BaseException,e :
print(e)
return
def main():
# server_id is your slave identifier, it should be unique.
# set blocking to True if you want to block and wait for the next event at
# the end of the stream
stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS,
server_id=3,
blocking=True)
for binlogevent in stream:
#print binlogevent
#if isinstance(binlogevent, QueryEvent):
# print binlogevent.query
if isinstance(binlogevent, WriteRowsEvent):
for rows in binlogevent.rows:
print rows
#print binlogevent.query
#binlogevent.dump()
stream.close()
def main():
print 'Replicator Started'
io = Emitter(dict(
host=settings.SOCKETIO_SETTINGS['host'],
port=settings.SOCKETIO_SETTINGS['port']
)).Of(settings.SOCKETIO_SETTINGS['namespace'])
stream = BinLogStreamReader(connection_settings=settings.MYSQL_SETTINGS,
server_id=3,
blocking=True,
resume_stream=True,
only_events=[UpdateRowsEvent],
only_tables=[settings.DB_SETTINGS['source_table']])
for binlogevent in stream:
for row in binlogevent.rows:
vals = row["after_values"]
print 'Updated rows for ' + json.dumps(vals)
io.Emit('update', json.dumps(vals))
stream.close()
def main():
## 几个初始化工作
parse_conf()
set_mysql_setting()
parse_binlog_conf()
stream = BinLogStreamReader(
connection_settings= MYSQL_SETTINGS,
server_id = serverid,
resume_stream = True,
log_file = binlog_info["binlog"],
log_pos = int(binlog_info["position"]),
only_events = [DeleteRowsEvent,
WriteRowsEvent,
UpdateRowsEvent,
RotateEvent,
XidEvent],
blocking = True)
currentbinlogfilename = ""
currentbinlogposition = ""
for binlogevent in stream:
if isinstance(binlogevent, RotateEvent):
currentbinlogfilename = binlogevent.next_binlog
currentbinlogposition = binlogevent.position
print currentbinlogfilename, currentbinlogposition
#sync_binlog_to_file(currentbinlogfilename,currentbinlogposition)
elif isinstance(binlogevent, XidEvent):
currentbinlogposition = binlogevent.packet.log_pos
print currentbinlogfilename, currentbinlogposition
#sync_binlog_to_file(currentbinlogfilename,currentbinlogposition)
elif (binlogevent.schema == sourcedb_conf["db_dbname"] and
binlogevent.table == sourcedb_conf["db_table"]):
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
syncDelete(row)
elif isinstance(binlogevent, UpdateRowsEvent):
syncUpdate(row)
elif isinstance(binlogevent, WriteRowsEvent):
syncInsert(row)
stream.close()
def main():
utils.drop_privileges()
if BinLogStreamReader is None:
utils.err("error: Python module `pymysqlreplication' is missing")
return 1
if pymysql is None:
utils.err("error: Python module `pymysql' is missing")
return 1
settings = zabbix_bridge_conf.get_settings()
# Set blocking to True if you want to block and wait for the next event at
# the end of the stream
stream = BinLogStreamReader(connection_settings=settings['mysql'],
server_id=settings['slaveid'],
only_events=[WriteRowsEvent],
resume_stream=True,
blocking=True)
hostmap = gethostmap(settings) # Prime initial hostmap
for binlogevent in stream:
if binlogevent.schema == settings['mysql']['db']:
table = binlogevent.table
log_pos = binlogevent.packet.log_pos
if table == 'history' or table == 'history_uint':
for row in binlogevent.rows:
r = row['values']
itemid = r['itemid']
try:
hm = hostmap[itemid]
print "zbx.%s %d %s host=%s proxy=%s" % (hm['key'], r['clock'], r['value'], hm['host'], hm['proxy'])
except KeyError:
# TODO: Consider https://wiki.python.org/moin/PythonDecoratorLibrary#Retry
hostmap = gethostmap(settings)
utils.err("error: Key lookup miss for %s" % (itemid))
sys.stdout.flush()
# if n seconds old, reload
# settings['gethostmap_interval']
stream.close()
def main():
r = redis.Redis()
stream = BinLogStreamReader(
connection_settings=MYSQL_SETTINGS,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
for binlogevent in stream:
prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
vals = row["values"]
r.delete(prefix + str(vals["id"]))
elif isinstance(binlogevent, UpdateRowsEvent):
vals = row["after_values"]
r.hmset(prefix + str(vals["id"]), vals)
elif isinstance(binlogevent, WriteRowsEvent):
vals = row["values"]
r.hmset(prefix + str(vals["id"]), vals)
stream.close()
class TestGtidBinLogStreamReader(base.PyMySQLReplicationTestCase):
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
self.stream.close()
self.stream = BinLogStreamReader(self.database, server_id=1024, blocking=True, auto_position=gtid)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# Insert first event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
firstevent = self.stream.fetchone()
self.assertIsInstance(firstevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# Insert second event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(2, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
secondevent = self.stream.fetchone()
self.assertIsInstance(secondevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertEqual(secondevent.gno, firstevent.gno + 1)
def test_position_gtid(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
query = "CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
self.stream.close()
self.stream = BinLogStreamReader(self.database, server_id=1024, blocking=True, auto_position=gtid)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), GtidEvent)
event = self.stream.fetchone()
self.assertEqual(
event.query, "CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
)
class MysqlEventStream(object):
def __init__(self, mysql_settings, server_id, dump_file_path,
log_file=None, log_pos=None,
gtid_set=None, table_filters=None):
# TODO: gtid mode support
# https://dev.mysql.com/doc/refman/en/replication-gtids.html
# TODO: wild chars in table_filters
self.mysql_settings = mysql_settings
self.server_id = server_id
self.log_file = log_file
self.log_pos = log_pos
self.dump_file_path = dump_file_path
if table_filters:
self.table_filters = {schema:frozenset(tables) for schema, tables in table_filters.items()}
only_schemas = [schema for schema in table_filters]
else:
self.table_filters = None
only_schemas = None
self.binlog_stream_reader = BinLogStreamReader(
connection_settings=self.mysql_settings,
server_id=self.server_id,
log_file=self.log_file,
log_pos=self.log_pos,
resume_stream=True,
blocking=False,
freeze_schema=True,
only_schemas=only_schemas,
)
def __iter__(self):
for ev in self.binlog_stream_reader:
yield (
self.binlog_stream_reader.log_file,
self.binlog_stream_reader.log_pos,
ev,
)
def process_commit(self, new_log_file, new_log_pos):
self.log_file = new_log_file
self.log_pos = new_log_pos
def close(self):
"""close connection to MySQL
"""
self.binlog_stream_reader.close()
def dumpf(self):
"""dump currently read binlog file and offset to a file
"""
data = {
'server_id': self.server_id,
'log_file':self.log_file,
'log_pos':self.log_pos,
}
data_str = six.u(json.dumps(data))
with io.open(self.dump_file_path, 'w', encoding='utf-8') as f:
f.write(data_str)
@classmethod
def loadf(cls, path, mysql_settings, table_filters=None):
"""construct from dump file
"""
with io.open(path, 'r', encoding='utf-8') as f:
data_str = f.read()
data = json.loads(data_str)
ev_stream = cls(
mysql_settings=mysql_settings,
server_id=data['server_id'],
log_file=data['log_file'],
log_pos=data['log_pos'],
dump_file_path=path,
table_filters=table_filters,
)
return ev_stream
#print("Update+++++++++++++++++")
#print(binlogevent.schema, binlogevent.table)
for row in binlogevent.rows:
#print(row)
str123 = "update `{0}`.`{1}` set {2} where {3};".format(binlogevent.schema, binlogevent.table, sql_format(row['after_values'], ", "), sql_format(row['before_values'], " AND "))
#list.append(str(str123))
sql_list.append(str123+"\n")
elif (isinstance(binlogevent, DeleteRowsEvent)):
#delete from db1.t1 where c1 = 'bbb';
#print("Delete>>>>>>>>>>>>>>>>>>>>")
#print(binlogevent.schema, binlogevent.table)
for row in binlogevent.rows:
#print(row)
str123 = "delete from `{0}`.`{1}` where {2};".format(binlogevent.schema, binlogevent.table, sql_format(row['values'], " AND "))
#list.append(str(str123))
sql_list.append(str123+"\n")
'''
stream.close()
bb = time.time()
print("binlog ok", bb - aa)
#my_file.writelines(sql_list)
#my_file.close()
bb = time.time()
print(bb - aa)
def main():
r = redis.Redis(host = config.REDIS_SETTINGS["host"],port = config.REDIS_SETTINGS["port"])
stream = BinLogStreamReader(
connection_settings=config.MYSQL_SETTINGS,
server_id=4,
blocking=True,
#log_file="mysql-bin.000028",
#log_pos=706478611,
resume_stream=True,
only_tables=["ax_newdata"],
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
now = int(math.floor(time.time()))
for binlogevent in stream:
prefix = "%s:%s:" % (binlogevent.schema, binlogevent.table)
statPrefix = "%s:%s:" % (binlogevent.schema,"statistics")
for row in binlogevent.rows:
#print str(binlogevent.table)
if binlogevent.table.find(config.DB_SETTINGS["newdata"]) == -1:
#print "newdata"
continue
elif isinstance(binlogevent, UpdateRowsEvent) and (row["after_values"]["ctime"] < now):
#if row["after_values"]["symbol"].find("EURGBP") != -1:
# print "olddata",row["after_values"]["volume"],row["after_values"]["high"],row["after_values"]["symbol"],now,row["after_values"]["ctime"]
continue#filter old data
elif isinstance(binlogevent, UpdateRowsEvent) and (binlogevent.table.find(config.DB_SETTINGS["newdata"]) != -1):
vals = row["after_values"]
#print vals
r.set("lastDbUpdateTime",int(math.floor(time.time())))
result = {}
result["price"]=vals["bid"]
result["timestamp"]=vals["ctime"]
for k,v in vals.items():
if isinstance(v,basestring):
result[k.encode("utf-8")] = v.encode("utf-8")
else:
result[k.encode("utf-8")] = v
r.set("R_" + vals["symbol"],str(result).replace("'","\""))
if config.STAT_SETTINGS["enabled"] == "true":
vals["date"] = datetime.now()
vals["event"] = time.time()
r.hmset(prefix + vals["symbol"], vals)
r.incr(statPrefix + "ALL")
eventTime = int(math.floor(vals["event"]))
cTime = vals["ctime"]
diffTime = eventTime - cTime
#print eventTime,cTime,diffTime
if diffTime <= 1:
r.incr(statPrefix + "1s")
elif diffTime <= 3:
r.incr(statPrefix + "3s")
elif diffTime <= 5:
r.incr(statPrefix + "5s")
elif diffTime <= 10:
r.incr(statPrefix + "10s")
elif diffTime > 10:
r.incr(statPrefix + "B10")
print datetime.now(),time.time(),vals["ctime"],vals["symbol"],vals["bid"],vals["ask"],vals["high"],vals["low"]
stream.close()
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_connection_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(connection_settings = self.database, blocking = True)
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')";
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_events(self):
self.stream.close()
self.stream = BinLogStreamReader(connection_settings = self.database, only_events = [QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the Create Table
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, WRITE_ROWS_EVENT)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, DELETE_ROWS_EVENT)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def main():
signal.signal(signal.SIGINT, signal_handler)
global patternGeneralCollector
global opsGeneralCollector
global totalOps # Reset on each interval
global countOps
global countPatterns
global patternC
global binLogEventSizes
global binLogReadBytes
patternGeneralCollector = {}
opsGeneralCollector = {}
queryStats = {}
rotateStats = {}
loadQueryStats = {}
totalOps = 0 # Reset on each interval
countOps = 0
countPatterns = 0
prevTimeFlag = 0
binLogEventSizes = 0
binLogReadBytes = 0
queryStats['count'] = 0
rotateStats['count'] = 0
rotateStats['size'] = 0
loadQueryStats['count'] = 0
firstRun = 1
# Great resource http://blog.mattheworiordan.com/post/13174566389/url-regular-expression-for-links-with-or-without
patternC = re.compile(r'[A-Za-z]{3,9}:\/\/(?:[A-Za-z0-9\.\-]+)')
# server_id is your slave identifier, it should be unique.
# set blocking to True if you want to block and wait for the next event at
# the end of the stream
#timeCheck = datetime.today() # If event is bigger, sets it to now() and prints stats.
timeCheck = time.time()
#print "Starting at %s " % (timeCheck)
# We always want to use MASTER_AUTO_POSITION = 1
# Only events help us to keep the stream shorter as we can.
stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS,
server_id=OPTIONS["serverid"],
log_file=OPTIONS["log_file"],
log_pos=OPTIONS["log_pos"],
resume_stream= True, # If no log_pos, set to False
#auto_position=1,
blocking=True,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent,QueryEvent, RotateEvent,BeginLoadQueryEvent, ExecuteLoadQueryEvent])
for binlogevent in stream:
patternCollector = None
occurrence = None
#if prevTimeFlag == 0:
# txTimeCheck = binlogevent.timestamp
# prevTimeFlag = 1
if firstRun == 1:
print "Binlog: %s Position: %s First Event TS: %s " % (OPTIONS["log_file"],OPTIONS["log_pos"], datetime.fromtimestamp(timeCheck))
firstRun = 0
if isinstance(binlogevent,QueryEvent):
queryStats['count'] += 1
continue
elif isinstance(binlogevent,RotateEvent):
rotateStats['count'] += 1
rotateStats['size'] += binlogevent.event_size
continue
elif isinstance(binlogevent,ExecuteLoadQueryEvent) or isinstance(binlogevent,BeginLoadQueryEvent):
loadQueryStats['count'] +=1
continue
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
vals = row["values"]
eventType = "delete"
elif isinstance(binlogevent, UpdateRowsEvent):
vals = row["after_values"]
eventType = "update"
elif isinstance(binlogevent, WriteRowsEvent):
vals = row["values"]
eventType = "insert"
prefix = "%s.%s" %( binlogevent.schema, binlogevent.table)
if (eventType, prefix) in opsGeneralCollector:
opsGeneralCollector[eventType,prefix] += 1
else:
opsGeneralCollector[eventType,prefix] = 1
occurrence = re.search(patternC, str(vals) )
if occurrence:
occurrence = re.sub('[A-Za-z]{3,9}:\/\/','',occurrence.group())
if (eventType,prefix,occurrence) in patternGeneralCollector.keys():
patternGeneralCollector[eventType,prefix,occurrence] += 1
else:
patternGeneralCollector[eventType,prefix,occurrence] = 1
totalOps += 1
binLogEventSizes += binlogevent.event_size
binLogReadBytes += binlogevent.packet.read_bytes
# If interval has been committed, print stats and reset everything
if (timeCheck + OPTIONS["interval"]) < time.time():
#or txTimeCheck + OPTIONS["interval"] > time.time():
printStats(opsGeneralCollector, \
patternGeneralCollector, \
binLogEventSizes, \
binLogReadBytes, \
queryStats,\
rotateStats,\
loadQueryStats)
# Reset everything to release memory
totalOps = 0
patternGeneralCollector = {}
opsGeneralCollector = {}
queryStats['count'] = 0
rotateStats['count'] = 0
rotateStats['size'] = 0
loadQueryStats['count'] = 0
timeCheck = time.time()
prevTimeFlag = 0
binLogEventSizes = 0
binLogReadBytes = 0
firstRun = 0
stream.close()
def _binlog_loader(self):
"""
read row from binlog
"""
if self.is_binlog_sync:
resume_stream = True
logging.info("Resume from binlog_file: {file} binlog_pos: {pos}".format(file=self.log_file,
pos=self.log_pos))
else:
resume_stream = False
stream = BinLogStreamReader(connection_settings=self.binlog_conf,
server_id=self.config['mysql']['server_id'],
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent, RotateEvent],
only_tables=self.tables,
resume_stream=resume_stream,
blocking=True,
log_file=self.log_file,
log_pos=self.log_pos)
for binlogevent in stream:
self.log_file = stream.log_file
self.log_pos = stream.log_pos
# RotateEvent to update binlog record when no related table changed
if isinstance(binlogevent, RotateEvent):
self._save_binlog_record()
continue
for row in binlogevent.rows:
if isinstance(binlogevent, DeleteRowsEvent):
if binlogevent.table == self.master:
rv = {
'action': 'delete',
'doc': row['values']
}
else:
rv = {
'action': 'update',
'doc': {k: row['values'][k] if self.id_key and self.id_key == k else None for k in row['values']}
}
elif isinstance(binlogevent, UpdateRowsEvent):
rv = {
'action': 'update',
'doc': row['after_values']
}
elif isinstance(binlogevent, WriteRowsEvent):
if binlogevent.table == self.master:
rv = {
'action': 'create',
'doc': row['values']
}
else:
rv = {
'action': 'update',
'doc': row['values']
}
else:
logging.error('unknown action type in binlog')
raise TypeError('unknown action type in binlog')
yield rv
# print(rv)
stream.close()
raise IOError('mysql connection closed')
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_read_query_event_with_unicode(self):
query = u"CREATE TABLE `testÈ` (id INT NOT NULL AUTO_INCREMENT, dataÈ VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_reading_rotate_event(self):
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.stream.close()
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
# Rotate event
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
def test_connection_stream_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, blocking=True, ignored_events=self.ignoredEvents()
)
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')"
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_events(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database, server_id=1024, only_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, "data")
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_log_pos(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
for i in range(6):
self.stream.fetchone()
# record position after insert
log_file, log_pos = self.stream.log_file, self.stream.log_pos
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
# resume stream from previous position
if self.stream is not None:
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=True,
log_file=log_file,
log_pos=log_pos,
ignored_events=self.ignoredEvents(),
)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), UpdateRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
def test_log_pos_handles_disconnects(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=False,
only_events=[FormatDescriptionEvent, QueryEvent, TableMapEvent, WriteRowsEvent, XidEvent],
)
query = "CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR (50) NOT NULL)"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertGreater(self.stream.log_pos, 0)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertGreater(self.stream.log_pos, 0)
class TestMultipleRowBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_insert_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.resetBinLog()
query = "INSERT INTO test (data) VALUES('Hello'),('World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello")
self.assertEqual(event.rows[1]["values"]["id"], 2)
self.assertEqual(event.rows[1]["values"]["data"], "World")
def test_update_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "INSERT INTO test (data) VALUES('World')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'Toto'"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "Toto")
self.assertEqual(event.rows[1]["before_values"]["id"], 2)
self.assertEqual(event.rows[1]["before_values"]["data"], "World")
self.assertEqual(event.rows[1]["after_values"]["id"], 2)
self.assertEqual(event.rows[1]["after_values"]["data"], "Toto")
def test_delete_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "INSERT INTO test (data) VALUES('World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello")
self.assertEqual(event.rows[1]["values"]["id"], 2)
self.assertEqual(event.rows[1]["values"]["data"], "World")
def test_drop_table(self):
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("DROP TABLE test")
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the Create Table
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual([], event.rows)
def test_drop_column(self):
self.stream.close()
self.execute("CREATE TABLE test_drop_column (id INTEGER(11), data VARCHAR(50))")
self.execute("INSERT INTO test_drop_column VALUES (1, 'A value')")
self.execute("COMMIT")
self.execute("ALTER TABLE test_drop_column DROP COLUMN data")
self.execute("INSERT INTO test_drop_column VALUES (2)")
self.execute("COMMIT")
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=(WriteRowsEvent,),
)
try:
self.stream.fetchone() # insert with two values
self.stream.fetchone() # insert with one value
except Exception as e:
self.fail("raised unexpected exception: {exception}".format(exception=e))
finally:
self.resetBinLog()
@unittest.expectedFailure
def test_alter_column(self):
self.stream.close()
self.execute("CREATE TABLE test_alter_column (id INTEGER(11), data VARCHAR(50))")
self.execute("INSERT INTO test_alter_column VALUES (1, 'A value')")
self.execute("COMMIT")
# this is a problem only when column is added in position other than at the end
self.execute("ALTER TABLE test_alter_column ADD COLUMN another_data VARCHAR(50) AFTER id")
self.execute("INSERT INTO test_alter_column VALUES (2, 'Another value', 'A value')")
self.execute("COMMIT")
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=(WriteRowsEvent,),
)
event = self.stream.fetchone() # insert with two values
# both of these asserts fail because of issue underlying proble described in issue #118
# because it got table schema info after the alter table, it wrongly assumes the second
# column of the first insert is 'another_data'
# ER: {'id': 1, 'data': 'A value'}
# AR: {'id': 1, 'another_data': 'A value'}
self.assertIn("data", event.rows[0]["values"])
self.assertNot("another_data", event.rows[0]["values"])
self.assertEqual(event.rows[0]["values"]["data"], 'A value')
self.stream.fetchone() # insert with three values
class BinLogDumper(object):
def __init__(self, master_connect_setting,
server_id, log_file, log_pos,
outputer,
only_tables=None,
used_update_sql_tables=[]):
'''
used_update_sql_tables: tables which used `UPDATE` SQL instead of `REPLACE INTO` SQL.
'''
self.server_id = server_id
self._master_connect_setting = master_connect_setting
self._output = outputer
assert callable(getattr(outputer, 'write')
), 'outputer must hash interface `write(event)`'
self._start_file = log_file
self._start_pos = log_pos
self.reading_file = self._start_pos
self.reading_pos = self._start_file
self.reading_timestamp = 0
self.reading_event = 0
self._master = sqlpool.SqlPool(**master_connect_setting)
self._master_log_file = None
self._master_log_pos = None
self._stop = False
self._running = False
self._tables_desc = {}
self._used_update_sql_tables = dict(
[(t, True) for t in used_update_sql_tables])
m = master_connect_setting
hostname, user, password, port = socket.gethostname(), m['user'], m[
'passwd'], m['port']
self._stream = BinLogStreamReader(connection_settings=master_connect_setting,
server_id=self.server_id,
log_pos=self._start_pos, log_file=self._start_file,
blocking=True, resume_stream=True,
freeze_schema=True,
only_tables=only_tables,
report_slave=(
hostname, user, password, port),
slave_uuid=uuid.uuid4(),
only_events=[QueryEvent,
WriteRowsEvent,
UpdateRowsEvent,
DeleteRowsEvent,
RotateEvent])
def status(self):
self._master_log_file, self._master_log_pos = self.show_master_status()
return (self._master_log_file, self._master_log_pos,
self.reading_file, self.reading_pos, self.reading_timestamp, self.reading_event)
def set_timer_status(self, second):
if self._stop:
return
threading.Timer(second, self.set_timer_status, (second,)).start()
mfile, mpos, rfile, rpos, timestamp, cnt = self.status()
logging.warn("Master.file[%s]:pos[%d]; Read.file[%s]:pos[%s];Seconds_Behind[%d],event.Cnt=[%d]",
mfile, mpos, rfile, rpos, time.time()-timestamp, cnt)
def stop(self):
self._stop = True
logging.warn('BinLogDumper.Stop() was called. stop binlog dump.')
if self._stream:
logging.warn("Close binlog stream.")
self._stream.close()
wait = 3
while wait and self._running:
logging.warn("wait running exit.")
time.sleep(1)
wait -= 1
def run(self):
try:
self._running = True
self._loop()
logging.warn("end loop in run()")
except AttributeError, a:
logging.info("AttributeError: %s", a.args)
except Exception, e:
if self._stop:
logging.warn("Exception:%s", e)
else:
self.stop()
logging.error("Exception:%s", traceback.format_exc())
raise e
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_allowed_event_list(self):
self.assertEqual(len(self.stream._allowed_event_list(None, None, False)), 13)
self.assertEqual(len(self.stream._allowed_event_list(None, None, True)), 12)
self.assertEqual(len(self.stream._allowed_event_list(None, [RotateEvent], False)), 12)
self.assertEqual(len(self.stream._allowed_event_list([RotateEvent], None, False)), 1)
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_read_query_event_with_unicode(self):
query = u"CREATE TABLE `testÈ` (id INT NOT NULL AUTO_INCREMENT, dataÈ VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_reading_rotate_event(self):
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.stream.close()
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
# Rotate event
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
""" `test_load_query_event` needs statement-based binlog
def test_load_query_event(self):
# prepare csv
with open("/tmp/test_load_query.csv", "w") as fp:
fp.write("1,aaa\n2,bbb\n3,ccc\n4,ddd\n")
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "LOAD DATA INFILE '/tmp/test_load_query.csv' INTO TABLE test \
FIELDS TERMINATED BY ',' \
ENCLOSED BY '\"' \
LINES TERMINATED BY '\r\n'"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# create table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
# begin
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), BeginLoadQueryEvent)
self.assertIsInstance(self.stream.fetchone(), ExecuteLoadQueryEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
"""
def test_connection_stream_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, blocking=True,
ignored_events=self.ignoredEvents())
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')"
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_only_events(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, only_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_filtering_ignore_events(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, ignored_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, RotateEvent)
def test_filtering_table_event(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=[WriteRowsEvent],
only_tables = ["test_2"])
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.execute("INSERT INTO test_2 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_3 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_2 (data) VALUES ('beta')")
self.execute("COMMIT")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_2")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_2")
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_minimal_image_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_minimal_image_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], None)
def test_minimal_image_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], None)
self.assertEqual(event.rows[0]["after_values"]["id"], None)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_log_pos(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
for i in range(6):
self.stream.fetchone()
# record position after insert
log_file, log_pos = self.stream.log_file, self.stream.log_pos
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
# resume stream from previous position
if self.stream is not None:
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=True,
log_file=log_file,
log_pos=log_pos,
ignored_events=self.ignoredEvents()
)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), UpdateRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
def test_log_pos_handles_disconnects(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=False,
only_events = [FormatDescriptionEvent, QueryEvent, TableMapEvent, WriteRowsEvent, XidEvent]
)
query = "CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR (50) NOT NULL)"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertGreater(self.stream.log_pos, 0)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertGreater(self.stream.log_pos, 0)
def test_skip_to_timestamp(self):
self.stream.close()
query = "CREATE TABLE test_1 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
time.sleep(1)
query = "SELECT UNIX_TIMESTAMP();"
timestamp = self.execute(query).fetchone()[0]
query2 = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query2)
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
skip_to_timestamp=timestamp,
ignored_events=self.ignoredEvents(),
)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query2)
