[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 variablesCURPID=$$DIR=/tmp/monmysqlSERVERS_ALLFILE=${DIR}/${CURPID}_allhost.list#NODES=37USER=rootPASSWD=pwdPORT=3306NETTIMER=2INTERFACE=eth0# config informationCFG=$DIR/config.txtCFGPID=$DIR/${CURPID}_configSCRIPTNAME=$(basename "$0")# clean files for muti-person run this scriptfor pid in  `ls -l $DIR/ | grep -v total |awk '{print $9}'|grep '_'|awk -F'_' '{print $1}' | uniq`doNUM=`ps -ef | awk '{ print $2 }' | grep -E '^${pid}$' | wc -l`if [ $NUM -ge 0 ] ; thenrm -rf $DIR/${pid}*fidone############################################################################## Display usage message and exit#############################################################################usage() {  cat <<EOFUsage: $SCRIPTNAME [-d dbname ]  -d  Database name  -h  Display basic helpEOF  exit 0}# Parse parameterswhile 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 ;;  esacdoneshift $(($OPTIND - 1))#  create temporary directoryif [ ! -d "$DIR" ] ; then mkdir "$DIR" fi # The remote server data acquisitionfunction 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 taskfunction subthread(){  HOST=$1  SERVERS_FILE=$DIR/${CURPID}_${HOST}.list  servermon $HOST > $SERVERS_FILE}function readcfg(){FIELD=$DBFLAGfirst=`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 threadtmp_fifofile="$DIR/$.fifo"mkfifo $tmp_fifofileexec 6<>$tmp_fifofilerm $tmp_fifofilethread=40for (( i=0;i<$thread;i++ )); doechodone >&6# clear screenclearwhile truedoreadcfg# The number of hostsNODES=`cat $CFGPID | wc -l`# hostname column widthHOSTNAME_WIDTH=`awk '{if(length($1)>s){s=length($1);l=NR}}END{print s}' $CFGPID`for (( i=1;i<=$NODES;i++ )); doread -u6{  for h in `cat $CFGPID`  do  HOST=$h  subthread $HOST  echo >&6  done}&donewaitsleep 3for (( 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 linessed -i '/^$/d' $SERVERS_ALLFILE# print info to screenclearcat $SERVERS_ALLFILEdoneexec 6>&-

 

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