Hunter的大杂烩

April 12, 2018

[原创]Mysql性能参数的bash统计脚本,可自定义关注指标

Filed under: 技术话题 — hunter @ 9:58 pm

参考ganglia gmond的ruby插件mysql.py,写了一个bash的mysql 数据统计脚本,更加轻量,不依赖第三方库

难点主要3个:
1. bash v3不支持关联数组,后来在国外网站上找到一个取巧的处理手段
2. bash函数传递参数是按值传递,无法做到在函数内修改变量,最后用返回值解决
3. 传递数组给函数折腾了好久~~

#!/bin/bash
#for bash3
#author:hunter
#site:www.hunterpro.net

ROOT_PATH=/data/admin
LOG_PATH=/data/admin/log

statusDict=()
oldStatusDict=()


function get_mysql_cmd()
{
    MYSQL_USER=root
    MYSQL_PASS=
    MYSQL_HOST=
    MYSQL_PORT=
    #SOCK_FILE=/var/lib/mysql/mysql.sock
    MYSQL_ADMIN_PATH=/usr/local/mysql/bin
    
    mysql_cmd_line=$MYSQL_ADMIN_PATH"/mysqladmin"
    
    if [ ! -z "$MYSQL_USER" ];then
        mysql_cmd_line=$mysql_cmd_line" -u "$MYSQL_USER
    fi
    
    if [ ! -z "$MYSQL_PASS" ];then
        mysql_cmd_line=$mysql_cmd_line" -p "$MYSQL_PASS
    fi
    
    if [ ! -z "$SOCK_FILE" ];then
        mysql_cmd_line=$mysql_cmd_line" -S "$SOCK_FILE
    fi
    
    if [ ! -z "$MYSQL_HOST" ];then
        mysql_cmd_line=$mysql_cmd_line" -h "$MYSQL_HOST
    fi
    
    if [ ! -z "$MYSQL_PORT" ];then
        mysql_cmd_line=$mysql_cmd_line" -P "$MYSQL_PORT
    fi
    echo "$mysql_cmd_line"
}

