如何解决MySQL主从复制延迟问题

MySQL主从复制是面试中绕不开的重要部分。虽然里面的知识点都是基础的，但是能够全部答对的学生并不多。今天我们就来说说一些常见的地方。

本文全文如下。

1。 MySQL主从

1.1 什么是MySQL主从？

MySQL主从复制是指可以将数据从MySQL数据库服务器主节点复制到一个或多个从节点。默认情况下，MySQL 使用异步复制。从节点可以复制主库中的所有数据库、特定数据库或特定表。

1.2 为什么使用MySQL主从？

MySQL主从复制架构是MySQL集群中最基本、最常用的架构实现，可以满足很多业务需求。大致来说有以下几种使用场景

1。数据备份：数据有多个镜像，数据冗余，可以防止单主机数据丢失，提高数据安全性。另外，数据在从服务器上进行备份，不影响主服务器的正常运行。

2。高可用性：当主机宕机时，可以切换到从服务器，并了解数据的一致性（标准异步复制）。

3。读写分离：业务中可以实现读写分离。即可以在从服务器上进行一些读操作，以减轻主服务器的负载。例如，在从服务器上创建数据报表和统计可以避免生产服务器过大的访问压力。

2。主从复制

2.1 主从复制原理

说到主从复制，就离不开binlog。我们先从binlog开始。

binlog是一个二进制文件，主要记录所有数据库表结构的更改（如CREATE、ALTER TABLE...）以及表数据修改的所有操作（INSERT、UPDATE、DELETE...）。

binlog是mysql的逻辑日志，由服务器层注册。使用任何存储引擎的Mysql数据库都会记录binlog日志。

在实际应用中，使用binlog主要有四种场景：

• 恢复：某些数据的恢复需要二进制日志。例如，恢复完整的数据库文件后，用户可以通过二进制日志进行时间点恢复。
• 复制：其原理与恢复类似。通过复制和执行二进制日志，远程MySQL数据库（一般称为从库或备用库）和MySQL数据库（一般称为主库或主库）可以实时通信。同步。
• 审计：用户可以查看二进制日志中的信息来确定数据库是否存在注入攻击。
• 用于数据备份。数据库备份文件生成后，binlog会保存数据库备份文件后的详细信息，以便下次备份可以从该备份点开始。

❝注：除了上面介绍的功能外，binlog在事务存储引擎的崩溃恢复中也发挥着非常重要的作用。当binlog开启时，为了保证binlog和redo的一致性，MySQL会使用事务两阶段提交协议。

当MySQL系统崩溃时，存储引擎中事务的状态可以是prepared或commit。

对于Prepared状态的事务，无论要进行commit操作还是rollback操作，此时都会参考binlog：如果binlog中存在该事务，则commit；如果binlog中没有找到，则回滚，这样就保证了主从库数据的一致性。

复制流程基础（联试）：

• 主数据库写入数据并生成binlog文件。在此过程中，MySQL根据binlog转储线程将事务串行写入二进制日志。
• 事件写入二进制日志后，Master通知存储引擎提交事务。
• 从目录服务器上的IO线程连接主服务器，请求从执行binlog日志文件中指定位置读取binlog到从目录。
• 主库收到从库的IO线程请求后，其上复制的IO线程会根据Slave的请求信息批量读取binlog文件，返回给从库的IO线程。
• 从服务器IO线程检索主服务器IO线程发送的日志指定内容、日志文件和位置后，binlog日志内容将被写入到从服务器自己的中继日志（即中继日志）文件的末尾按照顺序，新的 binlog 文件名和位置将记录在 master info 文件中。所以下次读取master页上的新的binlog日志时，可以要求Master服务器从新的binlog日志的指定文件和位置开始读取新的binlog日志内容。
• 从服务器SQL线程会实时监听本地Relay日志中新的日志内容，然后将RelayLog中的日志翻译成SQL并依次执行SQL来更新从数据。
• 从库将当前应用中继日志的文件名和位置记录在relay-log.info中，以供下次数据复制。

3。主从延时

3.1 什么是主从延时？

根据之前的主从复制原理可以看出，两者之间存在一定时期的数据不一致，也就是所谓的主从延迟。

我们看一下导致主从延迟的时间：

• 主库A完成一笔事务并写入binlog。该时刻记为T1。
• 发送到从库B，从库接受。 binlog中的时间记录为T2。
• 从库B执行完该事务后，时间记录为T3。

所谓主从延时就是同一个事务，从库执行的时间为T3。完成，主库执行完成。时间之差，即T3-T1。

我们还可以在从库上执行Show Slave status。返回结果会显示seconds_behind_master，表示当前从库延迟了多少秒。

seconds_behind_master 是如何计算的？

• 每个事务的bin log都有一个时间字段，用于记录写入主目录的时间。得到的就是seconds_behind_master，也就是前面描述的T3-T1。

3.2 主从延迟情况概述

• 从库机性能：从库机性能比主库机性能差。只需选择一台规格相同的机器作为主从库即可。
• 从库压力很大：可以搭建主多从架构，也可以将binlog连接到Hadoop等系统，让其提供查询能力。
• 从库数量过多：为了避免复制过多的从节点，一般3-5个从库数据就足够了。
• 大事务：如果一个事务需要10分钟才能执行，那么它将在主库执行完之后由从库执行。最终，该事务可能会导致从数据库延迟 10 分钟。在日常开发中，不要一次删除太多的SQL语句。必须分批进行。另外，大表的DDL语句也会导致大事务。
• 网络延迟：优化网络，例如将带宽从20M升级到100M。
• MySQL版本低：MySQL低版本仅支持单线程复制。如果主库并发高，不能及时传输到从库，就会造成延迟。您可以升级到支持多线程复制的更高版本的 MySQL。

3.3 如何减少主从延迟

主从同步问题始终是一致性和性能之间的权衡。这取决于实际的应用场景。如果想减少主从延迟时间，可以采用以下方法：

• 降低多线程大事务并发概率，优化业务逻辑
• 优化SQL，避免慢SQL，减少batch操作。建议以update-sleep的形式编写脚本。
• 改进从库机器的配置，缩小写binlog的主库和读binlog的从库之间的效率差距。
• 尽量使用短链路，即主库和从库服务器之间的距离尽可能短，以增加端口带宽，减少binlog传输的网络延迟。
• 有实时性要求的业务读取被迫走主库，从库仅用于容灾备份。

4。主从切换

4.1 一个主从

两台机器A、B，A为主数据库，负责读写，B为从数据库，负责读取数据。 如果A库出现故障，B库成为主库负责读写。故障修复后，A成为从库，主库B同步数据到从库A。

• 优点：从库支持读取，分担主库压力，提高并发，可自动切换如果一台机器出现故障。操作比较简单，公司的从库还可以充当数据备份；
• 缺点：一台从库的并发支持还是不够，总共有两台机器。仍然存在同时发生故障的可能性，但这种可能性还不够高，无法实现。

对于一主从模式，一般是小型企业使用。但在这种模式下，主从分离并没有真正的意义，因为小型企业的流量并不高，更多的是为了数据库和数据备份的可用性。

4.2 一主多从

在生产环境中，为了满足安全性、高可用、高并发的需求，MySQL数据库架构基本都是MHA、MGR等，并带有自动切换脚本。 。实施自动故障转移。 一个主库，多个从库。 A为主库，负责读写。 B、C、D是从库，负责读取数据。 如果A库出现故障，B库成为主库负责读写，C、D负责读取。错误修复后，A也成为从库，主库B同步数据到从库A。

• 优点：多个从库支持读取，分担主库压力，显着提升读取并发度。
• 缺点：只有一台主机可以写，所以写的并发度不高。

高级阿涛数据与人