MySQL三大日志undolog、redolog、binlog_mysql的redolog和undolog如何区分

在MySQL中，很多的功能也都需要基于日志实现，比如事务回滚、数据持久化、数据恢复、数据迁移、MVCC机制。其中undolog、redolog都是InnoDB引擎中的日志，而且都是在Buffer Pool中，而binlog在Server层中，位于每条线程中。

1. undo log（回滚日志）

undo log 叫做回滚日志，它保证了事务的 ACID 特性中的原子性（Atomicity），是存储引擎层生成的日志，记录的是逻辑操作，不会去修改磁盘数据页的数据。主要用于记录数据被修改前的信息，在表记录修改之前，会先把原始数据拷贝到undo log里，如果事务回滚，则可以通过undo log中的历史数据来恢复到事务执行前的数据。

（1）不同操作记录的内容

• insert 插入操作，会在undo log中记录本次插入的主键id，等事务回滚时，会delete此主键对应的记录；
• delete 删除操作，会记录一条删除前的数据的insert语句，回滚时，insert原来的数据；
• update 更新操作，会记录一条更新前旧值的update的语句，回滚时再次执行这条语句更新回原来的数据。

（2）作用

• 回滚事务时，恢复到修改前的数据。
• 实现 MVCC。

2. redo log（重做日志）

redo log 叫做重做日志，它保证了事务的 ACID 特性中的持久性（Durability），是存储引擎层生成的日志，记录的是物理操作。其目的主要是为了防止提交到Buffer Pool中的数据还没来的急刷到磁盘数据页服务崩溃而导致的数据丢失问题。将所有操作都记录到redo log日志文件中，在重启mysql服务的时候，根据redo log文件进行重做，就可以恢复mysql崩溃前未刷到磁盘表空间中的内存数据，从而保证事务的持久性。

（1）redo log 与 undo log主要区别

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

（2）为什么需要写Redo Log Buffer和Redo Log FIle

• InnoDB在磁盘中存储的基本单元是页，每页大小为16KB，可能本次修改只变更一页中几个字节或者多页中的几个字节，但是直接刷盘是需要刷新整页的数据，就很浪费资源；而写入Redo Log FIle一行记录可能就占几十Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值。
• 一个事务可能修改了多页中的数据，页之间又是不连续的，就会产生随机IO，性能很差；而写入Redo Log FIle是顺序追加在Redo Log FIle文件中，顺序IO的性能更好。

（3）刷盘策略

更新的数据写入Redo Log Buffer之后，并不会立即持久化到Redo Log FIle，需要等待操作系统调用fsync()操作，才会刷到磁盘的Redo Log FIle上。InnoDB通过innodb_flush_log_at_trx_commit 参数可以控制策略，可设置该参数的值为：0，1， 2。

1. 设置为0（延迟写）：每次数据从内存中写入到redo log buffer后，数据依然留在redo log buffer中，然后由后台线程每秒写入os cache中并调用fsync()写入到磁盘的Redo Log FIle中。这种方式每个1秒写入到磁盘文件，如果在这1秒之内，MySQL服务挂了，则会丢失这1秒的数据。
   

2. 设置为1 (实时写，实时刷， 默认值 )：每次数据写入到redo log buffer后，会立即写入到os cache中并调用fsync()写到磁盘的Redo Log FIle中。这种方式提交事务后立即会刷新到磁盘文件，所以不会丢失数据。
   

3. 设置为2（实时写，延时刷）：每次数据写入到redo log buffer后，会立即写入到os cache，然后由后台线程每秒调用fsync()将os cache的数据写入磁盘的Redo Log FIle中。这种方式如果仅仅只是MySQL服务挂了不会有任何数据丢失，但是如果操作系统挂了则会丢失1秒的数据。
   

除了上面3种策略进行刷盘以外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

• 当redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动触发刷盘操作。由于这个事务并没有提交，所以这个刷盘动作只是写入os cache，而没有调用 fsync()，也就是只留在了操作系统的 os cache中。
• 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

（4）日志文件组

磁盘上的redo log文件不只一个，而是以一个日志文件组的形式出现的。这些文件以ib_logfile[数字]（数字可以是0、1、2…）的形式进行命名，每个的Redo日志文件大小都是一样的，默认由两个文件组成。在写入数据的时候采用循环使用的方式向redo log日志文件组里写数据，而在整个日志文件组中还有两个重要的属性，分别是write pos、checkpoint。

