[Shell Command] MySQL叢集全局一覽表【源碼】

author：skate

time:2015/01/19

為了大家可以直接下載下傳使用此腳本，今天特意把代碼修改成通用版本。

MySQL叢集全局一覽表

用法：

# monrep -h

Usage: monrep [-d dbname ]

  -d  Database Cluster name

  -h  Display basic help

#

例如我的一個叢集名為dbx，那使用如下：

# monrep -d dbx

實作目的：

當主機比較多，而你又想檢視一個MySQL叢集内所有主機的和replication的基本實時情況時，就比較麻煩，如果你登入每個主機去檢視，那太out了，一般

可以通過監控平台去全局檢視（如果有的話）。我這裡為了友善，直接在路由機上用指令檢視，類似上面情況，

實作語言：linux shell

運作條件：

1.路由機到叢集任何機器是ssh可信任的

2.MySQL使用者可以從路由機遠端登入到任何MySQL Server

3.需要修改monrep的MySQL的使用者名，密碼，端口号和網卡類型（如eth0或bond0等，可以自己用cat /proc/net/dev 檢視下）

4.把叢集的主機名或ip位址填寫到配置檔案中，格式如下：

# more /tmp/monmysql/config.txt

[dby]

ip1

ip2

ip3

[/dby]

[dbx]

hostname1

hostname2

hostname3

[/dbx]

特點：

1.每兩秒重新整理一次

2.為了提高展現的速度，這裡用一個含有40個程序的程序池實作并發（盡量規避網絡因素）采集所有主機資訊

3.為了提供多使用者同時使用，采用每次運作的pid命名的臨時檔案來儲存臨時資訊

 Shell 源碼：

#!/bin/sh 
#
# Author:Skate
# Time:2015/01/16
# Function: all mysql replication monitor

# define variables
CURPID=$$
DIR=/tmp/monmysql
SERVERS_ALLFILE=${DIR}/${CURPID}_allhost.list
#NODES=37
USER=root
PASSWD=pwd
PORT=3306
NETTIMER=2
INTERFACE=eth0

# config information
CFG=$DIR/config.txt
CFGPID=$DIR/${CURPID}_config

SCRIPTNAME=$(basename "$0")

# clean files for muti-person run this script
for pid in  `ls -l $DIR/ | grep -v total |awk '{print $9}'|grep '_'|awk -F'_' '{print $1}' | uniq`
do
NUM=`ps -ef | awk '{ print $2 }' | grep -E '^${pid}$' | wc -l`
if [ $NUM -ge 0 ] ; then
rm -rf $DIR/${pid}*
fi
done


#############################################################################
# Display usage message and exit
#############################################################################
usage() {
  cat <<EOF
Usage: $SCRIPTNAME [-d dbname ]
  -d  Database name
  -h  Display basic help
EOF
  exit 0
}

# Parse parameters
while getopts ":d:h" opt; do
  case $opt in
    d )  DBFLAG=$OPTARG ;;
    h )  usage ;;
    \?)  echo "Invalid option: -$OPTARG"
         echo "For help, type: $SCRIPTNAME -h"
         exit 1 ;;
    : )  echo "Option -$OPTARG requires an argument"
         echo "For help, type: $SCRIPTNAME -h"
         exit 1 ;;
  esac
done

shift $(($OPTIND - 1))



#  create temporary directory
if [ ! -d "$DIR" ] ; then 
mkdir "$DIR" 
fi 


