Mysql – How to parse thesql bin log file for statements (Row/Mixed Format)

binlogMySQLmysqlbinlog

I have a mysql database running with binary log enabled. Unfortunately, a statement was executed by the client, which turned out erroneous. Since a number of records have been "affected", I want to begin by parsing the bin-log for data-changing statements from that time period. Then we might be able to begin picking up the pieces.

I have used:

mysqlbinlog -v --base64-output=decode-rows --start-datetime="2015-06-26 08:30:00" --stop-datetime="2015-06-26 09:30:00" data/mysql-bin.000005 > C:\temp\binlog.txt

The output is quite intimidating. What I would appreciate is a strategy for parsing out any INSERT, UPDATE or DELETE statements. Keep in mind I'm on MIXED MODE.

Thank you.

Best Answer

When you have binlog_format=MIXED some statements will appear as actual SQL. Other statements may get obfuscated like this:

### INSERT INTO `sanma`.`response`
### SET
###   @1=743096223
###   @2=0
###   @3='IR'
###   @4='UnderProcess'
###   @5=2015-05-29 20:51:34
###   @6=743053329
###   @7=NULL
###   @8=NULL
###   @9='758484829'
###   @10=1432912893
###   @11=758484829
###   @12='UnderProcess'
###   @13=0
###   @14=NULL
###   @15=NULL
###   @16=NULL
###   @17=NULL
# at 20535714
#150529 20:51:33 server id 71  end_log_pos 20535741     Xid = 84151212
COMMIT/*!*/;

That statement was possible to see because the INSERT ... SET syntax was used.

Other statements may get obfuscated beyond recognition. This is the risk of using MIXED or ROW.

I have dealt with this recently

The only thing you can do is 

# Look for INSERT, UPDATE, DELETE statements
mysqlbinlog ... > statements.sql
grep -i "^[IUD][NPE][SDL][EA][RT][TE]"     statements.sql  > dml.sql
grep -i "^### [IUD][NPE][SDL][EA][RT][TE]" statements.sql >> dml.sql

There are no guarantees you will catch every change in statement form when mysqld switches to binlog_format ROW intermittently.

Related Question