function dumpArray()
{
    tmpArray=($1)
    arrayLength=${#tmpArray[*]}
    echo "dumpArray:"$arrayLength
    for ((i=0;i<$arrayLength;i++))
    do   
        echo ${tmpArray[$i]} 
    done
}

function getArrayKey()
{
    local item=$1
    local KEY="${item%%:*}"
    echo $KEY
}

function getArrayValue()
{
    local item=$1
    local VALUE="${item##*:}"
    echo $VALUE
}

function addItem2Array()
{
    array=($1)
    key=$2
    value="$3"
    
    arrayLength=${#array[*]}
    #echo "addItem2Array:"$arrayLength"|k:" $key
    
    #array =("${array[@]}" "$key:$value")
    #statusDict[${#statusDict[@]}]="$key:$value"

    array[$arrayLength]="$key:$value"
    
    echo "${array[*]}"
}

function getFileNameWithNoSuffix()
{
    pathArr=(${1//\// })
    lastPathOffset=`expr ${#pathArr[@]} - 1`
    fileName=${pathArr[$lastPathOffset]}
    fileNameWithNoSuffix=`echo $fileName|awk -F'.' '{print $1}'`
    echo $fileNameWithNoSuffix
}

function getLastUpdateTime()
{
    cur_time=`date +%s`
    timeFileName=$1".time"
    if [ ! -f $LOG_PATH"/"$timeFileName ];then
        echo 0
    else
        last_update=`cat $LOG_PATH"/"$timeFileName`
        echo $last_update
    fi
}

function updateLastTime()
{
    cur_time=`date +%s`
    timeFileName=$1".time"
    echo $cur_time > $LOG_PATH"/"$timeFileName
}

function initArray()
{
    local cacheFilePath=$2
    while read line 
    do
        key=`echo $line|awk -F'|' '{print $1}'`
        value=`echo $line|awk -F'|' '{print $2}'`
        if [ $1 = "new" ];then
            statusDict=($(addItem2Array "${statusDict[*]}" $key "$value"))
        else
            oldStatusDict=($(addItem2Array "${oldStatusDict[*]}" $key "$value"))
        fi
    
    done < $cacheFilePath
    
    #echo $cacheFilePath
    #dumpArray "${statusDict[*]}"
}

function getDeltaTime()
{
	newArray=($1)
	oldArray=($2)
}

function showMysqlInfo()
{
    interesting_global_status_vars=('aborted_clients'
		'aborted_connects'
		'binlog_cache_disk_use'
		'binlog_cache_use'
		'bytes_received'
		'bytes_sent'
		'com_delete'
		'com_delete_multi'
		'com_insert'
		'com_insert_select'
		'com_load'
		'com_replace'
		'com_replace_select'
		'com_select'
		'com_update'
		'com_update_multi'
		'connections'
		'created_tmp_disk_tables'
		'created_tmp_files'
		'created_tmp_tables'
		'key_reads'
		'key_read_requests'
		'key_writes'
		'key_write_requests'
		'max_used_connections'
		'open_files'
		'open_tables'
		'opened_tables'
		'qcache_free_blocks'
		'qcache_free_memory'
		'qcache_hits'
		'qcache_inserts'
		'qcache_lowmem_prunes'
		'qcache_not_cached'
		'qcache_queries_in_cache'
		'qcache_total_blocks'
		'questions'
		'select_full_join'
		'select_full_range_join'
		'select_range'
		'select_range_check'
		'select_scan'
		'slave_open_temp_tables'
		'slave_retried_transactions'
		'slow_launch_threads'
		'slow_queries'
		'sort_range'
		'sort_rows'
		'sort_scan'
		'table_locks_immediate'
		'table_locks_waited'
		'threads_cached'
		'threads_connected'
		'threads_created'
		'threads_running'
		'uptime'
	)
	
	non_delta=('max_used_connections'
		'open_files'
		'open_tables'
		'qcache_free_blocks'
		'qcache_free_memory'
		'qcache_total_blocks'
		'slave_open_temp_tables'
		'threads_cached'
		'threads_connected'
		'threads_running'
		'uptime'
	)
	newArray=($1)
	oldArray=($2)
	should_update=$3
	for item in ${newArray[@]} ;
	do
        key=$(getArrayKey $item)
        value=$(getArrayValue $item)
	    for var in ${interesting_global_status_vars[@]} ;
	    do
	        if [ $key = $var ];then
	            flag=0
	            for var2 in ${non_delta[@]} ;
	            do
	                if [ $var2 = $key ];then
    	                #echo "n|"$key":"$value
    	                echo $key":"$value
    	                flag=1
    	                break
    	            fi
	            done
	            if [[ $flag -eq 0 && $should_update -eq 1 ]];then
	                for item2 in ${oldArray[@]} ;
	                do
                        key2=$(getArrayKey $item2)
                        value2=$(getArrayValue $item2)
                        if [ $key = $key2 ];then
                            vv=`expr $value - $value2`
                            #echo "d|"$key":"$vv
                            #echo $key":"$vv"|"$value"|"$value2
                            echo $key":"$vv
                            break
                        fi
                    done
	            fi
	            break
	        fi
	    done
	done
    
}

fileNameWithNoSuffix=$(getFileNameWithNoSuffix $0)
cacheFilePath=$LOG_PATH"/"$fileNameWithNoSuffix".tmp"
oldCacheFilePath=$LOG_PATH"/"$fileNameWithNoSuffix".old.tmp"

mysql_cmd_line=$(get_mysql_cmd)
$mysql_cmd_line extended-status|  awk '  function ltrim(s) { sub(/^[ \t\r\n]+/, "", s); return s }   function rtrim(s) { sub(/[ \t\r\n]+$/, "", s); return s }   function trim(s) { return rtrim(ltrim(s)); }    BEGIN{FS="|"} {print trim($2)"|"trim($3)}' |tr 'A-Z' 'a-z'| sed -n '4,$p'|egrep -v "^[|]" > $cacheFilePath

initArray "new" $cacheFilePath

cur_time=`date +%s`
last_update=$(getLastUpdateTime $fileNameWithNoSuffix)
should_update_time=`date -d "40 second ago" +%s`
should_update=0

if [ $last_update -eq 0 ];then
    mv $cacheFilePath $oldCacheFilePath
    oldStatusDict=$statusDict
    $(updateLastTime $fileNameWithNoSuffix)
    #echo "first time"
elif [ $last_update -le $should_update_time ];then
    initArray "old" $oldCacheFilePath
    should_update=1
    #echo "second time"
    $(updateLastTime $fileNameWithNoSuffix)
else
    diff=`expr $should_update_time - $last_update`
    #echo "not update yet:"$diff
fi

#dumpArray "${statusDict[*]}"
showMysqlInfo "${statusDict[*]}" "${oldStatusDict[*]}" $should_update

if [ $should_update -eq 1 ];then
    mv $cacheFilePath $oldCacheFilePath

fi

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress