monitoring script

monitoring script 짤 때 사용하면 될 듯.

env.ini 파일 내용

export MySQL_VER=5.6.12

export MySQL_BASE=/usr1/mysql

export MySQL_HOME=$MySQL_BASE/$MySQL_VER/bin

export MySQL_SID=vm21

export MySQL_PORT=10614

export MySQL_SOCKET=/tmp/mysql_sandbox10614.sock

export _DBDBA_UID=msandbox

export _DBDBA_PWD='msandbox'

export _DBBACKUP_UID=msandbox

export _DBBACKUP_PWD='msandbox'

export _DBMON_UID=msandbox

export _DBMON_PWD='msandbox'

mon_script.sh 파일 내용

#!/bin/bash

cd /usr1/mysql/dba/

. .env.ini

clear

echo "######################################"

echo "###         Script List            ###"

echo "######################################"

echo ""

echo "  1.Transaction"

echo "  2.Blocking trans"

echo " 11.Process (All)"

echo " 12.Process (Long Time : 5 sec)"

echo ""

while :

do

   echo ""

   echo "Select > "

   read ScriptNo

   case $ScriptNo in

      1)

         SQLSTR=" select trx_id, trx_state, trx_started, trx_requested_lock_id, trx_mysql_thread_id"

         SQLSTR+="     , substr(trx_query,1, 50) as tsql, trx_operation_state, trx_tables_locked "

         SQLSTR+="  from information_schema.innodb_trx;"

         break ;;

      2)

         SQLSTR=" select il.*"

         SQLSTR+="  from information_schema.innodb_locks       il"

         SQLSTR+="  join information_schema.innodb_lock_waits  ilw"

         SQLSTR+="    on (il.lock_trx_id = ilw.blocking_trx_id);"

         break ;;

      11)

         SQLSTR=" select *"

         SQLSTR+="  from information_schema.processlist"

         SQLSTR+=" order by time desc limit 50;"

         break ;;

      12)

         SQLSTR=" select *"

         SQLSTR+="  from information_schema.processlist"

         SQLSTR+="  where time > 5 order by time desc limit 50;"

         break ;;

       *)      ;;

   esac

done

while (true)

do

   clear

   echo "##################################"

   date "+%Y/%m/%d %H:%m:%S"

   echo "##################################"

   mysql -u ${_DBMON_UID} --password=$_DBMON_PWD --port=${MySQL_PORT} --socket=${MySQL_SOCKET} -t -e "$SQLSTR"

   sleep 1

done