Search code examples
mysqldata-recoverymysqlbinlog

MySQL: How to properly extract blob data from mysqlbinlog output

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.

Solution

  • 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.