Real-time check MySQL database latency replication Interrupt Data Latency

Script Writing ideas:

(1) According to show slave statusg;l column seconds_behind_master;

(2) seconds_behind_master= NULL to determine which of the IO or SQL processes stopped or both stopped

(3) seconds_behind_master= 0 copy Normal

(4) seconds_behind_master>0 description Master-Slave delay

The above (2), (4) will send a mail alarm, normal ignore, script 10s capture data once.

#!/bin/bash

########################################

# automatic check MySQL replacation status from Salve database #

# Edit by RGF in 2014/09/03 #

# The Scropt distributes in slave databases,if slave db faults, #

# then it'll send email to administrator or DBA #

########################################

Export LANG=ZH_CN

Base_dir=/usr/bin

Logs_dir=/opt/mysql/log

Db_user=root

db_pwd=123456

emailfile= $logs _dir/${hostname}_mail_ ' date +%y%m%d '. Log

[Email protected]

While True

Do

echo "" > $EmailFile

echo "-----------------------" >> $EmailFile

echo "' Date +"%y-%m-%d%h:%m:%s "'" >> $EmailFile

echo "-------------------------" >> $EmailFile

Port= ' Netstat-ntlp|grep mysqld | Awk-f ' {print $4} ' | Awk-f ': ' {print $} '

ip= ' ifconfig |grep "inet addr:" |grep-v "inet addr:127.0.0.1" | Awk-f ' {print $} ' | Awk-f ': ' {print $} '

echo "${hostname}: $Ip: $Port" >> $EmailFile

Seconds_behind_master= ' ${base_dir}/mysql-u$db_user-p$db_pwd-e "show slave statusg;" | grep "Seconds_behind_master:" | Awk-f ': ' {print $} ' &>/dev/null

Slave_io_running= ' ${base_dir}/mysql-u$db_user-p$db_pwd-e "show Slave statusg;" | grep "Slave_io_running:" | Awk-f ': ' {print $} ' &>/dev/null

Slave_sql_running= ' ${base_dir}/mysql-u$db_user-p$db_pwd-e "show Slave statusg;" | grep "Slave_sql_running:" | Awk-f ': ' {print $} ' &>/dev/null

if [$Seconds _behind_master = "NULL"];then

echo "Disruption of replication" >> $EmailFile

if [$Slave _io_running = "Yes"-a $Slave _sql_running = "No"];then

echo "Slave_io_running is OK and slave_sql_running are failure" >> $EmailFile

Mail-s "Slave_io OK and Slave_sql failured!!" $Email < $EmailFile

elif [$Slave _io_running = "No"-a $Slave _sql_running = "Yes"];then

echo "Slave_io_running is failure and slave_sql_running are OK" >> $EmailFile

Mail-s "Slave_io failured and Slave_sql ok!!" $Email < $EmailFile

Else

echo "Slave_io_running is failure and slave_sql_running are failure" >> $EmailFile

Mail-s "Slave_io failure and Slave_sql failure!!" $Email < $EmailFile

Fi

elif [$Seconds _behind_master-eq 0];then

echo "Slave is ok!" &>/dev/null

elif [$Seconds _behind_master-gt 0];then

echo "Slave have beened delayed compared with master" >> $EmailFile

Mail-s "Slave has beened delayed" $Email < $EmailFile

Else

echo "Slave Unknown fault!" >> $EmailFile

Mail-s "Slave Unknown fault!!" $Email < $EmailFile

Fi

Sleep 10

Done

Real-time check MySQL database latency replication Interrupt Data Latency