MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43184 人已学习
课程目录
已完结 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讲
登录|注册

25 | MySQL是怎么保证高可用的?

林晓斌 2019-01-09
在上一篇文章中,我和你介绍了 binlog 的基本内容,在一个主备关系中,每个备库接收主库的 binlog 并执行。
正常情况下,只要主库执行更新生成的所有 binlog,都可以传到备库并被正确地执行,备库就能达到跟主库一致的状态,这就是最终一致性。
但是,MySQL 要提供高可用能力,只有最终一致性是不够的。为什么这么说呢?今天我就着重和你分析一下。
这里,我再放一次上一篇文章中讲到的双 M 结构的主备切换流程图。
图 1 MySQL 主备切换流程 -- 双 M 结构

主备延迟

主备切换可能是一个主动运维动作,比如软件升级、主库所在机器按计划下线等,也可能是被动操作,比如主库所在机器掉电。
接下来,我们先一起看看主动切换的场景。
在介绍主动切换流程的详细步骤之前,我要先跟你说明一个概念,即“同步延迟”。与数据同步有关的时间点主要包括以下三个:
主库 A 执行完成一个事务,写入 binlog,我们把这个时刻记为 T1;
之后传给备库 B,我们把备库 B 接收完这个 binlog 的时刻记为 T2;
备库 B 执行完成这个事务,我们把这个时刻记为 T3。
所谓主备延迟,就是同一个事务,在备库执行完成的时间和主库执行完成的时间之间的差值,也就是 T3-T1。
你可以在备库上执行 show slave status 命令,它的返回结果里面会显示 seconds_behind_master,用于表示当前备库延迟了多少秒。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(76)

  • 某、人
    遇到过下面几种造成主从延迟的情况:
    1.主库DML语句并发大,从库qps高
    2.从库服务器配置差或者一台服务器上几台从库(资源竞争激烈,特别是io)
    3.主库和从库的参数配置不一样
    4.大事务(DDL,我觉得DDL也相当于一个大事务)
    5.从库上在进行备份操作
    6.表上无主键的情况(主库利用索引更改数据,备库回放只能用全表扫描,这种情况可以调整slave_rows_search_algorithms参数适当优化下)
    7.设置的是延迟备库
    8.备库空间不足的情况下

    这期的问题:
    看这曲线,应该是从库正在应用一个大事务,或者一个大表上无主键的情况(有该表的更新)
    应该是T3随着时间的增长在增长,而T1这个时间点是没变的,造成的现象就是
    随着时间的增长,second_behind_master也是有规律的增长

    作者回复: 分析的点很准确👍

    2019-01-10
    46
  • linqw
    总结下学习完高可用,老师有空帮忙看下
    1、主备延迟,就是在同一个事务在备库执行完成的时间和主库执行完成的时间之间的差值,包括主库事务执行完成时间和将binlog发送给备库,备库事务的执行完成时间的差值。每个事务的seconds_behind_master延迟时间,每个事务的 binlog 里面都有一个时间字段,用于记录主库上的写入时间,备库取出当前正在执行的事务的时间字段的值,计算它与当前系统时的差值。
    2、主备延迟的来源①首先,有些部署条件下,备库所在机器的性能要比主库所在的机器性能差,原因多个备库部署在同一台机器上,大量的查询会导致io资源的竞争,解决办法是配置”双1“,redo log和binlog都只write fs page cache②备库的压力大,产生的原因大量的查询操作在备库操作,耗费了大量的cpu,导致同步延迟,解决办法,使用一主多从,多个从减少备的查询压力③大事务,因为如果一个大的事务的dml操作导致执行时间过长,将其事务binlog发送给备库,备库也需执行那么长时间,导致主备延迟,解决办法尽量减少大事务,比如delete操作,使用limit分批删除,可以防止大事务也可以减少锁的范围。
    ④大表的ddl,会导致主库将其ddl binlog发送给备库,备库解析中转日志,同步,后续的dml binlog发送过来,需等待ddl的mdl写锁释放,导致主备延迟。
    3、可靠性优先策略,①判断备库 B 现在的 seconds_behind_master如果小于某个值(比如 5 秒)继续下一步,否则持续重试这一步,②把主库 A 改成只读状态,即把 readonly 设置为 true,③判断备库 B 的 seconds_behind_master的值,直到这个值变成 0 为止; 把备库 B 改成可读写也就是把 readonly 设置为 false; 把业务请求切换到备库,个人理解如果发送过来的binlog在中转日志中有多个事务,业务不可用的时间,就是多个事务被运用的总时间。如果非正常情况下,主库掉电,会导致出现的问题,如果备库和主库的延迟时间短,在中转日志运用完成,业务才能正常使用,如果在中转日志还未运用完成,切换为备库会导致之前完成的事务,”数据丢失“,但是在一些业务场景下不可接受。
    4、可用性策略,出现的问题:在双m,且binlog_format=mixed,会导致主备数据不一致,使用使用 row 格式的 binlog 时,数据不一致的问题更容易发现,因为binlog row会记录字段的所有值。
    5、老师有个问题不太理解,就是主备延迟时,会导致备库在没有运用中转日志时,业务查询时导致”数据丢失“,那如何解决了?

    作者回复: 1~4 很好的总结
    5. 也是好问题,直接看《28 | 读写分离有哪些坑?》😆

    2019-02-17
    8
  • 7号
    老师,生产环境有一张表需要清理,该表大小140G。要保留最近一个月的数据,又不能按时间直接用detele删(全表扫描),本来想通过清空分区表删,但是分区表又是哈希的。。有没好的办法呢?

    作者回复: 估计下一个月占多少比例,如果比较小就建新表,把数据导过去吧
    如果一个月占比高的话,只能一点点删了。

    时间字段有索引的话,每个分区按时间过滤出来删除

    2019-01-09
    1
    7
  • 梁中华
    我有一个比较极端一点的HA问题,假设主库的binlog刚写成功还未来得及把binlog同步到从库,主库就掉电了,这时候从库的数据会不完整吗?
    第二个问题,原主库重启加入集群后,那条没有传出去的binlog会如何处理?

    作者回复: 1.可能会丢
    2. 要看重启之后的拓扑结构了,如果还有节点是这个库的从库,还是会拿走的

    2019-01-09
    2
    7
  • John
    循环复制根本原因是binlog中引入了非当前主机的server id,可以通过ignore server ids过滤,但是一般情况如果出现循环复制,数据的可靠性就值得怀疑了,不管是过滤还是重新找点都很难保证循环的部分完整执行过,最后都要验证数据的状态,属于特别严重故障😂

    作者回复: 你这个方法好👍🏿

    数据问题的话,如果是设置的row 格式的binlog还好,因为insert和delete都会报错,会出现循环的就是update, 然后update都是可重入的

    2019-01-10
    6
  • 万勇
    主备同步延迟,工作中常遇到几种情况:
    1.主库做大量的dml操作,引起延迟
    2.主库有个大事务在处理,引起延迟
    3.对myisam存储引擎的表做dml操作,从库会有延迟。
    4.利用pt工具对主库的大表做字段新增、修改和添加索引等操作,从库会有延迟。

    作者回复: 👍🏿
    你是有故事的😄

    2019-01-09
    6
  • aubrey
    semi-sync在网络故障超时的情况下会退化成async,这个时候如果刚好主库掉电了,有些binlog还没有传给从库,从库无法判断数据跟主库是否一致,如果强行切换可能会导致丢数据,在金融业务场景下只能"人工智能"来做切换,服务中断时间长。AliSQL采用双通道复制更容易判断主备数据是否一致,如果一致可以自动切换,如果不一致才需要人工恢复数据。

    作者回复: 👍内行😆

    2019-02-14
    5
  • 崔伟协
    发生主从切换的时候,主有的最新数据没同步到从,会出现这种情况吗,出现了会怎么样

    作者回复: 异常切换有可能的

    要根据你的处理策略了,如果不能丢,有几个可选的
    1.不切换(等这个库自己恢复起来)
    2. 使用semi-sync策略
    3. 启动后业务做数据对账(这个一般用得少,成本高)

    2019-01-11
    4
  • undifined
    问题答案:
    1. 备库在执行复杂查询,导致资源被占用
    2. 备库正在执行一个大事务
    3. DML 语句执行

    老师我的理解对吗

    作者回复: 1不太准确,明天我会提到哈

    23对的

    2019-01-09
    4
  • 可可
    老师,我先来讲个笑话
    昨天去面试另一家公司,问mysql的问题,问罢之后。
    面试官:我看你对mysql了解的还蛮深得,是不是也看了极客时间的。。。~
    我: 没有没有(连忙否认,有一种提前看了考试答案的罪恶感😂😂😂)


    所以最后我有个问题,如果后面还有这样的问题,老师您觉得我应该怎么回答?

    作者回复: 额。。这个不好说,得看面试官是什么类型的😅

    最好的情况是那种,面试官问你的专栏讲到的知识点你都回答了,
    然后面试官又把问题延伸了,然后你还顺利回答出来了。

    这样就大大方方说学过了, 面试官一定觉得你又好学又勤于思考哟😆

    (先预祝一波顺利拿到offer哈)

    2019-03-27
    3
  • 康磊
    老师你好,现在一般采用读写分离,读的是从库,那么主从如果出现延迟的话,读库就读的不是最新数据,对这种问题有什么好建议吗?

    作者回复: 第28篇专门讲这个问题,敬请期待🤝

    2019-01-11
    3
  • cyberty
    请问老师,如果备库连接主库之后,主库的系统时间修改了,备库同步的时候是否会自动修正?

    作者回复: 好问题,不会

    2019-01-10
    3
  • Long
    老师,您好:

    一直追这个课程,解决了我自己的很多知识盲点,或者更加深入的了解一些知识点,已经在试读留言下推荐了这个课程。

    但是有的时候在分析问题的时候,看很多日志,比如error log中的死锁日志,报错日志中每个字段是什么意思,以及show innodb engine status中每个日志段的意思,比如在将redo log的时候innodb status就会记录,这样就可以结合课程中的log write,page cahce和fsync逻辑到数据库中实际感受到学到的原因,在应用中怎么一一对应。
    比如,show innodb engine status中:
     --------
     FILE I/O
     --------
    ***
    ***
     Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,
      ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
     Pending flushes (fsync) log: 1; buffer pool: 0
     14321192292 OS file reads, 120057595 OS file writes, 60413577 OS fsyncs
     10 pending preads, 1 pending pwrites
     4648.01 reads/s, 16383 avg bytes/read, 48.09 writes/s, 34.98 fsyncs/s


     ---
     LOG
     ---
     Log sequence number 3893849611607
     Log flushed up to 3893849603096
     Pages flushed up to 3893705803837
     Last checkpoint at 3893705803837
     1 pending log writes, 0 pending chkp writes
     28053287 log i/o's done, 10.15 log i/o's/second

    之前看过网上的一些分析,由于和原理脱离,所以理解的都不深。
    非常期待老师能结合error log一些常见问题分析比如dead lock,常见crash啊之类的,
    以及show innodb engine status中的重点内容!

    多谢

    作者回复: 嗯,这这个建议很好,我会考虑加的哈🤝

    2019-01-09
    3
  • WilliamX
    大事务导致主从延时的问题,我修改下。
    主库上即使有大事务,但只要影响行数不多,传送到从库时间为t1,完成时间为T3,那这个时间gap和是否大事务没关系吧?

    作者回复: 嗯,你这里说的大事务,是那种“查很多,更新少”,这种没关系的;

    一般我们在说主备延迟的大事务,是指“更新很多行”的

    2019-01-09
    3
  • via
    通过 binlog 输出到外部系统,比如 Hadoop 这类...

    文中这个具体是可采用什么工具呢?
     

    作者回复: canal 可以了解下

    2019-01-09
    3
  • aliang
    老师,我有一个问题:(1)seconds_behind_master的计算方法是通过从库的系统时间戳减去sql_thead线程正在执行的binlog_event上的时间戳的差值。当从库系统时间不准时也不会影响seconds的值,因为从库连接到主库时会通过select unix_timestamp()查询主库的系统时间,若发现和从库不一致会在计算seconds这个值时作调整(2)我的疑惑是在主从网络正常时,select unix_timestamp执行的频率和触发条件是怎样的(换句话说(1)中描述的从库连接到主库这个行为是一直存在的还是有其他触发条件?)。如果这个频率不高,那在两次select unix_timestamp期间从库系统时间发生变化,seconds的值岂不是不准了?

    作者回复: 嗯,取时间这个动作只发生在“主从建立连接”的过程中,
    如果已经连着的时候,时间戳改掉,是会不准的

    2019-02-23
    2
  • EAGLE
    文中提到“如果一个主库上的语句执行 10 分钟,那这个事务很可能就会导致从库延迟 10 分钟”。这个延迟是针对当前delete事务,还是所有的事务都延迟。

    作者回复: 要看有没有开并行复制,默认是串行,一个堵全部堵

    2019-01-09
    2
  • Sr7vy
    问题1:T3的解释是:备库执行完这个事物。则:Seconds_Behind_Master=T3-T1。如T1=30min,主执行完成,备没有执行。猜测1:那么Seconds_Behind_Master=30min吗?猜测2:备执需要先把这个30min的事务执行完后,Seconds_Behind_Master=30min?
    问题2:很多时候是否能把Seconds_Behind_Master当作真正的延迟时间(面试常被问)?如果能,pt-heartbeat存在还有啥意义啊?

    作者回复: 问题1:
    1.备库没收到,还是收到没执行,前者0,后者30
    2. 第二问没看懂

    问题2:
    类似的,主库把日志都发给备库了吗

    2019-01-09
    2
  •  JJ
    请问老师,主库断电了,怎么把binlog传给从库同步数据,怎么使的SBM为0主从切换呢?

    作者回复: 等应用完就认为是SBM=0

    如果不能接受主库有来不及传的,就使用semi-sync

    2019-01-09
    2
  • Sinyo
    老师,在 binlog row模式下,insert 到表中一条记录,这条记录中某个字段不填,该字段在表中有设置默认值,利用canal解析binlog出来,这个不填的字段会不存在;难道 binlog 只记录有插入的字段数据,表字段的默认数据就不会记录么?mysql版本5.7.22 canal版本1.0.3

    作者回复: 不会啊
    insert记录的时候肯定都记录的
    你的默认值是什么?

    2019-01-09
    2
收起评论
76
返回
顶部