MySQL高可用：主备延时如何解决？

我们知道主备同步是依赖于 binlog，主库负责生产 binlog，备库负责消费 binlog，从而实现主备同步。

今天我们来学习一下主备同步里的一个重点的问题：主备延时。

主备延时，简单来说，就是主库和备库的数据一致出现一定的时间差，比如备库的此刻的数据快照是主备5分钟前的数据快照，那就说明主备延时有5分钟。

主备延迟是怎么产生的

产生主备延迟的根本原因是备库上消费 binlog 的速度赶不上主库产生 binlog 的速度。比如：

• 大事务，例如一次性delete很多数据；

• 大表的DDL；

• 备库压力大。例如有些像运维、订单等统计分析在备机上跑；

• 主备库的服务器的配置不同，主库的服务器配置好，备库的服务器配置差。

主备延迟的排查之路

网络

网络可能导致主备延迟的问题，比如主库或者备库的带宽满负载、主备之间网络延迟很大，有可能会导致主库的 binlog 没有全量传输到备库，造成延迟。

机器性能

备库 使用了烂机器？ 比如主库使用了 SSD，而备库使用的是 SATA。

备库 高负载？ 可能在备库上做统计分析，导致备库的负载很高。可使用 top 命令进行排查。

备库 磁盘有问题？ 磁盘、raid卡、调度策略有问题的情况下，有的时候会出现单个IO延迟很高的情况。可使用 iostat 查看 IO 运行情况。

大事务

是否经常有大事务？ 比如在 RBR 模式下，执行带有大量的 delete 操作，或者一个表的 alter 操作等，都会导致延时情况的发生。可通过 processlist 命令查看相关信息，或者使用 mysqlbinlog 查看 binlog 中的 SQL 就能快速确认。

锁

锁冲突问题也可能导致备库的 SQL 线程执行慢。比如一些 select ... for update 的 SQL。可通过 processlist 和 查看 information_schema 下面与锁和事务相关的表来查看分析。

参数

如果使用的是 InnoDB 引擎，可以调整 innodb_flush_log_at_trx_commit、sync_binlog 参数来提升复制速度。

sync_binlog 的默认值是 0，MySQL 不会将 binlog 同步到磁盘，其值表示每写多少 binlog 同步一次磁盘。

innodb_flush_log_at_trx_commit 其值表示每一次事务提交或事务外的指令需要把日志 flush 到磁盘。

注：这种调整可能会影响数据的安全性，需要结合业务来考虑。

多线程

在 MySQL 5.6 版本之前，MySQL采用单线程复制，而从 5.6 开始，正式支持多线程复制。

如果是单线程同步，单个线程存在写入瓶颈，导致主备延迟，那就先调整为多线程试试效果。

可以通过 show processlist 查看是否有多个同步线程，也可以查看参数的方式查看是否使用多线程（show variables like '%备库_parallel%'）

image

当你看到是上图这种结果的时候，恭喜你，你使用的是单线程。使用下面那行命令改造成多线程复制：

STOP 备库 SQL_THREAD;SET GLOBAL 备库_parallel_type='LOGICAL_CLOCK';SET GLOBAL 备库_parallel_workers=8;START 备库 SQL_THREAD;

改造后如下图所示：

image

作者：大杂草
原文链接：https://www.cnblogs.com/liang24/p/14149425.html