Home >Database >Mysql Tutorial >MYSQL 数据库状态检查脚本(Python版)_MySQL

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

WBOY
WBOYOriginal
2016-06-01 13:48:121120browse

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 -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 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版<img id="code_img_closed_547cb232-79f2-4f40-9763-d38b744e9424" class="code_img_closed" alt="" src="http://img.bitscn.com/upimg/allimg/c140719/1405L9330U940-33564.jpg"><img onclick="cnblogs_code_hide('547cb232-79f2-4f40-9763-d38b744e9424',event)" id="code_img_opened_547cb232-79f2-4f40-9763-d38b744e9424" class="code_img_opened"   style="max-width:90%" alt="" src="http://img.bitscn.com/upimg/allimg/c140719/1405L9331093P-423U.jpg">View Code
<pre class="brush:php;toolbar:false">  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(
<p>#根据这个方法,可以添加更多的状态项。</p>
<p>#效果图</p>
<p><img alt="" src="http://img.bitscn.com/upimg/allimg/c140719/1405L933135940-593L.jpg"></p>
bitsCN.com
    
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn