check_mysql.sh查看MySQL状态

发表于： 2020-11-29 2020-11-29
分类： SRE
标签： awk, grep, json, MySQL
#! /bin/bash
export LANG=en_US.UTF-8

#WARNLIMIT=-1      #警告值 超过该值则认为是需要告警
#ERRORLIMIT=-1         #错误值 超过该值认为是错误

keys=(FLAGEPID CONN MEMORY CPU RUNTIME STARTTIME Slow_queries Slave_IO_Running Slave_SQL_Running Seconds_Behind_Master locks_waited Role Uptime Version Max_connections Max_connect_errors
Open_files_limit Table_open_cache Max_tmp_tables Max_allowed_packet Open_files Open_tables Threads_connected Threads_running Threads_waits Threads_cached Connections Connections_persecond
Bytes_received_persecond Bytes_sent_persecond Com_select_persecond Com_insert_persecond Com_update_persecond Com_delete_persecond Com_commit_persecond Com_rollback_persecond Questions_persecond
Queries_persecond Table_locks_rate Key_buffer_size Sort_buffer_size Key_read_requests_persecond Key_reads_persecond Key_write_requests_persecond Key_writes_persecond 
Innodb_buffer_pool_instances Innodb_buffer_pool_size Innodb_buffer_pool_pages_total Innodb_buffer_pool_pages_data Transaction_persecond
Innodb_buffer_pool_pages_dirty Innodb_buffer_pool_pages_flushed Innodb_buffer_pool_pages_free Innodb_buffer_pool_pages_free_rage
Innodb_buffer_pool_pages_misc 
Innodb_rows_Deleted_persecond Innodb_rows_Inserted_persecond Innodb_rows_read_persecond Innodb_rows_Updated_persecond
Key_buffer_read_rate Key_buffer_write_rate Key_blocks_used_rate
)
#echo ${#keys[@]} 看看多少个指标，不包含cpname
declare -A map=()

for var in ${keys[@]};  
do  
    map[$var]=""  
done

CURNUM=             #当前的性能参数
MYSQL_SLOW_LOG_PATH=
MYSQLHOME="/usr/bin"
MYSQLHOST=127.0.0.1
USER=root
PASSWORD=
PORT=3306
PARAM_USERNAME="<<username>>"
PARAM_PASSWORD="<<password>>"


#连接数
CONN=-1
#CPU
CPU=-1
#内存
MEMORY=-1
#慢查询数目
Slow_queries=-1
FLAGE=
FLAGEID=
#是否输出FLAGPID
FLAGEOPID=1
#是否输出RUNTIME
RUNTIME=1
#是否输出STARTTIME
STARTTIME=0
QUOTASTR=
quota=
CORENUM=1
SlowResult=
mysql_acquisition_json=""

function gotErr(){
    if [ "$1" -eq 3 ];then 
        echo "mailstatedes=无法获取到组件PID,疑似组件故障,请确认并请检查监控脚本和运维平台配置 statedes=无法获取到指标,疑似组件故障,请确认并请检查监控脚本和运维平台配置|$DataResultStr"
    elif [ "$1" -eq 4 ];then
        echo "mailstatedes=该组件进程为僵尸进程,请确认并请检查该组件状态 statedes=该组件进程为僵尸进程,请确认并请检查该组件状态|$DataResultStr"
        exit 3
    fi

    exit $1
}

function isAlive(){
    CORENUM=`cat /proc/cpuinfo |grep "physical id"|wc -l`
    FLAGEID=$(ss -lnpt "( sport == :$PORT )"|tail -n 1|awk -F " " '{print $6}'|awk -F ',' '{print $2}')
    if [[ "$FLAGEID" == "" ]];then
        gotErr 3
    fi

    ZOMBIE=$(ps -A -ostat,pid | grep -e '^[Zz]' | grep $FLAGEID)
    if [[ "$ZOMBIE" != "" ]];then
        gotErr 4
    fi

    #mysql_acquisition_json="$mysql_acquisition_json,'FLAGEPID':$FLAGEID"
    map["FLAGEPID"]="$FLAGEID"
}

#处理连接数
function getConnNum(){
    CURNUM=$( netstat -apn | grep $FLAGEID | grep ESTABLISHED | wc -l)
    #mysql_acquisition_json="$mysql_acquisition_json,'CONN':$CURNUM"
    map["CONN"]="$CURNUM"
}

#ps aux 4是内存 累加4的值
function getMemory(){
    CURNUM=$(ps aux | grep $FLAGEID |awk 'BEGIN{sum=0}{sum+=$4}END{print sum}')
    #mysql_acquisition_json="$mysql_acquisition_json,'MEMORY':$CURNUM"
    map["MEMORY"]="$CURNUM"
}

#ps aux 3是CPU 累加3的值
function getCPU(){
    CURNUM=$(ps aux | grep $FLAGEID |awk 'BEGIN{sum=0}{sum+=$3}END{print sum}')
    if [ "$CORENUM" -ne 0 ];then
       CURNUM=$(printf "%.2f" `echo "scale=2;$CURNUM/$CORENUM" | bc`)
    fi
    #mysql_acquisition_json="$mysql_acquisition_json,'CPU':$CURNUM"
    map["CPU"]="$CURNUM"
}

#ps -eo pid,etime 是查询进程运行时间
function getRuntime(){
    CURNUM=$(ps -eo pid,etime | grep $FLAGEID |awk '{if($1=='$FLAGEID') print $2}')
    #mysql_acquisition_json="$mysql_acquisition_json,'RUNTIME':'$CURNUM'"
    map["RUNTIME"]="$CURNUM"
}

#ps -eo pid,lstart是查询进程的开始时间
function getStarttime(){
    CURNUM=$(ps -eo pid,lstart | grep $FLAGEID |awk '{if($1=='$FLAGEID') print $2 ,$3 ,$4 ,$5 ,$6}')
    #mysql_acquisition_json="$mysql_acquisition_json,'STARTTIME':'$CURNUM'"
    map["STARTTIME"]="$CURNUM"
}

#################################################新增指标################################################


#获取Slave_IO_Running状态
function getSlave_IO_Running(){
    Slave_IO_Running=`$mysqlStatStr 2>/dev/null -e "show slave status\G" | grep -i "running"| grep Slave_IO_Running | awk  '{print $2}'`
    if [ "${Slave_IO_Running}" = "No" ];then
        CURNUM=2
    else
        CURNUM=0
    fi
    #mysql_acquisition_json="$mysql_acquisition_json,'Slave_IO_Running':'$CURNUM'"
    map["Slave_IO_Running"]="$CURNUM"
}

#获取Slave_SQL_Running状态
function getSlave_SQL_Running(){
    Slave_SQL_Running=`$mysqlStatStr 2>/dev/null -e "show slave status\G" | grep -i "running"| grep Slave_SQL_Running: | awk ' {print $2}'`
    if [ "${Slave_SQL_Running}" = "No" ];then
        CURNUM=2
    else
        CURNUM=0
    fi
    #mysql_acquisition_json="$mysql_acquisition_json,'Slave_SQL_Running':'$CURNUM'"
    map["Slave_SQL_Running"]="$CURNUM"
}

#复制延迟时间
function getSeconds_Behind_Master(){
    Seconds_Behind_Master=`$mysqlStatStr 2>/dev/null -e "show slave status\G" | grep Seconds_Behind_Master | awk '{print $2}'`
    CURNUM=${Seconds_Behind_Master}
    if [ "$CURNUM" == "" ];then
        CURNUM=0
    elif [ $CURNUM == "NULL" ];then
        CURNUM=65536
    fi
    #mysql_acquisition_json="$mysql_acquisition_json,'Seconds_Behind_Master':'$CURNUM'"
    map["Seconds_Behind_Master"]="$CURNUM"  
}

function getRole(){
Master_Host=`$mysqlStatStr 2>/dev/null -e "show slave status \G" | grep "Master_Host" | awk '{print $2}'`
    if [[ "$Master_Host" == "" ]];then
        CURNUM=0
    else
        CURNUM=1
    fi
    #mysql_acquisition_json="$mysql_acquisition_json,'Role':'$CURNUM'"
    map["Role"]="$CURNUM"
}

