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--------