MySQL主从数据库同步延迟问题解决

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

MySQL主从数据库同步延迟问题

     

                        摘要: MySQL的主从同步是一个很成熟的架构,优点为:①在从服务器可以执行查询工作(即我们常说的读功能),降低主服务器压力;②在从主服务器进行备份,避免备份期间影响主服务器服务;③当主服务器出现问题时,可以切换到从服务器。 相信大家对于这些好处已经非常了解了,在项目的部署中也采用这种方案。但是MySQL...



MySQL的主从同步是一个很成熟的架构,优点为:①在从服务器可以执行查询工作(即我们常说的读功能),降低主服务器压力;②在从主服务器进行备份,避免备份期间影响主服务器服务;③当主服务器出现问题时,可以切换到从服务器。

相信大家对于这些好处已经非常了解了,在项目的部署中也采用这种方案。但是MySQL的主从同步一直有从库延迟的问题,那么为什么会有这种问题。这种问题如何解决呢?

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

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

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

 

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

答:谈到MySQL数据库主从同步延迟原理,得从mysql的数据库主从复制原理说起,mysql的主从复制都是单线程的操作,主库对所有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数据库主从同步延迟解决方案

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

mysql-5.6.3已经支持了多线程的主从复制。原理和丁奇的类似,丁奇的是以表做多线程,Oracle使用的是以数据库(schema)为单位做多线程,不同的库可以使用不同的复制线程。

 

基于局域网的master/slave机制在通常情况下已经可以满足'实时'备份的要求了。如果延迟比较大,就先确认以下几个因素: 
1. 网络延迟
2. master负载
3. slave负载
一般的做法是,使用多台slave来分摊读请求,再从这些slave中取一台专用的服务器,只作为备份用,不进行其他任何操作,就能相对最大限度地达到'实时'的要求了

slave_net_timeout单位为秒 默认设置为 3600秒

参数含义:当slave从主数据库读取log数据失败后,等待多久重新建立连接并获取数据

master-connect-retry单位为秒 默认设置为 60秒

参数含义:当重新建立主从连接时,如果连接建立失败,间隔多久后重试。

通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟

 

判断主从延时,通常有两个方法:

1. Seconds_Behind_Master  vs  2. mk-heartbeat,下面具体说下两者在实现功能的差别。

可以通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断,是否有发生主从延时。
其值有这么几种:
NULL - 表示io_thread或是sql_thread有任何一个发生故障,也就是该线程的Running状态是No,而非Yes.
0 - 该值为零,是我们极为渴望看到的情况,表示主从复制良好,可以认为lag不存在。
正值 - 表示主从已经出现延时,数字越大表示从库落后主库越多。
负值 - 几乎很少见,只是听一些资深的DBA说见过,其实,这是一个BUG值,该参数是不支持负值的,也就是不应该出现。

Seconds_Behind_Master是通过比较sql_thread执行的event的timestamp和io_thread复制好的 event的timestamp(简写为ts)进行比较,而得到的这么一个差值。我们都知道的relay-log和主库的bin-log里面的内容完全一 样,在记录sql语句的同时会被记录上当时的ts,所以比较参考的值来自于binlog,其实主从没有必要与NTP进行同步,也就是说无需保证主从时钟的 一致。你也会发现,其实比较真正是发生在io_thread与sql_thread之间,而io_thread才真正与主库有关联,于是,问题就出来了, 当主库I/O负载很大或是网络阻塞,io_thread不能及时复制binlog(没有中断,也在复制),而sql_thread一直都能跟上 io_thread的脚本,这时Seconds_Behind_Master的值是0,也就是我们认为的无延时,但是,实际上不是,你懂得。这也就是为什 么大家要批判用这个参数来监控数据库是否发生延时不准的原因,但是这个值并不是总是不准,如果当io_thread与master网络很好的情况下,那么 该值也是很有价值的。(就好比:妈–儿子–媳妇的关系,妈与儿子亲人,媳妇和儿子也亲人,不见得媳妇与妈就很亲。开个玩笑:-)之前,提到 Seconds_Behind_Master这个参数会有负值出现,我们已经知道该值是io_thread的最近跟新的ts与sql_thread执行到 的ts差值,前者始终是大于后者的,唯一的肯能就是某个event的ts发生了错误,比之前的小了,那么当这种情况发生时,负值出现就成为可能。

方法2. mk-heartbeat,Maatkit万能工具包中的一个工具,被认为可以准确判断复制延时的方法。

mk-heartbeat的实现也是借助timestmp的比较实现的,它首先需要保证主从服务器必须要保持一致,通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表,里面至少有id与ts两个字段,id为server_id,ts就是当前的时间戳 now(),该结构也会被复制到从库上,表建好以后,会在主库上以后台进程的模式去执行一行更新操作的命令,定期去向表中的插入数据,这个周期默认为1 秒,同时从库也会在后台执行一个监控命令,与主库保持一致的周期去比较,复制过来记录的ts值与主库上的同一条ts值,差值为0表示无延时,差值越大表示 延时的秒数越多。我们都知道复制是异步的ts不肯完全一致,所以该工具允许半秒的差距,在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复 制,巧妙的借用timestamp来检查延时,赞一个!

pt-heartbeat也不错

http://douya.blog.51cto.com/6173221/1735386




      本文转自crazy_charles 51CTO博客,原文链接:http://blog.51cto.com/douya/1860938,如需转载请自行联系原作者



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
存储 SQL 关系型数据库
MySQL的安装&数据库的简单操作
本文介绍了数据库的基本概念及MySQL的安装配置。首先解释了数据库、数据库管理系统和SQL的概念,接着详细描述了MySQL的安装步骤及其全局配置文件my.ini的调整方法。文章还介绍了如何启动MySQL服务,包括配置环境变量和使用命令行的方法。最后,详细说明了数据库的各种操作,如创建、选择和删除数据库的SQL语句,并提供了实际操作示例。
58 13
MySQL的安装&数据库的简单操作
|
7天前
|
消息中间件 canal 关系型数据库
Maxwell:binlog 解析器,轻松同步 MySQL 数据
Maxwell:binlog 解析器,轻松同步 MySQL 数据
42 11
|
4天前
|
关系型数据库 Unix MySQL
MySQL是一种关系型数据库管理系统
MySQL是一种关系型数据库管理系统
12 2
|
7天前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
18 2
|
12天前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
23 4
|
20天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
22天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤:启动软件后,通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后,选择“数据导出”,确认导出的数据库及格式(推荐SQL格式),设置存储路径,点击“开始导出”。完成后,可在指定路径找到备份文件,建议定期备份并存储于安全位置。
162 11
|
2月前
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID:如何让数据库备份恢复变得轻松简单?
【8月更文挑战第22天】MySQL的binlog日志记录数据变更,用于恢复、复制和点恢复;GTID为每笔事务分配唯一ID,简化复制和恢复流程。开启binlog和GTID后,可通过`mysqldump`进行逻辑备份,包含binlog位置信息,或用`xtrabackup`做物理备份。恢复时,使用`mysql`命令执行备份文件,或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
173 2
|
2月前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库,一步就能脱胎换骨
阿里云瑶池数据库来开课啦!自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定!点击阅读原文完成实验就可获得一本日历哦~
|
2月前
|
关系型数据库 MySQL 数据库
RDS MySQL灾备服务协同解决方案构建问题之数据库备份数据的云上云下迁移如何解决
RDS MySQL灾备服务协同解决方案构建问题之数据库备份数据的云上云下迁移如何解决
下一篇
无影云桌面