MySQL日志

MySQL日志

MySQL三大日志

1.undo log

undo log是InnoDB存储引擎层的日志，实现了事务的原子性，主要用于事务回滚和MVCC。在事务没有提交之前，InnoDB会先记录更新前的数据，记录在undo log中，回滚时利用undo log来回滚。

2.redo log

redo log也是InnoDB存储引擎层的日志，属于物理日志，记录了某个数据页做了什么修改，实现了事务的持久性，主要用于掉电等故障恢复。比如，某个事务提交了，脏页数据还没有刷盘，但是MySQL机器断电了，脏页的数据就丢失了，MySQL重启后通过redo log来把已经提交事务的数据恢复回来。

3.binlog

binlog是Server层的日志，主要用于数据备份和主从复制。在完成一条更新操作后，Server层会生成一条binlog日志，等之后事务提交的时候，会把该事务执行过程中产生的所有binlog统一写入binlog文件。binlog文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作。

binlog的三种格式

• STATEMENT(默认)

每条修改数据的SQL都会被记录到binlog中，主从复制中Slave端再根据SQL语句重现。

缺点：STATEMENT有动态函数的问题，比如用了uuid或者now这些函数，主库上执行的结果并不是你在从库中执行的结果，这种随时在变的函数会导致复制的数据不一致。

• ROW

记录行数据最终被修改成什么样了，不会出现STATEMENT下动态函数出现的问题。

缺点：但是ROW的缺点是每条行变化结果都会被记录。

比如批量执行update语句，更新多少行就会产生多少条记录，使binlog文件过大，而在STATEMENT格式下只会记录一条update语句。

• MIXED

包含了STATEMENT和ROW格式，会自动根据情况选择使用哪种格式。

redo log 和 binlog的区别和应用场景

1.适用对象不同

binlog是MySQL的Server层实现的日志，所有存储引擎都可以使用

redo log是InnoDB存储引擎层的日志

2.文件格式不同

binlog有3种格式类型，分别是STATEMENT(默认格式)、ROW、MIXED

redo log是物理日志，记录的是某个数据页做了什么修改，比如XXX表空间中的YYY数据页ZZZ偏移量的地方做了AAA的更新。

3.写入方式不同

binlog是追加写，写满了一个文件，就创建一个新的文件继续写，不覆盖以前的日志，保存的是全量的日志

redo log是循环写，日志的空间大小是固定的，写满就会从头开始写，保存未被刷入磁盘的脏页日志。

4.用途不同

binlog用于备份恢复、主从复制

redo log用于掉电等故障恢复

为什么崩溃恢复不用binlog而用redo log？

binlog是Sever层的日志，不会记录InnoDB存储引擎层中有哪些数据页没有被刷盘，redo log是InnoDB层的日志，可以记录哪些脏页没有被刷盘，崩溃恢复的时候，恢复的粒度更细，可以精确到需要恢复的数据页，而bin log保存的是全量日志，没有办法做到这一点，所以崩溃恢复用的是redo log。

binlog虽然可以恢复，但是恢复性能极差。

redo log如何实现持久化

事务执行过程中更新数据，并不是在事务提交的时候，就把修改的数据刷入磁盘，而是修改Buffer Pool中的数据页，并标记为脏页，后面找合适的时间刷盘。

此时，如果事务提交了，还没有刷盘，数据库发生了宕机，那么就会导致事务修改的数据丢失了。

所以MySQL引入了redo log，redo log保存的内容是物理日志，主要是记录InnoDB对某个数据页的修改操作，当事务提交时，redo log会先刷入磁盘，因为redo log保存了数据页的修改操作，即使脏页数据没有刷盘时数据库发生了党纪，重启后MySQL通过重放redo log，就能恢复未刷盘的脏页，保证了数据的持久化。

redo log还能做什么？

写入redo log的方式使用了追加操作，所以磁盘操作就是顺序写(多个redo log记录在同一个文件中，一起写入磁盘)；而写入数据需要先找到写入位置，然后才写入磁盘，所以磁盘操作是随机写(Buffer poll中修改的多个数据可能在磁盘的不同位置，每次写入一个都需要寻找一次位置，开销很大)。

磁盘的顺序写比随机写高效的多，因此redo log写入磁盘的开销会更小。

为什么顺序写比随机写高效？

因为你在写作业的时候，按顺序一页一页往下写，肯定比每一个字都去找对应页写快的多。

