如何解决主从数据库同步延迟问题？

如何解决主库插入记录后，无法从从库中及时读取的问题,如何从架构上避免这种问题
在网上见过新建一个版本库的表，然后利用mysql proxy判断数据是否是最新的，然后路由到主库或者是从库，请问这个方案是可行的吗？具体如何操作？

回复内容：

从你描述的场景来看，你需要在主机写入之后，保证在备机一定能够读取到已经写入的数据，也就是说，你需要主从架构下的强一致性。

主机与备机之间的物理延迟是不可控的，也是无法避免的。但是如果仅仅需要满足这种强一致性，是相对简单的事：只需要在主机写入时，确认更新已经同步到备机之后，再返回写操作成功即可。主流数据库均支持这种完全的同步模式。已经有人提到MySQL的Semi-sync功能（从MySQL 5.6开始官方支持，此前的版本可以考虑Google出的非官方补丁），就是基于这种原理。

不过，一般不建议使用这种同步模式。显而易见，如果写操作必须等待更新同步完成，肯定会极大地影响性能，除非你不在乎性能。

问题的关键在于，主从架构是一种用于数据容错的高可用性解决方案，而不是一种处理高并发压力的解决方案。它的目的是主机当机以后备机可以马上顶上，而不是让备机来分担并发压力。完全同步机制也只是用于保证主机当机以后数据不会丢失，而不是保证从备机读取数据时的一致性。因此，我根本也不主张你使用从备机读取数据以分担并发压力这种方式。

解决方式是，不要试图在数据库层解决并发的读操作问题，至少不要在主从架构的数据库层解决。要在数据库层之上架构一个redis这样的分布式缓存来解决，它是专门干这个的。其性能肯定远高于从备机读取数据。

分布式缓存也存在着一些限制，例如不能完全支持事务处理。这取决于你的应用场景。对于一般的互联网应用，并发压力大但不要求支持事务，可以考虑分布式缓存。对于银行这样严格要求强一致性的应用，对于写入延迟一般没什么要求（延迟几个小时都可以接受，数据不出错就行），可以适用完全同步的模式。

另外，不建议你使用“通过版本库判断最新版本再分别路由到主机或备机”的山寨版解决方案。这会对应用层的代码造成严重污染。

MySQL replication
我的经验里，最重要的是尽量避免让任何一台mysql服务器跑满。
所以真正的解决方案是精准的capacity planning，适时地scale out。 尽量不依赖主从同步，比如多主方案。 主从同步延迟是必然现象，不是问题。关键看具体业务，因同步延迟带来什么问题，然后再解决。

举个简单的例子

假设某论坛是主从数据库，我发一个帖子后立即刷新页面，因为显示帖子是读，这个时候如果延迟比较厉害，就会提示 404 -———帖子不存在，这就有问题了；我们还要假设用户的容忍度是看见自己的新内容，别人新的内容可以有延迟（实际上延迟是很小的时间单位）。

针对这个假设的问题，可以采取几种方案：
1、有更新数据后的 读取相关数据动作，都从默认到主库；
2、利用缓存；插入新的数据，会有last_id返回，组装成数据，缓存到前端。读取此 id 数据时，先从缓存取。 题主说的方案感觉非常不靠谱。
不过mysql-proxy本人也几乎没怎么接触，它能否实现上诉功能有些不大确定，即使它有，也不建议为了这个就用它，官网自己都不推荐用到生产环境。

针对主从延迟，本人的经验如下：

• 业务量不大的

主库能处理业务就全放在主库吧，从库只做灾备，备份，对实时性要求不高的统计报表类工作；

• 已经出现延迟的

一般来说，就慢慢等吧，试图通过重启db之类的操作是无法解决的，还会因为大事务回滚再重做导致花的时间更长。

• 延迟N天无法解决的

那就重做slave。
为什么会延迟N天，难道仅仅是因为从库单线程吗？
我感觉大部分都是主库上采用mixed的binlog_format，由于某种限制，无法基于statement，只好row模式复制。
那么如果当前sql是全表扫描，传到slave上执行时就是茫茫多次的全表扫描了。
一般来说在slave上show proceslist看查看当前的system user正在执行什么，那就是问题SQL。如果pos点一直不动，也可以去主库对应的binlog上查看下执行的是什么玩意。

• 出现延迟时，查看下当前slave的cpu和磁盘状况

一般来说如果从库没有其他业务，单线程的原因，cpu跑满一个核已经是极限了。磁盘io满的话，确认下是否有其他进程或mysql线程影响了它(比如从库正在dump或者超大的sql在执行)，也可以尝试调整下slave上关于io的几个参数

• 从库raid卡，务必设置成write back的写策略
  

这点本人深受其害，查了几个月才发现为什么我的SSD io性能这么烂。

• 批量的dml操作
  

批量的dml操作如果不做处理，一般必然会出现延迟，建议业务低峰期执行，并将批量操作做下调整，一次dml 10000行，sleep一会，再dml 10000行。
具体的行数和sleep需要自己根据业务确定，能保证从库不延迟就好。

• 一点别的tips：
  

1. 如果还是经常性的短时间延迟，那就尝试加大从库的硬件配置，比如上sata SSD，pcie等
   
2. 延迟的监控到位，可通过pt-heart-beat来准确监控延迟值，及时发现查看。
   
3. 5.5以后版本的，可以考虑采用半同步复制，能解决少量延迟引起的问题，不过对tps性能损耗较大
   
4. 升级到mysql 5.7吧，多线程复制，几乎完美解决单线程复制引起的从库延迟。

完全同步是一个非常昂贵和复杂的操作，负载量大的话几乎不可能完成。所以聪明的办法是调整上层的逻辑，避免这种需求。 可以采用Redis技术， 新浪微博就大量使用了Redis技术。 区别于Memcached的是，redis会周期性的把更新的数据写入磁盘或者把修改操作写入追加的记录文件，并且在此基础上实现了主从同步。这需要假设Redis服务器了。具体请看新浪网首席DBA杨海朝的视频和PPT http://blog.nosqlfan.com/html/2692.html。 中间加个nosql层

可以试一试 采用主动复制的技术来解决MySQL主从之间复制的延迟问题，比如Twitter还专门开发了用于复制和分区的中间件gizzard(twitter/gizzard · GitHub) 。
主动复制虽然解决了被动复制的延迟问题，但也带来了新的问题，就是数据的一致性问题

可以改善延时,没法杜绝

你可以看看这个:Percona, http://www.percona.com/ 或许能满足你的要求...