MySQL 日志相关知识总结

数据库中用于存储数据的文件称为data file，日志文件称为log file。此外，如果每次读写都是直接访问磁盘，性能很差，所以数据库是有缓存的，数据缓存是data buffer，日志缓存log buffer。

sql执行顺序

当我们执行一条更新语句时，比如 update table set c=c+1 where id = 2，执行顺序如下：

• 执行器通过存储引擎获取id=2的行记录。如果id=2的行记录所在的数据页已经在内存中，则直接返回；否则，需要从磁盘读取数据
• 执行器拿到返回的行数据，把字段c的值+1，得到新的行数据，然后调用存储引擎接口写入行数据
• 引擎把这行数据更新到内存，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告诉执行器执行完成，随时可以提交事务
• 执行器生成这个操作的bin log，并把bin log写入磁盘
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成commit状态，更新完成
  

补充：MySQL的基本存储结构是页(记录都存在页里边)，所以MySQL是先把这条记录所在的页找到，然后把该页加载到内存中，再修改对应的记录。

bin log

是什么

bin log称为归档日志、二进制日志，属于MySQL Server层面的，用于记录数据库表结构和表数据的变更，可以简单理解为存储每条变更的sql语句，比如insert、delete、update（当然，不仅是sql，还有事务id，执行时间等等）。

什么时候产生

事务提交的时候，一次性将事务中的sql语句按照一定格式记录到bin log

有什么用

主要有两个作用：主从复制和恢复数据

• 目前大部分数据库架构都是一主多从，从服务器通过访问主服务器的bin log，保证数据一致性
• bin log记录数据库的变更，可以通过它恢复数据
  

什么时候落盘

区分innodb_flush_log_at_trx_commit和sync_binlog

​ 二进制日志取决于sync_binlog参数

• 0：事务提交后，由操作系统决定什么时候把缓存刷新到磁盘（性能最好，安全性最差）
• 1：每提交一次事务，调用一次fsync将缓存写入到磁盘（安全性最好，性能最差）
• n：当提交n次事务后，调用一次fsync将缓存写入到磁盘
  

文件记录模式

bin log有三种文件记录模式，分别是row、statement、mixed

• row（row-based replication，PBR）：记录每一行数据的修改情况

优点：能够清楚记录每行数据修改细节，能够完全保证主从数据一致性
缺点：批量操作时会产生大量的日志，比如alter table

• statement：记录每条修改数据的sql，可认为sql语句复制
  

优点：日志数据量小，减少磁盘IO，提高存储和恢复速度
缺点：在某些情况下会出现主从不一致，比如sql语句中包含**now()**等函数

• mixed：上面两种模式的混合，MySQL会根据sql语句选择写入模式，一般使用statement模式保存bin log，对于statement模式无法复制的操作，使用row模式保存bin log。
  

redo log

是什么

redo log称为重做日志，属于InnoDB存储引擎层的日志，记录物理页的修改信息，而不是某一行或几行修改成什么样

什么时候产生

事务开始，就会写入redo log。redo log写入到磁盘并不是随着事务提交才写入，而是在事务执行过程中，就已经写入到磁盘

有什么用

可用于恢复数据。redo log是在事务开始后就写入到磁盘，且是顺序IO，写入速度较快。如果服务器突然掉电，InnoDB引擎会使用redo log把数据库恢复到掉电前的时刻，保证数据的完整性

什么时候落盘

InnoDB先把日志写到缓冲区（log buffer），然后再把日志从log buffer刷到os buffer，最后调用文件系统的fsync函数将日志刷新到磁盘。重做日志写入时机由参数innodb_flush_log_at_trx_commit决定

• 0：每秒一次，把log buffer写入os buffer，并调用fsync刷到磁盘
• 1：每次提交事务时，把log buffer写入os buffer，并调用fsync刷到磁盘
• 2：每次提交事务时，只是写入到os buffer，然后每秒一次调用fsync将日志刷新到磁盘
  

一般取值为2，因为即使MySQL宕机，数据也没有丢失。只有整个服务器挂了，才损失1秒的数据

bin log VS redo log

看了以上的介绍，感觉bin log和redo log很像，都是记录数据变更，可用于恢复。其实，它们还是有明显区别的。

• bin log属于MySQL Server层面的，redo log属于InnoDB存储引擎层面
• bin log是逻辑日志，记录的是sql语句的原始逻辑；redo log是物理日志，记录的是物理页面更新的内容
• bin log是追加写，文件达到限制后会更换下个文件，不会覆盖；redo log是循环写，文件大小固定，写满就重头开始写，覆盖原来的内容
• bin log作用是主从复制和恢复数据，当数据库被删除、或者从库同步主库数据时，由于bin log记录变更数据的sql，所以可通过bin log恢复。而redo log作用是持久化，当发生服务器宕机或者掉电等情况，数据丢失，可以通过redo log恢复。
• bin log是提交事务时才写入磁盘，而redo log在开启事务时，就开始写入到磁盘

如果整个数据库被删除，可以通过redo log恢复吗？

不行！因为redo log侧重点是保存某次事务的数据变更，当内存中的数据刷到磁盘后，redo log的数据其实已经没有参考价值。此外，redo log会覆盖历史数据，也不可能通过它来恢复所有数据。

undo log

详细分析MySQL事务日志

是什么

undo log称为回滚日志，属于InnoDB存储引擎层，是逻辑日志，记录每行数据。当我们变更数据时，就会产生undo log，可以认为insert一条数据，undo log会记录一条对应的delete日志，反之亦然。

什么时候产生

在事务开始前，将当前版本生成undo log

有什么用

主要作用：提供回滚和多版本并发控制（MVCC）

• 回滚：当需要rollback时，从undo log的逻辑记录读取相应的内容进行回滚
• MVCC：undo log记录中存储的是旧版本数据，当一个事务需要读取数据时，会顺着undo链找到满足其可见性的记录