Mysql【日志系统】

最新推荐文章于 2023-02-18 19:58:29 发布

醒然自然醒

最新推荐文章于 2023-02-18 19:58:29 发布

阅读量487

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_40662405/article/details/125381434

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Mysql 里会接触到三个核心日志分别是

binlog ( server 层)
redo log ( innodb )
undo log ( innodb )

binlog

binlog 是作为mysql操作记录归档的日志，这个日志记录了所有对数据库的数据、表结构、索引等等变更的操作。

mysql 里我们就是通过 binlog 来归档、验证、恢复、同步数据 (主从一致)。

binlog 是逻辑日志。

DML(Data Manipulation Language)数据操纵语言

DDL(Data Definition Language)数据定义语言

binlog 记录了除了查询语句(select、show)之外的所有的 DDL 和 DML 语句。

基本上所有对数据库的操作变更都会记录到binlog里面。

binlog以事件形式记录，不仅记录了操作的语句，同时还记录了语句所执行的消耗的时间。

binlog 有三种记录格式:

1、ROW： 基于变更的数据行进行记录，如果一个update语句修改一百行数据，那么这种模式下就会记录100行对应的记录日志。

**2、STATEMENT：**基于SQL语句级别的记录日志，相对于ROW模式，STATEMENT模式下只会记录这个update 的语句。所以此模式下会非常节省日志空间，也避免着大量的IO操作。

3、MIXED： 混合模式，此模式是ROW模式和STATEMENT模式的混合体，一般的语句修改使用statment格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存binlog。

binlog 写入策略

在进行事务的过程中，首先会把binlog 写入到binlog cache中（因为写入到cache中会比较快，一个事务通常会有多个操作，避免每个操作都直接写磁盘导致性能降低），事务最终提交的时候再吧binlog 写入到磁盘中。当然事务在最终commit的时候binlog是否马上写入到磁盘中是由参数 sync_binlog 配置来决定的。

1、sync_binlog=0 的时候，表示每次提交事务binlog不会马上写入到磁盘，而是先写到page cache,相对于磁盘写入来说写page cache要快得多,不过在Mysql 崩溃的时候会有丢失日志的风险。

2、sync_binlog=1 的时候，表示每次提交事务都会执行 fsync 写入到磁盘；

3、sync_binlog的值大于1 的时候，表示每次提交事务都先写到page cache，只有等到积累了N个事务之后才fsync 写入到磁盘，同样在此设置下Mysql 崩溃的时候会有丢失N个事务日志的风险。

很显然三种模式下，sync_binlog=1 是强一致的选择，选择0或者N的情况下在极端情况下就会有丢失日志的风险，具体选择什么模式还是得看系统对于一致性的要求。

binlog 是追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

kafka log也是追加写入的

MySQL如何判断binlog完整性？

如果binlog是statement模式的，最后面会出现一个commit的标识

use `test`/*!*/;
SET TIMESTAMP=1590418900/*!*/;
insert into test values (1,'zhangsan')
/*!*/;
# at 490
#200525 23:01:40 server id 2025725  end_log_pos 521 
COMMIT/*!*/;

如果binlog是row模式的，最后面会出现一个xid的event事件：

### UPDATE `mysql`.`ha_health_check`
### WHERE
###   @1=1582266682672 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='m' /* STRING(3) meta=65027 nullable=0 is_null=0 */
### SET
###   @1=1582266698557 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='m' /* STRING(3) meta=65027 nullable=0 is_null=0 */
# at 790
#200221 14:31:38 server id 3141372998  end_log_pos 821 CRC32 0xf63b387d         Xid = 1289289427
COMMIT/*!*/;
# at 821
#200221 14:31:44 server id 3141372998  end_log_pos 886 CRC32 0xc01e5aea         GTID    last_committed=2   sequence_number=3

除此之外，MySQL5.6中还引入了binlog checksum的参数，用来确认binlog的正确性，一般情况下，这个参数在主从上的设置应该保持一致，要么都为none，要么都为CRC32

redo log和binlog是如何关联起来的

redo log对于用户是不可见的，如果你强制用vim打开redo log，你会看到一堆乱码。在binlog中，我们可以看到binlog的xid值，这个值就是用来关联redo log和binlog的。

如果碰到既有prepare、又有commit的redo log，就直接提交；
如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务，能找到完整事务，则提交，找不到，则回滚。

为什么prepare、binlog写入之后事务需要提交？不能同时抛弃么？

