检查MySQL数据库服务器的shell脚本

标签: OS System get_local_kpi InnoDB innodb_buffer_pool_size ioutil | 发表时间:2011-09-23 09:06 | 作者:Eugene 铭文
分享到:
出处:http://www.mysqlops.com

导读

某著名电子商务公司的同事,编写的shell脚本,用于获得数据库服务器的数据库性能和配置,以及服务器负载LOAD等信息。shell脚本较长,也对shell脚本做了部分修改,同时为使技术朋友们更容易理解和使用,添加相关的文字和图片描述作为手册。

Shell代码的描述

1.         功能描述

执行shell命令:sh Get_Local_Kpi.sh –help,能显示相关信息,如图1-1:

1-1

可以为脚本Get_local_skpi指定参数的方式,把指定结果输出到指定的文件,需要检查的VIP地址,检查某项特定的信息,例如:

1-2

2.         配置文件

Get_Local_Kpi.sh需要读取一个数据库访问的账号密码配置文件,则可能修改代码中的二个地方:

(1).     密码配置文件存放的路径:CONF_DIR=/home/mysqldata/conf

(2).     密码文件头部分:PASS_FILE=”$CONF_DIR”/.mysql_info.”$MY_PORT”

(3).     脚本考虑了一台主机部署多个实例的生产环境,为此你只要执行的时候带上参数 –port=3306的格式即可,若是没有指定此参数则默认赋值为3306

3.         软件安装

Get_Local_Kpi.sh使用了iostat命令工具,若是服务器没有安装软件,则脚本程序会自动通过yum方式帮你安装,但是你的服务器没有配置yum源的话,则需要手工下载软件包:sysstat.x86_64,并且手工安装,软件rpm包下载地址:

http://rpm.pbone.net/index.php3/stat/3/srodzaj/2/search/sysstat-7.0.2-3.el5.src.rpm

4.         脚本缺点及优点

脚本实现部分信息收集的功能,并且shell脚本函数化的方式编写,但是没有完全抽象起来,导致代码较长,对于一些没有条件的技术朋友们,可以借鉴,以及继续添加相关功能。

Shell代码

#! /bin/bash
######################################
# Get MySQL option status with MySQL machine
# Create by 
# Created at : 2010.04.29 #
#ALTER BY Eugene
#ALTER TIME:2011-09-23
# The reslut of program will write to  $CONF_FILE
# Example :
#   rollbackcommit
#   dml
#   innodbio
#   qps
#   ioutil
#   iorwkb
#   slaveio
#   slavesql
#   slavelag
#   innodb_bufsize
#   myisam_keysize
#   trans_isolation
#   char_server
#   char_client
#   char_conn
#   sesscnt
#   session
#   load
#   role
######################################
###### Check parameters 

usage ()
{
cat <<EOF
Usage: $0 [OPTIONS]
  --port=3306                      MySQL Port ,Defalt 3306
  --outfile=/tmp/mysql3306.start   OutPut result to file
  --vip=10.2.334.252
  --key=qps,load,iorwkb...         What you want to Check. separated with ","
          Key List: $ALL_KEY  

If no "--ip" specified,Program will get first ip in result of  IPCONFIG .
All other options are passed to the program.

EOF
exit 1

}

for Parms in $*
  do
    Pram=$1
    Val=`echo "$Pram" | sed -e "s;--[^=]*=;;"`

    case "$Pram" in
       --port=*)
         MY_PORT=$Val
       ;;
       --outfile=*)
         MY_OUTFILE=$Val
       ;;
       --vip=*)
         MY_VIP=$Val
       ;;
       --key=*)
         MY_KEY=$Val
       ;;
       *)
       usage
       exit 1
       ;;
    esac
    shift
done

#####  Variables Define -- Begin

[ -z ~/.bash_profile ] && . ~/.bash_profile 

if [ -z "$MY_PORT" ] ; then
  MY_PORT=3306
fi

CONF_DIR=/home/mysqldata/conf
PASS_FILE="$CONF_DIR"/.mysql_info_sa."$MY_PORT"

if [ -f "$PASS_FILE" ] ; then
   . "$PASS_FILE"
else
  echo "$PASS_FILE IS NOT EXISTS!"
  exit
fi

MY_USER="$MYSQL_USER"
MY_PASSWD="$MYSQL_PASSWORD"
MY_SOCKET="$MYSQL_SOCK"
#MY_HOST=
MY_DATABASE=
ALL_KEY=rollbackcommit,dml,innodbio,qps,ioutil,iorwkb,slaveio,slavesql,slavelag,innodb_bufsize,myisam_keysize,trans_isolation,char_server,char_client,char_conn,sesscnt,session,load,role,

MYADMIN=$(which mysqladmin)
MYSQL=$(which mysql)

[ -z "$MY_PASSWD" ] ||  MY_PASSWD="-p"${MY_PASSWD} 

#####  Variables Define -- End

######################################### Funtions Begin

getstat_mysql()
{
# Get status of MySQL

if [ -z "$MYSQL" ] ; then
	if [ -x /usr/bin/mysql ] ; then
		MYSQL=/usr/bin/mysql
	else
	  echo "no_cmd_mysql."
    return 1
  fi
fi

 #$MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -e "EXIT"  > /dev/null

 $MYSQL -u$MY_USER  $MY_PASSWD -S $MY_SOCKET  -e "EXIT"  > /dev/null

 if [ $? -ne 0 ]  ; then
        echo "MySQL_error!"
        return 2
 fi
return 0
}

getstat_mysqladmin()
{
# Get status of MySQL

if [ -z "$MYADMIN" ] ; then
	  echo "no_cmd_mysqladmin."
    return 1
fi

return 0
}

getstat_Questions()
{
  # Get status of  Questions between 3 sec.
  getstat_mysql
  if [ $? -ne 0 ] ;then
   echo "NO_MYSQL"
   return 1
  fi

  local _var1=Questions

  if [ $VERSION_FLAG -eq 1 ] ; then
  	# Mysql version = 4.x
    #local _var1stat1=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "show status like 'Questions%' " | awk '{print $2}' )
    local _var1stat1=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "show status like 'Questions%' " | awk '{print $2}' )

  else
    #local _var1stat1=$($MYSQL -u$MY_USER -h$MY_HOST -p$MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL STATUS LIKE  'Questions%' " | awk '{print $2}' )
    local _var1stat1=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL STATUS LIKE  'Questions%' " | awk '{print $2}' )
  fi

  sleep 3 

  if [ $VERSION_FLAG -eq 1 ] ; then
  	# Mysql version = 4.x
    #local _var1stat2=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "show status like 'Questions%' " | awk '{print $2}' )
    local _var1stat2=$($MYSQL -u$MY_USER  $MY_PASSWD -S $MY_SOCKET -N -s -e "show status like 'Questions%' " | awk '{print $2}' )
  else
    #local _var1stat2=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL STATUS LIKE  'Questions%' " | awk '{print $2}' )
    local _var1stat2=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL STATUS LIKE  'Questions%' " | awk '{print $2}' )
  fi

  local _stat1=$(echo "$_var1stat1,$_var1stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')    

  echo $_stat1

  return 0
}

getstat_slaveio()
{
# Get Slave_IO_thread status of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi

#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "show slave status \G" | grep Slave_IO_Running | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "show slave status \G" | grep Slave_IO_Running | awk {'print $NF'} )

if [ -z "${_stat}" ] ; then
  _stat="NULL"
fi
echo $_stat
return 0
}

getstat_slavesql()
{
# Get Slave_SQL_thread status of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi

#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "show slave status \G" | grep Slave_SQL_Running | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "show slave status \G" | grep Slave_SQL_Running | awk {'print $NF'} )

if [ -z "${_stat}" ] ; then
  _stat="NULL"
fi
echo $_stat
return 0
}

