MySQL 主从，5 分钟带你掌握

MySQL 主从一直是面试常客，里面的知识点虽然基础，但是能回答全的同学不多。

比如楼哥之前面试小米，就被问到过主从复制的原理，以及主从延迟的解决方案，因为回答的非常不错，给面试官留下非常好的印象。你之前面试，有遇到过哪些 MySQL 主从的问题呢？

所谓 MySQL 主从，就是建立两个完全一样的数据库，一个是主库，一个是从库， 主库对外提供读写的 *** 作，从库对外提供读的 *** 作 ，下面是一主一从模式：

对于数据库单机部署，在 4 核 8G 的机器上运行 MySQL 5.7 时，大概可以支撑 500 的 TPS 和 10000 的 QPS， 当遇到一些活动时，查询流量骤然，就需要进行主从分离。

大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级，所以我们可以通过一主多从的方式， 主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力。

MySQL 主从还能做到服务高可用，当主库宕机时，从库可以切成主库，保证服务的高可用，然后主库也可以做数据的容灾备份。

整体场景总结如下：

MySQL 的主从复制是依赖于 binlog 的，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。

主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的 *** 作不会等待 binlog 同步的完成。

详细流程如下：

当主库和从库数据同步时，突然中断怎么办？因为主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的。

对于下面的情况，假如主库执行如下 SQL，其中 a 和 create_time 都是索引：

我们知道，数据选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的。

所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条 SQL 时，使用的是索引 a，而从库在执行这条 SQL 时，使用了索引 create_time，最后主从数据不一致了。

那么我们改如何解决呢？

可以把 binlog 格式修改为 row，row 格式的 binlog 日志记录的不是 SQL 原文，而是两个 event:Table_map 和 Delete_rows。

Table_map event 说明要 *** 作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。 row 格式的 binlog 记录的就是要删除的主键 ID 信息，因此不会出现主从不一致的问题。

但是如果 SQL 删除 10 万行数据，使用 row 格式就会很占空间的，10 万条数据都在 binlog 里面，写 binlog 的时候也很耗 IO。但是 statement 格式的 binlog 可能会导致数据不一致。

设计 MySQL 的大叔想了一个折中的方案，mixed 格式的 binlog，其实就是 row 和 statement 格式混合使用， 当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用就用 statement 格式。

有时候我们遇到从数据库中获取不到信息的诡异问题时，会纠结于代码中是否有一些逻辑会把之前写入的内容删除，但是你又会发现，过了一段时间再去查询时又可以读到数据了，这基本上就是主从延迟在作怪。

主从延迟，其实就是“从库回放” 完成的时间，与 “主库写 binlog” 完成时间的差值， 会导致从库查询的数据，和主库的不一致 。

谈到 MySQL 数据库主从同步延迟原理，得从 MySQL 的主从复制原理说起：

总结一下主从延迟的主要原因 ：主从延迟主要是出现在 “relay log 回放” 这一步，当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了，当然还有就是可能与从库的大型 query 语句产生了锁等待。

我们一般会把从库落后的时间作为一个重点的数据库指标做监控和报警，正常的时间是在毫秒级别，一旦落后的时间达到了秒级别就需要告警了。

解决该问题的方法，除了缩短主从延迟的时间，还有一些其它的方法，基本原理都是尽量不查询从库。

具体解决方案如下：

在实际应用场景中，对于一些非常核心的场景，比如库存，支付订单等，需要直接查询从库，其它非核心场景，就不要去查主库了。

两台机器 A 和 B，A 为主库，负责读写，B 为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，修复故障后，A 成为从库，主库 B 同步数据到从库 A。

一台主库多台从库，A 为主库，负责读写，B、C、D为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，C、D负责读，修复故障后，A 也成为从库，主库 B 同步数据到从库 A。

主从延时排查方法：

第一种方法：

1.showmasterstatus\G#查看主库的position号记录到多少了。

2.从库中执行showslavestatus\G#查看从库现在获取到哪个position号了.

3.如果从库的postion号远小于主库的position号,则表示主库dump线程传送二进制出问题了.

第二种方法(推荐)：

通过监控showslavestatus命令输出的“Seconds_Behind_Master”参数的值来判断NULL，表示io_thread或是sql_thread有任何一个发生故障；

0，该值为零，表示主从复制良好；正值，表示主从已经出现延时，数字越大表示从库延迟越严重。为了再现这种高并发时刻，测试指令为：ab-c12-n10000http://tp5pro.com/index/test。

当我们的数据库压力主键变大的时候，我们会尝试增加一些从节点来分摊主节点的查询压力。而一般来说，我们是用一主多从的结构来作为读写分离的基本结构。

而一般来说我们有两种常用的方法来实现读且分离架构：

客户端直接分离

这种方式是由客户端，或者我们的微服务直接进行数据库的读写选择。将读库选择路由到主库上进行，将查询路由到从主库上进行。

这种方式的优点在于因为是直连所以性能比较高，但是需要由业务团队了解数据库的实例细节，当数据库做调整的时候就需要业务侧同步改造。

使用数据中间件代理

这种方式是由一层代理层对数据的读写做分发，业务层将所有的请求都通过代理来实现。

这种方式的优点在于对于业务层不需要感知到数据库的存在，但问题在于数据中间件的性能要求较高，还需要专人来进行优化和维护，整体架构较为复杂。

