引言

任何项目都会有日志，MySQL也不例外，而且MySQL更是其中的佼佼者，日志种类繁多，而本篇的目的就是全解MySQL中的各类日志，如撤销日志、错误日志、慢查询日志、中继日志、回滚日志…..

其实日志的作用不言而喻，无论是线上排查，亦或是性能优化，几乎都需要从日志中来获得信息作为依据，而MySQL中，很多很多的功能也都需要基于日志实现，比如事务回滚、数据持久化、数据恢复、数据迁移、MVCC机制…..，因此是串联MySQL的重要媒介与基础。

OK，废话不多说了，正文现在开始~

一、Undo-log撤销日志

Undo即撤销的意思，但咱们通常也习惯称它为回滚日志，在日常开发过程中，如果代码敲错了，一般会习惯性的按下Ctrl+Z撤销，而Undo-log的作用也是如此，但它是用来给MySQL撤销SQL操作的。

在之前的《SQL执行篇》中曾聊到过，当一条写入类型的SQL执行时，都会记录Undo-log日志，会生成相应的反SQL放入到Undo-log中，例如：

  • 如果目前是insert插入操作，则生成一个对应的delete操作。

  • 如果目前是delete删除操作，InnoDB中会修改隐藏字段deleted_bit=1，则生成改为0的语句。

  • 如果目前的update修改操作，比如将姓名从竹子改成了熊猫，那就生成一个从熊猫改回竹子的操作。

当事务中某条SQL执行失败时，MySQL就需要回滚事务中其他执行成功的SQL，此时就会找到这个事务在Undo-log中生成的反SQL，然后将库中的数据改回事务发生前的样子。

大家看这段话，似乎没有啥问题对不？也包括我之前的文章中也是这样去描述的，但这里其实存在些许误导，在之前的《SQL执行篇》中，我们说一条写SQL执行前，会生成对应的反SQL记录在Undo-log中，但实际上并不会生成反SQL，这样去叙述仅是为了方便大家理解罢了。

那咱们怎么证明不会生成反SQL呢？如果大家有仔细研究过MySQL的日志，应该会发现Undo-log并不存在单独的日志文件，也就是磁盘中并不会存在xx-undo.log这类的文件，那Undo-log存在哪儿呢？InnoDB默认是将Undo-log存储在xx.ibdata共享表数据文件当中，默认采用段的形式存储。

也就是当一个事务尝试写某行表数据时，首先会将旧数据拷贝到xx.ibdata文件中，将表中行数据的隐藏字段：roll_ptr回滚指针会指向xx.ibdata文件中的旧数据，然后再写表上的数据。

那Undo-log究竟在xx.ibdata文件中怎么存储呢？在共享表数据文件中，有一块区域名为Rollback Segment回滚段，每个回滚段中有1024个Undo-log Segment，每个Undo段可存储一条旧数据，而执行写SQL时，Undo-log就是写入到这些段中。

不过在MySQL5.5版本前，默认只有一个Rollback Segment，而在MySQL5.5版本后，默认有128个回滚段，即支持128*1024条Undo记录同时存在。

1.1、对于事务回滚原理的纠正

结合上述纠正后的内容，咱们再对事务的回滚原理稍作更正，实际上当一个事务需要回滚时，本质上并不会以执行反SQL的模式还原数据，而是直接将roll_ptr回滚指针指向的Undo记录，从xx.ibdata共享表数据文件中拷贝到xx.ibd表数据文件，覆盖掉原本改动过的数据。

还是上个图简单理解一下吧，如下：

一条写SQL执行的流程如上图中的序号所示，当需要回滚事务时，直接用Undo旧记录覆盖表中修改过的新记录即可！

如果是insert操作，由于插入之前这条数据都不存在，那么就不会产生Undo记录，此时回滚时如何删除这条记录呢？因为插入操作不会产生Undo旧记录，因此隐藏字段中的roll_ptr=null，因此直接用null覆盖插入的新记录即可，这样也就实现了删除数据的效果~

1.2、基于Undo版本链实现MVCC

认真阅读过《MySQL-MVCC机制原理剖析》的小伙伴对于这点并不陌生，Undo-log中记录的旧数据并不仅仅只有一条，一条相同的行数据可能存在多条不同版本的Undo记录，内部会通过roll_ptr回滚指针，组成一个单向链表，而这个链表则被称之为Undo版本链，案例如下：

