1

Mysql 5.7 on Windows:

show global variables like 'binlog_format';

using me the STATEMENT

show master status;

gives me log-bin.000053

Now I execute this:

DELETE FROM test.emp WHERE EMPNO=109;

Now I want to read the contents of my mysqlbinlog:

mysqlbinlog log-bin.000053 > c:\backup\log.txt

An excerpt of the log file is this:

# at 1438
#170227  0:18:47 server id 1  end_log_pos 1514 CRC32 0x63bd770e     Query   thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1488151127/*!*/;
BEGIN
/*!*/;
# at 1514
#170227  0:18:47 server id 1  end_log_pos 1576 CRC32 0xf178e099     Table_map: `corsosql`.`emp` mapped to number 296
# at 1576
#170227  0:18:47 server id 1  end_log_pos 1644 CRC32 0x3efad847     Delete_rows: table id 296 flags: STMT_END_F

BINLOG '
V2KzWBMBAAAAPgAAACgGAAAAACgBAAAAAAEACGNvcnNvc3FsAANlbXAABwMPDwMK9gMGPAAeAAgC
f5ngePE=
V2KzWCABAAAARAAAAGwGAAAAACgBAAAAAAEAAgAH/4BxAAAABEx1aXMHQUNDT1VOVGwAAACHnw+A
GvQACgAAAEfY+j4=
'/*!*/;
# at 1644
#170227  0:18:47 server id 1  end_log_pos 1675 CRC32 0xfe9e00e6     Xid = 322
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Where is my delete?

Why can I not see it?

How can I see the delete that I've just made in this file?

In all docs that I've read the command is the same, but when I tried to do it, I cannot see the delete.

Thank you.


** EDIT: **

I created a new myisam test table and inserted/deleted some rows.

After that, now magically the statements is readable in the log.

Why??

Mysql now shows all the statements that i make in both innodb and myisam tables.

I wasted 3 hours trying to read sql statements from the log, and from the moment i created a new myisam table, mysql starts showing me the statements.

Strange.

Seems like the table creation has awakened mysql starting to show the statements.

Someone can explain me why?

2
  • 1
    It could me that the delete never fully processed and was executing (or idle...) for a significant portion of time before the statement committed. The statement would not appear in the binlog unless it was committed. Commented Feb 27, 2017 at 16:19
  • Try DELETE FROM test.emp WHERE EMPNO=109;COMMIT; And then mysqlbinlog... Maybe your my.cnf innodb configuration is not writing on disk and everything remain only in ib_logfilex Commented Aug 17, 2018 at 14:46

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.