mysql 主从延迟原因以及解决方案

  1. MySQL 数据库主从同步延迟原理。
  2. MySQL 数据库主从同步延迟是怎么产生的。
  3. MySQL 数据库主从同步延迟解决方案。
  4. MySQL 数据库主从同步延迟产生的因素。

1.MySQL 数据库主从同步延迟原理。

答:谈到 MySQL 数据库主从同步延迟原理,得从 mysql 的数据库主从复制原理说起,mysql 的主从复制都是单线程的操作,主库对所有 DDL 和 DML 产生 binlog,binlog 是顺序写,所以效率很高;slave 的 SlaveIORunning 线程会到主库取日志,效率会比较高,slave 的 SlaveSQLRunning 线程将主库的 DDL 和 DML 操作都在 slave 实施。DML 和 DDL 的 IO 操作是随机的,不是顺序的,因此成本会很高,还可能是 slave 上的其他查询产生 lock 争用,由于 SlaveSQLRunning 也是单线程的,所以一个 DDL 卡主了,需要执行 10 分钟,那么所有之后的 DDL 会等待这个 DDL 执行完才会继续执行,这就导致了延时。有朋友会问:“主库上那个相同的 DDL 也需要执行 10 分,为什么 slave 会延时?”,答案是 master 可以并发,SlaveSQLRunning 线程却不可以。

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

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

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

答:最简单的减少 slave 同步延时的方案就是在架构上做优化,尽量让主库的 DDL 快速执行。还有就是主库是写,对数据安全性较高,比如 syncbinlog=1,innodbflushlogattrxcommit = 1 之类的设置,而 slave 则不需要这么高的数据安全,完全可以讲 syncbinlog 设置为 0 或者关闭 binlog,innodbflushlog 也可以设置为 0 来提高 sql 的执行效率。另外就是使用比主库更好的硬件设备作为 slave。

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

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