-- 事务T1：trx_id=1（两次修改同一条数据） UPDATE `zz_users` SET user_name = "竹子" WHERE user_id = 1; UPDATE `zz_users` SET user_sex = "男" WHERE user_id = 1; 复制代码

Undo-log中的旧数据版本链示意图大致如下：

当然，对于如何利用版本链实现MVCC机制，这点就不反复赘述了，没了解过的可以去看关于MVCC原理剖析的那篇文章。

1.3、Undo-log的内存缓冲区

InnoDB在MySQL启动时，会在内存中构建一个BufferPool，而这个缓冲池主要存放两类东西，一类是数据相关的缓冲，如索引、锁、表数据等，另一类则是各种日志的缓冲，如Undo、Bin、Redo….等日志。

而当一条写SQL执行时，不会直接去往磁盘中的xx.ibdata文件写数据，而是会写在undo_log_buffer缓冲区中，因为工作线程直接去写磁盘太影响效率了，写进缓冲区后会由后台线程去刷写磁盘。

OK~，那么如果当一个事务提交时，Undo的旧记录会不会立马被删除呢？因为事务都提交了，不需要再回滚改动过的数据，似乎用不上Undo旧记录了，对吗？确实如此，但不会立马删除Undo记录，对于旧记录的删除工作，InnoDB中会有专门的purger线程负责，purger线程内部会维护一个ReadView，它会以此作为判断依据，来决定何时移除Undo记录。

为什么不是事务提交后立马删除Undo记录呢？因为可能会有其他事务在通过快照，读Undo版本链中的旧数据，直接移除可能会导致其他事务读不到数据，因此删除的工作就交给了purger线程。

1.4、Undo-log相关的参数

最后再来看看关于Undo-log的一些参数，其实在MySQL5.5之前没有太多参数，如下：

  • innodb_max_undo_log_size：本地磁盘文件中，Undo-log的最大值，默认1GB。

  • innodb_rollback_segments：指定回滚段的数量，默认为1个。

除开上述两个参数外，其他参数基本上是在MySQL5.6才有的，如下：

  • innodb_undo_directory：指定Undo-log的存放目录，默认放在.ibdata文件中。

  • innodb_undo_logs：指定回滚段的数量，默认为128个，也就是之前的innodb_rollback_segments。

  • innodb_undo_tablespaces：指定Undo-log分成几个文件来存储，必须开启innodb_undo_directory参数。

  • innodb_undo_log_truncate：是否开启Undo-log的在线压缩功能，即日志文件超过大小一半时自动压缩，默认OFF关闭。

没错，在MySQL5.5版本以后，Undo-log日志支持单独存放，并且多出了几个参数可以调整Undo-log的区域。

二、Redo-log重做日志

详细聊明白了Undo-log后，紧接着再来看看它的同胞兄弟：Redo-log日志，为啥说它两是同胞兄弟呢？因为这两日志都是InnoDB引擎独有的，Undo-log主要用于实现事务回滚和MVCC机制，而Redo-log则用来实现数据的恢复。还记得在《MySQL事务篇》中聊到的数据恢复机制嘛？

​Redo-log日志的作用就在于此，下面详细的聊一下它。

2.1、为何需要Redo-log日志？

众所周知的一点：MySQL绝大部分引擎都是是基于磁盘存储数据的，但如若每次读写数据都走磁盘，其效率必然十分低下，因此InnoDB引擎在设计时，当MySQL启动后就会在内存中创建一个BufferPool，运行过程中会将大量操作汇集在内存中进行，比如写入数据时，先写到内存中，然后由后台线程再刷写到磁盘。

虽然使用BufferPool提升了MySQL整体的读写性能，但它是基于内存的，也就意味着随着机器的宕机、重启，其中保存的数据会消失，那当一个事务向内存中写入数据后，MySQL突然宕机了，岂不代表这条未刷写到磁盘的数据会丢失吗？答案是Yes，也正由于该原因，Redo-log应运而生！

