引言
作者:竹子爱熊猫
任何项目都会有日志,MySQL
也不例外,而且MySQL
更是其中的佼佼者,日志种类繁多,而本篇的目的就是全解MySQL
中的各类日志,如撤销日志、错误日志、慢查询日志、中继日志、回滚日志.....
其实日志的作用不言而喻,无论是线上排查,亦或是性能优化,几乎都需要从日志中来获得信息作为依据,而MySQL
中,很多很多的功能也都需要基于日志实现,比如事务回滚、数据持久化、数据恢复、数据迁移、MVCC
机制.....,因此本篇去阐述日志,也是为了方便撰写后续的其他文章。
OK,废话不多说了,咱们现在就开始吧~
一、Undo-log撤销日志
Undo
即撤销的意思,但咱们通常也习惯称它为回滚日志,在日常开发过程中,如果代码敲错了,一般会习惯性的按下Ctrl+Z
撤销,而Undo-log
的作用也是如此,但它是用来给MySQL
撤销SQL
操作的。
在之前的《SQL执行篇》[1]中曾聊到过,当一条写入类型的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机制原理剖析》[2]的小伙伴对于这点并不陌生,Undo-log
中记录的旧数据并不仅仅只有一条,一条相同的行数据可能存在多条不同版本的Undo
记录,内部会通过roll_ptr
回滚指针,组成一个单向链表,而这个链表则被称之为Undo
版本链,案例如下:
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事务篇》[3]中聊到的数据恢复机制嘛?
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执行时的日志操作》[4]中简单的提到过:
简单来说就是刷盘的时机由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
问题有关,具体的可以参考:《这篇文章》[5]。 -
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
的设计,就类似于咱们之前讲《并发编程》[6]时讲过的《ThreadLocal线程变量副本》[7]。
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 |
+
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
语句,而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了。
这又怎么理解呢?还是以前面的重置密码的例子来说:
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.3、为什么有了Bin-log还需要Redo-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.4、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.5、不小心删库后应该跑路吗?
首先来说一下,为啥要讨论这个问题呢,这是由于之前《MySQL架构篇》[8]的评论区的一位小伙伴提出的:
这里有两个问题:①删库后跑路会不会被人发现?②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.6、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.7、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
引擎,最大原因也在于此。