• write pos是当前写入记录的位置，一边写一边后移；
• checkpoint是当前要擦除记录的位置，也是往后推移。
  
  上图中从write pos位置顺时针开始写，红色部分是可以写入的的区域，从checkpoint位置顺时针开始清除redo log文件中已经写入的数据，蓝色部分是已经被写入的区域。
  • 每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。
  • 每次MySQL加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把checkpoint后移更新。
  • 如果write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log记录，MySQL的停下来，清空一些记录，把 checkpoint 推进一下才能继续写入新的记录。

3. bin log

bin log是二进制日志文件，它记录了数据库所有执行的 DDL 和 DML 等数据库更新的语句，但是不包含select或者show等没有修改任何数据的语句。它是MySQL级别的日志，也就是说所有的存储引擎都会产生bin log，而redo log或者undo log事务日志只有innoDB存储引擎才有。

（1）主要作用

1. 数据恢复：如果MySQL数据库意外挂了，可以利用bin log进行数据恢复，因为该日志记录所有数据库所有的变更，保证数据的安全性。
2. 主从复制：将主节点的bin log日志传递给从节点，再利用bin log日志来恢复从节点的数据，从而实现主从库数据的一致性，实现架构的高可用和高性能。

（2）bin log与redo log主要区别

• redo log主要实现故障情况下的数据恢复，保证事务的持久性；bin log主要用于数据灾备、同步。
• redo log是"物理日志", 记录的是具体数据页上做了什么修改；bin log是"逻辑日志", 记录内容是语句的原始逻辑。
• redo log是InnoDB存储引擎生成的事务日志，其他存储引擎没有；bin log是MySQL Server生成的日志，所有的存储引擎都有。
• redo log是在事务执行过程中就会写入；bin log是在事务提交的时候写入。

（3）刷盘策略

bin log和 redo log类似，都有3种刷盘策略， bin log的write和fsync时机是由参数 sync_binlog 控制，默认是 0 。

• 为0的时候：表示每次提交事务都只写入到os cache，再由系统自行判断什么时候执行fsync。虽然性能得到提升，但是系统挂了，os cache里面的 binglog 会丢失。
• 为1的时候：表示每次提交事务都会写入os cache并由系统立即调用fsync()写入磁盘文件中，这种方式数据不会丢失，更加安全。
• 为N(N>1)的时候：表示每次提交事务都只写入os cache，但累积N个事务后系统才调用fsync()写入磁盘文件中。

（4）查看bin log开启情况

可以通过命令show variables like '%log_bin%';查看bin log开启状态以及最终输出的位置。

（5）查看日志文件列表和大小

通过SHOW BINARY LOGS;查看当前的二进制日志文件列表及大小。

（6）查看bin log日志中的内容

可以用show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];命令工具查看bin log日志中的内容。
IN ‘log_name’：指定要查询的binlog文件名（不指定就是第一个binlog文件）；
FROM pos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）；
LIMIT [offset] ：偏移量(不指定就是0)；
row_count :查询总条数（不指定就是所有行）；

（7）设置bin log

修改MySQL的 my.cnf(linux) 或my.ini(windows)文件可以设置二进制日志的相关参数。

# 日志保存的位置
log-bin=SC-bin
# 日志保存的时间，单位是秒
binlog_expire_logs_seconds=600
# 单个bin log日志的容量
max_binlog_size=100M
1
2
3
4
5
6

（8）查看bin log 格式

bin log输出的格式类型有3种，默认是ROW类型。通过show variables like 'binlog_format';查看bin log 日志格式。

1. Statement格式：记录的是逻辑SQL语句，每一条会修改数据的sql都会记录在bin log中。
   • 优点：日志文件小，不需要记录每一行的变化，节约IO，提高性能。
   • 缺点：准确性差，对一些系统函数不能准确复制，如now()，可能导致主从同步、恢复数据不一致。
2. ROW格式：记录表的行更改情况。
   • 优点：准确性强，能准确复制数据的变更。
   • 缺点：日志文件大，较大的网络IO和磁盘IO。
3. Mixed格式: 混合模式，即Statment、Row的结合版。
   • 优点：准确性强，文件大小适中。
   • 缺点：有可能发生主从不一致的问题。