其实对于主库来讲，redo log和binlog要么同时存在，要么同时回滚，都不影响redo log和binlog的一致性。之所以在redo log prepare阶段完成、binlog写入后让事务提交，本质上还是为了保证主库和从库的一致性。因为binlog一旦写入，会通过dump thread同步给从库，从库会应用这个binlog，那么如果主库上crash之后，将写入的binlog回滚了，就有可能造成主库和从库的数据不一致现象。

只用binlog或者redo log不能支持崩溃恢复么

数据更新到内存—写binlog—提交事务.

这种情况下，如果写完binlog之后MySQL发生了crash，那么内存中的数据页是无法修复的，由于MySQL采用的是WAL技术，也就是先写内存日志再写磁盘，而binlog是没有能力恢复损坏的内存数据页的。

如果只有redo log，那么因为redo log是循环写的，也就没有办法保留很长的周期，失去了binlog归档变更操作的功能。再者主从复制的结构可能会更脆弱，高可用架构也就更谈不上了。

数据落盘是从redo log落盘的还是从buffer pool

其实数据落盘和redo log是没有关系的，redo log本身不记录数据页的完整数据，它只记录数据也的物理变更。

数据的落盘其实是将buffer pool中的脏页刷新到磁盘的过程。所谓的脏页，就是buffer pool中被修改的和磁盘上不一致的数据页。

在MySQL崩溃回复的过程中，如果发现某个数据页可能在崩溃回复的过程中，丢失了更新，就会将这个数据页加载到内存，也就是buffer pool，让redo log更新内存中的内容。更新完成之后，这个数据页就变为脏页，可以刷新回磁盘了。

redo log buffer和redo log的写入顺序

假设有这么一个事务：

begin；

insert xxx 1

insert xxx 2

commit;

在commit之前，需要保存这两个insert产生的redo log，但是又不能直接写入到redo log文件里面，此时这些redo log就先保存在redo log buffer里面，当我们执行commit的时候，才会把redo log写入到iblogfile里面。

所以写入顺序上来讲，redo log buffer先写入，而redo log文件后写入。

redolog

redo log 设计目标是支持innodb的 事务 的特性。

binlog 没有 crash-safe 的能力，binlog 日志只能用于归档。

crash-safe ：数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe

redo log 能保证对于已经COMMIT的事务产生的数据变更，即使是系统宕机崩溃也可以通过它来进行数据重做，达到数据的一致性，这也就是事务持久性的特征，一旦事务成功提交后，只要修改的数据都会进行持久化，不会因为异常、宕机而造成数据错误或丢失。

WAL : Write-Ahead Logging 即当事务提交时，先写重做日志，再修改页，当由于发生宕机而导致数据丢失时，可以通过 redo log 来完成数据的恢复。

有了binlog 为什么还需要 redolog ？为什么 redo log 可以保证数据库宕机之后，还是可以保证数据一致性 ?

redo log记录的数据变更粒度和binlog的数据变更粒度是不一样的，也正因为这个binlog是没有进行崩溃恢复事务数据的能力的。

以修改数据为例，binlog 是以表为记录主体，在ROW模式下，binlog保存的表的每行变更记录。

redo log则是记录着磁盘数据的变更日志，以磁盘的最小单位“页” ( 4K ) 来进行记录。

当我们把数据从内存保存到磁盘的过程中，Mysql是以页为单位进行刷盘的，这里的页并不是磁盘的页，而是Mysql自己的单位，Mysql里的一页数据单位为16K，所以在刷盘的过程中需要把数据刷新到磁盘的多个扇区中去。

把16K数据刷到磁盘的每个扇区里这个过程是无法保证原子性的，也就意味着Mysql把数据从内存刷到磁盘的过程中，如果数据库宕机，那么就可能会造成一步分数据成功，一部分数据失败的结果。而这个时候通过binlog这种级别的日志是无法恢复的，一个update可能更改了多个磁盘区域的数据。

需要通过redo log这种记录到磁盘数据级别的日志进行数据恢复。

redo log写入策略

redo lo占用的空间是一定的，并不会无限增大— checkPoint 技术。写入的时候是进顺序写的，所以写入的性能比较高。当redo log空间满了之后又会从头开始以循环的方式进行覆盖式的写入。

在写入redo log的时候也有一个redo log buffer，日志什么时候会刷到磁盘是通过innodb_flush_log_at_trx_commit 参数决定。

innodb_flush_log_at_trx_commit=0 ，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
innodb_flush_log_at_trx_commit=1，表示每次事务提交时都将 redo log 直接持久化到磁盘；
innodb_flush_log_at_trx_commit=2，表示每次事务提交时都只是把 redo log 写到 page cache。

checkPoint

