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

24 | MySQL是怎么保证主备一致的?

林晓斌 2019-01-07
在前面的文章中,我不止一次地和你提到了 binlog,大家知道 binlog 可以用来归档,也可以用来做主备同步,但它的内容是什么样的呢?为什么备库执行了 binlog 就可以跟主库保持一致了呢?今天我就正式地和你介绍一下它。
毫不夸张地说,MySQL 能够成为现下最流行的开源数据库,binlog 功不可没。
在最开始,MySQL 是以容易学习和方便的高可用架构,被开发人员青睐的。而它的几乎所有的高可用架构,都直接依赖于 binlog。虽然这些高可用架构已经呈现出越来越复杂的趋势,但都是从最基本的一主一备演化过来的。
今天这篇文章我主要为你介绍主备的基本原理。理解了背后的设计原理,你也可以从业务开发的角度,来借鉴这些设计思想。

MySQL 主备的基本原理

如图 1 所示就是基本的主备切换流程。
图 1 MySQL 主备切换流程
在状态 1 中,客户端的读写都直接访问节点 A,而节点 B 是 A 的备库,只是将 A 的更新都同步过来,到本地执行。这样可以保持节点 B 和 A 的数据是相同的。
当需要切换的时候,就切成状态 2。这时候客户端读写访问的都是节点 B,而节点 A 是 B 的备库。
在状态 1 中,虽然节点 B 没有被直接访问,但是我依然建议你把节点 B(也就是备库)设置成只读(readonly)模式。这样做,有以下几个考虑:
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(62)

  • Sinyo 置顶
    主库 A 从本地读取 binlog,发给从库 B;
    老师,请问这里的本地是指文件系统的 page cache还是disk呢?

    作者回复: 好问题,
    是这样的,对于A的线程来说,就是“读文件”,
    1. 如果这个文件现在还在 page cache中,那就最好了,直接读走;
    2. 如果不在page cache里,就只好去磁盘读

    这个行为是文件系统控制的,MySQL只是执行“读文件”这个操作

    2019-01-21
    1
    20
  • Leon📷 置顶
    老师,我想问下双M架构下,主从复制,是不是一方判断自己的数据比对方少就从对方复制,判断依据是什么

    作者回复: 好问题。

    一开始创建主备关系的时候, 是由备库指定的。
    比如基于位点的主备关系,备库说“我要从binlog文件A的位置P”开始同步, 主库就从这个指定的位置开始往后发。


    而主备复制关系搭建完成以后,是主库来决定“要发数据给备库”的。

    所以主库有生成新的日志,就会发给备库。

    2019-01-25
    2
    18
  • HuaMax
    课后题。如果在同步的过程中修改了server id,那用原server id 生成的log被两个M认为都不是自己的而被循环执行,不知这种情况会不会发生

    作者回复: 是的,会

    2019-01-07
    12
  • 观弈道人
    老师你好,问个备份问题,假如周日23点做了备份,周二20点需要恢复数据,那么在用binlog恢复时,如何恰好定位到周日23点的binlog,谢谢。

    作者回复: Mysqlbinlog有个参数—stop-datetime

    2019-01-07
    12
  • 妥妥
    老师,我想问下,如果一张表并没有主键,插入的一条数据和这张表原有的一条数据所有字段都是一样的,然后对插入的这条数据做恢复,会不会把原有的那条数据删除?不知道在没有主键的情况下binlog会不会也记录数据库为其生成的主键id

    作者回复: 好问题,
    会删除一条,但确实可能删除到之前的那条。

    主要就是因为,没有主键的时候,binlog里面就不会记录主键字段。

    2019-02-28
    10
  • 三木禾
    老师,双M可能会造成数据不一致的情况么? 比如,A B同时更新同一条数据?

    作者回复: 一般说双M是只AB之间设置为互为主备,不过任何时刻只有一个节点在接受更新的

    2019-03-31
    1
    8
  • hua168
    大神,我前些天去面试,面试官问了一题:
    mysql做主从,一段时间后发现从库在高峰期会发生一两条条数据丢失(不记得是查询行空白还是查询不到了),主从正常,怎么判断?
    1.我问他是不是所以从库都是一样,他说不一样
    2.我说低峰期重做新的从库观察,查看日志有没有报错?他好像不满意这个答案。

     二、他还问主库挂了怎么办?
    1. mysql主从+keepalived/heartbeat
         有脑裂,还是有前面丢数据问题
    2. 用MMM或HMA之类
    3.用ZK之类

    三、写的压力大怎么办?
    我回答,分库,分表

    感觉整天他都不怎么满意,果然没让我复试了,我郁闷呀,我就面试运维的,问数据这么详细。😂
    大神,能说下我哪里有问题吗?现在我都想不明白😂

    作者回复: 运维现在要求也挺高的

    第一个问题其实我也没看懂,“高峰期丢数据”是指主备延迟查不到数据,还是真的丢了,得先问清楚下

    不过你回答的第二点不太好,低峰期重做这个大家都知道要这么做,而且只是修复动作,没办法用来定位原因,面试官是要问你分析问题的方法(方向错误)
    重搭从库错误日志里面什么都没有的(这个比较可惜,暴露了对字节不够了解,一般不了解的方法在面试的时候是不如不说的)

    第二个问题三点都是你回答的吗?那还算回答得可以的,但是不能只讲名词,要找个你熟悉细节的方案展开一下

    三方向也是对的

    我估计就是第一个问题减分比较厉害

    2019-01-08
    8
  • Joker
    老师您好,读到您关于binlog的文章之后,我有个疑问。
    我之前理解是,mysql 每执行一条事务所产生的binlog准备写到 binlog file时,都会先判断当前文件写入这条binlog之后是否会超过设置的max_binlog_size值。 如果超过,则rotate 自动生成下个binlog flie 来记录这条binlog信息。
    那如果 事务所有产生的binlog 大于 max_binlog_size 值呢? 那不是永久地rotate吗? mysql是如何处理的?
    谢谢。

    作者回复: 好问题

    一个事务的binlog日志不会被拆到两个binlog文件,所以会等到这个事务的日志写完再rotate,所以你会看见超过配置大小上限的binlog 文件

    2019-03-06
    1
    7
  • 一大只😴
    死循环第二种情况:
    双主,log_slave_updates=on,binlog_format=statement
    配置文件里写成statement格式,然后两个master都重启
    (从row格式改成statement试了几次没有成功,因为binlog中记录格式还是row)
    测试:
    表t (id ,c,d) 主键id,有一条数据(1,2,1);
    M1执行
         stop slave;
         update t set c=c+1 ;或 update t set c=c+1 where id=1;
         set global server_id=new_server_id;
         start slave;
    然后就能看到c的值在不断变大,想停止就把server_id改回原来的就可以了。


    作者回复: 赞

    2019-01-07
    6
  • 陈扬鸿
    老师,我现在生产上用的是MySQL5.6的主从同步,主库用的是ssd硬盘,备库用的是机械硬盘,现在从库落后主库好几个小时,主库上数据的写入更新比较大,这个问题是由于两端硬件问题造成的吗?线上只有一个数据库,有什么好的同步加速方案吗?麻烦老师给我解答一下,谢谢!

    作者回复: 最好是换硬件,把备库的磁盘能力提上来,

    可以考虑一下备库设置 innodb_flush_log_at_trx_commit 和 sync_binlog 为非双1 试试

    2019-02-24
    1
    5
  • linqw
    写下学习完这篇的总结和理解,老师有空帮忙看下哦
    1、简单主备,一主多备,主进行更新操作,将生成binlog文件发送给备,但是比较好奇一点的是所有备向主拿binlog文件的时候,主都是一个线程进行将binlog文件依次发送给备么?两个库互为主备可以将一个负责数据的写入,生成binlog文件,另一个作为数据的同步,将其改变的binlog同步到自身,然后其他备再从其同步binlog,多master可以做到一台宕机,快速切换到另一台作为主,防止主库宕机对业务造成的影响,但是这样可能导致一定程度的同步延迟。
    2、主备复制关系搭建完成,主有数据写入的时候,发送给备的应该不是整个binlog log文件吧,是每次写入的binlog event么?
    3、在图 2 主备流程图对bg-thread->undolog(disk)->data(disk)不太理解,回滚段也是先记录到内存,再记录在磁盘么?undolog(disk)再到data(disk),看了下undo log的控制参数没有看到控制类似行为的,没想通?老师帮忙解答下哦
    4、binlog的三种格式,statement,记录数据库原句,有可能导致,主备所选择的索引不一致,导致主备数据不一致。row,binlog log记录的是操作的字段值,根据binlog_row_image 的默认配置是 FULL包括操作行为的所有字段值,binlog_row_image 设置为 MINIMAL,则会记录必须的字段,一般设置为row,可以根据binlog文件做其他操作,比如在误删除一行数据时,可以做insert,恢复数据。
    5、如果执行的是 update 语句的话,binlog 里面会记录修改前整行的数据和修改后的整行数据,在二级索引的普通索引,有个change buffer优化,防止频繁的将数据页读入进来,可以减少buffer pool的消耗,可以在读取数据时,再将其marge,或者后台线程marge,但是在binlog log设置row格式的,update时,需要记录更新前后的数据,那这样的话,chage buffer不是用不上了么?还是说设置成row格式的时候,change buffer会没生效?老师麻烦帮忙解答下哦,没想明白

    作者回复: 2. 流式发送,一个事务提交就会发
    3. “回滚段也是先记录到内存,再记录在磁盘么?” 是的。 undolog(disk)不需要到data(disk),undo log的作用看一下08篇
    5. “update时,需要记录更新前后的数据,那这样的话,chage buffer不是用不上了么” --- 不是的,binlog里面的内容用的是主键索引上的,主键索引确实用不上change buffer,但是普通索引可以

    2019-02-17
    4
  • 汪炜
    老师,问个问题,希望能被回答:
    mmysql不是双一设置的时候,破坏了二阶段提交,事务已提交,redo没有及时刷盘,binlog刷盘了,这种情况,mysql是怎么恢复的,这个事务到底算不算提交?

    作者回复: 如果”redo没有及时刷盘,binlog刷盘了”之后瞬间数据库所在主机掉电,
    主机重启,MySQL重启以后,这个事务会丢失;这里确实会引起日志和数据不一致,
    这个就是我们说要默认设置为双1的原因之一哈

    2019-01-23
    4
  • 不迷失
    请教一下,生产环境能不能使用正常使用表连接?要注意哪些地方?DBA总是说不建议用,还催促我将使用了表连接的地方改造,但也说不出个所以然。目前在两个百万级数据的表中有用到内连接,并没有觉得有什么问题

    作者回复: 索引使用正确,不要出现全表扫描,其实OK的

    2019-01-08
    4
  • 堕落天使
    老师,您好,问一个关于change buffer的问题。
    对于insert语句来说,change buffer的优化主要在非唯一的二级索引上,因为主键是唯一索引,插入必须要判断是否存在。
    那么对于update语句呢?如下(假设c有非唯一索引,id是主键,d没有索引):
    update t set d=2 where c=10;
    原先以为:从索引c取出id之后,不会回表,也不会把修改行的数据读入内存,而是直接在change buffer中记录一下。但看了今天得内容之后又迷糊了,因为如果不把修改行的数据读入内存,它又怎么把旧数据写入binlog中呢?
    所以我想问的就是,上面的sql语句会不会把修改行的内容也读进内存?如果读进内存,那读进内存的这一步难道就为了写binlog吗?如果不读进内存,那binlog中的旧数据又是怎么来的呢?还有delete语句也同理。

    作者回复: 修改的行要读入内存呀

    写binlog只需要主键索引上的值

    你这个语句的话,如果字段c d上都有索引,那么c用不上chsnge buffer,
    D可能可以同上

    2019-01-07
    4
  • D.L
    老师您好,我这里有个问题想问一下。在主库宕机后,还没同步到从库的binlog在从库上是看不到的,这种问题是如何解决的?
    2019-03-26
    1
    3
  • 风二中
    在主库执行这条 SQL 语句的时候,用的是索引 a;而在备库执行这条 SQL 语句的时候,却使用了索引 t_modified
    老师,您好,这里索引选择不一样,是因为前面提到的mysql 会选错索引吗?这种情况应该发生比较少吧,这里应该都会选择索引a吧,还是说这里只是一个事例,还有更复杂的情况

    作者回复: 对,只是一个举例的

    2019-01-12
    3
  • 鸠翱
    也就是说 statement格式是不能用来恢复数据的是嘛……

    作者回复: 不能用在使用flashback机制恢复

    2019-01-08
    3
  • 夜空中最亮的星(华仔)
    级联复制,3个数据库,首尾相连,应会出现死循环

    作者回复: 不会哦,1给2,2给3,3给1,1就放弃了

    不过引入第三个节点的思路是对的哈😄

    2019-01-08
    3
  • 柚子
    大佬您好,文中说现在越来越多的使用row方式的binlog,那么只能选择接受写入慢和占用空间大的弊端么?

    作者回复: 是的,当然还有minimal可选,会好些😄

    2019-01-07
    3
  • 还一棵树
    第二遍到这里啦,看留言有个疑问:
    关于没有主键的表 有同样的两行数据,在主库上 limit 1 删除其中的一条,备库也是随机删除其中的一条。
    这里有个疑问:解析主库的binlog 看到的row记录完整的binlog如下:
    # at 1245
    #190523 16:58:32 server id 1 end_log_pos 1293 CRC32 0x598e440d GTID last_committed=0 sequence_number=0 rbr_only=no
    SET @@SESSION.GTID_NEXT= 'f15197b2-f235-11e8-88f1-00163e02236b:6'/*!*/;
    # at 1293
    #190523 16:58:32 server id 1 end_log_pos 1365 CRC32 0x61325084 Query thread_id=2 exec_time=0 error_code=0
    SET TIMESTAMP=1558601912/*!*/;
    BEGIN
    /*!*/;
    # at 1365
    #190523 16:58:32 server id 1 end_log_pos 1411 CRC32 0x2b9b4aea Table_map: `test`.`bbb` mapped to number 78
    # at 1411
    #190523 16:58:32 server id 1 end_log_pos 1451 CRC32 0x31dec98e Delete_rows: table id 78 flags: STMT_END_F

    BINLOG '
    uGDmXBMBAAAALgAAAIMFAAAAAE4AAAAAAAEABHRlc3QAA2JiYgABAwAB6kqbKw==
    uGDmXCABAAAAKAAAAKsFAAAAAE4AAAAAAAEAAgAB//4BAAAAjsneMQ==
    '/*!*/;
    ### DELETE FROM `test`.`bbb`
    ### WHERE
    ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
    # at 1451
    #190523 16:58:32 server id 1 end_log_pos 1482 CRC32 0xd6be57a8 Xid = 216
    COMMIT/*!*/;

    --从binlog看应该会删除2条的,但不知为什么只删除了一条,这个mysql是怎么控制的?

    作者回复: 因为这个binlog event里面只删除了一条
    所以在备库应用的时候,删了一条以后,就退出了

    2019-05-23
    2
收起评论
62
返回
顶部