getstat_slavelag()
{
# Get Slave_lag of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi

#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "show slave status \G" | grep Seconds_Behind_Master | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "show slave status \G" | grep Seconds_Behind_Master | awk {'print $NF'} )

if [ -z "${_stat}" ] ; then
  _stat="NULL"
fi
echo $_stat
return 0
}

getstat_innodb_bufsize()
{
# Get Innodb innodb_buffer_pool_size of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi
#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'" | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'" | awk {'print $NF'} )

echo $_stat
return 0
}

getstat_myisam_keysize()
{
# Get Myisam key_buffer_size of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi
#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL VARIABLES LIKE 'key_buffer_size'" | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL VARIABLES LIKE 'key_buffer_size'" | awk {'print $NF'} )

echo $_stat
return 0
}

getstat_trans_isolation()
{
# Get transaction_isolation of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi
#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL VARIABLES LIKE 'tx_isolation'" | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL VARIABLES LIKE 'tx_isolation'" | awk {'print $NF'} )

echo $_stat
return 0
}

getstat_char_server()
{
# Get Character set of server of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi
#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL VARIABLES LIKE 'character_set_server'" | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL VARIABLES LIKE 'character_set_server'" | awk {'print $NF'} )

echo $_stat
return 0
}

getstat_char_client()
{
# Get Character set of client of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi

#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL VARIABLES LIKE 'character_set_client'" | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL VARIABLES LIKE 'character_set_client'" | awk {'print $NF'} )

echo $_stat
return 0
}

getstat_char_conn()
{
# Get Character set of connection of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi
#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW GLOBAL VARIABLES LIKE 'character_set_client'" | awk {'print $NF'} )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW GLOBAL VARIABLES LIKE 'character_set_client'" | awk {'print $NF'} )

echo $_stat
return 0
}

getstat_sesscnt()
{
# Get all connection of MySQL
  getstat_mysql
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQL"
    return 1
  fi
#local _stat=$($MYSQL -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT -N -s -e "SHOW PROCESSLIST;" | wc -l )
local _stat=$($MYSQL -u$MY_USER $MY_PASSWD -S $MY_SOCKET -N -s -e "SHOW PROCESSLIST;" | wc -l )

echo $_stat
return 0
}

getstat_session()
{
  # Get status of  active_session/total_session
  local _stat1
  local _stat2
  local _var
  local _result
  local _tmpfile=/tmp/stat_sesscnt_$$.tmp

  getstat_mysqladmin
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQLADMIN"
    return 1
  fi

  #$MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT PROCESSLIST | grep "^|" | grep -v "Command"  > $_tmpfile
  $MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET PROCESSLIST | grep "^|" | grep -v "Command"  > $_tmpfile

  _var=active_session
  _stat1=$(cat $_tmpfile | grep -v "Sleep" | wc -l )

  _var=total_session
  _stat2=$(cat $_tmpfile | wc -l )

  echo ${_stat1},${_stat2} 

  \rm -f $_tmpfile
  return 0
}

getstat_load()
{
# Get load of Os
local _stat=$(w |  head -1 | awk -F ":" '{print $NF}' | tr -d ' ' )
echo ${_stat}
return 0
}

getstat_role()
{
#### Get Role : MASTER/SLAVE
# Please Think about :VIP , Qps , Slave_io="" , number of processlist 

local REMOTE_SLAVE_IO=$(getstat_slaveio)
local REMOTE_SLAVE_SQL=$(getstat_slavesql)

if [ -z "$MY_VIP" ] ; then

	# There is no vip .

	if [  "$REMOTE_SLAVE_IO" = "NULL"  -a  "$REMOTE_SLAVE_SQL" = "NULL"  ] ; then

    #  There is no slave processs .
      echo "MASTER"	

  else

  	#  Running with slave process ,check processlist count >= 10
  	local REMOTE_SESS_CNT=$(getstat_sesscnt)
  	  if [ $REMOTE_SESS_CNT -ge $MAX_PROC_CNT ] ; then
	      echo "MASTER"
	    else
	  	  echo "SLAVE"
	  	fi
  fi

else

	# check with a vip

    local VIP_CNT=$( /sbin/ifconfig | grep "$MY_VIP " | wc -l ) 

	  if [ $VIP_CNT	-ne 1 ] ; then
	    echo "SLAVE"
	  else
	  	echo "MASTER"
	  fi

fi

return 0

}

