开发需要懂的MYSQL-日志机制整理

二楼
456 阅读10分钟

binlog

binlog指二进制日志，它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中，它可以用来查看数据库的变更历史、数据库增量备份和恢复、MySQL的复制（主从数据库的复制）

  1. Binlog文件名默认为“主机名_binlog-序列号”格式
  2. binlog文件大小,保留时间可以配置 
    ## 打开my.cnf配置文件
[root@localhost mysql]# vim /etc/my.cnf
## [mysqld]模块下添加：max_binlog_size
[mysqld]
expire_logs_days = 30
max_binlog_size = 500M
  3. binlog_cache,默认为每个session分配32K的cache内存空间,可以通过binlog_cache_size调整
  4. binlog_cache临时文件 binlog_cache溢出的时候使用,max_binlog_cache_size调整大小
  5. 存储格式
    1. statement：基于SQL语句的赋值。只记录SQL，不记录数据变更，日志文件比较小，能够节约网络和磁盘IO，但是准确性不高，对一些系统函数，比如now()，不能准确复制。
    2. row：基于行的变更。不记录SQL，记录每行实际数据的变更，准确性比较高，但是由于记录了数据变更，所以日志文件较大，相对于statement有更高的网络和磁盘IO，通常建议使用这个级别。(mysql5.7以上版本默认使用row)
    3. mixed：基于statement和row的混合模式。默认使用statement，statement无法复制的操作则使用row。可能发生binlog丢失，导致主从不一致

redolog

redolog是InnoDB引擎实现的,WAL(Write-ahead logging,预写式日志),所有的修改都先被写入到日志中,然后再被应用到系统中

  1. redolog是物理日志
  2. redolog buffer: 数据库启动时申请一个redolog buffer的连续内存空间,默认16M,InnoDB为了更好的进行系统崩溃恢复,把redo日志都放在了大小为512字节的块(block)中,块内部结构上网查吧,一次数据的原子操作可能涉及多个聚簇索引,二级索引等修改,所以要以组提交,要么都修改,要么都不修改
  3. 日志文件组: 默认ib_logfile0和ib_logfile1,从redolog buffer中顺序写入文件,0写满了写1,1满了写0,所以文件大小限制太小会频繁切换文件,影响性能
  4. mysql8.0 对redolog的优化,很将锁粒度减小blog.csdn.net/weixin_3519…

redolog保证数据的持久性

数据的持久性主要体现在及时落盘和崩溃恢复

LSN - 日志定位号

InnoDB中存储了几种LSN

  • log sequence number: 代表当前的重做日志redo log在内存中的LSN,存在redolog(内存)
  • log flushed up to: 代表刷到redo log file on disk中的LSN,存在redolog(磁盘)
  • pages flushed up to: 代表已经刷到磁盘数据页上的LSN,即flush链表中被最早修改的那个页面对应的oldest_modification属性值,存在数据页面(内存与磁盘)
  • last checkpoint at: 代表上一次检查点所在位置的LSN(数据库恢复的时候,如果和pages flushed up to一样,说明上次是正常关闭)，存在redo-log(内存与磁盘)

checkpoint机制

  • Sharp checkpoint: 发生在关闭数据库时,将所有脏页刷回磁盘
  • Fuzzy checkpoint：进行部分脏页的刷新,有效循环利用redo日志
    1. flush_lru_list: buffer pool的LRU列表需要保留一定的空闲,空闲不够了就触发,阈值可调,在Page Cleaner Thread中异步进行
    2. dirty Page: 脏页占比太高了会触发,阈值可调,默认75%,在Page Cleaner Thread中异步进行
    3. Master Thread以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，不会阻塞查询线程。
    4. redolog满了,Async/Sync Flush Checkpoint，阻塞住刷脏页

流程

