这段时间用MySQL的Master-Slave来实现位于两个不同IDC的数据同步(Master为windows,slave为CentOS),发现有时候延时比较大,而>show slave status\G;却显示Seconds_Behind_Master为0,只有stop slave和start slave后,数据迅速就同步过来了,说明是数据通讯有点问题,slave没有收到master过来的数据。
经过Google后,MySQL的replication确实有这个问题,Seconds_Behind_Master并不可靠,因为Slave有时候检测网络正常失败,例如拔网线或者网络ping突然很慢的情况。于是决定监控一下这个时延到底有多大,也是master执行sql后到slave到底有多久。mysql的函数now()和sysdate()的不同点就是执行前和执行时的时间,原理上可以满足sql同步的延时计算,过程如下:
一、首先在master和slave端创建heartbeat表:
CREATE TABLE `heartbeat` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`master_datetime` DATETIME NOT NULL,
`slave_datetime` DATETIME NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='gbk_chinese_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1
#filename: run_mysql_replication_heartbeat.py
#encoding=gbk
import datetime,time
import os,sys
from public import db
import db_conf
source_folder = db_conf.SOURCE_FOLDER
def init_eviroment_path():
print sys.path
python_path = (source_folder)
for i in python_path:
if i not in sys.path:
sys.path.append(i)
print sys.path
def main():
conn, cursor = db.GetMysqlCursor('update')
cursor.execute("insert into heartbeat (master_datetime,slave_datetime) values(now(),sysdate())")
cursor.close()
conn.close()
if __name__ == '__main__':
init_eviroment_path()
os.system("title MySQL Replication心跳")
count = 1
while True:
main()
print "(%d)%s"%(count,datetime.datetime.now())
count+=1
time.sleep(60)
三、也就是最关键的一步,修改master端binlog模式:
修改my.ini(linux为my.cnf),binlog_format=statement(默认是mix),之所以这么修改是因为必须是statement的SQL语句同步模式才行,否则mix下有可能是ROW的结果数据同步模式就不行,这个我也是通过master>show binlog events才找到这个原因。
如果要立刻看到结果,只要把master端的时间修改一下,例如提前一个小时,执行:
insert into heartbeat (master_datetime,slave_datetime) values(now(),sysdate()) 在slave上就可以看到类似如下结果:
然后你就可以在slave机上搞一个python脚本来监控延时情况,例如超过1分钟,就自动发邮件或者短信通知,或者直接调用mysql的stop slave和start slave来重启slave。
补充一下,master和slave机必须要定时与标准时间同步,否则几天就会相差几秒。