getstat_rollbackcommit()
{
  # Get status of  rollback/commit

  getstat_mysqladmin
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQLADMIN"
    return 1
  fi

  local _var1=Com_commit
  local _var2=Com_rollback

  #local _var1stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  #local _var2stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var2" " | head -1 | awk '{print $4}')

  local _var1stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  local _var2stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var2" " | head -1 | awk '{print $4}')

  sleep 3 

  #local  _var1stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  #local  _var2stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var2" " | head -1 | awk '{print $4}')

  local  _var1stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  local  _var2stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var2" " | head -1 | awk '{print $4}')

  local _stat1=$(echo "$_var1stat1,$_var1stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')
  local _stat2=$(echo "$_var2stat1,$_var2stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')    

#  local _result=${_result}${_var1}"="${_stat1}"&"
#        _result=${_result}${_var2}"="${_stat2}"&"

  echo ${_stat2},${_stat1}

  return 0
}

getstat_dml()
{
  #  Get status of  delete/insert/select/update

  getstat_mysqladmin
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQLADMIN"
    return 1
  fi

  local _var1=Innodb_rows_deleted
  local _var2=Innodb_rows_inserted
  local _var3=Innodb_rows_read
  local _var4=Innodb_rows_updated

  #local _var1stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  #local _var2stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  #local _var3stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var3" " | head -1 | awk '{print $4}')
  #local _var4stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var4" " | head -1 | awk '{print $4}')

  local _var1stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  local _var2stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  local _var3stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var3" " | head -1 | awk '{print $4}')
  local _var4stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var4" " | head -1 | awk '{print $4}')

  sleep 3

  #local _var1stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  #local _var2stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  #local _var3stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var3" " | head -1 | awk '{print $4}')
  #local _var4stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var4" " | head -1 | awk '{print $4}')

  local _var1stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  local _var2stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  local _var3stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var3" " | head -1 | awk '{print $4}')
  local _var4stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var4" " | head -1 | awk '{print $4}')

  local _stat1=$(echo "$_var1stat1,$_var1stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')
  local _stat2=$(echo "$_var2stat1,$_var2stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')
  local _stat3=$(echo "$_var3stat1,$_var3stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')
  local _stat4=$(echo "$_var4stat1,$_var4stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')    

#  local _result=${_result}${_var1}"="${_stat1}"&"
#        _result=${_result}${_var2}"="${_stat2}"&"
#        _result=${_result}${_var3}"="${_stat3}"&"
#        _result=${_result}${_var4}"="${_stat4}"&"

#  echo $(echo $_result | sed -e 's/\&$//' )
  echo ${_stat2},${_stat1},${_stat4},${_stat3}
  return 0
}

getstat_innodbio()
{
  # Get status of  Get status of  Innodb_buffer_pool_read_requests
  #                                        /Innodb_data_reads/Innodb_data_writes

  getstat_mysqladmin
  if [ $? -ne 0 ] ;then
    echo "NO_MYSQLADMIN"
    return 1
  fi

  local _var1=Innodb_buffer_pool_read_requests
  local _var2=Innodb_data_reads
  local _var3=Innodb_data_writes

  #local _var1stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  #local _var2stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  #local _var3stat1=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var3" " | head -1 | awk '{print $4}')

  local _var1stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  local _var2stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  local _var3stat1=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var3" " | head -1 | awk '{print $4}')

   sleep 3
  #local _var1stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  #local _var2stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  #local _var3stat2=$($MYADMIN -u$MY_USER -h$MY_HOST $MY_PASSWD -P$MY_PORT extended-status | grep $_var3" " | head -1 | awk '{print $4}')

  local _var1stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var1" " | head -1 | awk '{print $4}')
  local _var2stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var2" " | head -1 | awk '{print $4}')
  local _var3stat2=$($MYADMIN -u$MY_USER $MY_PASSWD -S $MY_SOCKET extended-status | grep $_var3" " | head -1 | awk '{print $4}')

  local _stat1=$(echo "$_var1stat1,$_var1stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')
  local _stat2=$(echo "$_var2stat1,$_var2stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')
  local _stat3=$(echo "$_var3stat1,$_var3stat2" | awk -F "," '{total=($2-$1)/3}{printf "%d",total}')    

#  local _result=${_result}${_var1}"="${_stat1}"&"
#        _result=${_result}${_var2}"="${_stat2}"&"
#        _result=${_result}${_var3}"="${_stat3}"&"
#
#  echo $(echo $_result | sed -e 's/\&$//' )
   echo ${_stat1},${_stat2},${_stat3}
  return 0
}

getstat_ioutil()
{
   # Get status of  ioutil.
  local _tmpfile=/tmp/stat__ioutil_$$.tmp
  local IOSTAT
  local _cnttime=4
  local DISKS

  IOSTAT=$(which iostat)
  if [ "$?" != 0 ] ; then
    yum install -y sysstat.x86_64
    echo "test"
  fi 

  if [ -z ${IOSTAT} ] ;  then
  	 echo "iostat_error"
  	 return 1
  fi

  $IOSTAT -x -k 1 $_cnttime > $_tmpfile

  local diskcnt=$(echo $(cat $_tmpfile | wc -l),${_cnttime} | awk -F "," '{total=($1-2)/$2+2}{printf "%d",total}')
  sed -i 1,${diskcnt}'d' $_tmpfile

  MAXAVG=0

  for DISKS in $(/sbin/fdisk -l | grep "^Disk" |awk -F ":" '{print $1}' | awk -F "/" '{print $NF}' )
  do
    MAXAVG_TMP=$(grep "${DISKS} "  $_tmpfile | awk '{ if( $NF != "0.00" ) {total+=$NF;cnt+=1;printf "%f\n", total/cnt}}' | tail -1)
    MAXAVG=$(echo $MAXAVG $MAXAVG_TMP | awk '{if ($2 > $1) {print $2} else {print $1} }' )
  done

  _stat=$MAXAVG
  _var=ioutil
  _result=${_result}${_var}"="${_stat}"&"

#  echo $(echo $_result | sed -e 's/\&$//' )
  echo  ${_stat} 

  \rm -f $_tmpfile
  return 0

}

getstat_iorwkb()
{
   # Get status of  io rKB/s wKB/s .
  local _tmpfile=/tmp/stat_iorwkb_$$.tmp
  local IOSTAT
  local _cnttime=4
  local DISKS
  local _stat1
  local _stat2

  IOSTAT=$(which iostat)

  if [ -z ${IOSTAT} ] ;  then
  	 echo "iostat_error"
  	 return 1
  fi

  $IOSTAT -k 1 $_cnttime > $_tmpfile

  local diskcnt=$(echo $(cat $_tmpfile | wc -l),${_cnttime} | awk -F "," '{total=($1-2)/$2+2}{printf "%d",total}')
  sed -i 1,${diskcnt}'d' $_tmpfile

  local RKB_MAXAVG=0
  local WKB_MAXAVG=0

  for DISKS in $(/sbin/fdisk -l | grep "^Disk" |awk -F ":" '{print $1}' | awk -F "/" '{print $NF}' )
  do
    RKB_MAXAVG_TMP=$(grep "${DISKS} "  $_tmpfile | awk '{ if( $3 != "0.00" ) {total+=$3;cnt+=1;printf "%f\n", total/cnt}}' | tail -1)
    RKB_MAXAVG=$(echo $RKB_MAXAVG $RKB_MAXAVG_TMP | awk '{if ($2 > $1) {print $2} else {print $1} }' )

    WKB_MAXAVG_TMP=$(grep "${DISKS} "  $_tmpfile | awk '{ if( $4 != "0.00" ) {total+=$4;cnt+=1;printf "%f\n", total/cnt}}' | tail -1)
    WKB_MAXAVG=$(echo $WKB_MAXAVG $WKB_MAXAVG_TMP | awk '{if ($2 > $1) {print $2} else {print $1} }' )
  done

  _stat1=$RKB_MAXAVG
  _var=rkb
  _result=${_result}${_var}"="${_stat}"&"

  _stat=$WKB_MAXAVG
  _var=wkb
  _result=${_result}${_var}"="${_stat}"&"

#  echo $(echo $_result | sed -e 's/\&$//' )
  echo ${RKB_MAXAVG},${WKB_MAXAVG}

  \rm -f $_tmpfile
  return 0

}

getvalue()
{

# Get values of key
 local _key=$1
 case "${_key}" in
   'rollbackcommit')
     RESULT="ROLLBACK/COMMIT:"$(getstat_rollbackcommit)
   ;;
   'dml')
     RESULT="DML(I/D/U/S):"$(getstat_dml)
   ;;
   'innodbio')
     RESULT="INNODBIO(Pool_read_qps/reads/writes):"$(getstat_innodbio)
   ;;
   'qps')
     RESULT="QPS:"$(getstat_Questions)
   ;;
   'ioutil')
     RESULT="IOUTIL:"$(getstat_ioutil)
   ;;
   'iorwkb')
     RESULT="OS_IO(R/W):"$(getstat_iorwkb)
   ;;
   'slaveio')
     RESULT="SLAVE_IO:"$(getstat_slaveio)
   ;;
   'slavesql')
     RESULT="SLAVE_SQL:"$(getstat_slavesql)
   ;;
   'slavelag')
     RESULT="SLAVE_LAG:"$(getstat_slavelag)
   ;;
   'innodb_bufsize')
     RESULT="INNODB_BUFSIZE:"$(getstat_innodb_bufsize)
   ;;
   'myisam_keysize')
     RESULT="MYISAM_KEYSIZE:"$(getstat_myisam_keysize)
   ;;
   'trans_isolation')
     RESULT="TX_ISOLATION:"$(getstat_trans_isolation)
   ;;
   'char_server')
     RESULT="CHARSET_SERVER:"$(getstat_char_server)
   ;;
   'char_client')
     RESULT="CHARSET_Client:"$(getstat_char_client)
   ;;
   'char_conn')
     RESULT="CHARSET_Conn:"$(getstat_char_conn)
   ;;
   'sesscnt')
     RESULT="SESSION(ALL):"$(getstat_sesscnt)
   ;;
   'session')
     RESULT="SESSION(ACTIVE/ALL):"$(getstat_session)
   ;;
   'load')
     RESULT="LOAD:"$(getstat_load)
   ;;
   'role')
     RESULT="ROLE:"$(getstat_role)
   ;;
   *)
    echo "${_key}:No_Such_Key"
   ;;