上面一堆文字是概念,从流程上梳理一下

  1. 事务开启,执行器想要修改一条数据
  2. 去InnoDB的buffer pool里找这条数据，发现没有，从磁盘中把数据页取出放到buffer pool里，这里放到LRU链表里，同时增加一条undolog日志，回滚的时候用
  3. 更新buffer pool里的数据页，此时数据页变成脏页，计算出新的LSN，数据页控制块写入新的LSN值
  4. 向redolog buffer追加一组MTR(包含数据页的修改,索引的修改等等)，也写入新的LSN值,其实这里3和4步谁先谁后原理上讲无所谓，都在内存里刚开始折腾，这时候断电一点影响没有，反正MYSQL是先page后redolog
  5. 此时事务还未结束，如果innodb_flush_log_at_trx_commit = 0，redologbuffer可能就已经被Log Thread开始写入OsCache并fsync了，当然即使不是这个配置,redolog(in buffer)也可能被其他事务的刷盘顺带刷到磁盘里
  6. 此时事务结束，如果innodb_flush_log_at_trx_commit = 1，Thread Log就开始把redolog(in buffer)写入OsCache，并刷入disk
  7. 不管redolog(in buffer)开始落盘没有，data page早就已经实打实的变成了脏页，Fuzzy checkpoint随时会把脏页刷磁盘里
  8. 这时候如果checkpoint不小心把还没提交的脏页给落盘了，咋办呢，不着急，咱还有undolog，再用它把数据滚回来，所以undolog其实不是为mvcc而生的，是为了跟redolog配合保证数据持久性和原子性，从这也能看出来，undolog也必须持久化，不然此时断电，重启后未提交的脏数据已经落盘，再也回不去了
  9. 如果这时候checkpoint把一些已提交flush链表里的脏页落盘了(它肯定是从最老的脏页开始刷)，这时候就开始LSN如何保证持久性的问题

    1. 首先要明白，有4种LSN

      • redolog(in buffer)记录了redolog LSN(LSN1)
      • redolog(in disk)记录了redolog 刷到磁盘的LSN(LSN2)，这个就是write position
      • flush链表尾部节点记录了page LSN(LSN3)，小于这个LSN的脏页都已经刷盘
      • redolog(in disk)记录了checkpoint(LSN4),小于这个LSN的脏页都已经刷盘 即LSN1 >= LSN2 >= LSN3 >=LSN4

    2. 实际假设一下场景

      • 假设一开始buffer pool里空空如也，这时候有个任务来了，执行一个事务，更新1条数据，LRU链表中就会有1个page，把这个page变为脏页，flush链表尾部节点LSN3=1，redolog里也记录了最新LSN1=1redo1.png 此时redolog(in buffer)刷disk,LSN1 = 1 LSN2 = 1 LSN3 = 1,LSN4 = 0, LSN4->LSN2中间代表还没有刷盘的脏页
      • 随着时间的推移，flush链表里的脏页增多，redolog日志落盘加快，LSN2会逐渐追上checkpoint(LSN4)，一旦追上了，redolog disk就不可写了，业务就要阻塞，所以Fuzzy checkpoint机制就是各种机会去刷新脏页，更新checkpoint(LSN4)，留下LSN2->LSN4中间的空白可以写redolog
      • 当断电/地震/核爆炸/AI统治地球导致数据库崩溃的时候，checkpoint(LSN4)->LSN2中间这段就代表了写了redolog还没刷脏页的，需要恢复，所以redolog落盘越及时，可恢复的数据就越完整，innodb_flush_log_at_trx_commit = 1的情况下可以保证崩溃恢复时数据完全不丢失.

redolog保证数据的原子性

事务的原子性就是整个事务要么整个提交,要么全部回滚,不存在中间状态

这里就需要redolog和undolog的配合,上文提到过了，另外redolog以组的方式提交buffer也在另一个层面保证了原子性.

redolog + 两次写 保证脏页刷盘不损坏