因为数据写到内存后有丢失风险，这明显违背了事务ACID原则中的持久性，所以Redo-log的出现就是为了解决该问题，Redo-log是一种预写式日志，即在向内存写入数据前，会先写日志，当后续数据未被刷写到磁盘、MySQL崩溃时，就可以通过日志来恢复数据，确保所有提交的事务都会被持久化。

但是要注意：工作线程执行SQL前，写的Redo-log日志，也是写在了内存中的redo_log_buffer缓冲区。

既然Redo-log日志也是先写内存，那Redo-log有没有丢失的风险呢？这跟Redo-log的刷盘策略有关。

2.2、Redo-log的刷盘策略

对于内存中的redo_log_buffer缓冲区，其中写入的数据会何时被刷写到磁盘？对于这点在之前《SQL执行篇-写SQL执行时的日志操作》中简单的提到过：

​

简单来说就是刷盘的时机由innodb_flush_log_at_trx_commit参数来控制，默认是处于第二个级别，也就是每次提交事务时都会刷盘，这也就意味着一个事务执行成功后，相应的Redo-log日志绝对会被刷写到磁盘中，因此无需担心会出现丢失风险。

那如果事务还未提交时，MySQL宕机怎么办？对于这个问题在事务篇的那个截图中有，不再反复赘述！

但再来思考一个问题：既然Redo-log要写磁盘，那为何不在写日志的时候，直接把数据写到磁盘里面去呢？

2.3、Redo-log中为何“多此一举”？

先刷写一次Redo-log日志到磁盘，后台线程再根据Redo-log日志把数据落盘，这个动作似乎看起来有些多余对吧？但实际上这样做好处很大：

  • ①日志比数据先落入磁盘，因此就算MySQL崩溃也可以通过日志恢复数据。

  • ②写日志时是以追加形式写到末尾，而写数据时则是计算数据位置，随机插入。

对于第一点好处就不多说了，重点来聊一聊第二点，因为写日志的时候，只需要将记录追加到日志文件的尾部即可，这是按顺序写入，但写入表数据时，还需要先先计算数据的位置，比如修改一条数据时，需要先判断这条数据在磁盘文件中的那个位置，找到了位置再写入，这是随机写入，顺序写入的速度会比随机写入快很多很多。

因为写日志会比写数据落盘快，因此日志落盘后返回，比数据落盘后返回要快，对于客户端而言，响应时间会更短~

2.4、Redo-log相关的参数

这里也列举出几个Redo-log日志中，较为重要的系统参数：

  • innodb_flush_log_at_trx_commit：设置redo_log_buffer的刷盘策略，默认每次提交事务都刷盘。

  • innodb_log_group_home_dir：指定redo-log日志文件的保存路径，默认为./。

  • innodb_log_buffer_size：指定redo_log_buffer缓冲区的大小，默认为16MB。

  • innodb_log_files_in_group：指定redo日志的磁盘文件个数，默认为2个。

  • innodb_log_file_size：指定redo日志的每个磁盘文件的大小限制，默认为48MB。

其中主要讲一下Redo-log的本地磁盘文件个数，为啥默认是两个呢？因为MySQL通过来回写这两个文件的形式记录Redo-log日志，用两个日志文件组成一个“环形”，如下：

​

先来简单解释一下图中存在的两根指针：

  • write pos：这根指针用来表示当前Redo-log文件写到了哪个位置。

  • check point：这根指针表示目前哪些Redo-log记录已经失效且可以被擦除（覆盖）。

两根指针中间区域，也就是图中的红色区域，代表是可以写入日志记录的可用空间，而蓝色区域则表示日志落盘但数据还未落盘的记录，这句话怎么理解呢？

当一个事务写了redo-log日志、并将数据写入缓冲区后，但数据还未写到本地的表数据文件中，此时这个事务对应的redo-log记录就为上图中的蓝色，而当一个事务所写的数据也落盘后，对应的redo-log记录就会变为红色。

当write pos指针追上check point指针时，红色区域就会消失，也就代表Redo-log文件满了，再当MySQL执行写操作时就会被阻塞，因为无法再写入redo-log日志了，所以会触发checkpoint刷盘机制，将redo-log记录对应的事务数据，全部刷写到磁盘中的表数据文件后，阻塞的写事务才能继续执行。

触发checkpoint刷盘机制后，随着数据的落盘，check point指针也会不断的向后移动，红色区域也会不断增长，因此阻塞的写事务才能继续执行。

