MySQL解决主从延迟

MySQL解决主从延迟 

简单说下几种方案，优缺点自行比较

1、应用代码来实现  延迟较大时直接从主库读取或者sleep

2、采用mysql proxy 自己编写lua脚本来实现 原来就是主从数据库创建一个同步表,主库上利用一个触发器保证任何数据更新时同步表的数据也被更新,任何一条查询之前mysql proxy都需要检测主从数据库的同步表是否同步,同步则查询发送到从库否则发送到主库

3、google的SemiSyncReplication补丁。大致原理是主库更新数据库时会先commit然后等待从库的应答,在确认从库同步完成之后,再返回该次commit的结果。详细资料百度吧.

暂时还未发现特别完美的解决方案。根据项目实际需要来决定采用什么方案吧

--slave-net-timeout=seconds    参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据 

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

 --master-connect-retry=seconds  参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试。

  master-connect-retry单位为秒 默认设置为 60秒 通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟   

MySQL的主从同步机制非常方便的解决了高并发读的应用需求，给Web方面开发带来了极大的便利。但这种方式有个比较大的缺陷在于MySQL的同步机制 是依赖Slave主动向Master发请求来获取数据的，而且由于服务器负载、网络拥堵等方面的原因，Master与Slave 之间的数据同步延迟是完全没有保证的。短在1秒内，长则几秒、几十秒甚至更长都有可能。 

由于数据延迟问题的存在，当应用程序在Master 上进行数据更新，然后又立刻需要从数据库中读取数据时，这时候如果应用程序从Slave上取数据(这也是当前Web开发的常规做法)，就可能出现读取不到期望的数据，造成程序运行异常。 

解决这个问题有多种方式，比如最简单的在所有的insert和update之后，强制sleep几秒钟。这是非常粗鲁的方式，对于更新操作不是很高的中小型系统，此方式基本能解决问题。 

另外一种方式是应用程序把被更新的数据保存在本机的内存(或者集中式缓存)中，如果在写入数据完成后需要直接读取数据，则从本机内存中读取。这种方式的缺点是极大的增加了应用程序的复杂度，而且可靠性并不能完全得到保障。 

使用MySQL Proxy可以很方便的解决这个问题。MySQL Proxy是基于MySQL Client 和 MySQL Server之间的代理程序，能够完成对Client所发请求的监控、修改。从Client角度看，通过Proxy访问Server和直接访问 Server没有任何区别。对于既有的程序而言，只要把直接被访问的Server的IP地址和端口号换成Proxy的IP地址和端口号就可以。 

MySQL Proxy的工作原理也较简单。在Proxy启动时可以指定Proxy所需要使用的lua脚本，在lua脚本中预先实现6个方法： 

    * connect_server()     // 接收到Client的连接请求时调用 
    * read_handshake()   // 
    * read_auth()               // 读取Client的认证信息时调用 
    * read_auth_result() // 读取认证结果时调用 
    * read_query()            // 读取Client的query请求时调用 
    * read_query_result()   //读取query结果时调用 

当 Proxy接收到Client请求时，在请求的不同的阶段会调用上面的不同方法。这样Proxy使用者就可以根据自己的业务需求，自由的实现这6个方法达到目的。 

通过在read_query()中加入代码，我们可以截取出当前的请求是insert、update还是select，然后把 insert和update请求发送到Master中，把select请求发送到Slave中，这样就解决了读写分离的问题。 

在解决了读写分离后，如何解决同步延迟呢？ 

方法是在Master上增加一个自增表，这个表仅含有1个的字段。当Master接收到任何数据更新的请求时，均会触发这个触发器，该触发器更新自增表中的记录。如下图所示： 
 
mysql_proxy_write 

由于Count_table也参与Mysq的主从同步，因此在Master上作的 Update更新也会同步到Slave上。当Client通过Proxy进行数据读取时，Proxy可以先向Master和Slave的 Count_table表发送查询请求，当二者的数据相同时，Proxy可以认定 Master和Slave的数据状态是一致的，然后把select请求发送到Slave服务器上，否则就发送到Master上。如下图所示： 
 
mysql_proxy_read 

通过这种方式，就可以比较完美的结果MySQL的同步延迟不可控问题。之所以所“比较完美”，是因为这种方案double了查询请求，对 Master和Slave构成了额外的压力。不过由于Proxy与真实的Mysql Server采用连接池的方式连接，因此额外的压力还是可以接受的。