function getUptime(){
uptime=`$mysqlStatStr 2>/dev/null -e "show global status like 'uptime' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$uptime
#mysql_acquisition_json="$mysql_acquisition_json,'Uptime':'$CURNUM'"
map["Uptime"]="$CURNUM"
}

function getVersion(){
version=`$mysqlStatStr 2>/dev/null -e "show global variables like 'version' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$version
mysql_acquisition_json="$mysql_acquisition_json,'Version':'$CURNUM'"
map["Version"]="$CURNUM"
}
function getMax_connections(){
max_connections=`$mysqlStatStr 2>/dev/null -e "show global variables like 'max_connections' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$max_connections
#mysql_acquisition_json="$mysql_acquisition_json,'Max_connections':'$CURNUM'"
map["Max_connections"]="$CURNUM"
}
function getMax_connect_errors(){
max_connect_errors=`$mysqlStatStr 2>/dev/null -e "show global variables like 'max_connect_errors' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$max_connect_errors
#mysql_acquisition_json="$mysql_acquisition_json,'Max_connect_errors':'$CURNUM'"
map["Max_connect_errors"]="$CURNUM"
}
function getOpen_files_limit(){
open_files_limit=`$mysqlStatStr 2>/dev/null -e "show global variables like 'open_files_limit' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$open_files_limit
#mysql_acquisition_json="$mysql_acquisition_json,'Open_files_limit':'$CURNUM'"
map["Open_files_limit"]="$CURNUM"
}
function getTable_open_cache(){
max_connect_errors=`$mysqlStatStr 2>/dev/null -e "show global variables like 'max_connect_errors' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$max_connect_errors
#mysql_acquisition_json="$mysql_acquisition_json,'Table_open_cache':'$CURNUM'"
map["Table_open_cache"]="$CURNUM"
}
function getMax_tmp_tables(){
max_tmp_tables=`$mysqlStatStr 2>/dev/null -e "show global variables like 'max_tmp_tables' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$max_tmp_tables
mysql_acquisition_json="$mysql_acquisition_json,'Max_tmp_tables':'$CURNUM'"
map["Max_tmp_tables"]="$CURNUM"
}
function getMax_allowed_packet(){
max_allowed_packet=`$mysqlStatStr 2>/dev/null -e "show global variables like 'max_allowed_packet' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$max_allowed_packet
mysql_acquisition_json="$mysql_acquisition_json,'Max_allowed_packet':'$CURNUM'"
map["Max_allowed_packet"]="$CURNUM"
}
function getOpen_files(){
open_files=`$mysqlStatStr 2>/dev/null -e "show global status like 'open_files' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$open_files
mysql_acquisition_json="$mysql_acquisition_json,'Open_files':'$CURNUM'"
map["Open_files"]="$CURNUM"
}
function getOpen_tables(){
open_tables=`$mysqlStatStr 2>/dev/null -e "show global status like 'open_tables' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$open_tables
mysql_acquisition_json="$mysql_acquisition_json,'Open_tables':'$CURNUM'"
map["Open_tables"]="$CURNUM"
}
function getThreads_connected(){
threads_connected=`$mysqlStatStr 2>/dev/null -e "show global status like 'threads_connected' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$threads_connected
mysql_acquisition_json="$mysql_acquisition_json,'Threads_connected':'$CURNUM'"
map["Threads_connected"]="$CURNUM"
}
function getThreads_running(){
threads_running=`$mysqlStatStr 2>/dev/null -e "show global status like 'threads_running' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$threads_running
mysql_acquisition_json="$mysql_acquisition_json,'Threads_running':'$CURNUM'"
map["Threads_running"]="$CURNUM"
}
function getThreads_waits(){
threads_waits=`$mysqlStatStr 2>/dev/null -e "show global status like 'threads_waits' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$threads_waits
mysql_acquisition_json="$mysql_acquisition_json,'Threads_waits':'$CURNUM'"
map["Threads_waits"]="$CURNUM"
}
function getThreads_created(){
threads_created=`$mysqlStatStr 2>/dev/null -e "show global status like 'threads_created' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$threads_created
mysql_acquisition_json="$mysql_acquisition_json,'Threads_created':'$CURNUM'"
map["Threads_created"]="$CURNUM"
}
function getThreads_cached(){
threads_cached=`$mysqlStatStr 2>/dev/null -e "show global status like 'threads_cached' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$threads_cached
mysql_acquisition_json="$mysql_acquisition_json,'Threads_cached':'$CURNUM'"
map["Threads_cached"]="$CURNUM"
}
function getConnections(){
connections=`$mysqlStatStr 2>/dev/null -e "show global status like 'connections' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$connections
mysql_acquisition_json="$mysql_acquisition_json,'Connections':'$CURNUM'"
map["Connections"]="$CURNUM"
}

