MySQL 日志相关知识总结

2022-05-21 09:51

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

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

sql执行顺序

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

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

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

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

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

区分innodb_flush_log_at_trx_commit和sync_binlog

二进制日志取决于sync_binlog参数

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

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

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

mixed：上面两种模式的混合，MySQL会根据sql语句选择写入模式，一般使用statement模式保存bin log，对于statement模式无法复制的操作，使用row模式保存bin 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决定

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

看了以上的介绍，感觉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会覆盖历史数据，也不可能通过它来恢复所有数据。