#! /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 "}"
