Skip to content

Instantly share code, notes, and snippets.

@shlomi-noach
Last active May 23, 2024 06:44
Show Gist options
  • Save shlomi-noach/cc243fd690403e7617e3 to your computer and use it in GitHub Desktop.
Save shlomi-noach/cc243fd690403e7617e3 to your computer and use it in GitHub Desktop.
binlog-rbr-to-sbr
#!/usr/bin/python
#
# Convert a Row-Based-Replication binary log to Statement-Based-Replication format, cheating a little.
# This script exists since Percona Toolkit's pt-query-digest cannot digest RBR format. The script
# generates enough for it to work with.
# Expecting standard input
# Expected input is the output of "mysqlbinlog --verbose --base64-output=DECODE-ROWS <binlog_file_name>"
# For example:
# $ mysqlbinlog --verbose --base64-output=DECODE-ROWS mysql-bin.000006 | python binlog-rbr-to-sbr.py | pt-query-digest --type=binlog --order-by Query_time:cnt --group-by fingerprint
#
import fileinput
def convert_rbr_to_pseudo_sbr():
inside_rbr_statement = False
for line in fileinput.input():
line = line.strip()
if line.startswith("#") and "end_log_pos" in line:
for rbr_token in ["Update_rows:", "Write_rows:", "Delete_rows:", "Rows_query:", "Table_map:",]:
if rbr_token in line:
line = "%s%s" % (line.split(rbr_token)[0], "Query\tthread_id=1\texec_time=0\terror_code=0")
if line.startswith("### "):
inside_rbr_statement = True
# The "### " commented rows are the pseudo-statement interpreted by mysqlbinlog's "--verbose",
# and which we will feed into pt-query-digest
line = line.split(" ", 1)[1].strip()
else:
if inside_rbr_statement:
print("/*!*/;")
inside_rbr_statement = False
print(line)
convert_rbr_to_pseudo_sbr()
@mcrauwel
Copy link

mcrauwel commented Mar 4, 2016

I've updated the comment parsing as we discussed on the chat

@michaeldg
Copy link

I added a script for everybody not remembering all the mysqlbinlog and pt-query-digest switches.
Usage: binlog-digest.sh
Gist repo: https://gist.github.com/michaeldg/d6cddbc979d0ff127661dc9e4b7c7388

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment