检查MySQL数据库服务器的shell脚本
- 铭文 - MySQLOPS 数据库与运维自动化技术分享某著名电子商务公司的同事，编写的shell脚本，用于获得数据库服务器的数据库性能和配置，以及服务器负载LOAD等信息. shell脚本较长，也对shell脚本做了部分修改，同时为使技术朋友们更容易理解和使用，添加相关的文字和图片描述作为手册. 1. 功能描述. 执行shell命令：sh Get_Local_Kpi.sh –help，能显示相关信息，如图1-1：.
检查MySQL数据库服务器的shell脚本
标签：
OS
System
get_local_kpi
InnoDB
innodb_buffer_pool_size
ioutil
| 发表时间：2011-09-23 17:06 | 作者：Eugene 铭文
出处：http://www.mysqlops.com
【导读】
某著名电子商务公司的同事，编写的shell脚本，用于获得数据库服务器的数据库性能和配置，以及服务器负载LOAD等信息。shell脚本较长，也对shell脚本做了部分修改，同时为使技术朋友们更容易理解和使用，添加相关的文字和图片描述作为手册。
n 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脚本函数化的方式编写，但是没有完全抽象起来，导致代码较长，对于一些没有条件的技术朋友们，可以借鉴，以及继续添加相关功能。
n Shell代码
#! /bin/bash ###################################### # Get MySQL option status with MySQL machine # Create by steven # 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数据库的修复
- Xin - 博客园-首页原创精华区找到mysql的安装目录的bin/myisamchk工具，在命令行中输入：. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql，问题就解决了. 当你试图修复一个被破坏的表的问题时，有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立，删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.
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语句优化、数据库参数配置、恰当的硬件资源和操作系统，这个顺序也表现了这四个工作对性能影响的大小. 一、数据库设计
适度的反范式,注意是适度的.
Google数据库产品LevelDB对决MySQL
- - HTML5研究小组去年一月份，Google发布了LevelDB. LevelDB是Key-Value嵌入式数据库管理系统编程库，目前的版本能够支持Billion级别的数据量. LevelDB是一个C++库，可按照字符串键值顺序映射. 源于其本身的良好设计，特别是LSM算法，LevelDB性能非常之高. 在一台4个Q6600的CPU机器上，每秒钟写数据超过40w，而随机读的性能每秒钟超过10w.
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).
c/c++连接mysql数据库
- - CSDN博客数据库推荐文章 由于项目需要，要用c/c++链接mysql数据库. 网上很多类似的解说，但是大部分都需要在本机器上安装完整版的msyql. 其实，有时候我们并不想在改变自己电脑上原有的环境，但是我们却希望通过我们的程序链接数据库. 比如：我在本机上已经安装了一个mysql，但并不是完整版的（比如appserv集成mysql或者wamp集成mysql），或者我的工作在局域网中，我只需要链接另外一台机器上的mysql.
理解MySQL数据库覆盖索引
- - haohtml's blog看AUTO_INCREMENT就知道数据并不多，75万条. 很简单对不对?怪异的地方在于：. 如果换成MyISAM做存储引擎的时候，查询耗时只需要0.01s，用InnoDB却会是0.15s左右. 如果只是就这么点差距其实不是什么大不了的事，但是真实的业务需求比这个复杂，造成的差距也很大：MyISAM只需要0.12s，InnoDB则需要2.2s.，最终定位到问题症结是在这条SQL.
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个数量级.