MySQL: How to properly extract blob data from mysqlbinlog output

Question

I am trying to restore a few erroneous updates to a customer MySQL 5.7 database. Binlogs are enabled (binlog_format=MIXED). I am trying to write a script that will go through the binlogs and restore the rows to their previous values. I am using mysqbinlog like this:

mysqlbinlog -vv --base64-output=decode-rows mysql-bin-000001.

The only problem is that the values are BLOB fields containing binary data, and unfortunately I can't find a way to handle them using the mysqlbinlog utility:

#220131  7:41:04 server id 227  end_log_pos 216096 CRC32 0xc1899f91     Update_rows: table id 109 flags: STMT_END_F
### UPDATE `db`.`contents`
### WHERE
###   @1=31508332458 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='���N�0\x10�...' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=0 is_null=0 */
### SET
###   @1=31508332458 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='\u' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=0 is_null=0 */

Ideally, I want to be able to represent the second field @2 using hex, so I can export the data to csv (@1,HEX(@2)), but that doesn't seem to be possible. Is there any way to extract blobs without treating them as strings, so I can get the data back? Thanks.

danblack · Accepted Answer · 2022-01-31 23:41:25Z

2

Untested, but I'd grab the mysqlbinlog from MariaDB and use flashback to generate the SQL.

In theory, being just DML, this should be MySQL compatible or only require small modification to achieve the final result.

answered Jan 31, 2022 at 23:41

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jacket Over a year ago

Never heard of the flashback switch before. This did it! I was able to revert a copy of the database to a state before the erroneous updates. The rest is easy. Case closed. Thank you!

Collectives™ on Stack Overflow

MySQL: How to properly extract blob data from mysqlbinlog output

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related