所以说，这是WAL技术的另一个优点：MySQL的写操作从磁盘上的随机写变成了顺序写，提升了语句的执行性能。因为MySQL的写操作不是直接更新的磁盘上，而是先写到日志中，等合适的时间再去更新到磁盘上。

总之，redo log不仅实现了事务的持久性，保证MySQL在任何时间段突然崩溃，重启后之前提交的记录都不丢失，还把写操作从随机写变成了顺序写，提升MySQL写入磁盘的性能。

redo log的三种刷盘策略

单独执行一个更新语句时，InnoDB会自己启动一个事务，在执行更新语句的过程中，生成的redo log先写入到redo log buffer中，然后等待事务提交，再把缓存在redo log buffer中的redo log 按组的方式顺序写到磁盘。

上面的这种刷盘时机是在事务提交的时候，这个是默认的行为。除此之外，还有两种策略，由参数innodb_flush_log_at_trx_commit控制，取值可为0、1、2，默认是1：

参数设置为0：每次事务提交时，还是将redo log留在redo log buffer中，该模式下在事务提交时不会主动触发写入磁盘的操作。

参数设置为1：每次事务提交时，都将缓存在redo log buffer中的redo log直接持久化到磁盘，这样可以保证MySQL异常重启后数据不会丢失。

参数设置为2：每次事务提交时，都是缓存在redo log buffer中的redo log写到redo log文件，注意写入到redo log文件不代表写入磁盘，因为操作系统的文件系统中有个PageCache，PageCache是专门来缓存文件数据的，所以写入redo log文件相当于写入操作系统的文件缓存。

那参数为0、2时，什么时候把数据刷到磁盘上？

0：InnoDB的后台线程每隔1秒，会把缓存缓存在redo log buffer中的redo log，通过write()写入操作系统的PageCache，然后调用fsync()持久化到磁盘。

所以参数为0，MySQL进程崩溃会导致上一秒的事务数据全部丢失。

2：InnoDB的后台线程每隔1秒，调用fsync()将缓存在操作系统中PageCache里的redo log持久化到磁盘。

所以参数为2，比参数为0较为安全，因为MySQL进程崩溃不会丢失数据，只有在操作系统崩溃或系统断电时，上一秒所有事务才可能丢失。

三种策略的安全性和写入性能比较：

安全性：1 > 2 > 0

性能：0 > 2 > 1

所以安全性和性能是不可兼得的。

• 对于数据安全容忍度低的场景中，参数设置为1
• 对于可以容忍1秒内数据的丢失的场景中，参数设置为0，这样可以明显减少日志同步到磁盘的IO操作。
• 安全性和性能最折中的方案就是参数设置为2。

两阶段提交

什么是？为什么？

事务提交后，redo log和binlog都有持久化到磁盘，但是这是两个独立的逻辑，可能出现半成功状态，这样两个日志之间的逻辑就不一致。

比如，id=1这行数据的字段，name原本是张三。然后执行update user set name='李四' where id=1;后，如果持久化redo log和binlog日志时出现了半成功状态，那么：

• ==如果在将redo log刷入磁盘后，MySQL突然宕机了，而binlog还没来的及写入。==MySQL重启后，通过redo log能将buffer pool中的name字段恢复为李四，但是binlog没有记录这条更新语句，在主从架构中，binlog会被复制到从库，由于binlog丢失了这条更新语句，从库的这一行name仍然是张三，与主库的值不一致。
• ==如果binlog刷入磁盘后，MySQL宕机了，而redo log还没来的及写入。==由于redo log还没有写入，那么这个事务无效，name字段的值仍然是旧值张三，而binlog记录了这个更新语句，在主从架构中，binlog会被复制到从库，从库执行了这条更新语句，那么从库的这一行name是李四，与主库的值不一致。

所以，在持久化redo log和binlog时，出现半成功状态，就会导致主从库的数据不一致问题。redo log影响主库，binlog影响从库数据，所以redo log和binlog需要保持一致，才能保证主从库的一致。

两阶段提交过程

两阶段提交把事务拆分为两个阶段，准备阶段和提交阶段。

• 准备阶段，把redo log状态设置为prepare状态，然后把redo log刷入磁盘；
• 提交阶段，把binlog刷入磁盘，然后把redo log状态设置为commit状态，两阶段结束。

总的来说，两阶段提交是把binlog刷入磁盘时机作为事务提交成功的标志的。

• 如果binlog还没被刷入磁盘，MySQL发生了崩溃，MySQL重启就要回滚事务。

• 如果binlog刷入磁盘，即使redo log没有设置commit状态，MySQL发生崩溃，MySQL重启后就会提交事务。