MySQL two phase commit

Parameter Introduction

Innodb_flush_log_at_trx_commit

0: Every 1s, the system background thread brush log buffer, that is, the redo log brush disk, here will call Fsync, so you may lose the last 1s transaction.

1: Each commit, brush redo log, determine Fsync brush disc

2: Each time the submission, brush Redo log to the file system, do not call Fsync brush disk, 5.6.6 before is every 1s brush disc, after the version is through the parameters innodb_flush_log_at_timeout settings, the default is 1s. So it's possible to lose one last second of business. If there is a power-down protection component, it can be switched on.

Sync_binlog

Indicates how many sync events trigger a true binlog fsync brush disk, which defaults to 1, indicating that Binlog will fsync each commit.

Two phase commit

This two-phase commit is not a two-phase commit of a distributed transaction, but a two-phase commit between redo and Binlog after the Binlog is turned on. Two-phase commit, first redo log prepare, then write Binlog, and finally redo log commit.

• If after redo log prepare, binlog before the outage, rollback the transaction, the log is as follows:

2015-07-29 17:03:1821957 [note starting crash recovery ... 2015-07-29 17:03:18 7ffff7fe4780 innodb:starting recovery for XA transactions ... 2015-07-29 17:03:18 7ffff7fe4780 innodb:transaction 35077 in prepared state after recovery2015-07-29 17: 03:18 7ffff7fe4780 innodb:transaction contains changes to 1 rows2015-07-29 17:03:18 7ffff7fe4780 InnoDB: 1 transactions in prepared state after recovery2015-07-29 17:03:18 21957 [note Found 1 prepared Transaction (s) in innodb2015-07-29 17:03:18 21957 [note] rollback XID  ' mysqlxid\1\0\0\0\0\0\0\0\6\0\0\0\0\0\0\0 '         

• If the binlog is down after writing, the transaction is recover.

2015-07-29 17:06:23 7ffff7fe4780 innodb:starting Recoveryfor XA transactions ... 2015-07-29 17:06:23 7ffff7fe4780 innodb:transaction 35590 in prepared state after recovery2015-07-29 17: 06:23 7ffff7fe4780 innodb:transaction contains changes to 1 rows2015-07-29 17:06:23 7ffff7fe4780 InnoDB : 1 transactions in prepared state after recovery2015-07-29 17:06:23 22040 [note" Found 1 prepared Transaction (s) in innodb2015-07-29 17:06:23 22040 [note] commit XID  Mysqlxid\1\0\0\0\0\0\0\0\6\0\0\0\0\0\0\0 ' 2015-07-29 17:06:23 22040 [Note  Crash recovery finished.            

What happens when Binlog writes, but the actual data does not exist in the library?

Innodb_flush_log_at_trx_commit for 0, sync_binlog=1, at this time redo log no brush disk, Binlog brush disk, recover will not be restored according to Binlog.

Therefore, it is strongly recommended that both parameters be set to 1.

MySQL two phase commit