mysql replication 상태 반복 체크

mysql 복제 상태 show replication status\G 를 주기적으로 반복 실행하는 쉘 만들어보자 

원격서버의 상태를 체크한다고 하면 

mysql -h [호스트명] -P[포트번호] -u [사용자명] -p[패스워드] -e "SHOW SLAVE STATUS\G"

============================================================== 

#!/bin/bash

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="your_username"
MYSQL_PASS="your_password"

# 중요한 상태 항목들
ITEMS_TO_SHOW="Source_Log_File|Read_Source_Log_Pos|Exec_Source_Log_Pos|Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_IO_Error|Last_SQL_Error"

while true
do
  echo "==== $(date) ===="
  mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW REPLICA STATUS\G" | grep -E "$ITEMS_TO_SHOW"
  
  # 복제 지연 확인
  SECONDS_BEHIND=$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW REPLICA STATUS\G" | grep "Seconds_Behind_Source" | awk '{print $2}')
  if [ "$SECONDS_BEHIND" != "NULL" ] && [ "$SECONDS_BEHIND" -gt 0 ]; then
    echo "경고: 복제 지연이 $SECONDS_BEHIND 초 발생했습니다."
  fi
  
  echo ""
  sleep 10
done

==============================================================

 

===== window 버전 =============================

set HOST=[호스트명]
set PORT=[포트번호]
set USER=[사용자명]
set PASS=[비밀번호]

:loop
cls
echo Checking replication status...
mysql -h %HOST% -P %PORT% -u %USER% -p%PASS% -e "SHOW SLAVE STATUS\G" | findstr /C:"Slave_IO_Running" /C:"Slave_SQL_Running" /C:"Seconds_Behind_Master"
timeout /t 5
goto loop