function getConnections_persecond(){
connections=`$mysqlStatStr 2>/dev/null -e "show global status like 'connections' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_connections_time} == "" ]];then
echo "connections ${connections} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "connections ${connections} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec - last_connections_time))
diff=$((last_connections - connections))
if [ ${diff} -lt 0 ];then
 diff=0
fi

if [ "${diffsec}" = "" -o ${diffsec} -eq 0 ];then
    CURNUM=0
else
    CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Connections_persecond':'$CURNUM'"
map["Connections_persecond"]="$CURNUM"
}
function getBytes_received_persecond(){
bytes_received=`$mysqlStatStr 2>/dev/null -e "show global status like 'bytes_received' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$bytes_received
mysql_acquisition_json="$mysql_acquisition_json,'Bytes_received_persecond':'$CURNUM'"
}
function getBytes_sent_persecond(){
bytes_sent=`$mysqlStatStr 2>/dev/null -e "show global status like 'bytes_sent' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_bytes_sent_time} == "" ]];then
echo "bytes_sent ${bytes_sent} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "bytes_sent ${bytes_sent} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_bytes_sent_time))
diff=$((bytes_sent-last_bytes_sent))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Bytes_sent_persecond':'$CURNUM'"
map["Bytes_sent_persecond"]="$CURNUM"
}
function getCom_select_persecond(){
com_select=`$mysqlStatStr 2>/dev/null -e "show global status like 'com_select' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$com_select
mysql_acquisition_json="$mysql_acquisition_json,'Com_select_persecond':'$CURNUM'"
map["Com_select_persecond"]="$CURNUM"
}
function getCom_insert_persecond(){
com_insert=`$mysqlStatStr 2>/dev/null -e "show global status like 'com_insert' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$com_insert
mysql_acquisition_json="$mysql_acquisition_json,'Com_insert_persecond':'$CURNUM'"
map["Com_insert_persecond"]="$CURNUM"
}
function getCom_update_persecond(){
com_update=`$mysqlStatStr 2>/dev/null -e "show global status like 'com_update' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$com_update
mysql_acquisition_json="$mysql_acquisition_json,'Com_update_persecond':'$CURNUM'"
map["Com_update_persecond"]="$CURNUM"
}
function getCom_delete_persecond(){
com_delete=`$mysqlStatStr 2>/dev/null -e "show global status like 'com_delete' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$com_delete
mysql_acquisition_json="$mysql_acquisition_json,'Com_delete_persecond':'$CURNUM'"
map["Com_delete_persecond"]="$CURNUM"
}
function getCom_commit_persecond(){
com_commit=`$mysqlStatStr 2>/dev/null -e "show global status like 'com_commit' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$com_commit
mysql_acquisition_json="$mysql_acquisition_json,'Com_commit_persecond':'$CURNUM'"
map["Com_commit_persecond"]="$CURNUM"
}
function getCom_rollback_persecond(){
com_rollback=`$mysqlStatStr 2>/dev/null -e "show global status like 'com_rollback' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$com_rollback
mysql_acquisition_json="$mysql_acquisition_json,'Com_rollback_persecond':'$CURNUM'"
map["Com_rollback_persecond"]="$CURNUM"
}
function getQuestions_persecond(){
questions=`$mysqlStatStr 2>/dev/null -e "show global status like 'questions' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$questions
mysql_acquisition_json="$mysql_acquisition_json,'Questions_persecond':'$CURNUM'"
map["Questions_persecond"]="$CURNUM"
}

function getQueries_persecond(){
queries=`$mysqlStatStr 2>/dev/null -e "show global status like 'queries' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_queries_time} == "" ]];then
echo "queries ${queries} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "queries ${queries} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_queries_time))
diff=$((queries-last_queries))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Queries_persecond':'$CURNUM'"
map["Queries_persecond"]="$CURNUM"
}

function getTransaction_persecond(){
transaction=`$mysqlStatStr 2>/dev/null -e "show global status like 'transaction' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_transaction_time} == "" ]];then
echo "transaction ${transaction} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "transaction ${transaction} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_transaction_time))
diff=$((transaction-last_transaction))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Transaction_persecond':'$CURNUM'"
map["Transaction_persecond"]="$CURNUM"
}

function getlocks_waited(){
#echo "$mysqlStatStr 2>/dev/null -e "show global status like 'table_locks_waited' \G" | grep "Value" | awk '{print $2}'"
table_locks_waited=`$mysqlStatStr 2>/dev/null -e "show global status like 'table_locks_waited' \G" | grep "Value" | awk '{print $2}'`
table_locks_immediate=`$mysqlStatStr 2>/dev/null -e "show global status like 'table_locks_waited' \G" | grep "Value" | awk '{print $2}'`
if [[ "$table_locks_immediate" == "" || "$table_locks_immediate" -eq 0 ]]; then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${table_locks_waited}'/'${table_locks_immediate}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'locks_waited':'$CURNUM'"
map["locks_waited"]="$CURNUM"
}

function getTable_locks_rate(){
table_locks_waited=`$mysqlStatStr 2>/dev/null -e "show global status like 'table_locks_waited' \G" | grep "Value" | awk '{print $2}'`
table_locks_immediate=`$mysqlStatStr 2>/dev/null -e "show global status like 'table_locks_waited' \G" | grep "Value" | awk '{print $2}'`
if [[ "$table_locks_immediate" == "" || "$table_locks_immediate" -eq 0 ]];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${table_locks_waited}'/'${table_locks_immediate}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Table_locks_rate':'$CURNUM'"
map["Table_locks_rate"]="$CURNUM"
}
function getKey_buffer_size(){
key_buffer_size=`$mysqlStatStr 2>/dev/null -e "show global variables like 'key_buffer_size' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$key_buffer_size
mysql_acquisition_json="$mysql_acquisition_json,'Key_buffer_size':'$CURNUM'"
map["Key_buffer_size"]="$CURNUM"
}

function getSort_buffer_size(){
sort_buffer_size=`$mysqlStatStr 2>/dev/null -e "show global variables like 'sort_buffer_size' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$sort_buffer_size
mysql_acquisition_json="$mysql_acquisition_json,'Sort_buffer_size':'$CURNUM'"
map["Sort_buffer_size"]="$CURNUM"
}

function getKey_read_requests_persecond(){
key_read_requests=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_read_requests' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${key_read_requests_time} == "" ]];then
echo "key_read_requests ${key_read_requests} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "key_read_requests ${key_read_requests} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_key_read_requests_time))
diff=$((key_read_requests-last_key_read_requests))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Key_read_requests_persecond':'$CURNUM'"
map["Key_read_requests_persecond"]="$CURNUM"
}
function getKey_reads_persecond(){
key_reads=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_reads' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${key_reads_time} == "" ]];then
echo "key_reads ${key_reads} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "key_reads ${key_reads} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_key_reads_time))
diff=$((key_reads-last_key_reads))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Key_reads_persecond':'$CURNUM'"
map["Key_reads_persecond"]="$CURNUM"
}

function getKey_write_requests_persecond(){
key_write_requests=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_write_requests' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${key_write_requests_time} == "" ]];then
echo "key_write_requests ${key_write_requests} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "key_write_requests ${key_write_requests} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_key_write_requests_time))
diff=$((key_write_requests-last_key_write_requests))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Key_write_requests_persecond':'$CURNUM'"
map["Key_write_requests_persecond"]="$CURNUM"
}
function getKey_writes_persecond(){
key_writes=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_writes' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${key_writes_time} == "" ]];then
echo "key_writes ${key_writes} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "key_writes ${key_writes} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_key_writes_time))
diff=$((key_writes-last_key_writes))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Key_writes_persecond':'$CURNUM'"
map["Key_writes_persecond"]="$CURNUM"
}

function getInnodb_buffer_pool_instances(){
innodb_buffer_pool_instances=`$mysqlStatStr 2>/dev/null -e "show global variables like 'innodb_buffer_pool_instances' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_instances
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_instances':'$CURNUM'"
map["Innodb_buffer_pool_instances"]="$CURNUM"
}

function getInnodb_buffer_pool_size(){
innodb_buffer_pool_size=`$mysqlStatStr 2>/dev/null -e "show global variables like 'innodb_buffer_pool_size' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_size
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_size':'$CURNUM'"
map["Innodb_buffer_pool_size"]="$CURNUM"
}