OK~，再补齐一些关于checkpoint机制的系统参数：

  • innodb_log_write_ahead_size：设置checkpoint刷盘机制每次落盘动作的大小，默认为8K，如果你要设置，必须要为4k的整数倍，这跟read-on-write问题有关，具体的可以参考：《这篇文章》。

  • innodb_log_compressed_pages：是否对Redo日志开启页压缩机制，默认ON，这跟InnoDB的页压缩技术有关，后续《特性篇》聊。

  • innodb_log_checksums：Redo日志完整性效验机制，默认开启，必须要开启，否则有可能刷写数据时，只刷一半，出现类似于“网络粘包”的问题。

后续几个参数略微有些复杂，因为主要跟MySQL5.6之后的优化有关，后续在《MySQL特性篇》中会再次细聊。

三、Bin-log变更日志

Bin-log日志也被称之为二进制日志，作用与Redo-log类似，主要是记录所有对数据库表结构变更和表数据修改的操作，对于select、show这类读操作并不会记录。bin-log是MySQL-Server级别的日志，也就是所有引擎都能用的日志，而redo-log、undo-log都是InnoDB引擎专享的，无法跨引擎生效。

​OK~，再看到这张写SQL的执行流程图，重点观察里面的第⑨步，无论当前表使用的是什么引擎，实际上都需要完成记录bin-log日志这步操作，和之前分析的两种日志相同，bin-log也由内存日志缓冲区+本地磁盘文件两部分组成，这也就意味着：写bin-log日志时，也会先写缓冲区，然后由后台线程去刷盘。

3.1、bin-log的缓冲区

为啥要单独把bin-log的缓冲区拎出来讲呢？因为它跟redo-log、undo-log的缓冲区并不同，前面分析的两种日志缓冲区，都位于InnoDB创建的共享BufferPool中，而bin_log_buffer是位于每条线程中的，关系图如下：

​也就是说，MySQL-Server会给每一条工作线程，都分配一个bin_log_buffer，而并不是放在共享缓冲区中，这是为啥呢？因为MySQL设计时要兼容所有引擎，直接将bin-log的缓冲区，设计在线程的工作内存中，这样就能够让所有引擎通用，并且不同线程/事务之间，由于写的都是自己工作内存中的bin-log缓冲，因此并发执行时也不会冲突！

bin_log_buffer的设计，就类似于咱们之前讲《并发编程》时讲过的《ThreadLocal线程变量副本》。

OK~，简单理解bin-log缓冲区的设计后，对于bin-log的刷盘策略就不反复赘述了，就是通过sync_binlog参数控制，与之前redo-log类似（上面有）。

3.2、Bin-log本地日志文件的格式

bin-log的本地日志文件，采用的是追加写的模式，也就是一直向文件末尾写入新的日志记录，当一个日志文件写满后，会创建一个新的bin-log日志文件，每个日志文件的命名为mysql-bin.000001、mysql-bin.000002、mysql-bin.00000x….，可以通过show binary logs;命令查看已有的bin-log日志文件。

接着再来聊聊bin-log文件的内部格式~

在bin-log的本地文件中，其中存储的日志记录共有Statment、Row、Mixed三种格式，分别是啥意思呢？

Statment：每一条会对数据库产生变更的SQL语句都会记录到bin-log中。

咋理解这句话呢？举个例子：

-- 查询一次用户表数据，如下： SELECT * FROM `zz_users`; +---------+-----------+----------+----------+---------------------+ | user_id | user_name | user_sex | password | register_time | +---------+-----------+----------+----------+---------------------+ | 1 | 熊猫 | 女 | 6666 | 2022-08-14 15:22:01 | | 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 | | 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 | | 4 | 猫熊 | 女 | 8888 | 2022-09-27 17:22:59 | | 9 | 黑竹 | 男 | 9999 | 2022-09-28 22:31:44 | +---------+-----------+----------+----------+---------------------+ -- 将用户表中所有 ID>3的密码重置 update `zz_users` set `password` = "1111" where user_id > 3; 复制代码

比如上述这个事务执行时，MySQL会将第二条update语句记录在bin-log日志中，但对于select语句则不会记录（在记录SQL时，还会记录一下SQL的上下文信息，如执行时间、事务ID、日志量……）。

