参考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