最全【阿里最新数据库面试题】MySQL主从一致性，隔壁都馋哭了

2301_82242675

于 2024-05-13 16:57:05 发布

阅读量1k

点赞数 11

分类专栏：程序员文章标签： java 面试学习

本文链接：https://blog.csdn.net/2301_82242675/article/details/138809930

版权

程序员专栏收录该内容

124 篇文章 1 订阅

订阅专栏

最近我根据上述的技术体系图搜集了几十套腾讯、头条、阿里、美团等公司21年的面试题，把技术点整理成了视频（实际上比预期多花了不少精力），包含知识脉络 + 诸多细节，由于篇幅有限，这里以图片的形式给大家展示一部分

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作
防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致
可以用readonly状态，来判断节点的角色。

把备库设置成只读了，还能和主库保持同步更新吗？

用于同步更新的线程，拥有超级权限。 readonly设置对超级(super)权限用户无效。

A到B这条线的内部流程是什么样的？下图中画出的就是一个update语句在节点A执行，然后同步到节点B的完整流程图

主备流程图

TODO

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。

备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的过程：

在备库B通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量
在备库B上执行start slave命令，这时备库会启动io_thread、sql_thread。io_thread负责与主库建立连接
主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B
备库B拿到binlog后，写到本地文件，称为中转日志（relay log）
sql_thread读取中转日志，解析出日志里的命令，并执行

后来由于多线程复制方案的引入，sql_thread演化成为了多个线程。

binlog里到底是什么

===========================================================================

为什么备库拿过去可以直接执行。

创建个表并初始化数据

要在表中删除一行，这个delete语句的binlog是怎么记录的。

注意，下面这个语句包含注释，如果你用MySQL客户端来做这个实验的话，要记得加-c参数，否则客户端会自动去掉注释。

delete

from ttt -c /comment/

where a >= 4

and t_modified <= ‘2018-11-10’

limit 1;

当binlog_format=statement时，binlog里面记录的就是SQL语句的原文。你可以用

查看binlog内容，上面是 ROW 格式，下面是 statement 格式。

mysql> show binlog events in ‘binlog.000034’;

±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+

±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+

| binlog.000034 | 786 | Anonymous_Gtid | 1 | 865 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |

| binlog.000034 | 865 | Query | 1 | 977 | BEGIN |

| binlog.000034 | 977 | Query | 1 | 1151 | use common_mistakes; delete from ttt where a >= 4 and t_modified <= ‘2018-11-10’ limit 1 |

| binlog.000034 | 1151 | Query | 1 | 1264 | COMMIT |

±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+

16 rows in set (0.00 sec)

BEGIN和最后的COMMIT匹配，表示中间是个事务
第三行就是真实执行的语句。在真实执行的delete命令之前，还有一个use命令。这条命令是MySQL根据当前要操作的表所在的数据库而自行添加的。这么做，可以保证日志传到备库去执行时，不论当前工作线程在哪个库，都能够正确更新到common_mistakes库的ttt表。
后面的delete 语句，就是SQL原语句
最后一行是一个COMMIT写着xid。

当前binlog设置的是statement格式，并且语句中有limit，该命令可能是unsafe的。因为delete 带limit，可能出现主备数据不一致。比如上面这个例子：

若delete使用的是索引a，则会根据索引a找到第一个满足条件的行，即删除的是a=4这一行
但若使用的是索引t_modified，则删除的就是 t_modified='2018-11-09'，即a=5这行

由于statement格式下，记录到binlog里的是原语句，可能发生：在主库执行该SQL时，用的是索引a；而在备库执行该SQL时，却使用索引t_modified。因此，这样写是有风险的。

若把binlog改为ROW 格式，是不是就没这问题了？

row格式binlog

mysql> show binlog events in ‘binlog.000034’;

±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+

±--------------±-----±---------------±----------±------------±-------------------------------------------------------------------------------------------+

| binlog.000034 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |

| binlog.000034 | 235 | Query | 1 | 329 | BEGIN |

