MySQL Forums
Forum List  »  Utilities

crash recovery of sync_binlog=1 and innodb_flush_log_at_trx_commit=2
Posted by: sss sss
Date: November 20, 2013 02:04PM

Hey

recently i discussed the crash recovery with sync_binlog=1 and innodb_flush_log_at_trx_commit=2 with one of my colleagues. What i understood is that mysql will write the statement into the binary log and will hand over the statement to innodb. On innodb level the statement is written into the redo log that is also known as logfile. Since the logfile is not flushed after every commit to save I/O, my question is:

what will happen if the logfile is not fsync'ed but:
1) binary log is written and flushed
2) statement is handed over to innodb
3) innodb finished its work and data is still on memory and not on the logfile
4) power failure will occur

Since the data is on the binary log but not on the logfile, is Mysql 5.5/5.6 able to recover in such situations and how it works? On the documentation of 5.0 its said that there is a state between binlog and innodb that the statement is present on binlog but will be rolled back on innodb
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html. this state will not occour if the xa support is enabled...
in case of a power failure and not fsync'ed logfile, this state will not been reached, since the logfile is untouched.

any ideas if the missing statements on the binlog will be re-executed?

Options: ReplyQuote


Subject
Views
Written By
Posted
crash recovery of sync_binlog=1 and innodb_flush_log_at_trx_commit=2
2610
November 20, 2013 02:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.