这种方式的优势很明显，由于只记录对数据库产生变更操作的SQL，所以不会产生太大的日志量，节约空间，恢复数据时因为数据量小，所以磁盘IO次数少，因此性能会比较不错。同时做主备等高可用架构时，数据同步也会较小，因此比较节省带宽。

但虽然优势不小，但缺点页很明显，即恢复数据、主从同步数据时，有时会出现数据不一致的情况，如SQL中使用了sysdate()、now()这类函数，比如举个简单的例子：

insert into `zz_users` values(11,"棕熊","男","3333",sysdate()); 复制代码

比如这条插入语句，由于对用户表产生了变更操作，所以会被记录到bin-log中，但当主从架构之间做数据同步时，假设将这条SQL同步到从机上执行，此时问题就来了，sysdate()函数会获取机器的当前时间，但主机和从机执行这条SQL显然不是同一时间，因此就会导致ID=11的这条数据，在主机和从机的用户表中，注册时间会出现不一致。

Row：这种模式就是为了解决Statment模式的缺陷，Row模式中不再记录每条造成变更的SQL语句，而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了。

这又怎么理解呢？还是以前面的重置密码的例子来说：

-- 将用户表中所有 ID>3的密码重置（ID=4、9的两条数据会被重置） update `zz_users` set `password` = "1111" where user_id > 3; 复制代码

在这种模式下，就不会记录这条update语句，而是记录发生改变的行数据，即ID=4、9的两条用户数据，会将其更改后的值记录到bin-log日志中。

这种方式因为不记录SQL，而是记录修改后的值，因此有个很大的好处是：当主从同步数据时，复制的是主机上的数据，因此不会出现主从数据不一致的情况。但缺陷同样很明显，比如表中有800W数据，现在我对ID<600W的所有数据进行了修改操作，哪也就意味着会有600W条记录写入bin-log日志，这个数据量可想而知，其磁盘IO、网络带宽开销会很高。

Mixed：这种被称为混合模式，即Statment、Row的结合版，因为Statment模式会导致数据出现不一致，而Row模式数据量又会很大，因此Mixed模式结合了两者的优劣势，对于可以复制的SQL采用Statment模式记录，对于无法复制的SQL采用Row记录。

这样即保留了Statment模式的数据量小，又具备Row模式的数据精准性，鱼和熊掌兼得焉~

其实看到这里，如果比较熟悉Redis4.x版本的小伙伴应该会有种熟悉感，Redis的RDB、AOF持久化模式，正好对应MySQL的Statment、Row模式，而Redis4.0引入了混合持久化机制，MySQL5.1版本也引入了混合日志模式~

3.2、为什么有了Redo-log还需要Bin-log？

Redo-log、Bin-log都是记录更新数据库的操作，但为什么会同时设计两个呢？这其实跟InnoDB有关，如若对MySQL旧史较为熟悉的小伙伴应该知道，MySQL自己的官方引擎实际上最初是MyISAM，InnoDB是Innobase-Oy公司开发的一款可拔插式引擎，由于InnoDB被MySQL支持后使用频率越来越高，后面MySQL官方才用InnoDB替换了MyISAM作为默认引擎。

那这跟上面的问题有啥关系呢？其实关系很大，MySQL-Server、MyISAM是出自于官方的产品，因此MyISAM中并未设计记录变更操作的日志，记录变更操作由MySQL-Server来通过Bin-log完成。

但因为MyISAM不支持事务，所以MySQL-Server设计的Bin-log无法用于灾难恢复，因此InnoDB在设计时，又重新设计出Redo-log日志，可以利用该日志实现crash-safe灾难恢复能力，确保任何事务提交后数据都不会丢失。

3.3、Redo-log、Bin-log两者的区别

对于Redo-log、Bin-log两者的区别，主要可以从四个维度上来说：

  • ①生效范围不同，Redo-log是InnoDB专享的，Bin-log是所有引擎通用的。

  • ②写入方式不同，Redo-log是用两个文件循环写，而Bin-log是不断创建新文件追加写。

  • ③文件格式不同，Redo-log中记录的都是变更后的数据，而Bin-log会记录变更SQL语句。

  • ④使用场景不同，Redo-log主要实现故障情况下的数据恢复，Bin-log则用于数据灾备、同步。

