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:

Decoding binary log Using binlog2sql:

How Does binlog2sql Support PITR?

  • Binlog2sql tool has the option “–flashback”, which will help to generate the ROLLBACK statements.
  • We can recover the data from DELETE and UPDATE statements. 
  • It will not support DDL ( DROP, TRUNCATE ) as the actual row events will not be available in the binary logs.

Case 1 – PITR from DELETE:

To test this case, I have deleted two records “id in (4,5)” from my test table “binlog2sql”.

For the reference, I am generating the raw SQL using binlog2sql.

The above DELETE’s are the exact statements, which executed to delete the records. Now, I am going to generate the ROLLBACK statements using the option “–flashback” as shown below,

You can see those DELETE statements are now converted to INSERT statements. Now, I can load the rollback statements into the server,

Recovered the data from DELETE.

Case 2 – PITR from UPDATE:

In this case, I am going to update two rows. I changed the status from “A” to “NA” for “id in (4,5)”.

For the reference, I am generating the executed UPDATEs using binlog2sql.

Generating the ROLLBACK statements.

Loading the rollback statements into the server.

The changes have been rolled back.

Conclusion

  • As I mentioned earlier, the tool will support rollback only for DELETE/UPDATE.
  • The tool will not support DDL because the DDL statements do not log any actual data in the binary logs. 
  • The tool will not work with encrypted/compressed binary logs.
  • The tool has been tested on MySQL 5.6 and MySQL 5.7 environments. It has the support for MySQL 8 with “pymysql – 0.9.3”.

Share this post

Leave a Reply