mysql主备模型一写多读,mysql读写分离（双写多读高可用）的尝试方案

一、设计目标

写入库双活：主从热备模式，程序只向一个写入库写入数据，主库W1死了后备库W2顶上，确保业务正常可用，运维修复W1之后重新加入集群，当后备库。

读写分离：由于项目业务逻辑复杂，需求调研混乱，程序毫无设计导致SQL性能奇差，为了防止select把cpu拉到100%导致写入失败，必须读写分离，目前设计为3台用于搜索的读取库(R1、R2、R3)，采用简单轮询的负载均衡机制。

二、设计思路

1、写入库双活

W1和W2均开启binlog，半同步插件，然后相互进行binlog同步。

通过keepalive的虚拟IP技术，W1和W2共享一个IP(10.0.0.10)，应用程序只面对10.0.0.10写入数据，不需要知道W1和W2的存在。

正常情况下10.0.0.10会被W1征用，数据直接写入W1再同步给W2；

W1的mysql如果故障，W1上面的keepalive自杀，10.0.0.10会被W2征用数据直接写入W2。

等到W1修复之后重新上线，W2的数据会同步给W1，实现双向热备。

2、读取库同步

这里需要先搞清楚mysql做binlog同步时的机制，简单点说mysql是将master_log发布给从库达到数据同步的效果，例如当我们向W1写入数据时，SQL会被记录到W1的master_log里面再同步给W2，但是W2不会把SQL写入master_log，所以订阅W2不会收到W1同步给W2的数据。这样的机制下当W1被写入时，读取库需要同步W1的master_log，而W1故障W2被写入时，我们需要同步W2的master_log。基于这样的机制，我们采用了多channel同步(多源复制)方式，确保W1和W2的数据都能同步到读取库，形成一个合集。

读取库的3台配置都一样，以下只写1台。

读取库开启binlog，半同步插件，然后使用for channel建立两个同步连接：

change master to master_host='10.0.0.11',master_user='repl',MASTER_PORT=3306,master_password='passw0rd',master_file='binlog-000001',master_position=1 FOR CHANNEL 'W1';

change master to master_host='10.0.0.12',master_user='repl',MASTER_PORT=3306,master_password='passw0rd',master_file='binlog-000001',master_position=1 FOR CHANNEL 'W2';

3、JAVA内置读取负载均衡

java项目的读写分离及读库负载均衡我们采用sharding jdbc 4.0.0 RC2，master配置为10.0.0.10，slaves为R1、R2、R3。

程序运行会轮流从R1、R2、R3中读取数据，有效分摊搜索压力。

三、总体结构图

四、存在问题

1、keepalive检测到mysql无效到切换IP到W2，大概会有5秒时间系统无法写入。

这个在可以接受的范围内，暂时不解决。

2、某一个读取库故障时，系统有1/3的概率读取数据失败，这时需要修改java配置，摘除故障的读取库，然后重启java程序，大概耗时5分钟。

由于java的连接池与mysql之间是长连接模式，用haproxy和nginx均无法实现读取库热拔插，目前未找到更好解决方法。