# The remote server data acquisition
function servermon(){
  HOST=$1
  CMD="cat /proc/loadavg | awk '{print \$1}'; mysql -u${USER} -p${PASSWD} -e 'show slave status \G' | grep -E 'Seconds_Behind_Master|Running:' | awk  '{print \$2}';netstat -natp | grep :${PORT} | wc -l;cat /proc/meminfo|grep MemFree|awk '{print \$2/1024}'|cut -d. -f1;cat /proc/net/dev | grep '${INTERFACE}' | sed -e 's/\(.*\)\:\(.*\)/\2/g' | awk '{print \$1}';cat /proc/net/dev | grep '${INTERFACE}' | sed -e 's/\(.*\)\:\(.*\)/\2/g' | awk '{print \$9}';sleep ${NETTIMER} ;cat /proc/net/dev | grep '${INTERFACE}' | sed -e 's/\(.*\)\:\(.*\)/\2/g' | awk '{print \$1}';cat /proc/net/dev | grep '${INTERFACE}' | sed -e 's/\(.*\)\:\(.*\)/\2/g' | awk '{print \$9}';"
  SERVER_TEMLATE=$DIR/${CURPID}_${HOST}.log

  ssh $HOST "$CMD" > $SERVER_TEMLATE  2>&1 
  sed -i '/Warning/d' $SERVER_TEMLATE 
 
  LOAD=`cat $SERVER_TEMLATE|sed -n '1p'`
  IO_STATUS=`cat $SERVER_TEMLATE|sed -n '2p'`
  SQL_STATUS=`cat $SERVER_TEMLATE|sed -n '3p'`
  BEHINDS=`cat $SERVER_TEMLATE|sed -n '4p'`
  SESS=`cat $SERVER_TEMLATE|sed -n '5p'`
  MEMFREE=`cat $SERVER_TEMLATE|sed -n '6p'`
  IN_OLD=`cat $SERVER_TEMLATE|sed -n '7p'`
  OUT_OLD=`cat $SERVER_TEMLATE|sed -n '8p'`
  IN=`cat $SERVER_TEMLATE|sed -n '9p'`
  OUT=`cat $SERVER_TEMLATE|sed -n '10p'`
  DIF_IN=$(((IN-IN_OLD)/NETTIMER/1024))
  DIF_OUT=$(((OUT-OUT_OLD)/NETTIMER/1024))

  printf "|%${HOSTNAME_WIDTH}s|" $HOST
  printf "%5s|"  $LOAD
  printf "%9s|"  $MEMFREE
  printf "%9s|"  $DIF_IN
  printf "%9s|"  $DIF_OUT
  printf "%7s|"  $SESS
  printf "%7s|"  $IO_STATUS
  printf "%7s|"  $SQL_STATUS
  printf "%9s|\n" $BEHINDS 
  
}

# Thread task
function subthread()
{
  HOST=$1
  SERVERS_FILE=$DIR/${CURPID}_${HOST}.list
  servermon $HOST > $SERVERS_FILE

}

function readcfg()
{
FIELD=$DBFLAG
first=`sed  -n "/\[$FIELD\]/=" $CFG`
last=`sed  -n "/\[\/$FIELD\]/=" $CFG`
sed -n "$((first+1)),$((last-1))p"  $CFG > $CFGPID 
sed -i '/^$/d' $CFGPID
}


# Simulation of multi thread
tmp_fifofile="$DIR/$.fifo"
mkfifo $tmp_fifofile
exec 6<>$tmp_fifofile
rm $tmp_fifofile
thread=40
for (( i=0;i<$thread;i++ )); do
echo
done >&6

# clear screen
clear

while true
do
readcfg

# The number of hosts
NODES=`cat $CFGPID | wc -l`

# hostname column width
HOSTNAME_WIDTH=`awk '{if(length($1)>s){s=length($1);l=NR}}END{print s}' $CFGPID`

for (( i=1;i<=$NODES;i++ )); do
read -u6
{
  for h in `cat $CFGPID`
  do
  HOST=$h
  subthread $HOST
  echo >&6
  done
}&
done
wait
sleep 3


for (( i=1;i<=$NODES;i++ )); do
      HOST=`sed -n "${i}p" $CFGPID`
      SERVERS_FILE=$DIR/${CURPID}_${HOST}.list

   if [ $(($i%${NODES})) -eq  1 ] ; then
     printf "******************************************\n" > $SERVERS_ALLFILE
     printf "MySQL Cluster(Demandforce) Real-Time View\n\n\n" >> $SERVERS_ALLFILE
     printf "******************************************\n" >> $SERVERS_ALLFILE
     printf "|%${HOSTNAME_WIDTH}s|" 'hostname' >> $SERVERS_ALLFILE
     printf "%5s|" 'load'  >> $SERVERS_ALLFILE
     printf "%9s|" 'mfree(M)'  >> $SERVERS_ALLFILE
     printf "%9s|" 'netin(K)'  >> $SERVERS_ALLFILE
     printf "%9s|" 'netout(K)'  >> $SERVERS_ALLFILE
     printf "%7s|" 'sess'  >> $SERVERS_ALLFILE
     printf "%7s|" 'io_run'   >> $SERVERS_ALLFILE
     printf "%7s|" 'sql_run'  >> $SERVERS_ALLFILE
     printf "%9s|\n" 'behind'  >> $SERVERS_ALLFILE
     cat $SERVERS_FILE >> $SERVERS_ALLFILE
   else
     cat $SERVERS_FILE  >> $SERVERS_ALLFILE
   fi 
done
# delete blank lines
sed -i '/^$/d' $SERVERS_ALLFILE
# print info to screen
clear
cat $SERVERS_ALLFILE
done
exec 6>&-





           

--------end--------