esac

echo $RESULT
}

######################################### Funtions End 

#### Main -- Begin 

if [ -z "$MY_HOST" ] ; then
  MY_HOST=`/sbin/ifconfig | grep "inet addr" | awk -F: '{print $2}' | awk {'print $1'} | head -1`
fi

if [ -z "$MY_PORT" ] ; then
   MY_PORT=3306
fi 

if [ -z "$MY_KEY" ] ; then
   MY_KEY=${ALL_KEY}
fi 

KEYLIST=$(echo ${MY_KEY} | tr -d ' ' | sed 's/,/ /g' )

CURRDATE=`date +%F`
MAX_PROC_CNT=10

#### Get Mysql Version
# if verion = 4.X , VERSION_FLAG=1
VERSION_FLAG=$($MYSQL --version | grep "Distrib 4." | wc -l)
[ -z "${VERSION_FLAG}" ] && VERSION_FLAG=0

#### Start to Loop
for KEY in $KEYLIST
do
	echo $(getvalue $KEY)
done

exit 0

#### Main -- End

相关 [检查 mysql 数据库] 推荐:

检查MySQL数据库服务器的shell脚本

- 铭文 - MySQLOPS 数据库与运维自动化技术分享
某著名电子商务公司的同事,编写的shell脚本,用于获得数据库服务器的数据库性能和配置,以及服务器负载LOAD等信息. shell脚本较长,也对shell脚本做了部分修改,同时为使技术朋友们更容易理解和使用,添加相关的文字和图片描述作为手册. 1.         功能描述. 执行shell命令:sh Get_Local_Kpi.sh –help,能显示相关信息,如图1-1:.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

