Master/Slave replication latency judgment

In the production environment, master-slave replication often has replication latency, mainly because the master node writes data concurrently, and the slave is a single-threaded application relay log, which leads to replication latency, in MySQL 5.6, multi-threaded database-based replication is available. There is also mariadb's parallel replication. However, we also use many MySQL 5.5 versions. How can we determine whether replication is delayed? The tool can now use Pt-heartbeat, but if we know how to determine whether replication is delayed, we can write a simple shell script or Python script.

The criteria for determining whether replication is delayed are as follows::

Do not use seconds_behind_master to determine the latency between the SQL thread and the IO thread on slave.

1. check whether there are differences between relay_master_log_file and master_log_file. 2. If there is a difference between relay_master_log_file and master_log_file, the latency is very high. 3. If there is no difference between relay_master_log_file and master_log_file, let's look at the differences between exec_master_log_pos and read_master_log_pos, the more rigorous approach is to execute show Master Status in the master database and compare the output of show slave status in the slave database. MHA ensures data consistency. Mmm does not. This is also a better place for MHA than mmm.  So, according to the above rules, I wrote a simple shell script, as follows:

#! /Bin/bash # determine whether the master-slave replication delay # write by Yayun 2014-07-23 # http://www.cnblogs.com/gomysql/# slave = 123456s_user = roots_port = 3306s_host = localhost # masterm_psswd = 123456m_user = rootm_port = 3306m_host = bytes _ wan_ip' ifconfig | sed-n'/inet/{S /. * ADDR: //; S /. * //; p} '| head-N1' while truedo sleep 1 echo-e "\ e [1; 33 m #################################### \ e [0 m "master_log_file =$ (MySQL-U $ s_user-p $ s_ps SWD-h $ s_host-p $ s_port-e "show slave status \ G" | grep-W master_log_file | awk-F ":" '{print $2 }') relay_master_log_file = $ (MySQL-U $ s_user-p $ s_psswd-h $ s_host-p $ s_port-e "show slave status \ G" | grep-W relay_master_log_file | awk-F": "'{print $2 }') read_master_log_pos = $ (MySQL-U $ s_user-p $ s_psswd-h $ s_host-p $ s_port-e "show slave status \ G" | grep-W read_master_log_pos | awk-F": "'{prin T $2 }') exec_master_log_pos = $ (MySQL-U $ s_user-p $ s_psswd-h $ s_host-p $ s_port-e "show slave status \ G" | grep-W exec_master_log_pos | awk-F": "'{print $2}' | SED's/[\ t] * $ // G') master_log_file_num = 'echo $ master_log_file | awk-F '. ''{print $2} '| SED's/^ 0 \ + // ''master_file =$ (MySQL-U $ m_user-p $ m_psswd-h $ m_host-P $ m_port-neuron "show Master Status" | awk '{print $1 }') master_pos = $ (MySQL-U $ m_user -P $ m_psswd-h $ m_host-p $ m_port-neuron "show Master Status" | awk '{print $2}' | SED's/[\ t] * $/ /G ') master_file_num = 'echo $ master_file | awk-F '. ''{print $2} '| SED's/^ 0 \ + //'' if [-Z $ master_log_file] & [-Z $ relay_master_log_file] & [- Z $ read_master_log_pos] & [-Z $ exec_master_log_pos] Then ECHO-e "\ e [1; 31 mslave does not get the value. Please check the parameter settings! \ E [0 m "Exit 1 Fi if [$ master_log_file = $ logs] & [$ read_master_log_pos = $ exec_master_log_pos] Then if [$ master_log_file = $ master_file] & [$ exec_master_log_pos = $ master_pos] Then ECHO-e "\ e [1; 32mmaster-slave replication without delay ^ _ ^ \ e [0 m "else if [$ master_log_file_num-GT $ master_file_num] | [$ master_pos-GT $ exec_master_log_pos] Then log_count = $ (expr $ master_log_file_num-$ MAS Ter_file_num) pos_count = $ (expr $ master_pos-$ exec_master_log_pos) echo-e "\ e [1; 31mmaster-slave replication delay !!! \ E [0 m "echo-e" \ e [1; 31 mmaster: $ m_host slave: $ slave_wan_ip \ e [0 m "echo-e" \ e [1; 31mmaster current BINLOG: $ master_file "echo-e" \ e [1; 31mslave current BINLOG: $ master_log_file "echo-e" \ e [1; 31mbinlog different files: $ log_count \ e [0 m "echo-e" \ e [1; 31mpos point difference: $ pos_count \ e [0 m "fi fidone

If you think the judgment criteria or scripts are not complete enough, you can exchange ideas with each other. Pai_^