MySQL Forums
Forum List  »  Utilities

Re: crash recovery of sync_binlog=1 and innodb_flush_log_at_trx_commit=2
Posted by: Mats Kindahl
Date: November 21, 2013 01:29AM

Hi!

This is not the entire picture of what happends. The procedure is the following:

1. The transaction is prepared and an entry is written to the (InnoDB) logfile.
2. The logfile is fsync:ed.
3. The transaction is written to the binary log.
- Since sync_binlog=1, the transaction will be flushed in 5.5.
- It works a differently in 5.6: see http://mysqlmusings.blogspot.se/2012/06/binary-log-group-commit-in-mysql-56.html
4. The transaction is committed by writing an entry to the (InnoDB) logfile.
- innodb...trx_commit=2, the log may not be fsync:ed.

If a power failure occur between 2 and 3, then there is no entry in the binary log and on recovery the transaction prepared in 1 will be rolled back.

If a power failure occur between 3 and 4 (or after 4, but the logfile was not fsync:ed), there is an entry in the binary log and on recovery the transaction prepared in 1 will be committed.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: crash recovery of sync_binlog=1 and innodb_flush_log_at_trx_commit=2
1353
November 21, 2013 01:29AM


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.