| binlog.000034 | 329 | Table_map | 1 | 392 | table_id: 102 (common_mistakes.ttt) |

| binlog.000034 | 392 | Delete_rows | 1 | 440 | table_id: 102 flags: STMT_END_F |

| binlog.000034 | 440 | Xid | 1 | 471 | COMMIT /* xid=71 */

BEGIN和COMMIT是一样的。但row格式的binlog里没有原SQL语句，而两个event：

Table_map event

说明接下来要操作的表是test库的表t

Delete_rows event

定义删除的行为

其实还需要借助mysqlbinlog工具，用下面的命令解析和查看binlog内容。因为图5中的信息显示，这个事务的binlog是从8900这个位置开始的，所以可以用start-position参数来指定从这个位置的日志开始解析。

/usr/local/Cellar/mysql/8.0.21_1/bin/mysqlbinlog

-vv /usr/local/var/mysql/binlog.000034

–start-position=156;

row格式binlog 示例的详细信息

BEGIN

/!/;

at 329

#210606 14:11:44 server id 1 end_log_pos 392 CRC32 0xe8d79800 Table_map: common_mistakes.ttt mapped to number 102

at 392

#210606 14:11:44 server id 1 end_log_pos 440 CRC32 0x8b3b43d1 Delete_rows: table id 102 flags: STMT_END_F

BINLOG ’

IGe8YBMBAAAAPwAAAIgBAAAAAGYAAAAAAAEAD2NvbW1vbl9taXN0YWtlcwADdHR0AAMDAxEBAAIB

AQAAmNfo

IGe8YCABAAAAMAAAALgBAAAAAGYAAAAAAAEAAgAD/wAEAAAABAAAAFvlrwDRQzuL

'/!/;

DELETE FROM `common_mistakes`.`ttt`

WHERE

@1=4 /* INT meta=0 nullable=0 is_null=0 */

@2=4 /* INT meta=0 nullable=1 is_null=0 */

@3=1541779200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

at 440

#210606 14:11:44 server id 1 end_log_pos 471 CRC32 0x8e0dab1d Xid = 71

COMMIT/!/;

server id 1

该事务在server_id=1的库上执行

每个event都有CRC32值，因为binlog_checksum是CRC32

Table_map event

显示了接下来要打开的表，map到数字226。现在我们这条SQL语句只操作了一张表，若操作多表呢？每个表都有一个对应的Table_map event、都会map到一个单独的数字，用于区分对不同表的操作。

-vv参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4这些值）
binlog_row_image

默认配置是FULL，因此Delete_event里面，包含了删掉的行的所有字段的值。若把binlog_row_image设置为MINIMAL，则只会记录必要的信息。在该例，就只会记录id=4。

Xid event

表示事务被正确提交了。

可见，当binlog_format=row，binlog记录了真实删除行的主键id，这样binlog传到备库时，就肯定会删除id=4的行，不会有主备删除不同行的问题。

为何binlog有mixed格式？

================================================================================

Docker步步实践

目录文档：

①Docker简介

②基本概念

③安装Docker

④使用镜像：

⑤操作容器：

⑥访问仓库：

⑦数据管理：

⑧使用网络：

⑨高级网络配置：

⑩安全：

⑪底层实现：

⑫其他项目：

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

转存中…(img-TIbhdHeC-1715590603029)]

⑪底层实现：

[外链图片转存中…(img-mSUmG3od-1715590603029)]

⑫其他项目：

[外链图片转存中…(img-JTiXwMNW-1715590603029)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

2301_82242675

关注

11
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
最全【阿里最新数据库面试题】MySQL主从一致性，隔壁都馋哭了

最近我根据上述的技术体系图搜集了几十套腾讯、头条、阿里、美团等公司21年的面试题，把技术点整理成了视频（实际上比预期多花了不少精力），包含知识脉络 + 诸多细节，由于篇幅有限，这里以图片的形式给大家展示一部分有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致可以用readonly状态，来判断节点的角色。把备库设置成只读了，还能和主库保持同步更新吗？用于同步更新的线程，拥有超级权限。
复制链接

扫一扫