Mysql 建立心跳錶來監控Replication的Slave是否延遲

mysql replication的slave同步是否真實延遲，大部分人都是在slave上執行show slave status\G來捕獲Slave_IO_Running: Yes和Slave_SQL_Running: Yes和Seconds_Behind_Master: 0這三列資訊，今天我們自己另闢蹊蹺不同過show slave status\G來捕獲，而是直接監控master庫的資料是否真實寫入到slave庫



1、Master上的mysql庫上建立心跳錶Heartbeat_table

CREATE TABLE `heartbeat_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `master_datetime` datetime NOT NULL,
  `slave_datetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

2、Master上的mysql庫上建立一個儲存過程往心跳錶Heartbeat_table插資料

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_heartbeat_table`()
BEGIN
DECLARE  master_datetime datetime;
declare  slave_datetime  datetime;
INSERT INTO Heartbeat_table (master_datetime,slave_datetime) VALUES(NOW(),SYSDATE());
END$$

3、Master上的mysql庫上建立建立一個事件每分鐘執行一次步驟2的儲存過程
CREATE EVENT event_replication_insert ON SCHEDULE EVERY 60 SECOND  DO call sp_insert_heartbeat_table();


4、Master上的mysql庫上建立建立一個事件每分鐘刪除心跳錶3天前的記錄
CREATE EVENT event_replication_delete ON SCHEDULE EVERY 60 SECOND  DO delete from Heartbeat_table where id in (select id from (select id from Heartbeat_table c1 where UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(master_datetime)>259200) t1)

5、在slave伺服器上編寫一個監控slave同步的郵件的指令碼，延遲超過120秒就報警
#!/bin/bash
mysql_user="root"
mysql_pass="pwd"
hostname=echo `hostname`

delay=`/usr/bin/mysql -u${mysql_user} -p${mysql_pass} -e"SELECT NOW()-MAX(master_datetime) FROM mysql.heartbeat_table"`
delay_seconds=`echo $delay |awk '{print $2}'`

if [ "$delay_seconds" -gt 120 ];then
                echo "$hostname Mysql Salve delay $delay_seconds seconds,please check!!！"| mailx  -s "$hostname Mysql Salve delay $delay_seconds seconds,please check!!！" emailname@XX.com
fi