function getInnodb_buffer_pool_pages_total(){
innodb_buffer_pool_pages_total=`$mysqlStatStr 2>/dev/null -e "show global status like 'Innodb_buffer_pool_pages_total' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_pages_total
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_pages_total':'$CURNUM'"
map["Innodb_buffer_pool_pages_total"]="$CURNUM"
}

function getInnodb_buffer_pool_pages_data(){
innodb_buffer_pool_pages_data=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_buffer_pool_pages_data' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_pages_data
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_pages_data':'$CURNUM'"
map["Innodb_buffer_pool_pages_data"]="$CURNUM"
}

function getInnodb_buffer_pool_pages_dirty(){
innodb_buffer_pool_pages_dirty=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_buffer_pool_pages_dirty' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_pages_dirty
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_pages_dirty':'$CURNUM'"
map["Innodb_buffer_pool_pages_dirty"]="$CURNUM"
}

function getInnodb_buffer_pool_pages_flushed(){
innodb_buffer_pool_pages_flushed=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_buffer_pool_pages_flushed' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_pages_flushed
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_pages_flushed':'$CURNUM'"
map["Innodb_buffer_pool_pages_flushed"]="$CURNUM"
}

function getInnodb_buffer_pool_pages_free(){
innodb_buffer_pool_pages_free=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_buffer_pool_pages_free' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_pages_free
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_pages_free':'$CURNUM'"
map["Innodb_buffer_pool_pages_free"]="$CURNUM"
}

function getInnodb_buffer_pool_pages_misc(){
innodb_buffer_pool_pages_misc=`$mysqlStatStr 2>/dev/null -e "show global status like 'Innodb_buffer_pool_pages_misc' \G" | grep "Value" | awk '{print $2}'`
CURNUM=$innodb_buffer_pool_pages_misc
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_pages_misc':'$CURNUM'"
map["Innodb_buffer_pool_pages_misc"]="$CURNUM"
}

function getInnodb_buffer_pool_pages_free_rage(){
getInnodb_buffer_pool_pages_free
innodb_buffer_pool_pages_free=$?
getInnodb_buffer_pool_pages_total
innodb_buffer_pool_pages_total=$?
if [ "$innodb_buffer_pool_pages_total" == "" -o "$innodb_buffer_pool_pages_total" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${innodb_buffer_pool_pages_free}'/'${innodb_buffer_pool_pages_total}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_buffer_pool_pages_free_rage':'$CURNUM'"
map["Innodb_buffer_pool_pages_free_rage"]="$CURNUM"
}

function getInnodb_rows_read_persecond(){
innodb_rows_read=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_rows_read' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_rows_read_time} == "" ]];then
echo "innodb_rows_read ${innodb_rows_read} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "innodb_rows_read ${innodb_rows_read} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_rows_read_time))
diff=$((last_innodb_rows_read-innodb_rows_read))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_rows_read_persecond':'$CURNUM'"
map["Innodb_rows_read_persecond"]="$CURNUM"

}
function getInnodb_rows_inserted_persecond(){
innodb_rows_inserted=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_rows_inserted' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_rows_inserted_time} == "" ]];then
echo "innodb_rows_inserted ${innodb_rows_inserted} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "innodb_rows_inserted ${innodb_rows_inserted} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_rows_inserted_time))
diff=$((last_innodb_rows_inserted-innodb_rows_inserted))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_rows_inserted_persecond':'$CURNUM'"
map["Innodb_rows_inserted_persecond"]="$CURNUM"
}

function getInnodb_rows_updated_persecond(){
innodb_rows_updated=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_rows_updated' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_rows_updated_time} == "" ]];then
echo "innodb_rows_updated ${innodb_rows_updated} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "innodb_rows_updated ${innodb_rows_updated} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_rows_updated_time))
diff=$((last_innodb_rows_updated-innodb_rows_updated))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_rows_updated_persecond':'$CURNUM'"
map["Innodb_rows_updated_persecond"]="$CURNUM"
}
function getInnodb_rows_deleted_persecond(){
innodb_rows_deleted=`$mysqlStatStr 2>/dev/null -e "show global status like 'innodb_rows_deleted' \G" | grep "Value" | awk '{print $2}'`
nowsec=`date +%s`
if [[ ${last_rows_deleted_time} == "" ]];then
echo "innodb_rows_deleted ${innodb_rows_deleted} ${nowsec}" >> ${PORT}.txt
CURNUM=
return
fi
echo "innodb_rows_deleted ${innodb_rows_deleted} ${nowsec}" >> ${PORT}.txt
diffsec=$((nowsec-last_rows_deleted_time))
diff=$((last_innodb_rows_deleted-innodb_rows_deleted))
if [ "$diffsec" == "" -o "$diffsec" -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${diff}'/'${diffsec}')}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Innodb_rows_deleted_persecond':'$CURNUM'"
map["Innodb_rows_deleted_persecond"]="$CURNUM"
}

function getKey_buffer_read_rate(){
key_reads=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_reads' \G" | grep "Value" | awk '{print $2}'`
key_read_requests=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_read_requests' \G" | grep "Value" | awk '{print $2}'`
if [[ "$key_read_requests" == "" || "$key_read_requests" -eq 0 ]];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${key_reads}'/'${key_read_requests}')*100}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Key_buffer_read_rate':'$CURNUM'"
}
function getKey_buffer_write_rate(){
key_writes=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_writes' \G" | grep "Value" | awk '{print $2}'`
key_write_requests=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_write_requests' \G" | grep "Value" | awk '{print $2}'`
if [[ "$key_write_requests" == "" || "$key_write_requests" -eq 0 ]];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${key_writes}'/'${key_write_requests}')*100}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Key_buffer_write_rate':'$CURNUM'"
map["Key_buffer_write_rate"]="$CURNUM"
}
function getKey_blocks_used_rate(){
key_blocks_unused=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_blocks_unused' \G" | grep "Value" | awk '{print $2}'`
key_blocks_used=`$mysqlStatStr 2>/dev/null -e "show global status like 'key_blocks_used' \G" | grep "Value" | awk '{print $2}'`
key_blocks_total=$((key_blocks_used+key_blocks_unused))
if [ "$key_blocks_total" == "" -o $key_blocks_total -eq 0 ];then
CURNUM=0
else
CURNUM=`awk 'BEGIN{printf "%.2f\n",('${key_blocks_used}'/'${key_blocks_total}')*100}'`
fi
mysql_acquisition_json="$mysql_acquisition_json,'Key_blocks_used_rate':'$CURNUM'"
map["Key_blocks_used_rate"]="$CURNUM"
}

function slow_log(){
if [ "$MYSQL_SLOW_LOG_PATH" = "<<slow_query_path>>" ];then
       SlowResult="慢日志文件路径缺省"
       EXITCODE=3
    else
        slow_query_script="$(cd `dirname $0`;pwd)/check_slow_query.py"
        SlowResult=`$slow_query_script -f $MYSQLHOME -l $MYSQL_SLOW_LOG_PATH`
        EXITCODE=$?
    fi

    if [[ "$EXITCODE" -eq 3 ]];then
        CURNUM=
        SlowResult=""
    else
        COUNT=$(echo $SlowResult| awk -F '<<COUNT>>' '{print $1}')
        SlowResult=$(echo $SlowResult| awk -F '<<COUNT>>' '{print $2}')
        CURNUM=${COUNT}
        if [[ "$CURNUM" == "" ]];then
              CURNUM=0
        elif [[ "$CURNUM" =~ "文件不存在" ]];then
              CURNUM=
        fi
        #parseFileds "Slow_queries" $Slow_queries_min $Slow_queries_max
    fi
    #########################################慢查询待修改
    mysql_acquisition_json="$mysql_acquisition_json,'Slow_queries':'$CURNUM'"
    map["Slow_queries"]="$CURNUM"
}