Google数据库产品LevelDB对决MySQL

- - HTML5研究小组
去年一月份,Google发布了LevelDB. LevelDB是Key-Value嵌入式数据库管理系统编程库,目前的版本能够支持Billion级别的数据量. LevelDB是一个C++库,可按照字符串键值顺序映射. 源于其本身的良好设计,特别是LSM算法,LevelDB性能非常之高. 在一台4个Q6600的CPU机器上,每秒钟写数据超过40w,而随机读的性能每秒钟超过10w.

MySQL数据库的IO操作

- - haohtml's blog
         淘宝丁奇分享的PPT:MySQL数据库的IO操作,详细分享了四块的内容,并且告诉大家如何调整MySQL数据库IO操作相关的参数,给出了详细的选择策略,现替其整理成文章分享与此. 4.影响io行为的一些参数和选择策略. 一个简单的查询 select * from t where id>=(  select id from t where k1=100 limit 100000,1) limit 2;.

MySQL数据库优化总结

- - CSDN博客推荐文章
        对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要. 一般来说,要保证数据库的效率,要做好以下四个方面的工作:数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统,这个顺序也表现了这四个工作对性能影响的大小.        一、数据库设计   适度的反范式,注意是适度的.

理解MySQL数据库覆盖索引

- - haohtml's blog
看AUTO_INCREMENT就知道数据并不多,75万条. 很简单对不对?怪异的地方在于:. 如果换成MyISAM做存储引擎的时候,查询耗时只需要0.01s,用InnoDB却会是0.15s左右. 如果只是就这么点差距其实不是什么大不了的事,但是真实的业务需求比这个复杂,造成的差距也很大:MyISAM只需要0.12s,InnoDB则需要2.2s.,最终定位到问题症结是在这条SQL.

