MySQL主从同步延迟原因及解决办法

共计 1534 个字符，预计需要花费 4 分钟才能阅读完成。

MySQL 主从延迟原因以及解决方案：谈到 MySQL 数据库主从同步延迟原理，得从 mysql 的数据库主从复制原理说起，mysql 的主从复制都是单线程的操作 (mysql5.6 版本之前)，主库对所有 DDL 和 DML 产生 binlog，binlog 是顺序写，所以效率很高。

slave 的 Slave_IO_Running 线程会到主库取日志，效率会比较高，slave 的 Slave_SQL_Running 线程将主库的 DDL 和 DML 操作都在 slave 实施。DML 和 DDL 的 IO 操作是随机的，不是顺序的，因此成本会很高，还可能是 slave 上的其他查询产生 lock 争用，由于 Slave_SQL_Running 也是单线程的，所以一个 DDL 卡主了，需要执行 10 分钟，那么所有之后的 DDL 会等待这个 DDL 执行完才会继续执行，这就导致了延时。有朋友会说：“主库上那个相同的 DDL 也需要执行 10 分，为什么 slave 会延时？”，答案是 master 可以并发，Slave_SQL_Running 线程却不可以。

2.MySQL 数据库主从同步延迟是怎么产生的。

当主库的 TPS 并发较高时，产生的 DDL 数量超过 slave 一个 sql 线程所能承受的范围，那么延时就产生了，当然还有就是可能与 slave 的大型 query 语句产生了锁等待。

3.MySQL 数据库主从同步延迟解决方案

（1）最简单的减少 slave 同步延时的方案就是在架构上做优化，尽量让主库的 DDL 快速执行。还有就是主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而 slave 则不需要这么高的数据安全，完全可以讲 sync_binlog 设置为 0 或者关闭 binlog，innodb_flushlog 也可以设置为 0 来提高 sql 的执行效率。

（2）另外就是使用比主库更好的硬件设备作为 slave。

就是把，一台从服务器当度作为备份使用，而不提供查询，那边他的负载下来了，执行 relay log 里面的 SQL 效率自然就高了。

（3）增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载。

4.MySQL 数据库主从同步延迟产生的因素。

1. 网络延迟 2. master 负载 3. slave 负载 一般的做法是，使用多台 slave 来分摊读请求，再从这些 slave 中取一台专用的服务器，只作为备份用，不进行其他任何操作，就能相对最大限度地达到’实时’的要求了

另外，再介绍 2 个可以减少延迟的参数 –slave-net-timeout=seconds 参数含义：当 slave 从主数据库读取 log 数据失败后，等待多久重新建立连接并获取数据 slave_net_timeout 单位为秒 默认设置为 3600 秒 slave_net_timeout 3600 –master-connect-retry=seconds 参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。master-connect-retry 单位为秒 默认设置为 60 秒 通常配置以上 2 个参数可以减少网络问题导致的主从数据同步延迟。

MySQL5.6 主从复制搭建基于日志（binlog）http://www.linuxidc.com/Linux/2017-05/144162.htm

MySQL 主从配置图文详解  http://www.linuxidc.com/Linux/2017-04/143211.htm

Linux 环境下 MySQL 数据库主从同步配置  http://www.linuxidc.com/Linux/2017-04/143017.htm

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-06/144585.htm