但是我们发现，尽管这两种方式各有优劣。但核心都是通过数据的写入、查询请求的路由而实现的，那么这就会引发标题的问题：

主备同步存在延迟，所以在延迟时间内对插入的内容进行查询则无法查询到最新提交的事务。

那么如何保证主从一致性的问题，其实就变成了如何处理主从延迟的问题。

根据项目的大小，团队的规模以及主机的部署模式。我们处理问题的方法也有很多种。

最简单强硬的就是强制读主库。

一般情况下我们在不同的查询中会有不同程度的一致性要求。我们可以将需要保证数据一致性的请求配置强制查询主库，而对于无强依赖的查询请求仍然查询备库。

尽管这个方案不是很优雅，但是是最简单实现的方法，并且在Spring等框架的支持下一般只需要加一个注解就能实现。但这个方法的问题也是显而易见的，如果存在大量的强一致性要求的查询语句，则相当于没有进行读写分离与扩展。那么这种方法就会导致系统在数据库层面没有有效的扩展手段了。

由于问题产生的来源是主从延迟，所以在下一次查询的时候进行一段时间的等待以弥补这种延迟即可。

所以在进行主库的数据插入之后，让数据库数据连接或者对应的执行线程等待一段时间后返回。通过等待时间来消化掉主从备份的延迟时间。但是这个方法也有一些问题比如：这个等待时间一般是固定的，即便主从已经无延迟了也会继续等待到时间结束；如果在服务高峰时期，有可能数据在等待时间结束后仍然没有完成同步则仍然会存在一致性问题。

但这种方法优雅的地方是可以配合业务来进行实现，举例来说当用户下单之后，通过下单送卷或者下单抽奖的方式从前端拖住用户，从而当用户在一次连续 *** 作中再次查询自己订单的时候中间必然会间隔一定时间，也就让需要再次查询数据的时候保证了数据的一致性。

上述两种方案看起来可能不那么“技术”，感觉有点投机取巧。那么下面咱们可以分两种情况来讨论用更高技术的方法如何实现一致性。

对于主从复制来说，是当主库完成一个事务后，通知给从库，当从库接受到后，则主库完成返回客户端。所以当主库完成事务后，仅能确保从库已经接受到了，但是不能保证从库执行完成，也就是导致了主从备份延迟。

但是从库执行数据是有进度的，而这个进度是可以通过show slave status语句中的seconds_behind_master来进行描述，这个参数描述从库落后了主库数据多少秒，当这个参数为0时，我们可以认为从库和主库已经基本上没有延迟了，那么这时候就可以查询请求。

但seconds_behind_master是秒级的，所以只能大概地判断，由于精度较低，所以还是可能出现不一致的情况。

如果要求精准执行的话，我们可以比较同步文件的执行记录，具体来说是：

所以当Relay_Master_Log_File和Exec_Master_Log_Pos和其一致的时候，就说明从库的已执行数据已经追上主库了，那么这时就可以说保证了主从一致性了

但是比较同步文件的执行记录方法的问题在于，如果当前的这个事务的binlog尚未传入到从库，即Master_Log_File和Read_Master_Log_Pos未更新，也就无法保证从库已经包含最新的主库事务了。

而为了保证在一主一备的情况下，从库里一定接受到数据了，也就是Master_Log_File和Read_Master_Log_Pos中的数据是和主库一致的，我们可以开启semi-sync replication半同步复制。

半同步复制的原理是在主库提交事务前先将binlog发送给从库，然后当从库接受后返回一个应答，主库只有在接受到这个应答之后才返回事务执行完成。这样就可以保证从库的Master_Log_File和Read_Master_Log_Pos与主库是一致的，从而解决了主从一致的问题。

半同步复制可以解决一主一备的情况，但是当一主多备的时候，只要主库接受到一个从库的应答，就会返回事务执行完成。而这时当请求打到未完成同步的从库上时就会发生主从延迟。

所以针对一主多备的情况，我们可以将目光集中在执行查询的从库上，即确保 我们即将查询的备库已经执行了我们预期的事务。 那么我们的问题就变成两部分：1. 确认主库事务，2. 查询数据条件。

确认主库事务

当我们提交完一个事务后，可以通过执行show master status来得到主库中的数据事务文件（File）和位置记录（Position）。

查询数据条件

当我们要查询从库数据的时候，我们可以通过语句select master_pos_wait(File, Position, 1)来查询当前是否已经执行到了该记录（当返回值>=0的时候说明已经执行过了）。其中最后的数字1表示阻塞时长。

通过先确认主库事务记录，再判确认备库是否已经执行了了主库对应的事务。

但是可以发现，这种方法要求查询的时候知道主库的事务信息，对场景有很大的限制。

主从一致的问题源自主从延迟，所以我们就是从如何消除延迟来解决问题。简单点的方案我们可以不走备库、或者直接等待一段时间来忽略延迟的影响。在一主一备的情况下我们可以粗力度的用seconds_behind_master来判断或者用Relay_Master_Log_File和Exec_Master_Log_Pos来判断。而当一主多从的情况下我们则需要在查询前传入主库执行的事务记录才能保证数据一致性。

可以看出，当数据规模和部署方式变更的时候，好的解决方案将会越来越多。我认为根据实际业务情况选择最合适的方法才是最重要的。

---