binlog2sql: Binlog to Raw SQL Conversion and Point In Time Recovery

binlog2sqlIn this blog, I will look at the tool binlog2sql, designed by Cao Danfeng. The tool will help to decode the MySQL binary logs and extract the raw SQL, which also helps to generate the ROLLBACK statements for point in time recovery (PITR) using the “flashback” feature. This feature is also implemented in MariaDB’s version of “mysqlbinlog” (Check out Flashback: Another Take on Point-In-Time Recovery in MySQL/MariaDB/Percona Server by Przemysław Malkowski, which describes this feature).

With the native mysqlbinlog tool, we can convert the binary logs from binary to text format. We can also generate the exact SQL statements using the mysqlbinlog utility. It depends on the “binlog_format & binlog_rows_query_log_events” parameters. Anyway, the tool really doesn’t have the feature to generate the ROLLBACK statements. 

  • With “binlog_format =  STATEMENT” format we do get SQL using mysqlbinlog. 
  • With the parameter binlog_rows_query_log_events = ON we do get both the SQL and binlog events using mysqlbinlog. 

Mysqlbinlog vs Binlog2sql

Before jumping into the binlog2sql tool, I wanted to compare the outputs from Mysqlbinlog and bilog2sql. In this section, I am going to show the output format from each tool on various options. For reference,  I have used the below query to generate the binlog events.

 

Mysqlbinlog:

Mysqlbinlog with ( binlog_format=ROW, binlog_rows_query_log_events=OFF ),

  • Output: Text format, not real SQL.
  • Rollback: The tool doesn’t have the feature to generate rollback statements.

Mysqlbinlog with ( binlog_format = STATEMENT ),

  • Output: SQL format.
  • Rollback: The tool doesn’t have the feature to generate rollback statements.

Mysqlbinlog with ( binlog_format=ROW and binlog_rows_query_log_events=ON ),

  • Output: SQL and Text format.
  • Rollback: The tool doesn’t have the feature to generate rollback statements.

Binlog2sql:

  • Output: SQL format
  • Rollback: Possible with binlog2sql

What is binlog2sql?

  • It is an open source tool to parse the binary logs.
  • It has the feature to extract the raw SQL statements from binary logs.
  • It has the feature to generate the ROLLBACK SQL from binary logs for point-in-time recovery.

MySQL Requirements

MySQL server must be active for two reasons:

  • binlog2sql is based on BINLOG_DUMP protocol to obtain binlog content.
  • binlog2sql is necessary to read the INFORMATION_SCHEMA.COLUMNS table from the server, to obtain metadata of the table. (A server with lots of tables can have significant overhead to query INFORMATION_SCHEMA.COLUMNS table.)

Installation

Tested Environment

  • Python 2.7, 3.4+
  • MySQL 5.6,5.7,8+

Note: Make sure you need to have “pymysql – 0.9.3 ” for MySQL 8+ support. 

Binlog2sql Options

How to Extract the Raw SQL From Binary Logs

For testing purposes, I have installed a MySQL server and created the following data: