MYSQL 数据库状态检查脚本(Python版）

#原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 -e"status;"|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 <<EOF 
121 Mysql Ver:$VER 
122 Uptime:$UPTIME days 
123 Threads connected:$threads_connected 
124 QPS:$qps 
125 TPS:$tps 
126 Reads:$reads 
127 Writes:$writes 
128 Read/Writes Ratio:$rwratio 
129 MyISAM Key buffer read hits(>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(<1%):$myisam_lock_contention 
138 Temp tables to disk ratio:$temp_tables_to_disk_ratio 
139 EOF 
140 
141 unset IFS 
142 
143 for i in `seq $width`;{ echo -n "*";};echo 
144 } 
145 
146 exit 0

#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(<1%):'.rjust(40),round(get_value('Table_locks_waited') / (get_value('Table_locks_immediate')*100),2),'%'
100 
101 print 'Temp tables to disk ratio:'.rjust(40),round(get_value('Created_tmp_disk_tables') / (get_value('Created_tmp_tables')*100),2),'%'
102 
103 print '*'*60

#根据这个方法，可以添加更多的状态项。

#效果图

转载于:https://www.cnblogs.com/zuoxingyu/archive/2012/09/26/2703887.html