关于IO的最小单位：

  1. 数据库IO的最小单位是16K(MySQL默认，oracle是8K)
  2. 文件系统IO的最小单位是4K(也有1K的)
  3. 磁盘IO的最小单位是512字节 因此，存在IO写入导致page损坏的风险

流程图: double.png

  1. checkpoint刷脏页时，不直接写入磁盘，而是先写入double write buffer
  2. 接着从double write buffer写入共享表空间，每次写1M，这个是连续写，所以效率很高
  3. 第2步完成后，脏页真正离散的刷到磁盘里，把对应的double write区域标记为可覆盖
  4. 此时脏页才是真刷盘了，结合上文，可以更新checkpoint LSN(LSN4)

怎么保证数据不丢失:

  1. 刷脏页时，还没写入共享表空间时崩溃，数据页还没写入，可以用redolog恢复
  2. 刷脏页时，共享表空间写一半崩溃了，数据页还没写入，可以用redolog恢复
  3. 刷脏页时，数据页写一半崩溃了，可以用共享表空间恢复

redolog为啥不用两次写

因为redolog的写入单位是512字节，已经是磁盘IO的最小单位了

通过两次写参数判断数据库写入压力

观察doublewrite运行情况：

SHOW GLOBAL STATUS LIKE 'innodb_dblwr%' 
Innodb_dblwr_pages_written 写到共享表空间的页数
Innodb_dblwr_writes 写到数据文件的次数

因为写入共享表空间时，每次最多可写1M(一个区的大小)，如果写入的页数每次都是满的即64页，则 Innodb_dblwr_pages_written/Innodb_dblwr_writes = 64，所以如果这个比例远小于64，说明数据库的写入压力不大

两阶段提交 - redolog保证和binlog数据一致

经过上文的分析，redolog + undolog自己完全可以实现保证数据不丢失，为啥还要搞这个两阶段提交呢？

因为mysql在大部分真实环境中，是要做高可用的，起码整个读写分离吧，不管是异步复制，半同步复制还是全同步复制，都是基于binlog的，所以光redolog保证数据的持久性还不行，redolog要和binlog也能保持一直，才能保证数据库主从一致。

所以本质上两阶段提交是由单独服务的数据不丢失，升级为还要保证分布式服务的数据一致性。

这是画的一张两阶段提交(2PC)的流程图 2pc.png

双一设置 - 保证数据持久性和分布式一致性

  1. innodb_flush_log_at_trx_commit = 1 redolog MTR每次产生都写入OsCache并fsync刷盘 redo.jpg
  2. sync_binlog = 1 跟上个配置同理，binlog直接fsync写入磁盘

经过双一配置，崩溃恢复的时候：

  1. 如果redolog有prepare部分，对应binlog不存在或不完整，这个事务就算崩溃时没有完成
  2. 如果redolog有prepare部分，binlog存在且完整(完整是会有结尾标识的)，就算最后没有redolog的commit, 为了保证和binlog一致，这个事务也算提交了

undolog

undolog用于回滚数据,是一个逻辑记录,记录的是相反的sql语句 undolog可以单独存储,也可以跟数据文件在一起,看配置

buffer pool

这里顺便总结一些MYSQL buffer pool的概念

  1. buffer pool的目的是降低磁盘访问
  2. buffer pool以页为单位存储数据，默认一页16k，共128M,InnoDB存储引擎中可以设置多个Buffer Pool，而每个Buffer Pool是由多个chunk（默认128M）组成
  3. 淘汰算法: 变种LRU，将LRU分为新生代和老年代，新加载的优先进入老年代，经过读取并停留时间大于一定时间，才能进入新生代，当缓存空间不够淘汰时优先从老年代淘汰
  4. free双向链表，节点是空闲的数据页控制块
  5. LRU双向链表，节点是使用中的数据页控制块
  6. flush双向链表，节点是已经成为脏页的数据页控制块，所以LRU包含flush链表
avatar
文章
阅读
粉丝