function initLast(){
if [ ! -f ${PORT}.txt ]; then
 touch ${PORT}.txt
fi

last_uptime=$(cat ${PORT}.txt |grep uptime|awk '{print $2}')
last_connections=$(cat ${PORT}.txt |grep connections|awk '{print $2}')
last_connections_time=$(cat ${PORT}.txt |grep connections|awk '{print $3}')
last_bytes_received=$(cat ${PORT}.txt |grep bytes_received|awk '{print $2}')
last_bytes_received_time=$(cat ${PORT}.txt |grep bytes_received|awk '{print $3}')
last_bytes_sent=$(cat ${PORT}.txt |grep bytes_sent|awk '{print $2}')
last_bytes_sent_time=$(cat ${PORT}.txt |grep bytes_sent|awk '{print $3}')
last_com_select=$(cat ${PORT}.txt |grep com_select|awk '{print $2}')
last_com_select_time=$(cat ${PORT}.txt |grep com_select|awk '{print $3}')
last_com_insert=$(cat ${PORT}.txt |grep com_insert|awk '{print $2}')
last_com_insert_time=$(cat ${PORT}.txt |grep com_insert|awk '{print $3}')
last_com_update=$(cat ${PORT}.txt |grep com_update|awk '{print $2}')
last_com_update_time=$(cat ${PORT}.txt |grep com_update|awk '{print $3}')
last_com_delete=$(cat ${PORT}.txt |grep com_delete|awk '{print $2}')
last_com_delete_time=$(cat ${PORT}.txt |grep com_delete|awk '{print $3}')
last_com_commit=$(cat ${PORT}.txt |grep com_commit|awk '{print $2}')
last_com_commit_time=$(cat ${PORT}.txt |grep com_commit|awk '{print $3}')
last_com_rollback=$(cat ${PORT}.txt |grep com_rollback|awk '{print $2}')
last_com_rollback_time=$(cat ${PORT}.txt |grep com_rollback|awk '{print $3}')
last_questions=$(cat ${PORT}.txt |grep questions|awk '{print $2}')
last_questions_time=$(cat ${PORT}.txt |grep questions|awk '{print $3}')
last_queries=$(cat ${PORT}.txt |grep queries|awk '{print $2}')
last_queries_time=$(cat ${PORT}.txt |grep queries|awk '{print $3}')
last_transactions=$(cat ${PORT}.txt |grep transactions|awk '{print $2}')
last_transactions_time=$(cat ${PORT}.txt |grep transactions|awk '{print $3}')

last_key_read_requests=$(cat ${PORT}.txt |grep key_read_requests|awk '{print $2}')
last_key_read_requests_time=$(cat ${PORT}.txt |grep key_read_requests|awk '{print $3}')
last_key_reads=$(cat ${PORT}.txt |grep key_reads|awk '{print $2}')
last_key_reads_time=$(cat ${PORT}.txt |grep key_reads|awk '{print $3}')
last_key_write_requests=$(cat ${PORT}.txt |grep key_write_requests|awk '{print $2}')
last_key_write_requests_time=$(cat ${PORT}.txt |grep key_write_requests|awk '{print $3}')
last_key_writes=$(cat ${PORT}.txt |grep key_writes|awk '{print $2}')
last_key_writes_time=$(cat ${PORT}.txt |grep key_writes|awk '{print $3}')

last_innodb_rows_read=$(cat ${PORT}.txt |grep innodb_rows_read|awk '{print $2}')
last_innodb_rows_read_time=$(cat ${PORT}.txt |grep innodb_rows_read|awk '{print $3}')
last_innodb_rows_inserted=$(cat ${PORT}.txt |grep innodb_rows_inserted|awk '{print $2}')
last_innodb_rows_inserted_time=$(cat ${PORT}.txt |grep innodb_rows_inserted|awk '{print $3}')
last_innodb_rows_updated=$(cat ${PORT}.txt |grep innodb_rows_updated|awk '{print $2}')
last_innodb_rows_updated_time=$(cat ${PORT}.txt |grep innodb_rows_updated|awk '{print $3}')
last_innodb_rows_deleted=$(cat ${PORT}.txt |grep innodb_rows_deleted|awk '{print $2}')
last_innodb_rows_deleted_time=$(cat ${PORT}.txt |grep innodb_rows_deleted|awk '{print $3}')

echo "##" > ${PORT}.txt

}



