MySQL日志详解,主要讲解bin log,redo log,undo log日志,还有buffer pool缓冲池原理,包括一条SQL语句怎么执行的,执行经过了哪些操作。
第一章、MySQL基础架构第二章、buffer pool缓冲池详解第三章、MySQL日志详解第四章、Hash表和B+Tree详解 第五章、全面解析MySQL索引
第六章、InnoDB引擎详解
第七章、MySQL事务的脏读,不可重复读,幻读
第八章、关于MySQL各种锁的详解
binLog日志
- 前言
- binLog日志
- redoLog日志
- 关于Bin Log和Redo Log的一些思考
- 数据恢复流程
- undoLog日志
前言
MySQL定义日志的一个原则,都是贯彻能使用内存,就尽量使用内存。
Mysql的日志分为错误日志,二进制日志,查询日志和慢查询日志
错误日志,服务运行过程中发生的严重错误日志,数据库无法启动时,就可以看看具体不能启动的原因是什么
查询日志,记录来自客户端的所有语句
二进制日志,BinLog,记录事务提交的执行日志
慢查询日志,记录所有响应时间超过阀值的SQL语句,默认是关闭状态,需要手动打开,参数为long_query_time
,默认值是10s
binLog日志
什么是binLog日志?
BinLog
即二进制日志,也叫逻辑日志,归档日志,主要记录了引起数据库改变事件,更新表结构,更新表数据(除了查询语句不记录),之所以叫归档日志,是因为binLog
不会像redoLog
那样擦除记录,而是追加写入,追加写入指的是binLog
写到单个日志的最大值(默认1G,通过变量max_binlog_size
设置),就会另起一个binLog
文件记录。
binLog
每一个binLog记录的是一个完整的事务,因此可能存在当前文件已经达到1G,而日志还在写入,此时不会换一个文件,而是继续写入到事务完成,所以会存在定义的max_binlog_size
与binLog
日志大小不一致的情况`
binLog
是在service层实现的,因此跟引擎不存在关系。binLog
只记录事务提交的更新SQL语句,其他事务回滚的不做记录
binLog日志存储的内容
- 日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件,简单说,记录的就是原始语句,如
-- 这种
insert into t value("张三",20);
- 索引文件(文件名后缀为.index)用于记录哪些日志文件正在被使用
binLog的主要作用
- 数据库数据的恢复或者备份,不小心删库了
- 主从数据库的数据同步,主库上有一个
log dump
线程,会将binLog
发送给从库。从库有两个线程,一个I/O线程,一个SQL线程,I/O线程读取主库传过来的binlog
内容并写入到relay log
,SQL线程从relay log
里面读取内容,写入从库的数据库。
binLog 为什么不具备 crash safe功能?
binLog
是在事务提交之后写入,存在场景,将字段A从1改为2,当事务提交数据更新之后,将要写入binLog
的时候,数据库崩了。所以当前数据库的数据就变成A = 2,但是binLog
的日志,并没有 “将A=1改为2” 这条SQL。所以当拿binLog
去做主从数据同步的时候,就会造成主从数据不一致的问题。
所以binLog
不具备灾后恢复功能,是因为从设计上就不支持,场景没有考虑进去。
Bin Log的写入机制
Bin Log在事务执行过程中,先把日志写入到binlog cache
中,事务提交的时候,再把binlog cache
写到binlog
文件中,并清空binlog cache
。
一个事务的BinLog
是不能被拆开的,因此不论事务多大,都要确保一次性写入。系统给每一个binlog cache
分配了一块内存,每个线程是一个,参数binlog_cache_size
用于控制内存的大小,如果超过这个大小,就要暂存在磁盘上。
数据存储在binlog cache上,什么时候进行刷盘操作呢?
刷盘也叫写入磁盘(fsync),主要操作的参数有两个
binlog_cache_size: 二进制日志缓存的大小,默认值32k
sync_binlog: 表示写缓冲多少次,刷一次盘,默认值为0,即由操作系统决定每隔一段时间刷盘
写入磁盘的操作主要由sync_binlog
控制
-
sync_binlog = 0
的时候,表示每次提交事务都只写入binlog
文件中,刷盘由操作系统决定每隔一段时间进行,性能最好 -
sync_binlog = 1
的时候,表示每次提交事务都写入到磁盘中,保证持久性,但是性能最差,一般不建议 -
sync_binlog = N (N>1)
的时候,表示每次提交事务都写入binlog文件中,当事务达到N的时候,再写入到磁盘中。但是如果存在异常宕机,可能会导致数据丢失,因为内存数据没有了。
除了这个设置,被动刷盘也有,内存不足,其他事务提交,也会导致被动刷盘操作。
sync_binlog设置多少最好?
默认是0,隔一段时间进行刷盘,但是如果出现IO瓶颈的情况下,可以设置sync_binlog = N,指定事务到达多少再进行刷盘操作,优点是,能够提升性能,但是考虑实际的场景,不允许设置过大,常见设置100-1000中某个值。因为一旦主机异常重启的情况,可能会丢失最近N个事务的binlog
日志
crash safe(灾后恢复能力)
出现前景:
binLog
日志能够在事务提交,保存修改数据库数据的语句到日志中。但是不能保证,在提交前服务器宕机等极端情况下出现的数据丢失。而binLog
又是作为主从同步的重要日志,就会出现主从数据不一致的情况
解决
InnoDB通过redoLog
日志,保证了crash safe
,而为了保证数据的一致性,使用了两阶段式提交
redoLog日志
先考虑一个问题,有了binLog,为什么还需要redoLog呢?
看了crash safe
的解释,心里大概有个底了,哦,redoLog
的出现,是InnoDB存储引擎为了实现灾后恢复特有的日志,结合binLog
一起使用,同时使用两阶段式提交,保证了数据的一致性。接下来,讨论redoLog
日志概念
redoLog日志概念
前面说过了,MySQL的原则尽可能使用内存,减少磁盘IO的读写操作,同样,redoLog
也遵循这个操作,也就是说,MySQL中,更新操作不会马上就写入到磁盘中,而是先写入到redoLog (redoLog buffer)
之中,并更新到内存(buffer pool)
中,更新操作就完成了,注意:更新操作并不会马上刷入到磁盘中。而是InnoDB会在适当的时候(系统空闲的时候),将内存中的数据更新到磁盘中。(刷脏页操作)
redoLog日志存储内容
redoLog
是InnoDB存储引擎特有的日志,又称为重做日志文件;redoLog是循环写日志,记录的是数据页的物理修改,而不是某一行或者某几行修改成怎么样。可以用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置)
redoLog日志写入机制
redoLog的大小是固定的,redolog采用循环写的方式记录,当写到结尾时,会回到开头循环写日志
将redo Log看做一个固定大小的块时,
write pos
表示当前记录的位置
check point
表示将日志记录的修改写进磁盘,完成数据落盘,数据落盘后checkpoint会将日志上的相关记录擦除掉,
即write pos->checkpoint
之间的部分是redo log空着的部分,用于记录新的记录,checkpoint->write pos
之间是redo log待落盘的数据修改记录。当writepos追上checkpoint时,得先停下记录,先推动checkpoint向前移动,空出位置记录新的日志。
这里的擦除完成是:将内存中的数据写入到磁盘中
redo Log的有关的几个参数
innodb_log_files_in_group: # 日志文件数量默认2个
innodb_log_file_size # 每个日志文件大小,默认是5M
# 为了控制redo log的写入策略,持久化到磁盘的设置有三种:
innodb_flush_log_at_trx_commit
# 0表示每次事务提交时都只是把redo log留在redo log buffer中;
# 默认是1,表示每次事务的redo log都直接持久化到磁盘,这样可以保证MySQL异常重启之后数据不丢失。性能最差
# 2表示每次事务提交时都只是把redo log写到page cache。
注意,持久化到磁盘操作,是不能再接收新的更新请求,所以有可能会导致MySQL卡顿,因此设置一个合理的日志文件大小是非常重要的!
redo log持久化到磁盘操作
默认后台InnoDB有一个后台线程,每隔1秒,就会把redo log buffer中的日志,调用write写到文件系统的page cache,然后调用fsync持久化到磁盘。 除此之外还有另外两种
redo log buffer
占用的空间即将达到innoDB_log_buffer_size
一半的时候,后台线程会主动写盘。
由于这个事务没有提交,所以这个写盘动作只是write
,而没有调用fsync
,也就是只留在了文件系统的page cache
。- 并行事务提交的时候,顺便把这个事务的redo log buffer持久化到磁盘。
假设一个事务A执行到一半,已经写了一些redo log到buffer上,这时候另外一个线程的事务B提交了,如果innoDB_flush_log_at_trx_commit
设置的是1,那么按照这个参数的逻辑,事务B要把redo log buffer的日志全部持久化到磁盘,也就是说,会把事务A已经写到buffer里的日志也持久化到磁盘上。
双1操作
如果innodb_flush_log_at_trx_commit
设置成1,那么redo log在prepare阶段就要持久化一次,因为有个崩溃恢复逻辑是依赖prepare阶段的redo和binlog来恢复的。
所以也就是说,在prepare阶段持久化到磁盘,redo log buffer
的数据就清空了。在commit阶段的redo log,write的时候,只是写入到page cache中。
只有等到每秒刷盘的时候,才会真正fsync
到磁盘中。
而双1操作,指的就是sync_binlog
和innodb_flush_log_at_trx_commit
都设置成 1。也就是说,一个事务完整提交前,需要等待两次刷盘,一次是redo log(prepare 阶段),一次是binlog。
redoLog的好处
- 最重要的保证了灾后恢复的能力
- 将随机写IO,改为顺序写IO,提高了性能,提高并发量
redo Log刷盘机制
MySQL利用WAL机制(先写日志,再写磁盘),将随机写IO改为顺序写IO,提升了数据库性能的同时,也带来了内存脏页的问题。
脏页:当内存数据页和磁盘数据页不一致的时候,将内存数据页称为脏页
干净页:内存数据写入到磁盘后,内存和磁盘上的数据页数据一致了,这种称之为干净页
有时候MySQL执行更新操作,感觉变慢了,其实是在执行刷脏页(flush)操作
触发数据库的flush操作有
- 内存(redo Log)满了,再有数据过来,系统需要停止所有更新操作,然后将内存的数据页写入到磁盘中。
- 内存满了,需要新的内存页的时候,如果有干净页,就直接写干净页,如果没有,那么就执行刷脏页操作;那么可以不执行写入内存操作,直接写入磁盘吗???不太理解
- 读取的时候,内存存在,直接返回内存
- 读取的时候,内存不存在,redo Log文件是正确的数据,读入内存后返回
- MySQL认为系统空闲的时候,就会系统的进行刷脏页操作
- MySQL正常关闭的时候,会将所有的脏页刷入到磁盘中
InnoDB用缓存池(buffer pool)管理内存,缓冲池中的内存页有三种状态
- 还没有使用的数据页
- 使用了但是数据已经刷入磁盘的干净页
- 使用了但是数据还存在的脏页
InnoDB的策略是尽可能使用内存,减少磁盘IO的读写操作,但是对于一个长时间运行的库来说,没有被使用的页很少,也就是内存的意思。
当要读入的数据页没有内存时,向缓冲池申请一个数据页。有几种情况
- 最久没有被使用的数据页从内存淘汰掉,如果淘汰的是一个干净页,就直接释放出来使用
- 如果是脏页的话,刷到磁盘中变成干净页再使用
刷脏页如果出现以下两种情况,则需要优化
- 一个查询如果要淘汰的脏页个数太多,会导致查询的响应时间变长
- 日志写满,更新堵塞,这种情况对于敏感业务时无法接受的。
bin Log 和redo Log 的区别
- Bin Log是Mysql的Service层实现的,所有引擎都可以使用;Redo Log是InnoDB引擎特有的
- Bin Log是逻辑日志,记录这个语句的原始逻辑,如:“ID = 2 这一行的 C 字段加1”;Redo Log是物理日志,记录的是,“在某个数据页做了什么修改”
- Bin Log是追加写,写到一定大小切换下一个,不会覆盖以前的;Redo Log是循环写,空间固定会用完,通过write pos和checkpoint来维持循环写入。
- Bin Log 不支持灾后恢复,Redo Log主要用于灾后恢复。
执行器和InnoDB引擎处理 binLog和redoLog的操作
二阶段式提交的操作
- 执行器先找引擎取ID=2这一行。ID是主键,引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。
- 执行器拿到引擎给的行数据,把这个值加上1,比如原来是N,现在就是N+1,得到新的一行数据,再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中(
InnoDB buffer pool
),同时将这个更新操作记录到redo log
里面,此时redo log
处于prepare
状态。然后告知执行器执行完成了,随时可以提交事务。 - 执行器生成这个操作的
binlog
,并把binlog
写入磁盘。 - 执行器调用引擎的提交事务接口,引擎把刚刚写入的
redo log
改成提交(commit
)状态,更新完成。
深色的表示执行器,浅色的表示引擎
关于Bin Log和Redo Log的一些思考
为什么Bin Log和Redo Log要分两阶段式提交?
因为Bin Log和Redo Log是两个独立的逻辑,如果不用两阶段式提交的话,数据备份恢复的时候,会出现两个库数据不一致的情况。
如:
先写Bin Log,再写Redo Log的情况,将C从0改为1,Bin Log写入之后宕机,因为Redo Log还没写入,宕机后恢复,RedoLog记录C的值为0,但是Bin Log已经记录了该值为 “把C从0改为1的情况”,所以这时候如果将Bin Log进行备份的话,那么临时库读取到该数据,C的值就变成了1,与原库的值是0,数据不一致。
先写Redo Log,再写Bin Log的情况,将C从0改为1,Redo Log写完之后,Bin Log还没写完宕机,通过Redo Log恢复日志,所以恢复后的C的值是1,但是,Bin Log还没写完就宕机了,所以Bin Log并不存在这条 "将C从0改为1"的语句,所以如果将Bin Log作为备份的话,那么临时库读取到该数据,此时C的值是0,原库的值是1,数据不一致。
两阶段式提交的作用,是让BinLog日志和Redo Log日志保持逻辑上的一致
另外小知识点:需要学习
高并发情况下,两阶段式提交顺序不一样,会导致主从数据的不一致,早期版本使用 prepare_commit_mutex 加锁解决,一个事务获取到锁才能进入prepare阶段,高并发场景下,争夺锁影响到性能。
5.6版本之后,使用组提交方式BLGC。
数据恢复流程
事务提交的过程中,MySQL进程突然崩溃的情况,重启后怎么保证数据不丢失?
- 读取redo log的记录
- 先恢复内存,从checkpoint开始,重新在内存中执行对数据页的修改?
- 检查redo log哪些事务是完整的并且处于prepare阶段
- 根据XID对照binlog的事务
- 是否完整事务,是的话,重新commit redo log 完成事务提交
- 不是完整事务,根据XID找到undo log的事务进行回滚。
简单说:redolog 是否回滚在于,在于XID是否能够在binlog找到commit标志的事务,如果找不到,则事务未提交通过undo log文件回滚即可。
undoLog日志
反向操作,当删除一条数据的时候,记录的是insert一条数据;当修改一条数据的时候,记录的是删除后新增的操作。这样,当事务进行回滚的时候,就可以从undo Log日志中读取相应的内容,进行回滚操作。同时也可以根据 undo Log日志,获取到一条被修改后的数据,方便问题定位
总结:
日志名称 | Bin Log | Redo Log | Undo Log |
出现场景 | 事务提交的操作 | 存在服务器宕机的情况,导致Bin Log记录不到数据,主从同步数据不一致 | 事务回滚之后,数据的还原操作 |
彼此的作用. | 保存事务提交的每次修改数据库的操作,select和show不保存 | 每次执行修改操作,都会保存执行语句到日志中,用于宕机后数据的恢复 | 保存事务回滚所需要的执行语句,如delete,在该日志中就是insert,反向记录记录数据。 |