mysql主备延迟

什么是主备延迟

1.主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;

2.之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;

3.备库 B 执行完成这个事务，我们把这个时刻记为 T3

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

什么情况会导致主备延迟

情况一：备库所在机器的性能要比主库所在的机器性能差。

情况二：备库的压力大。

​ 由于主库直接影响业务，大家使用起来会比较克制，反而忽视了备库的压力控制。结果就是，备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。

​ 备库压力大解决方案：

​ 1.一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。

​ 2.通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。

情况三：大事务。

​ 1.delete多行等操作。

​ 2.大表的DDL操作

注意主备延迟后，还是会有读到过期数据的情况，解决方案。

强制走主库方案：

将查询请求做分类，必须拿到最新数据的走主库，可以读到旧数据的走从库。

好像sharding-jdbc如果一个事务里先写后读，会从主库读取，不带写操作的事务会走从库。

sleep 方案：

​ select sleep(1)

​ 如果这个查询请求本来 0.5 秒就可以在从库上拿到正确结果，也会等 1 秒；

​ 如果延迟超过 1 秒，还是会出现过期读。

判断主备无延迟方案：

​ 方案1：show slave status 结果里的 seconds_behind_master

​ 方案2：对比位点确保主备无延迟：

​ Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点； Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。

​ 方案3：对比 GTID 集合确保主备无延迟：Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。

配合 semi-sync 方案：（一主一备）

​ 事务提交的时候，主库把 binlog 发给从库；

​ 从库收到 binlog 以后，发回给主库一个 ack，表示收到了；

​ 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

​ 当一主多从时：

​ 如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据；

​ 但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。

等主库位点方案：

​ select master_pos_wait(file, pos[, timeout]);

​ trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position；

​ 选定一个从库执行查询语句；

​ 在从库上执行 select master_pos_wait(File, Position, 1)；

​ 如果返回值是 >=0 的正整数，则在这个从库执行查询语句；

​ 否则，到主库执行查询语句。

等 GTID 方案：

​ select wait_for_executed_gtid_set(gtid_set, 1);

​ trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；

​ 选定一个从库执行查询语句；

​ 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；

​ 如果返回值是 0，则在这个从库执行查询语句；

​ 否则，到主库执行查询语句

应用

sharding-jdbc可以使用强制走主库方案。其他方案需要看是否可以自定义实现。

mycat可以使用判断主备无延迟方案。其他方案需要看是否可以自定义实现。