如果 redo log 可以无限增大，磁盘也足够大，缓存也足够大，能够缓存所有数据，那么就不需要将缓存中的脏页频繁刷新。

checkpoint：将缓存中的脏页刷回磁盘

checkpoint 的作用：

缩短数据库的恢复时间

当数据库宕机时，数据库不需要重做所有日志，因为CheckPoint之前的页都已经刷新回磁盘。只需对CheckPoint后的重做日志进行恢复，从而缩短恢复时间。
缓冲池不够用时，将脏页刷新到磁盘

当缓存池不够用时，LRU算法会溢出最近最少使用的页，若此页为脏页，会强制执行CheckPoint，将该脏页刷回磁盘。
重做日志不可用时，刷新脏页

checkpoint 种类：

Sharp CheckPoint

发生在数据库关闭时，会将所有的脏页刷回磁盘

Fuzzy CheckPoint

为提高性能，数据库运行时使用Fuzzy CheckPoint进行页的刷新，即只刷新一部分脏页

Fuzzy CheckPoint 四大类
- Master Thread CheckPoint
  
  每秒或每十秒的速度，从缓存池脏页列表中刷新一定比例的页，且此过程是异步的，因此不会阻塞其他操作。
- FLUSH_LRU_LIST CheckPoint
  
  Page Cleaner线程中进行，为了保证有足够的空闲页可以写入 redo log，将LRU 列表尾部的页移除，若为脏页，刷新回磁盘。
- Async/ Sync Flush CheckPoint
  
  Page Cleaner线程进行，也是为了保证足够的空闲页可以写入 redo log。
  - 脏页较多：sync 同步刷入磁盘
  - 脏页较少：async 异步刷入磁盘
- Dirty Page too much CheckPoint

脏页比例太多，还是为了保证缓冲池中有足够可用的页。由数innodb_max_dirty_pages_pct控制。若该值为75，表示当缓冲池中脏页占据75%时，强制CheckPoint

redo log 刷新到磁盘的时机：

flush_log_at_trix_commit 参数为1时，事务提交，直接将 redo log 持久化到磁盘。

定时刷新脏页、LRU 刷新脏页、异步同步刷新、脏页超过比例刷新。

两阶段提交

redo log 的写入拆成了两步: prepare commit 这就是 两阶段提交

如果不使用两阶段提交，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

为什么需要两阶段提交?

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

redolog 和 binlog 具有关联行，在恢复数据时，redolog用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。

每个阶段的log操作都是记录在磁盘的，在恢复数据时，redolog 状态为commit则说明binlog也成功，直接恢复数据；

如果redolog是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。

如何判断 binlog 和 redolog是否达成了一致

当MySQL写完redolog并将它标记为prepare状态时，并且会在redolog中记录一个XID，它全局唯一的标识着这个事务。

binlog结束的位置上也有一个XID。

只要这个XID和redolog中记录的XID是一致的，MySQL就会认为binlog和redolog逻辑上一致。就上面的场景来说就会commit，而如果仅仅是redo log中记录了XID，binlog中没有，MySQL就会RollBack。

undo log

undo log 保存的是数据的历史版本，实现了事务的回滚功能。是事务原子性的体现。

还是实现 MVCC 多版本并发控制的基础。

表格的隐藏列

DB_TRX_ID: 记录操作该数据事务的事务ID；

**DB_ROLL_PTR：**指向上一个版本数据在undo log 里的位置指针；

DB_ROW_ID: 隐藏ID ，当创建表没有合适的索引（唯一索引）作为聚集索引时，会用该隐藏ID创建聚集索引;

redo、undo、binlog的生成流程与崩溃恢复

当我们执行update user_info set name =“李四”where id=1 的时候大致流程如下：

1、从磁盘读取到id=1的记录，放到内存。

2、记录undo log 日志。

3、记录redo log (prepare 状态)

4、修改内存中的记录。

5、记录binlog

6、提交事务，写入redo log (commit状态)

学习资料：

极客时间《Mysql 45 讲》

数据库系列

醒然自然醒

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql【日志系统】

Mysql 里会接触到三个核心日志分别是binlog 是作为mysql操作记录归档的日志，这个日志记录了所有对数据库的数据、表结构、索引等等变更的操作。mysql 里我们就是通过 binlog 来归档、验证、恢复、同步数据 (主从一致)。binlog 是。DML(Data Manipulation Language)数据操纵语言DDL(Data Definition Language)数据定义语言binlog 记录了除了查询语句(select、show)之外的所有的和语句。基本上所有对数据库的操作变
复制链接

扫一扫