参考ganglia gmond的ruby插件mysql.py，写了一个bash的mysql 数据统计脚本，更加轻量，不依赖第三方库
难点主要3个：
1. bash v3不支持关联数组，后来在国外网站上找到一个取巧的处理手段
2. bash函数传递参数是按值传递，无法做到在函数内修改变量，最后用返回值解决
3. 传递数组给函数折腾了好久~~
[code language=”bash”]
#!/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
[/code]