MySQL的Binlog和InnoDB的redo log的关系、以及 "两阶段提交"

数据库的日志类型大概可以分为以下三种：
逻辑日志，记录的是sql语句的原始逻辑，面向对象是逻辑结构如表、列等
物理日志，记录的是文件记录的改变，面向对象是表空间、数据文件、数据页、偏移量等
逻辑物理日志，页面内的操作记录的是逻辑日志，页间的操作记录的是物理日志，physical to a page，logical within a page

redo log和binlog的关系和Oracle的redo log和archivelog的关系不一样。
Oracle的redo log是逻辑物理日志，记录文件号、block、字段等，archivelog是redo log的归档

Mysql的redo log是逻辑物理日志，页面内的操作记录的是逻辑日志，页间的操作记录的是物理日志，但是和Oracle相比 没有对应的archivelog，且redo log是innodb引擎特有的
binlog是逻辑日志，相当于是Mysql server层的日志，适用于所有引擎，且可以通过参数控制写入。

Mysql的redo log有点类似于Oracle里没有开启归档时的Online redo log

Mysql的redo log和binlog都不可或缺，所以保持redo log和binlog的事务一致性就很重要，从而就引出了"两阶段提交"这个说法。

DML执行流程(从数据完全取到内存后开始)：
数据页到内存中-->修改数据-->更新数据页-->写入redolog，状态为prepare-->写binlog-->提交事务，redolog状态修改为commit

在做Crash recovery时：
binlog有记录，redolog状态commit：正常完成的事务，不需要恢复
binlog有记录，redolog状态prepare：在binlog写完提交事务之前的crash，恢复操作：提交事务
binlog无记录，redolog状态prepare：在binlog写完之前的crash，恢复操作：回滚事务
binlog无记录，redolog无记录：在redolog写之前crash，恢复操作：回滚事务

可以看出来在这种"两阶段提交"下事务的提交or回滚是由binlog决定的，可以理解为靠binlog做判断，靠redo log做恢复操作

有两个关于binlog和redo log的参数 这里也记一下：
innodb_flush_log_at_trx_commit：
Scope：Global、Dynamic：Yes、Default Value 1
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
注：因为操作系统的"延迟写"特性，write to disk只是写到了操作系统的缓冲区中，flush to disk则是执行同步操作fsync()，这样才能保证日志持久化到了硬盘中。

For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal InnoDB activities that cause logs to be flushed independently of the innodb_flush_log_at_trx_commit setting, and sometimes less frequently due to scheduling issues. 
If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.

Log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows you to set log flushing frequency to N seconds (where N is 1 ... 2700, with a default value of 1).However, any mysqld process crash can erase up to N seconds of transactions.

innodb_flush_log_at_timeout:
Scope：Global、Dynamic：Yes、Default Value 1、Minimum Value 1、Maximum Value 2700
Write and flush the logs every N seconds. innodb_flush_log_at_timeout allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. 
The default setting for innodb_flush_log_at_timeout is once per second.

sync_binlog：
Scope：Global、Dynamic：Yes、Default Value (>= 5.7.7) 1、Default Value (<= 5.7.6) 0、Minimum Value 0、Maximum Value 4294967295
sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server.Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.
sync_binlog=0: MySQL server只执行write to disk，由操作系统来执行flush to disk操作
sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash,transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.
sync_binlog=1: Binlog的落盘在事务commit之前完成
sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. 
This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss.