共计 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