Everybody know
Also everybody know that ‘Seconds_Behind_Master’ shows difference in seconds between the slave SQL thread and the slave I/O thread.
Sometime it shows nonsense, and if you build monitoring It is not good practice to use ‘Seconds_Behind_Master’
Example from real life:
If replication becomes stalled due to connectivity problems, Seconds_Behind_Master shows 0 while replica is far away from master, changing timeout values not help. 🙁 i mean:
slave_net_timeout=300
So we implement following monitoring – every 3 second write current timestamp at master.
and check replica delay.
Or You can use percona heartbeat, It do almost the same.
1. Turn on mysql event daemon at master server (edit config file), no restart required, you can turn on event daemon via changing global variable
vi /etc/mysql/conf.d/slave.cnf event_scheduler=ON
2. Create event at master server
CREATE DATABASE IF NOT EXISTS slave_mon;
CREATE TABLE IF NOT EXISTS slave_mon.tstamp (ts TIMESTAMP);
USE slave_mon;
DELIMITER | ;
CREATE EVENT slave_ts ON SCHEDULE EVERY 3 SECOND
DO
BEGIN
DELETE FROM slave_mon.tstamp;
INSERT INTO slave_mon.tstamp SET ts=CURRENT_TIMESTAMP();
END | DELIMITER ;
3. How to look for replication lag at slave server:
4. Zabbix agent configuration:
a) screept to get lag (/home/scripts/mysql_lag.sh)
# /home/scripts/mysql_lag.sh
mysql --user=root --host=localhost --skip-column-names -e "SELECT TIME_TO_SEC(CURRENT_TIMESTAMP()-ts) from slave_mon.tstamp\G" |tail -1;
b) agent configuration
# Monitoring of MySQL lag behind master server
UserParameter=mysql.lag,/home/scripts/mysql_lag.sh;
Thats all… Create triggers, graphs, use force, Luke 🙂
Sergey Korobanov May 2013
is this method implement in PROD environment?
yep, we use event based replication check for several month, but better to use percona heartbeat.
As we do.
https://www.percona.com/doc/percona-toolkit/2.1/pt-heartbeat.html
Thanks for reply.