mysql状态监控脚本

mysql状态监控脚本

可将数据库密码放入环境变量，用base64转码一次

#!/bin/bash

. /etc/profile &> /dev/null

RED='\E[31;2m'
GREEN='\E[32;1m'
END='\E[0m'
db_user='-uhj'
db_pw="-p`echo $mp|base64 -d`"
db_host='-h127.0.0.1'
db_sql="mysql $db_user $db_pw -e"

t_run() {	#当前在执行的线程
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Threads_running'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}
t_conn() {	#当前连接数
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Threads_connected'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}
s_query(){	#慢查询数
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Slow_queries'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}	
c_error(){	#服务器内部错误(如无法启动新线程或内存不足)而被拒绝的连接数
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Connection_errors_internal'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}
c_max_error(){	#超出最大连接后的拒绝连接数
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Connection_errors_max_connections'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}
r_lock(){	#当前等待行锁释放数
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_current_waits'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}
t_lock(){	#当前等待表锁释放数
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Table_locks_waited'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}
l_wait_avg(){	#平均每次锁等待时间
	$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_time_avg'" $db_host 2>/dev/null|awk 'NR>1{print $2}'
}



b_sent(){	#发送吞吐量
	touch /tmp/data_sent
	old=`cat /tmp/data_sent`
	if [ $old = 0 ] ;then
		old=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Bytes_sent'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	fi
	new=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Bytes_sent'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	echo $new > /tmp/data_sent
	echo $new $old |awk '{printf("%.2f\n", $1-$2)}'
}
b_received(){	#接收吞吐量
	touch /tmp/data_revice
        old=`cat /tmp/data_revice`
        if [ $old = 0 ] ;then
                old=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Bytes_received'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
        fi
        new=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Bytes_received'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
        echo $new > /tmp/data_revice
        echo $new $old |awk '{printf("%.2f\n", $1-$2)}'

}
b_wait(){	#缓冲池写入时需要等待的次数
	touch /tmp/buff_wait
	old=`cat /tmp/buff_wait`
	if [ $old = 0 ] ;then
		old=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_wait_free'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	fi
	new=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_wait_free'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	echo $new > /tmp/buff_wait
	echo $new $old |awk '{printf("%.2f\n", $1-$2)}'	
}
l_wait(){	#锁等待个数
	touch /tmp/lock_wait
	old=`cat /tmp/lock_wait`
	if [ $old = 0 ] ;then
		old=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_waits'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	fi
	new=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_waits'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	echo $new > /tmp/lock_wait
	echo $new $old |awk '{printf("%.2f\n", $1-$2)}'
}
b_total(){	#总吞吐量
	sent=`b_sent`
	recive=`b_received`
	echo $sent $recive |awk '{printf("%.2f\n", $1+$2)}'
}
q_cache_hit(){		#查询缓存命中率
	hit=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Qcache_hits'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	miss=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Qcache_inserts'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	echo $hit $miss |awk '{printf("%.2f\n", $1/($1+$2)*100)}'
}
i_buffer_use(){		#缓冲池的使用率
	btotal=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_pages_total'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	bfree=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_pages_free'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	echo $btotal $bfree |awk '{printf("%.2f\n", ($1-$2)/$1*100)}'
}
i_buffer_request(){	#可直接从缓冲池中读取数据的比例(内存的高速读取)
	mem_read=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_read_requests'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	disk_read=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_reads'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	other_read=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_read_ahead'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	echo $mem_read $disk_read $other_read|awk '{printf("%.2f\n", $1/($1+$2+$3)*100)}'
}
k_miss_rate(){		#myisam引擎的高速读取未命中比率
	disk_read=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='Key_reads'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	db_uptime=`$db_sql "SHOW GLOBAL STATUS WHERE variable_name='uptime'" $db_host 2>/dev/null|awk 'NR>1{print $2}'`
	echo $disk_read $db_uptime|awk '{printf("%.2f\n", ($1/$2)*100)}'
}

var=$(echo `grep '()' $0|egrep -v 'grep|#'|awk -F'(' '{print $1}'`)
for i in $var ;do
	if [[ $i == $1 ]];then
		$i && num=1 && break
		
	else
		num=0
	fi
done
if [[ $num == '0' ]];then
	echo " 例: $0 `echo $var|tr -t ' ' '|'`"
fi