python
bitscn.com #原shell版view code 1 #!/bin/bash 2 3 # script name: mysql_status_check.sh 4 # description: check mysql servers status 5 # author: xinggang wang - opseye.com 6 # create date: 2012/3/30 7 8 #获取mysql所在服务器ip/端口/用户名/密码 9 read -p host= host 10 read -p port= port 11 read -p user= user 12 read -sp password= password 13 echo 14 15 #默认为127.0.0.1/3306/root 16 if [ ${host} = ] 17 then 18 host='127.0.0.1' 19 fi 20 21 if [ ${port} = ] 22 then 23 port='3306' 24 fi 25 26 if [ ${user} = ] 27 then 28 user='root' 29 fi 30 31 #注意密码为空的时候的格式 32 mysql_list= 33 $host:$port:$user:$password 34 35 #计算函数，提高脚本效率 36 compute(){ 37 formula=$1 38 awk 'begin{printf(%.2f,'$formula')}' 2>/dev/null && 39 echo $value || echo null 40 } 41 42 for mysql in $mysql_list 43 { 44 host=${mysql%%:*} 45 port=$(echo $mysql|awk -f: '{print $2}') 46 user=$(echo $mysql|awk -f: '{print $3}') 47 passwd=${mysql##*:} 48 49 [ -z $passwd ] && mysql=mysql -h$host -p$port -u$user || 50 mysql=mysql -h$host -p$port -u$user -p$passwd 51 52 unset uptime 53 # 把show global status的值赋给相应的参数名称（这里相当于大量的变量赋值操作） 54 eval $( $mysql -e show global status | awk '{print $1=/x27$2/047}') 55 [ x = x$uptime ] && continue 56 57 # mysql ver 58 ver=`$mysql -estatus;|grep 'server version'|awk '{print $3}'` 59 60 # uptime 61 uptime=`compute $uptime/3600/24` 62 63 # threads_connected 64 threads_connected=`compute $threads_connected` 65 66 # qps questions/uptime 67 qps=`compute $questions/$uptime` 68 69 # tps (com_commit + com_rollback)/uptime 70 tps=`compute ($com_commit+$com_rollback)/$uptime` 71 72 # reads com_select + qcache_hits 73 reads=`compute $com_select+$qcache_hits` 74 75 # writes com_insert + com_update + com_delete + com_replace 76 writes=`compute $com_insert+$com_update+$com_delete+$com_replace` 77 78 # read/writes ratio reads/writes*100% 79 rwratio=`compute $reads/$writes*100`% 80 81 # myisam key_buffer_read_hits (1 - key_reads/key_read_requests) * 100 82 key_buffer_read_hits=`compute (1-$key_reads/$key_read_requests)*100`% 83 84 # myisam key_buffer_write_hits (1 - key_writes/key_write_requests) * 100 85 key_buffer_write_hits=`compute (1-$key_writes/$key_write_requests)*100`% 86 87 # query_cache_hits (qcache_hits / (qcache_hits + qcache_inserts)) * 100% 88 query_cache_hits=`compute $qcache_hits/($qcache_hits+$qcache_inserts)*100`% 89 90 # innodb_buffer_read_hits (1 - innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100 91 innodb_buffer_read_hits=`compute (1-$innodb_buffer_pool_reads/$innodb_buffer_pool_read_requests)*100`% 92 93 # thread_cache_hits (1 - threads_created / connections) * 100% 94 thread_cache_hits=`compute (1-$threads_created/$connections)*100`% 95 96 # slow_queries_per_second slow_queries / uptime * 60 97 slow_queries_per_second=`compute $slow_queries/$uptime` 98 99 # select_full_join_per_second select_full_join / uptime * 60 100 select_full_join_per_second=`compute $select_full_join/$uptime*60` 101 102 # select_full_join_in_all_select (select_full_join / com_select) * 100 103 select_full_join_in_all_select=`compute ($select_full_join/$com_select)*100`% 104 105 # myisam lock contention (table_locks_waited / table_locks_immediate) * 100 106 myisam_lock_contention=`compute ($table_locks_waited/$table_locks_immediate)*100`% 107 108 # temp_tables_to_disk (created_tmp_disk_tables / created_tmp_tables) * 100 109 temp_tables_to_disk_ratio=`compute ($created_tmp_disk_tables/$created_tmp_tables)*100`% 110 111 # print formated mysql status report 112 title=******************** mysql--${host}--${port} *********************** 113 width=$((`echo $title|wc -c`-1)) 114 115 echo $title 116 117 export ifs=':' 118 while read name value ;do 119 printf %36s :/t%10s/n $name $value 120 done 99%):$key_buffer_read_hits 130 myisam key buffer write hits:$key_buffer_write_hits 131 query cache hits:$query_cache_hits 132 innodb buffer read hits(>95%):$innodb_buffer_read_hits 133 thread cache hits(>90%):$thread_cache_hits 134 slow queries per second:$slow_queries_per_second 135 select full join per second:$select_full_join_per_second 136 select full join in all select:$select_full_join_in_all_select 137 myisam lock contention(#python版view code 1 #!/usr/bin/env python 2 3 #-*- coding: utf-8 -*- 4 5 # script name: mysql_status_check.py 6 7 # description: check mysql servers status 8 9 # author: bruce.zuo 10 11 # create date: 2012/06/05 12 13 import os,sys 14 15 import mysqldb 16 17 import getpass 18 19 20 21 host=raw_input(host:) 22 23 user=raw_input(user:) 24 25 password=getpass.getpass() 26 27 28 29 try: 30 31 conn = mysqldb.connect(host = host, user=user ,passwd = password, db = 'test') 32 33 except mysqldb.error,e: 34 35 print error %d:%s%(e.args[0],e.args[1]) 36 37 exit(1) 38 39 cursor=conn.cursor() 40 41 42 43 cursor.execute('show global status;') 44 45 result_set=cursor.fetchall() 46 47 cursor.close() 48 49 conn.close() 50 51 52 53 def get_value(key_name): 54 55 for rows in result_set: 56 57 if rows[0]==key_name: 58 59 return float(rows[1]) 60 61 62 63 print ('mysql-'+host+'-3306').center(60,'*') 64 65 print 'uptime:'.rjust(40),get_value('uptime') 66 67 print 'threads_connected:'.rjust(40),get_value('threads_connected') 68 69 print 'qps:'.rjust(40),round(get_value('questions') / get_value('uptime'),2) 70 71 print 'tps:'.rjust(40),round(get_value('com_commit')+get_value('com_rollback') / get_value('uptime'),2) 72 73 reads=get_value('com_select')+ get_value('qcache_hits') 74 75 writes=get_value('com_insert')+get_value('com_update')+get_value('com_delete')+get_value('com_replace') 76 77 print 'reads:'.rjust(40),get_value('com_select')+ get_value('qcache_hits') 78 79 print 'writes:'.rjust(40),get_value('com_insert')+get_value('com_update')+get_value('com_delete')+get_value('com_replace') 80 81 print 'read/writes ratio:'.rjust(40),round(reads / writes,2),'%' 82 83 print 'myisam key buffer read hits(>99%):'.rjust(40),round(1-get_value('key_reads') / (get_value('key_read_requests')*100),2),'%' 84 85 print 'myisam key buffer write hits:'.rjust(40),round(1-get_value('key_writes') / (get_value('key_write_requests')*100),2),'%' 86 87 print 'query cache hits:'.rjust(40),round(get_value('qcache_hits') / (get_value('qcache_hits')+get_value('qcache_inserts'))*100,2),'%' 88 89 print 'innodb buffer read hits(>95%):'.rjust(40),round(1-get_value('innodb_buffer_pool_reads') / (get_value('innodb_buffer_pool_read_requests')*100),2),'%' 90 91 print 'thread cache hits(>90%):'.rjust(40),round(1-get_value('threads_created') / (get_value('connections')*100),2),'%' 92 93 print 'slow queries per second:'.rjust(40),round(get_value('slow_queries') / get_value('uptime'),2) 94 95 print 'select full join per second:'.rjust(40),round(get_value('select_full_join') / get_value('uptime'),2) 96 97 print 'select full join in all select:'.rjust(40),round(get_value('select_full_join') / (get_value('com_select')*100),2),'%' 98 99 print 'myisam lock contention(#根据这个方法，可以添加更多的状态项。
#效果图
bitscn.com