引言

任何项目都会有日志,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引擎,最大原因也在于此。