MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43046 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

23 | MySQL是怎么保证数据不丢的?

林晓斌 2019-01-04
今天这篇文章,我会继续和你介绍在业务高峰期临时提升性能的方法。从文章标题“MySQL 是怎么保证数据不丢的?”,你就可以看出来,今天我和你介绍的方法,跟数据的可靠性有关。
在专栏前面文章和答疑篇中,我都着重介绍了 WAL 机制(你可以再回顾下第 2 篇第 9 篇第 12 篇第 15 篇文章中的相关内容),得到的结论是:只要 redo log 和 binlog 保证持久化到磁盘,就能确保 MySQL 异常重启后,数据可以恢复。
评论区有同学又继续追问,redo log 的写入流程是怎么样的,如何保证 redo log 真实地写入了磁盘。那么今天,我们就再一起看看 MySQL 写入 binlog 和 redo log 的流程。

binlog 的写入机制

其实,binlog 的写入逻辑比较简单:事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。
一个事务的 binlog 是不能被拆开的,因此不论这个事务多大,也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。
系统给 binlog cache 分配了一片内存,每个线程一个,参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小,就要暂存到磁盘。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(95)

  • 锅子 置顶
    老师好,有一个疑问:当设置sync_binlog=0时,每次commit都只时write到page cache,并不会fsync。但是做实验时binlog文件中还是会有记录,这是什么原因呢?是不是后台线程每秒一次的轮询也会将binlog cache持久化到磁盘?还是有其他的参数控制呢?

    作者回复: 你看到的“binlog的记录”,也是从page cache读的哦。
    Page cache是操作系统文件系统上的😄

    好问题

    2019-01-04
    4
    19
  • 倪大人
    老师求解sync_binlog和binlog_group_commit_sync_no_delay_count这两个参数区别

    如果
           sync_binlog = N
           binlog_group_commit_sync_no_delay_count = M
           binlog_group_commit_sync_delay = 很大值
    这种情况fsync什么时候发生呀,min(N,M)吗?
    感觉sync_binlog搭配binlog_group_commit_sync_delay也可以实现组提交?

    如果
            sync_binlog = 0
             binlog_group_commit_sync_no_delay_count = 10
    这种情况下是累计10个事务fsync一次?

    作者回复: 好问题,我写这篇文章的时候也为了这个问题去翻了代码,是这样的:
    达到N次以后,可以刷盘了,然后再进入(sync_delay和no_delay_count)这个逻辑;

    Sync_delay如果很大,就达到no_delay_count才刷;

    只要sync_binlog=0,也会有前面的等待逻辑,但是等完后还是不调fsync😄

    2019-01-04
    1
    18
  • alias cd=rm -rf
    事务A是当前事务,这时候事务B提交了。事务B的redolog持久化时候,会顺道把A产生的redolog也持久化,这时候A的redolog状态是prepare状态么?

    作者回复: 不是。

    说明一下哈,所谓的 redo log prepare,是“当前事务提交”的一个阶段,也就是说,在事务A提交的时候,我们才会走到事务A的redo log prepare这个阶段。

    事务A在提交前,有一部分redo log被事务B提前持久化,但是事务A还没有进入提交阶段,是无所谓“redo log prepare”的。

    好问题

    2019-01-28
    1
    14
  • WilliamX
    为什么 binlog cache 是每个线程自己维护的,而 redo log buffer 是全局共用的?
    这个问题,感觉还有一点,binlog存储是以statement或者row格式存储的,而redo log是以page页格式存储的。page格式,天生就是共有的,而row格式,只跟当前事务相关

    作者回复: 嗯,这个解释也很好。👍🏿

    2019-01-04
    14
  • 某、人
    有调到非双1的时候,在大促时非核心库和从库延迟较多的情况。
    设置的是sync_binlog=0和innodb_flush_log_at_trx_commit=2
    针对0和2,在mysql crash时不会出现异常,在主机挂了时,会有几种风险:
    1.如果事务的binlog和redo log都还未fsync,则该事务数据丢失
    2.如果事务binlog fsync成功,redo log未fsync,则该事务数据丢失。
    虽然binlog落盘成功,但是binlog没有恢复redo log的能力,所以redo log不能恢复.
    不过后续可以解析binlog来恢复这部分数据
    3.如果事务binlog fsync未成功,redo log成功。
    由于redo log恢复数据是在引擎层,所以重新启动数据库,redo log能恢复数据,但是不能恢复server层的binlog,则binlog丢失。
    如果该事务还未从FS page cache里发送给从库,那么主从就会出现不一致的情况
    4.如果binlog和redo log都成功fsync,那么皆大欢喜。

    老师我有几个问题:
    1.因为binlog不能被打断,那么binlog做fsync是单线程吧?
    如果是的话,那么binlog的write到fsync的时间,就应该是redo log fsync+上一个事务的binlog fsync时间。
    但是测试到的现象,一个超大事务做fsync时,对其它事务的提交影响也不大。
    如果是多线程做fsync,怎么保证的一个事务binlog在磁盘上的连续性?
    2. 5.7的并行复制是基于binlog组成员并行的,为什么很多文章说是表级别的并行复制?

    作者回复: 1. Write的时候只要写进去了,fsync其实很快的。连续性是write的时候做的(写的时候保证了连续)

    2. 你的理解应该是对的。不是表级

    2019-01-06
    2
    9
  • melon
    老师帮忙看一下我binlog 组提交这块理解的对不对

    binlog write 阶段
    组里面第一个走到 binlog write 的事务记录一个时间戳,用于在 binlog fsync 阶段计算 sync delay了多少时间,姑且计为 start_time
    组里已 sync write 次数+1,姑且记为 group_write
    全局已 sync wirte 次数+1,姑且记为 global_write

    binlog fsync 阶段
    IF ( NOW - sart_time ) >= binlog_group_commit_sync_delay || group_write >= binlog_group_commit_sync_no_delay_count
        IF sync_binlog >0 && global_write >= sync_binlog
            fsync

        设置 binlog 组提交信号,让其它等待的事务继续
    ELSE
        等待 binlog 组提交信号

    另外 binlog_group_commit_sync_no_delay_count 这个参数是不是不应该设置的比并发线程数大,因为一个组里的事务应该不会比并发线程数多吧,设置大了也就没什么意义了,可以这么理解吧老师。

    作者回复: 前面的伪代码不错哈

    ”binlog_group_commit_sync_no_delay_count这个参数是不是不应该设置的比并发线程数大“,最好是这样的,否则的话,就只能等binlog_group_commit_sync_delay |时间到了

    2019-02-28
    6
  • 一大只😴
    你是怎么验证的?等于0的时候虽然有走这个逻辑,但是最后调用fsync之前判断是0,就啥也没做就走了
    回复老师:
           老师,我说的sync_binlog=0或=1效果一样,就是看语句实际执行的效果,参数binlog_group_commit_sync_delay我设置成了500000微秒,在=1或=0时,对表进行Insert,然后都会有0.5秒的等待,也就是执行时间都是0.51 sec,关闭binlog_group_commit_sync_delay,insert执行会飞快,所以我认为=1或=0都是受组提交参数的影响的。

    作者回复: 👍🏿
    非常好

    然后再补上我回答的这个逻辑,就完备了

    2019-01-05
    6
  • 往事随风,顺其自然
    redolog 里面有已经提交事物日志,还有未提交事物日志都持久化到磁盘,此时异常重启,binlog 里面不是多余记录的未提交事物,干嘛不设计不添加未提交事物不更好
    2019-01-04
    4
  • chris~jiang
    刚开始我也遇到了jacy一样的问题,认为binlog写到file里面就是写到disk了,就不理解为什么还要fsync,后来仔细回读了文章,发现binlog写到file是指写到pagecache,并不是disk。
    建议老师在描述binlog写盘的那两个步骤时,把写到file直接描述为写到pagecache,避免歧义
    2019-08-28
    3
  • Tunayoyo
    老师您好,innodb_flush_log_at_trx_commit设置和后台线程的刷盘操作的关系是啥?
    2019-05-31
    3
    3
  • Komine
    为什么binlog 是不能“被打断的”的呢?主要出于什么考虑?

    作者回复: 好问题

    我觉得一个比较重要的原因是,一个线程只能同时有一个事务在执行。

    由于这个设定,所以每当执行一个begin/start transaction的时候,就会默认提交上一个事务;
    这样如果一个事务的binlog被拆开的时候,在备库执行就会被当做多个事务分段自行,这样破坏了原子性,是有问题的。

    2019-01-22
    3
  • 猪哥哥
    老师 我想问下文件系统的page cache还是不是内存, 是不是文件系统向内核申请的一块的内存?

    作者回复: 你理解的是对的

    2019-01-10
    3
  • 一只羊
    也不知道还会不会有问题解答,试试。

    我的问题是:现在知道 redo log 和 binlog 的落盘时机。但是我不知道记录数据是在什么时候、什么方式落盘的。
    不知道还有机会得到解答不。

    作者回复: 记录数据就是先写内存,然后写日志(redo和binlog),后台会有机会将内存的数据写到数据盘

    2019-08-15
    2
  • xiaoyou
    老师,请教一个问题,文章说innodb的 redo log 在commit的时候不进行fsync,只会write 到page cache中。当sync_binlog>1,如果redo log 完成了prepare持久化落盘,binlog只是write page cache,此时commit标识完成write 但没有落盘,而client收到commit成功,这个时候主机掉电,启动的时候做崩溃恢复,没有commit标识和binglog,事务会回滚。我看文章说sync_binlog设置为大于1的值,会丢binlog日志,此时数据也会丢失吧?

    作者回复: 你说的对,分析得很好

    2019-01-09
    2
  • Mr.Strive.Z.H.L
    老师你好,看了@倪大人的问题,个人认为:
    sync_binlog和binlog_group_commit_sync_no_delay_count的最大区别主要在于,数据的丢失与否吧?

    sync_binlog = N:每个事务write后就响应客户端了。刷盘是N次事务后刷盘。N次事务之间宕机,数据丢失。

    binlog_group_commit_sync_no_delay_count=N: 必须等到N个后才能提交。换言之,会增加响应客户端的时间。但是一旦响应了,那么数据就一定持久化了。宕机的话,数据是不会丢失的。

    不知道我这么理解对不对?

    作者回复: 你的理解很到位

    2019-01-08
    2
  • Tunayoyo
    是不是说并行的事务,都在提交,redo log就不能被顺带持久化?

    作者回复: redo log不需要“顺序”持久化的

    2019-05-31
    1
  • jacy
    1.先把 binlog 从 binlog cache 中写到磁盘上的 binlog 文件;
    2.调用 fsync 持久化。

    老师,这两步我不不太理解,写到磁盘binlog文件,不就是持久化了吗,为啥还要调用fsync再刷一次盘呢?能否帮忙解答一下,感谢🙏

    作者回复: wrie很多次,fsync一次

    2019-05-30
    1
    1
  • 莫名
    老师,sync_binlog=N,N之间客户端已明确收到事务提交,而如果期间机器崩溃或掉电,重启会导致数据库数据也丢失或回滚,那不是客户端处理的数据可能也会有问题?比如账户类数据、银行转账等?望解惑!

    作者回复: 是的

    所以我们说,如果要保证数据不丢,就要设置=1

    2019-05-11
    1
  • poplar
    老师好,两阶段提交,redo log prepare阶段进行持久化,然后是binlog持久化,那么commit阶段做了什么,又是如何判断redo log已经是两阶段完成了呢
    2019-03-31
    1
  • miu
    老师,关于BINLOG_GROUP_COMMIT_SYNC_DELAY,
    BINLOG_GROUP_COMMIT_SYNC_NO_DELAY_COUNT,
    SYNC_BINLOG三个参数,我的理解是:
    若SYNC_BINLOG>1时,且设置了BINLOG_GROUP_COMMIT_SYNC_DELAY和BINLOG_GROUP_COMMIT_SYNC_NO_DELAY_COUNT两个参数。
    例如
    sync_binlog=2,
    BINLOG_GROUP_COMMIT_SYNC_DELAY=1000000,
    BINLOG_GROUP_COMMIT_SYNC_NO_DELAY_COUNT=3,
    那么在执行完第1个事务后,在第2个事务提交时,会根据后续的事务提交来判断fsync等待的时间,
    若后续在1秒内没有累积3个事务的提交,则会等待1秒后再做fsync,从SQL语句来看,执行第一个语句很快,第二个语句需要等待1秒才成功。这时延时等待的时间是BINLOG_GROUP_COMMIT_SYNC_DELAY所设置的值。
    若执行完第1个事务后,并行执行3个事务(1秒内完成),则后续3个事务会同时做fsync,这时延时等待的时间是BINLOG_GROUP_COMMIT_SYNC_NO_DELAY_COUNT设置的数量的事务提交的间隔时间。
    也就是sync_binlog+BINLOG_GROUP_COMMIT_SYNC_NO_DELAY_COUNT-1 个事务做一次fsync。
    我测试的版本是MySQL官方5.7.24,请老师点评。

    作者回复: 这两个逻辑不建议放到一起算
    就是按照这样:
    1. 有设置 BINLOG_GROUP_COMMIT_SYNC_NO_DELAY_COUNT这个值,(假设SYNC_DELAY很大),提交的时候就得等这么多次才能过;
    2. 到了提交阶段,又要按照sync_binlog来判断是否刷盘。


    新春快乐~

    2019-02-01
    1
收起评论
95
返回
顶部