Real-time check MySQL database latency replication Interrupt Data Latency

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.