开发者社区> wangtaotao> 正文

mysql主从复制延迟问题的相关知识与解决方案

简介:               一、如何监控发生了主从延迟?   在从库机器上,执行show slave status,查看Seconds_Behind_Master值,代表主从同步从库落后主库的时间,单位为秒,若同从同步无延迟,这个值为0。
+关注继续查看

 

 

 

 

 

 

 

 

一、如何监控发生了主从延迟?

 

在从库机器上,执行show slave status,查看Seconds_Behind_Master值,代表主从同步从库落后主库的时间,单位为秒,若同从同步无延迟,这个值为0。

 

Mysql主从延迟一个重要的原因之一是:mysql是以单线程串行执行。

 

主从复制数据时,在从服务器上的mysql,是一个线程在同步数据。

串行的方式,它是指,执行一个后才继续执行下一个。如果一个卡住了,要等待时间,才会继续下一个。串行与并行是相反的。

 

 

二、同步延迟发生的场景

 

当主库的TPS并发较高时,产生的DDL(修改类的sql语句)数量,超过了slave机器sql线程所能承受的能力,那么延时就会产生了。

 

主库写binlog日志到文件的时候,是顺序写入到磁盘,顺序写入速度是很快,避免了磁盘随机寻址。

 

从库的同步线程(Slave_IO_Running),将binlog在slave上执行的时候,实际上是随机的,速度肯定要慢点。

 

 从库的同步线程(Slave_IO_Running)只有应该线程在操作,整个mysql实例就一个这样的线程,那么,如果mysql有n个库的数据需要同步,全部要这个线程来处理。人手不够啊(mysql-5.6.3)

 

 

 

三、解决思路

 

如何避免或解决主从延迟?可以用来解决的办法,有如下的:

 

  • 从库优化Mysql参数。比如增大innodb_buffer_pool_size,让更多操作在Mysql内存中完成,减少磁盘操作。

  • 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机,使用物理主机,这样提升了i/o方面性。

  • 从库使用SSD磁盘。机械硬盘是靠磁头旋转到指定位置来读数据、写数据。转来转去的,我们叫做i/o。磁盘i/o存在速度瓶颈。固态硬盘是一个电子设备,电子设备不需要机械旋转,读写固态硬盘上任意位置的数据,速度都是一样的。

  • 业务代码的妥协。将实时性要求高的某些操作,使用主库做读操作。比如我写了数据到主库了,需要马上展示数据,不要到从库去读数据,因为从库可能还没同步过去呢。直接从主库读数据,保证是最新的数据展示。

  • 从库的线程改为多个同步线程同步数据。mysql-5.6.3为了解决这个问题,从服务器上,每一个库开一个线程来同步。
  • 网络优化。网络堵塞,也会导致同步延迟。跨机房的数据库同步,会存在同步延迟。保证主从在同一个机房里面去。

 

 

 

 

 

附:mysql主从复制的三种格式数据

 

第一种是,statement格式。也就是记录下原来执行的sql语句。

 

这是最早的一种方式,后来发现也不是很完美,在复制的过程中会存在一些问题。

举例:由于sql语句中使用了某些mysql函数,而这个mysql函数是特定版本才有的,其他版本是没有这个函数,放到slave端运行,假如slave的mysql版本不一样,就可能执行出现问题。

 

使用了特定的功能,如果sql中使用了last_insert_id()函数,当同样的sql语句复制到slave端执行的时候,last_insert_id()所得到的结果是不同的。

 

上面两种情况导致了:复制过程中,slave端的结果没有完全与master端一致了。

而基于row格式的就不会。于是发明了row格式的。

 

第二种,基于row格式的。会记录下每一行修改前和修改后的值。binlog中存储的就是被修改行的修改前和修改后的值,直接拿到结果即可。重做。

基于row的格式有个缺点:涉及到ddl操作,比如alter table,加一个字段,那么意味着整个表的行都要进行修改。那么binlog中记录的是整个表中行的数据,造成binlog中的数据量很大。

于是,又发明了基于statement格式和基于row格式的综合版,叫做mixed

 

第三种:mixed

 

遇到ddl表变更操作,则使用statement格式,遇到delete或update格式,则使用row格式。

 

 

三种格式的发展过程总结:

 

一直使用statement格式,到了5.1.5版本才支持row格式。后来存储过程的出现,又带来了新的问题。存储过程中调用一些函数,在slave端运行结果会不同。所以5.1.8版本开始支持mixed格式。上面所有策略的做法目标是,让master与slave的数据保持一致。从这个角度出发。

 

 

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【Mysql】Mysql数据表区分大小写问题解决方案
原因 由于有的环境下,对数据表的大小写是明确区分的,但是我在导出的时候的数据表全部都是小写的,这个时候需要对环境进行配置 解决 vim /etc/my.cnf 在[mysqld]下加入一行:lower_case_table_names=1 service mysq...
703 0
oracle、mysql、sybase和sqlserver复制表结构和数据
Sql Server(sybase): 1.复制表结构: 新建表student2,并且结构同表syn_xj_student一致。Sql语句如下: select * into syn_xj_student2 from syn_xj_student where 1=2 2.复制表数据,并...
724 0
MySQL 主从复制资料汇总
1,复制原理 官方参考文档:http://dev.mysql.com/doc/refman/5.6/en/replication.
1061 0
从Mysql slave system lock延迟说开去
本文主要分析 sql thread中system lock出现的原因,但是笔者并明没有系统的学习过master-slave的代码,这也是2018年的一个目标,2018年我都排满了,悲剧。所以如果有错误请指出,也作为一个笔记用于后期学习。
6981 0
mysql主从复制
mysql主从复制 (超简单) 怎么安装mysql数据库,这里不说了,只说它的主从复制,步骤如下: 1、主从服务器分别作以下操作:   1.
595 0
在msys里进行复制和粘贴操作
You can copy text from an MSYS window to the clipboard simply by selecting the text you want to copy.
1008 0
+关注
wangtaotao
专注于互联网系统设计
167
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载