OneProxy自动剔除延迟节点

　　在多种情况下，MySQL主从节点之间可能存在延迟。比如，主库的写入能力强于从库的写入能力、从库单线程复制、从库复制出错导致相关进程停止。为了保证数据的时效性，OneProxy提供了复制时效性检查，用于自动剔除延迟节点。

　　OneProxy提供两种时效性检查方式

　　A）运行show slave status\G，获得Seconds_Behind_Master值，即为延迟大小。

　　　　该方法好处是直接使用了MySQL自身提供的功能，只需查询从节点即可知道延迟。缺点在于该值在某些情况下不准确。

      B) 针对第一个方法的缺点，Percona公司提供了pt-heart-beat，可以准确的检测主从延迟。OneProxy吸收了pt-heart-beat的设计原理。

     假设主（192.168.15.128:3319）

　　　从1（192.168.15.128:3318）

　　　从2（192.168.15.128:3316）　

 

针对A)方案，在proxy.conf中增加如下配置项，其中用户名和密码的设置请参考http://www.cnblogs.com/youge-OneSQL/p/4579881.html

[oneproxy]
keepalive = 1
event-threads = 4
log-file = log/oneproxy.log
pid-file = log/oneproxy.pid
lck-file = log/oneproxy.lck

mysql-version = 5.7.12
proxy-address = :3307
proxy-master-addresses.1 = 192.168.15.128:3319@server1
proxy-slave-addresses.2 = 192.168.15.128:3318@server1
proxy-slave-addresses.3 = 192.168.15.128:3316@server1

proxy-user-list = test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test

proxy-charset = gbk_chinese_ci
proxy-group-policy = server1:read-slave

proxy-secure-client = 127.0.0.1
proxy-sequence.1 = default
proxy-httpserver =:8080

repadmin-username =test
repadmin-password =1378F6CC3A8E8A43CA388193FBED5405982FBBD3
proxy-group-slavedelay=server1:2
proxy-license = 9244B81320619F1C

 

针对B)方案，即是在A方案的基础上增加proxy-replication-check = 1 

keepalive = 1
event-threads = 4
log-file = log/oneproxy.log
pid-file = log/oneproxy.pid
lck-file = log/oneproxy.lck

mysql-version = 5.7.12
proxy-address = :3307
proxy-master-addresses.1 = 192.168.15.128:3319@server1

#如果是两个master的话，比如双机房可以把下面的注释去掉

#proxy-master-addresses.2 = 192.168.15.128:3320@server1

 

proxy-slave-addresses.2 = 192.168.15.128:3318@server1
proxy-slave-addresses.3 = 192.168.15.128:3316@server1

proxy-user-list = test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test

proxy-charset = gbk_chinese_ci
proxy-group-policy = server1:read-slave

proxy-secure-client = 127.0.0.1
proxy-sequence.1 = default
proxy-httpserver =:8080

repadmin-username =test
repadmin-password =1378F6CC3A8E8A43CA388193FBED5405982FBBD3
proxy-group-slavedelay=server1:2
proxy-license = 9244B81320619F1C

proxy-replication-check = 1 

 该方案会在默认的数据库test中创建一个表oneproxy_replication_check

select * from oneproxy_replication_check;
+----+------------+
| id | col2 |
+----+------------+
| 1 | 1475049446 |
+----+------------+
1 row in set (0.00 sec)

通过比较主从节点间的时间戳大小，即可计算出延迟。