3.4、不小心删库后应该跑路吗？

首先来说一下，为啥要讨论这个问题呢，这是由于之前说MySQL架构的时候有一位小伙伴提出的：

大佬MySQL能不能像oracle一样有”闪回”功能呢,之前把测试库的Oracle表全部update了,直接闪回了.还有调试JPA,结果程序一启动把表删了,都能直接—个SQL恢复了因,这些应该没人发现o.O

可以实现类似的功能，但是MySQL没有提供直接性的支持，可以通过操作日志去还原数据。
​这里有两个问题：①删库后跑路会不会被人发现？②MySQL能不能和Oracle一样具备闪回功能？

先来简单聊聊第一个问题，如果你在线上真的删库了，哪就先别想着跑路，你跑不掉！因为bin-log日志中会记录执行SQL的连接会话信息，同时一般规模较大的企业，都会搭建完善的监控系统，会监控服务的网络连接，因此当你删库后，可以顺着bin-log → session → network-connection这条线确定执行删库SQL的IP！如果你还未断开连接，直接通过MySQL的命令就能定位到删库的IP，因此基本上删库了，是可以定位到责任人的！

当然，如果项目配备的监控系统不够完善，同时你的连接已经断开，并且电脑换了一个局域网，同时时间来到了三天以后，如果还没人发现你，哪基本上跑路也不会有人发现，但这样干，会存在些许做贼心虚的嫌疑~

OK~，不过多的讨论这个话题了，总之你跑路肯定不能跑，误删了数据就要想办法恢复，咋恢复呢？通过日志恢复，但Redo-log、Bin-log都会记录数据库的变更操作，因此用谁比较合适呢？

答案是Bin-log，因为Redo-log采用循环写的方式，一边写会一边擦，里面无法得到完整的数据，而Bin-log是追加写的模式，你不去主动删除磁盘的日志文件，并且磁盘的空间还足够，一般Bin-log日志文件都会在本地，因此当你删库后，可以直接去本地找Bin-log的日志文件，然后拷贝出来一份，再打开最后一个文件，把里面删库的记录手动移除，再利用mysqlbinlog工具导出xx.SQL文件，最后执行该SQL文件即可恢复删库前的数据。

这里就叙说大体逻辑，具体的数据恢复操作，会在后续的《MySQL线上排查与数据恢复篇》细讲，其实也可以通过Flashback工具提供的闪回功能恢复数据，但以后再细聊~

3.5、bin-log相关的参数

  • log_bin：是否开启bin-log日志，默认ON开启，表示会记录变更DB的操作。

  • log_bin_basename：设置bin-log日志的存储目录和文件名前缀，默认为./bin.0000x。

  • log_bin_index：设置bin-log索引文件的存储位置，因为本地有多个日志文件，需要用索引来确定目前该操作的日志文件。

  • binlog_format：指定bin-log日志记录的存储方式，可选Statment、Row、Mixed。

  • max_binlog_size：设置bin-log本地单个文件的最大限制，最多只能调整到1GB。

  • binlog_cache_size：设置为每条线程的工作内存，分配多大的bin-log缓冲区。

  • sync_binlog：控制bin-log日志的刷盘频率。

  • binlog_do_db：设置后，只会收集指定库的bin-log日志，默认所有库都会记录。

  • ……：省略其他不常用参数。

3.6、Redo-log的两阶段提交

详细大家应该听说过MySQL事务两阶段提交方案，啥叫做事务两阶段提交呢？实则是指Redo-log分两次写入，如下：

​注意看之前给出的写SQL执行流程图，其中第⑤、⑩步，分别会写两次Redo-log日志，这个日志的作用前面讲的很明白了，主要用来做崩溃恢复，但为什么要分两次写呢？写一次不行嘛？

其实想要弄明白这个问题，要结合bin-log日志一起来聊。

如果只写一次的话，那到底先写bin-log还是redo-log呢？

先写bin-log，再写redo-log：当事务提交后，先写bin-log成功，结果在写redo-log时断电宕机了，再重启后由于redo-log中没有该事务的日志记录，因此不会恢复该事务提交的数据。但要注意，主从架构中同步数据是使用bin-log来实现的，而宕机前bin-log写入成功了，就代表这个事务提交的数据会被同步到从机，也就意味着从机会比主机多出一条数据。