#依据传入的参数值来判断我们要监控的服务是什么
#然后通过各个函数获取到具体的值
#值之间的
function analysisStat(){

    getConnNum
    getCPU
    getMemory
    CURNUM=$FLAGEID
    getRuntime
    getStarttime
    slow_log
    if [[  -n "$USER" &&  -n "$PASSWORD" && "$USER" != PARAM_USERNAME && "$PASSWORD" != PARAM_PASSWORD ]];then 
    initLast
    getSlave_IO_Running
    getSlave_SQL_Running
    getSeconds_Behind_Master
    getlocks_waited
    getRole
    getUptime
    getVersion
    getMax_connections
    getMax_connect_errors
    getOpen_files_limit
    getTable_open_cache
    getMax_tmp_tables
    getMax_allowed_packet
    getOpen_files
    getOpen_tables
    getThreads_connected
    getThreads_running
    getThreads_waits
    getThreads_cached
    getConnections
    getConnections_persecond
    getBytes_received_persecond
    getBytes_sent_persecond
    getCom_select_persecond
    getCom_insert_persecond
    getCom_update_persecond
    getCom_delete_persecond
    getCom_commit_persecond
    getCom_rollback_persecond
    getQuestions_persecond
    getQueries_persecond
    getTransaction_persecond
    getTable_locks_rate
    getKey_buffer_size
    getSort_buffer_size
    getKey_read_requests_persecond
    getKey_reads_persecond
    getKey_write_requests_persecond
    getKey_writes_persecond
    getInnodb_buffer_pool_instances
    getInnodb_buffer_pool_size
     getInnodb_buffer_pool_pages_total
    getInnodb_buffer_pool_pages_data
    getInnodb_buffer_pool_pages_dirty
    getInnodb_buffer_pool_pages_flushed
    getInnodb_buffer_pool_pages_free
    getInnodb_buffer_pool_pages_free_rage
    getInnodb_buffer_pool_pages_misc
    getInnodb_rows_deleted_persecond
    getInnodb_rows_inserted_persecond
    getInnodb_rows_read_persecond
    getInnodb_rows_updated_persecond
    getKey_buffer_read_rate
    getKey_buffer_write_rate
    getKey_blocks_used_rate
    fi

    #result="{'cpname':'mysql',${mysql_acquisition_json#*,}}"
    #result=`echo ${result//\'/\"}`
    #echo $result

}

#输出错误信息并且退出程序
function showHelp(){
    if [ "$1" != "" ];then
        echo "请输入$1"
    fi
    echo "check_mysql.sh 可以监听本地的mysql的状态 参数如下"
    echo "check_mysql.sh [-w 连接数,CPU,内存,慢查询数目，Slave_IO_Running，Slave_SQL_Running，Seconds_Behind_Master，locks_waited] [-c 连接数,CPU,内存,慢查询数目，Slave_SQL_Running，Seconds_Behind_Master，locks_waited] [ -b <str>] [ -h <str>] [ -P <str>] [ -u <str>] [ -p <str>] [ -l <str>]"
    echo "-b 表示安装目录 默认为/usr/bin"
    echo "-p 表示MySQL服务器的端口 默认为3306"
    echo "-f 用于标示进程的字符串"
    echo "-l 用于标示慢查询日志的路径"
    echo "-I 用于标识主机号"
    echo "-u 用于标识MySQL用户名"
    echo "-P 用于标识密码"
    exit 1
}

#如果无法返回 则认为环境变量有误，则所有指标置为0
function getMySQLStat(){
    #需要在代码里面加密？
    #暂时不处理加密，由外部传入加密串
    #user=`echo $USER | openssl aes-128-cbc -k cycore -base64`
    #password=`echo $PASSWORD | openssl aes-128-cbc -k cycore -base64`
    user=`echo $USER`
    password=`echo $PASSWORD`
    if [ "$PASSWORD" != "" ];then
        mysqlStatStr="$MYSQLHOME/mysql -u $user -p$password -h $MYSQLHOST -P $PORT"
    else
        mysqlStatStr=1
    fi
    #echo "mysqlStatStr=${mysqlStatStr}"

    if [[ "$mysqlStatStr" == "" ]];then
        HASBASEDIR=0
    fi
    return 0
}

while getopts "u:p:b:l:f:I:P:h" arg
do
    case $arg in
        u)
            USER=$OPTARG
            ;;
        P)
            PASSWORD=$OPTARG
            ;;
        b)
            MYSQLHOME=$OPTARG
            ;;
        l)
            MYSQL_SLOW_LOG_PATH=$OPTARG
            ;;
        f)
            FLAGE=$OPTARG
            ;;
        I)
            MYSQLHOST=$OPTARG
            ;;
        p)
            PORT=$OPTARG
            ;;
        h)
            showHelp
            ;;
        ?) 
            showHelp
            ;;
    esac
done

if [[ "$PORT" == "" ]];then
    showHelp "特征项"
fi

#checkALL
if [[ $? == 1 ]];then
    showHelp "正确参数"
fi

isAlive
getMySQLStat
analysisStat

echo -e "{\c"
for key in ${keys[@]};  
do  
echo -e "\"${key}\":\"${map[$key]//\'/\"}\",\c" 
done
echo -e "\"cpname\":\"mysql\"\c"
echo -e "}"
javaer, javaer

188
javaer, javaer

发表回复 取消回复

发表回复取消回复
