MYSQL主从不同步延迟原理分析及解决方案
更新时间：2013年10月4日 23:36 点击：3166
1. 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数据库主从同步延迟解决方案。
丁奇的transefer是一个不错的方案，不过一般公司受限于对mysql的代码修改能力的限制和对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)为单位做多线程，不同的库可以使用不同的复制线程。
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢 失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘 同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表，MySQL服务器 处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍 然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事务支持，该选项作废了），该选项可以提供更大程度的安全，使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收 回滚的语句)。
innodb_flush_log_at_trx_commit （这个很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。
要说延时原理，得从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数据库主从同步延迟解决方案。
丁奇的transefer是一个不错的方案，不过一般公司受限于对mysql的代码修改能力的限制和对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)为单位做多线程，不同的库可以使用不同的复制线程。
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢 失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘 同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表，MySQL服务器 处理COMMIT语句，它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃，重启时，事务被InnoDB回滚，但仍 然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释：在MySQL 5.1中不需要--innodb-safe-binlog；由于引入了XA事务支持，该选项作废了），该选项可以提供更大程度的安全，使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步，该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收 回滚的语句)。
innodb_flush_log_at_trx_commit （这个很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。
相关文章
- 鼠标划过实现延迟加载并隐藏层的js代码...2013-10-13
- 因此，正确的原子操作是真正被执行过的。是物理执行。在当前事务中确实能看到插入的记录。最后只不过删除了。但是AUTO_INCREMENT不会应删除而改变值。1、为什么auto_increament没有回滚？因为innodb的auto_increament的...2014-05-31
- 这篇文章主要介绍了c# 线程同步的相关资料，帮助大家更好的理解和学习c#，感兴趣的朋友可以了解下...2020-08-29
- 1. MySQL数据库主从同步延迟原理。要说延时原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日...2013-10-04
- 索引并不是时时都会生效的，比如以下几种情况，将导致索引失效： 1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引 ...2014-06-07
- 这篇文章主要介绍了C#实现程序等待延迟执行的方法,涉及C#动态链接库的使用及延迟的实现技巧,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了C#同步网络时间的方法,以实例形式较为详细的分析了C#获取网络时间与同步本机系统时间的相关技巧,非常具有实用价值,需要的朋友可以参考下...2020-06-25
- Underscore 是一个 JavaScript 工具库，它提供了一整套函数式编程的实用功能，但是没有扩展任何 JavaScript 内置对象。这篇文章主要介绍了underscore源码分析相关知识，感兴趣的朋友一起学习吧...2016-01-02
- 本文章介绍两个实例来介绍mysql同步状态检测实现程序有需要的朋友可参考一下。 代码如下 复制代码 #!/bin/sh #check MySQL_Slave St...2016-11-25
- 本文主要讲述了利用Python网络爬虫对指定京东商城中指定商品下的用户评论进行爬取，对数据预处理操作后进行文本情感分析，感兴趣的朋友可以了解下...2021-05-28
- Google是这样介绍PageRank的： Google 出类拔萃的地方在于专注开发“完美的搜索引擎”，联合创始人拉里·佩奇将这种搜索引擎定义为可“确解用户...2017-07-06
Fatal error: Cannot redeclare class 原因分析与解决办法
我使用的都是php __autoload状态自动加载类的，今天好好的程序不知道怎么在运行时提示Fatal error: Cannot redeclare class 了，看是重复定义了类，下面我来分析一下解决办...2016-11-25- 这篇文章主要介绍了VSCode 云同步扩展设置Settings Sync插件，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2020-06-29
- 先修改Master（10.1.123.197）的 my.cnf 配置在 [mysqld] 中新增以下内容:复制代码 代码如下:log-bin=mysql-binlog-bin-index=mysql-bin.indexserver-id = 1sync_binlog=1binlog_format=mixed然后指定要做同步的数据库，并...2014-05-31
- 这篇文章主要为大家详细介绍了C#多线程及同步示例，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2020-06-25
- 这篇文章主要介绍了C#延迟执行方法函数实例讲解，这是比较常用的函数，有需要的同学可以研究下...2021-03-06
- 这篇文章主要介绍了C#实现多线程的同步方法,实例分析了C#线程同步的实现技巧,具有一定参考借鉴价值,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了vue data变量相互赋值后被实时同步的解决步骤，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-08-05
- 文章简单的分析了在php文件包含时inlcude的一个漏洞分析，下面希望对大家有点用处哦。 基本的文件包含漏洞: 代码如下 复制代码 <?php include...2016-11-25
- php教程 echo print print_r三者区别分析 echo是PHP语句, print和print_r是函数,语句没有返回值,函数可以有返回值(即便没有用) print（） 只能打印出简单类型变量的...2016-11-25