mysql读写分离的坑

Posted on | Edited on | In ,

mysql读写分离的坑

本章主要介绍mysql读写分离的架构，读写分离遇到的问题，以及解决方案。

mysql读写分离结构介绍

直连架构

见图：
avator

proxy

见图：
avator

小结：

  • 直连的架构，往往后端会有一个管理器（zookeeper）来管理所有的节点，动态的做主从切换，踢掉失联节点，和动态更新节点。
  • 客户端链接proxy，业务往往只关注业务逻辑即可。现在大多采用此方案

mysql读写分离架构遇到的挑战以及解决方案

无论采用直连还是proxy架构都会遇到主从不同步的问题，这里我们叫过期读，我们该采用什么方式解决呢？

强制走主库

业务方自己判断某些查询走主库某些查询走备库。比如：刚修改的数据立刻就获取的最新的数据从主库查询；数据分析等离线的情况走从库；

  • 优点：比较简单，由业务自己掌控，大部分情况都可以用此种方式解决；
  • 缺点：可能会遇到所有查询都需要从主库查询的情况，这样一主多从的架构就失去了作用；

sleep方案

修改和插入数据时候等待一段时间，然后在从备库查询，例如：页面ajax提交了修改请求，页面直接返回结果，当下次刷新时候从从库取出数据；

  • 优点：业务方决定，比较简单，且可以利用一主多从
  • 缺点：过期时间的不确定性，可能会导致数据一致性有问题

判断主备无延迟方案

在sql语句执行前线判断主库和从库是否有延迟，没延迟读取从库，有延迟读取主库。

判断seconds_behind_master参数

当参数为0说明无延迟，可以从从库读取，但是时间不精确只能精确到秒

通过位点来判断

从库通过show slave status命令取得如下俩对参数：Master_Log_File和Read_Master_Log_Pos，Relay_Master_Log_File和Exec_Master_Log_Pos，当这俩对参数相等说明主备无延迟，

通过gtid来判断

  • Auto_Position=1 ，表示这对主备关系使用了GTID
  • Retrieved_Gtid_Set，表示备库收到的日志GTID_SET
  • Executed_Gtid_Set，表示备库执行完成的GTID_SET

当着俩个集合相等说明主备无延迟

但是，这种方案有个缺点就是，备库判断的是自己的,relaylog和binlog的差异，但是如果这时候主库提交了个事物，备库还没有转成relaylog，备库会认为没有延迟，但是依然从备库差不到刚才主库刚提交的事务的数据，如下图：

avator

tx3事务的数据主库提交了，但是备库会看不到

结合semi-sync方案

上图的问题，结合semi-sync（办同步）方案可解决，semi-sync原理是什么呢？

  1. 开启semi-sync后，主库在提交时候后会发送binlog给备库
  2. 备库收到binlog以后，发给主库一个ack信号，说明已经收到
  3. 主库收到这个信号后才能给客户端返回事务已经提交

这种方案会有如下俩个缺点：

  1. 对于一主多从的结构，当一个从库发送ack信号给主库，主库会返回一个事务已经提交，这时候如果从其他从库查询还是有可能出现过期读；
  2. 如果数据库更新频繁，你会发现g
  3. tid或者位点主从会一直不相等，这样就失去了从库读写分离的意义。

等主库位点（gtid）

我们针对上面的痛点可以采用等主库位点或者gtid的方式来处理这种情况

等主库位点

命令:select master_pos_wait(file, pos[, timeout]);

  • 该命令含义：返回从命令执行开始，经过timeout秒后，从库执行到file（binlog）的pos位置执行了多少个事物。
  • 返回值含义:
    • NULL:失联
    • -1：超时
    • 0：执行即到了改位置。执行了0个事物
    • N：从库到这个位置执行了N个事物。

具体流程如图:
avator

  1. tx1提交后，在master上执行show master status;获取主库的binlog和binlog_pos。
  2. 在从库上执行查询语句前，先执行select master_pos_wait(file, pos[, timeout]);
  3. 如果返回>=0,slect在从库执行，否则主库执行

等GTID

命令:select wait_for_executed_gtid_set(gtid_set, timeout);

  • 该命令含义：等待从库timeout秒后，执行的gtid_set包含了gtid_set
  • 返回值含义:
    • 0：包含
    • 1：超时

具体流程如图:
avator

  1. tx1提交后，在master上执行show master status;获取主库的gtid_set。（5.7后可以通过函数直接获取该事务返回的gtid,具体见后面）
  2. 在从库上执行查询语句前，先执行select wait_for_executed_gtid_set(gtid_set, timeout);
  3. 如果返回0从库，1主库

注：5.7之后获取事务提交后gtid的方法

set session_track_gtids=OWN_GTID
执行事务并且提交
客户端通过函数：mysql_session_track_get_first 这个函数只针对客户端调用

注：开启semi-sync待完善