先写redo-log，再写bin-log：当事务提交后，先写redo-log成功，但在写bin-log时宕机了，主节点重启后，会根据redo-log恢复数据，但从机依旧是依赖bin-log来同步数据的，因此从机无法将这个事务提交的数据同步过去，毕竟bin-log中没有撒，最终从机会比主机少一条数据。

经过上述分析后可得知：如果redo-log只写一次，那不管谁先写，都有可能造成主从同步数据时的不一致问题出现，为了解决该问题，redo-log就被设计成了两阶段提交模式，设置成两阶段提交后，整个执行过程有三处崩溃点：

  • redo-log(prepare)：在写入准备状态的redo记录时宕机，事务还未提交，不会影响一致性。

  • bin-log：在写bin记录时崩溃，重启后会根据redo记录中的事务ID，回滚前面已写入的数据。

  • redo-log(commit)：在bin-log写入成功后，写redo(commit)记录时崩溃，因为bin-log中已经写入成功了，所以从机也可以同步数据，因此重启时直接再次提交事务，写入一条redo(commit)记录即可。

通过这种两阶段提交的方案，就能够确保redo-log、bin-log两者的日志数据是相同的，bin-log中有的主机再恢复，如果bin-log没有则直接回滚主机上写入的数据，确保整个数据库系统的数据一致性。

OK~,最后再简单补充一点：为什么bin-log又被叫做二进制日志呢？因为记录日志时，MySQL写入的是二进制数据，而并非字符数据，也就意味着直接用cat/vim这类工具是无法打开的，必须要通过MySQL提供的mysqlbinlog工具解析查看。

四、Error-log错误日志

前面已经将最重要的undo-log、redo-log、bin-log三大日志讲明白了，这三个日志都是用来辅助MySQL、InnoDB在线上正常运行的，但凡其中一个出现问题，都有可能导致MySQL无法正常工作。

接下来再看几个辅助性的日志，即error-log、slow-log、relay-log。

  • error-log：MySQL线上MySQL由于非外在因素（断电、硬件损坏…）导致崩溃时，辅助线上排错的日志。

  • slow-log：系统响应缓慢时，用于定位问题SQL的日志，其中记录了查询时间较长的SQL。

  • relay-log：搭建MySQL高可用热备架构时，用于同步数据的辅助日志。

接下来先看error-log，这个日志的作用很明显，从名字都能得知它是用于记录MySQL报错信息的，其中涵盖了MySQL-Server的启动、停止运行的时间，以及报错的诊断信息，也包括了错误、警告和提示等多个级别的日志详情。

通过错误日志，一方面可以用来监控MySQL的运行状态，便于预防故障、发现故障，同时也可以在出现问题时，用来辅助排查问题、修复故障，因为MySQL-Server的错误日志是默认开启的，并且无法手动关闭！

一般来说，error-log日志文件默认是在MySQL安装目录下的data文件夹中，但如果你想要改变位置，哪也可以通过log-error这个参数，来手动指定保存的位置与文件名。

如果你不清楚错误日志的位置，也可以通过SHOW VARIABLES LIKE ‘log_error’;命令来查看。

最后稍微提一嘴，如何根据错误日志来排错问题呢？实际上非常简单，在MySQL故障的情况下，打开error-log文件，然后搜索Error、Waiting级别的日志记录，然后参考诊断信息即可。

五、Slow-log慢查询日志

对于线上响应缓慢的问题，一步步的排查过程之后还未找到问题，最终就会来到数据库，尝试对SQL或索引调优，但一个项目中，存在成千上万条SQL，到底是由于哪条SQL造成的响应缓慢，如果一条条去分析，其工作量定然非常吃力，为了排查问题时足够轻松，MySQL官方支持开启慢查询日志。

慢查询日志是什么呢？也就是当一条SQL执行的时间超过规定的阈值后，那么这些耗时的SQL就会被记录在慢查询日志中，当线下出现响应缓慢的问题时，可以直接通过查看慢查询日志定位问题，定位到产生问题的SQL后，再用explain这类工具去生成SQL的执行计划，然后根据生成的执行计划来判断为什么耗时长，是由于没走索引，还是索引失效等情况导致的。

