MySQL中常见的几种日志

MySQL 中有六种日志文件，分别是：

重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。

之前没认真学习过，上次去面试被问死了，痛定思痛整理下相关的知识。

binglog

最早接触 binlog 是做数据库主从同步的时候，知道是通过同步 binlog 实现的。binlog 是没有 MySQL sever 层维护的一种二进制日志，与 innodb 引擎中的 redo/undo log 是完全不同的日志。其主要是用来记录对 MySQL 数据更新或潜在发生更新的 SQL 语句，并以 “事务”的形式保存在磁盘中。

binlog 主要有以下作用：

复制：MySQL 主从复制在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 并回放来达到 master-slave 数据一致的目的
数据恢复：通过 mysqlbinlog 工具恢复数据
增量备份

几个知识点：

binlog 不会记录不修改数据的语句，比如Select或者Show
binlog 会重写日志中的密码，保证不以纯文本的形式出现
MySQL 8 之后的版本可以选择对 binlog 进行加密
具体的写入时间：在事务提交的时候，数据库会把 binlog cache 写入 binlog 文件中，但并没有执行fsync()操作，即只将文件内容写入到 OS 缓存中。随后根据配置判断是否执行 fsync。
删除时间：保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

管理

有这几个常用的命令可以查看 binlog 的状态：

binlog 的配置信息：show variables like '%log_bin%';

binlog 的格式：show variables like 'binlog_format';

日志的文件列表：show binary logs;

当前日志的写入状态：show master status;

清空 binlog 日志：reset master;

格式

binlog 日志有 Row、Statement、Mixed 三种格式。可以通过 my.cnf 配置文件及 set global binlog_format='ROW/STATEMENT/MIXED'进行修改，命令行 show variables like 'binlog_format' 命令查看 binglog 格式。

Row格式

Row 格式仅保存被修改的记录的细节，不记录 SQL 语句上下文相关信息。新版本的 MySQL 默认是 Row 格式。

优点：能非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息，因此不会发生某些特定情况下的存储过程、函数或者触发器的调用触发无法被正确复制的问题，任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性
缺点：由于所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多。比如一条 update 语句，如修改多条记录，则 binlog 中每一条修改都会有记录，这样造成 binlog 日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。

Statement格式

每一条会修改数据的 SQL 都会记录在 binlog 中。

优点：只需要记录执行的语句的细节和上下文环境，而不需要记录每一行的变化，在一些修改记录较多的情况下相比 Row 格式能大大减少 binlog 日志量，节约 IO，提高性能。

另外还可以用于实时的还原。

主从版本可以不一样，从服务器版本可以比主服务器版本高。
缺点：为了保证 sql 语句能在 slave 上正确执行，必须记录上下文信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同的结果。

另外，主从复制时，存在部分函数（如 sleep）及存储过程在 slave 上会出现与 master 结果不一致的情况，而相比 Row 记录每一行的变化细节，绝不会发生这种不一致的情况。

Mixed格式

以上两种格式混合。

经过前面的对比，可以发现 Row 和 Statement 各有优势，如果可以根据 SQL 语句取舍可能会有更好地性能和效果。Mixed 便是以上两种形式的结合。不过，新版本的 MySQL 对 Row 模式也做了优化，并不是所有的修改都会完全以 Row 形式来记录，像遇到表结构变更的时候就会以 Statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更；因此，现在一般使用 Row 即可。

主从复制

复制是 MySQL 最重要的功能之一，MySQL 集群的高可用、负载均衡和读写分离都是基于复制来实现。复制步骤如下：

Master 将数据改变记录到二进制日志(binary log)中。
Slave 上面的 IO 进程连接上 Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
Master 接收到来自 Slave 的 IO 进程的请求后，负责复制的 IO 进程会根据请求信息读取日志指定位置之后的日志信息，返回给 Slave 的 IO 进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到 Master 端的 binlog 文件的名称以及 binlog 的位置。
Slave 的 IO 进程接收到信息后，将接收到的日志内容依次添加到 Slave 端的 relaylog 文件的最末端，并将读取到的 Master 端的 binlog 的文件名和位置记录到 masterinfo 文件中，以便在下一次读取的时候能够清楚的告诉 Master 从某个 binlog 的哪个位置开始往后的日志内容
Slave 的 SQL 进程检测到 relaylog 中新增加了内容后，会马上解析 relaylog 的内容成为在 Master 端真实执行时候的那些可执行的内容，并在自身执行。

undo log 和 redo log

undo log 和 redo log 其实都不是 MySQL 数据库层面的日志，而是 InnoDB 存储引擎的日志。二者的作用联系紧密，事务的隔离性由锁来实现，原子性、一致性、持久性通过数据库的 redo log 或 redo log 来完成。redo log 又称为重做日志，用来保证事务的持久性，undo log 用来保证事务的原子性和 MVCC。

redo log

功能

和大多数关系型数据库一样，InnoDB 记录了对数据文件的物理更改，并保证总是日志先行，也就是所谓的 WAL，即在持久化数据文件前，保证之前的 redo 日志已经写到磁盘。由于 redo log 是顺序整块写入，所以性能要更好。

重做日志两部分组成：一是内存中的重做日志缓冲(redo log buffer)，是易失的；二是重做日志文件(redo log file)，是持久的。redo log 记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

写入过程

在一条语句进行执行的时候，InnoDB 引擎会把新记录写到 redo log 日志中，然后更新内存，更新完成后就算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将 redo log 中的内容更新到磁盘中。

更详细的步骤，需要了解两个关键词：checkpoint 和 LSN(Log Sequence Number)，前者检查点简单来说就是把脏页刷到磁盘的时间点，这个时间点之前的数据都已经保存到了持久存储。而 LSN 是 InnoDB 使用的一个版本标记的计数，它是一个单调递增的值。数据页和 redo log 都有各自的 LSN。每次把 redo log 中的内容写入到实际的数据页之后，就会把 LSN 也同步过去。如果发生了宕机，我们可以根据数据页中的 LSN 值和 redo log 中 LSN 的值判断需要恢复的 redo log 的位置和大小。redo log 同样也有自己的缓存，所以也涉及到刷盘策略，是通过innodb_flush_log_at_trx_commit这个参数控制的。

当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。