c/c++连接mysql数据库

- - CSDN博客数据库推荐文章
        由于项目需要,要用c/c++链接mysql数据库. 网上很多类似的解说,但是大部分都需要在本机器上安装完整版的msyql. 其实,有时候我们并不想在改变自己电脑上原有的环境,但是我们却希望通过我们的程序链接数据库. 比如:我在本机上已经安装了一个mysql,但并不是完整版的(比如appserv集成mysql或者wamp集成mysql),或者我的工作在局域网中,我只需要链接另外一台机器上的mysql.

excel数据导入mysql数据库

- - 互联网 - ITeye博客
1、excel另存为txt.       选中将要导出的数据列,然后另存为选择其它格式=>文本文件(制表符分割). E:\项目\fblike\game_code_san.txt. 2、txt导入到mysql数据库. load data infile 'E:\\项目\\fblike\\game_code_san.txt' into table game_code_san(code).

MySQL数据库设置主从同步

- - CSDN博客架构设计推荐文章
MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力. 1、可以作为一种备份机制,相当于热备份. 2、可以用来做读写分离,均衡数据库负载. 1、主从数据库版本一致,建议版本5.5以上. # 日志文件名 log-bin = mysql-bin # 日志格式,建议mixed binlog_format = mixed # 主数据库端ID号 server-id = 1.

浅谈MySQL 数据库性能优化

- - BlogJava-qileilove
数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理. 本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,. 日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化.   这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取一个数据是在毫秒级别,二者相差3个数量级.