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