2

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.

2

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.

0

please use this tool for parsing the SQL statement

https://github.com/danfengcao/binlog2sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.