mysql replication 상태체크

Posted by 주원이^^
2016.01.13 13:54 리눅스/스크립트

#!/bin/bash
#Server infomation
DB_ID="root"
DB_PW="12345"
S_ip=`ifconfig | grep "inet addr" | awk '{print $2}' | awk -F ":" '{print $2}' | head -n 1`
S_Hostname=`hostname`
Sql_ver=`/usr/local/mysql/bin/mysql -V | awk -F " " '{print $5}' | awk -F "," '{print $1}'`

#log check
Source="/usr/bin/"

##time setting
Log_DeleteDay="42"
Log_Time="*/1 * * * * root sh"

Time_Day="`/bin/date +%Y%m%d --date "$Day days ago"`"
Time_Second="`/bin/date +%H%M%S --date "$Day days ago"`"
#sendmail info
TITLE="[${S_ip}]Mysql Replication Error $S_Hostname"
To_mail="메일주소"


#SQL infomation
MASTER_HOST="`/usr/local/mysql/bin/mysql -u${DB_ID} -p${DB_PW} -e "show slave status \G" | grep Master_Host: | awk -F " " '{print $2}'`"
MASTER_POS="`/usr/local/mysql/bin/mysql -u${DB_ID} -p${DB_PW} -e "show slave status \G" | grep Read_Master_Log_Pos: | awk -F " " '{print $2}'`"
SLAVE_POS="`/usr/local/mysql/bin/mysql -u${DB_ID} -p${DB_PW} -e "show slave status \G" | grep Exec_Master_Log_Pos: | awk -F " " '{print $2}'`"
SLAVE_IO="`/usr/local/mysql/bin/mysql -u${DB_ID} -p${DB_PW} -e "show slave status \G" | grep Slave_IO_Running: | awk -F " " '{print $2}'`"
SLAVE_SQL="`/usr/local/mysql/bin/mysql -u${DB_ID} -p${DB_PW} -e "show slave status \G" | grep Slave_SQL_Running: | awk -F " " '{print $2}'`"

Log_check() {
if [ ! -d /usr/logs ]; then
        mkdir -p ${Source}logs
fi
if [ ! -f ${Source}logs/sql_replication_${Time_Day}.log ]; then
        touch ${Source}logs/sql_replication_${Time_Day}.log
fi
if [[ -z `grep "##Sql Replication Check" "/etc/crontab" | head -n 1` ]]; then
        echo -ne "##Sql Replication Check
${Log_Time} ${Source}Sql_replication.sh" >> /etc/crontab
/etc/init.d/crond restart
fi
}

Log_delete() {

`find ${Source}logs/sql_replication* -ctime +${Log_DeleteDay} -exec rm -rf {} \;`

}


Content() {
echo -ne "
Master Host : $MASTER_HOST
Master Pos : $MASTER_POS        Slave Pos : $SLAVE_POS
Slave IO : $SLAVE_IO            Slave SQL : $SLAVE_SQL
Mysql Version : $Sql_ver
"
}

Mail() {
        Content | /bin/mail -s "${TITLE}" ${To_mail}
}

Start() {
Log_check

echo -ne "  #####$Time_Second#####" >> ${Source}logs/sql_replication_${Time_Day}.log

Content >> ${Source}logs/sql_replication_${Time_Day}.log

if [[ $SLAVE_IO != Yes ]] && [[ $SLAVE_SQL != Yes ]]; then
        Mail
elif [[ $MASTER_POS != $SLAVE_POS ]]; then
        Mail
fi

Log_delete
}

Start

 

'리눅스 > 스크립트' 카테고리의 다른 글

웹 트래픽 로그 순위 추출  (0) 2016.11.03
apache 자동다운받기  (0) 2016.03.04
mysql replication 상태체크  (0) 2016.01.13
폴더내 파일 소문자로 일괄변경  (0) 2015.07.16
7일뒤 로그 삭제  (0) 2015.07.14
특정데몬 모니터링  (0) 2014.11.06
이 댓글을 비밀 댓글로