check_mysql.sh查看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

    exit1
}

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 ',' '{print2}')
    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 | grepFLAGEID |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 | grepFLAGEID |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 | grepFLAGEID |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 | grepFLAGEID |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  '{print2}'`
    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 ' {print2}'`
    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 '{print2}'`
    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 '{print2}'`
    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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'"
table_locks_waited=`mysqlStatStr 2>/dev/null -e "show global status like 'table_locks_waited' \G" | grep "Value" | awk '{print2}'`
table_locks_immediate=`mysqlStatStr 2>/dev/null -e "show global status like 'table_locks_waited' \G" | grep "Value" | awk '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
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 '{print2}'`
key_blocks_used=`mysqlStatStr 2>/dev/null -e "show global status like 'key_blocks_used' \G" | grep "Value" | awk '{print2}'`
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 -lMYSQL_SLOW_LOG_PATH`
        EXITCODE=?
    fi

    if [[ "EXITCODE" -eq 3 ]];then
        CURNUM=
        SlowResult=""
    else
        COUNT=(echoSlowResult| awk -F '<<COUNT>>' '{print 1}')
        SlowResult=(echo SlowResult| awk -F '<<COUNT>>' '{print2}')
        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 '{print2}')
last_connections_time=(cat{PORT}.txt |grep connections|awk '{print 3}')
last_bytes_received=(cat {PORT}.txt |grep bytes_received|awk '{print2}')
last_bytes_received_time=(cat{PORT}.txt |grep bytes_received|awk '{print 3}')
last_bytes_sent=(cat {PORT}.txt |grep bytes_sent|awk '{print2}')
last_bytes_sent_time=(cat{PORT}.txt |grep bytes_sent|awk '{print 3}')
last_com_select=(cat {PORT}.txt |grep com_select|awk '{print2}')
last_com_select_time=(cat{PORT}.txt |grep com_select|awk '{print 3}')
last_com_insert=(cat {PORT}.txt |grep com_insert|awk '{print2}')
last_com_insert_time=(cat{PORT}.txt |grep com_insert|awk '{print 3}')
last_com_update=(cat {PORT}.txt |grep com_update|awk '{print2}')
last_com_update_time=(cat{PORT}.txt |grep com_update|awk '{print 3}')
last_com_delete=(cat {PORT}.txt |grep com_delete|awk '{print2}')
last_com_delete_time=(cat{PORT}.txt |grep com_delete|awk '{print 3}')
last_com_commit=(cat {PORT}.txt |grep com_commit|awk '{print2}')
last_com_commit_time=(cat{PORT}.txt |grep com_commit|awk '{print 3}')
last_com_rollback=(cat {PORT}.txt |grep com_rollback|awk '{print2}')
last_com_rollback_time=(cat{PORT}.txt |grep com_rollback|awk '{print 3}')
last_questions=(cat {PORT}.txt |grep questions|awk '{print2}')
last_questions_time=(cat{PORT}.txt |grep questions|awk '{print 3}')
last_queries=(cat {PORT}.txt |grep queries|awk '{print2}')
last_queries_time=(cat{PORT}.txt |grep queries|awk '{print 3}')
last_transactions=(cat {PORT}.txt |grep transactions|awk '{print2}')
last_transactions_time=(cat{PORT}.txt |grep transactions|awk '{print 3}')

last_key_read_requests=(cat {PORT}.txt |grep key_read_requests|awk '{print2}')
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 '{print2}')
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 '{print2}')
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 '{print2}')
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 '{print2}')
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 '{print2}')
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 '{print2}')
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 '{print2}')
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//\'/\"}`
    #echoresult

}

#输出错误信息并且退出程序
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=`echoPASSWORD | openssl aes-128-cbc -k cycore -base64`
    user=`echo USER`
    password=`echoPASSWORD`
    if [ "PASSWORD" != "" ];then
        mysqlStatStr="MYSQLHOME/mysql -u user -ppassword -h MYSQLHOST -PPORT"
    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 "}"