不过对于慢查询SQL的监控，MySQL默认是关闭的，也就是说MySQL默认不会记录慢查询日志，因为为了后续线上问题好排查，项目上线前一定要记得开启！

  • slow_query_log：设置是否开启慢查询日志，默认OFF关闭。

  • slow_query_log_file：指定慢查询日志的存储目录及文件名。

可以通过这两个参数来开启慢查询日志，如果不设置存储目录，默认放在MySQL的具体库的目录下。当开启慢查询日志的监控后，可以通过设置long_query_time参数，来指定查询SQL的阈值：

set global long_query_time = 1; 复制代码

其默认单位是秒，因此如果要指定更细粒度的时间，可以通过0.01这种形式设置，0.01表示10ms。当然，该参数也可不设置，不指定阈值的情况下，默认为10s，即执行时间超过10s的查询SQL才会记录到慢查询日志中。

对于阈值的设置，并不是随咱们率性而为，这个参数一定要设置合理！因为该参数的大小会直接影响MySQL的性能，比如设置一个0.2s，但如果大量业务SQL执行时都会超出该时长，那最终会导致MySQL十分频繁的往慢查询日志中写数据。

要记住：慢查询日志在内存中是没有缓冲区的，也就意味着每次记录慢查询SQL，都必须触发磁盘IO来完成，因此阈值设的太小，容易使得MySQL性能下降；如果设的太大，又会导致无法检测到问题SQL，因此该值一定要设置一个合理值。

问题来了，这个值设成多大合理呢？可以先开启general log，观察后实际的业务情况后再决定。

General-log查询日志

general log即查询日志，MySQL会向其中写入所有收到的查询命令，如select、show等，同时要注意：无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。对于该日志可以通过下述参数开启：

  • general_log：是否开启查询日志，默认OFF关闭。

  • general_log_file：指定查询日志的存储路径和文件名（默认在库的目录下，主机名+.log）。

项目测试阶段，可以先开启查询日志，然后压测所有业务，紧接着再分析日志中SQL的平均耗时，再根据正常的SQL执行时间，设置一个偏大的慢查询阈值即可（这是个笨办法，如果项目规模较大，直接设置一个大概值，然后上灰度发布，走正式的运营场景效果会更佳）。

当然，压测阶段结束后，项目正式上线前，一定要记得关闭普通查询日志！！

六、Relay-log中继日志

relay log在单库中是见不到的，该类型的日志仅存在主从架构中的从机上，主从架构中的从机，其数据基本上都是复制主机bin-log日志同步过来的，而从主机复制过来的bin-log数据放在哪儿呢？也就是放在relay-log日志中，中继日志的作用就跟它的名字一样，仅仅只是作为主从同步数据的“中转站”。

当主机的增量数据被复制到中继日志后，从机的线程会不断从relay-log日志中读取数据并更新自身的数据，relay-log的结构和bin-log一模一样，同样存在一个xx-relaybin.index索引文件，以及多个xx-relaybin.00001、xx-relaybin.00002….数据文件。

对于这个日志的具体参数、工作过程，放在后续的《MySQL高可用-主从读写分离篇》阐述。

七、日志篇总结

叨叨絮絮下来，就大致将MySQL中的一些常见、较为重要的日志讲明白啦，其实重点搞清楚undo-log、redo-log、bin-log即可，其他的会在后续篇章中再次提到，最后稍微总结一下这三个比较核心的日志：

  • undo-log：主要用于实现事务ACID原则中的原子性和MVCC机制。

  • redo-log：主要用于实现事务原则中的持久性，确保事务提交后就不会丢失。

  • bin-log：主要结合redo-log实现事务原则中的一致性，确保事务提交前后，数据的一致。

对于其他几类日志，在本篇中也仅讲明了大概，毕竟后面的章节中会再出现，而对于上述三大日志也就基本上不会提到了，因此剖析的较为全面，那么咱们下篇见~，下面准备讲：《MySQL内存篇》，其实也主要是讲InnoDB Buffer Pool缓冲区，至于为什么半道出家的InnoDB能替换掉官方的MyIASM引擎，最大原因也在于此。