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

#原shell版

#!/bin/bash 

# Script Name: mysql_status_check.sh 
# Description: check mysql servers status 
# Author: Xinggang Wang - OpsEye.com 
# Create Date: 2012/3/30 

#获取MySQL所在服务器IP/端口/用户名/密码 
read -p "Host=" HOST 
read -p "Port=" PORT 
read -p "User=" USER 
read -sp "Password=" PASSWORD 
echo 

#默认为127.0.0.1/3306/root 
if [ "${HOST}" = "" ] 
then 
HOST='127.0.0.1' 
fi 

if [ "${PORT}" = "" ] 
then 
PORT='3306' 
fi 

if [ "${USER}" = "" ] 
then 
USER='root' 
fi 

#注意密码为空的时候的格式 
mysql_list=" 
$HOST:$PORT:$USER:$PASSWORD 
" 
#计算函数，提高脚本效率 
compute(){ 
formula="$1" 
awk 'BEGIN{printf("%.2f",'$formula')}' 2>/dev/null && 
echo $value || echo NULL 
} 

for mysql in $mysql_list 
{ 
host=${mysql%%:*} 
port=$(echo $mysql|awk -F: '{print $2}') 
user=$(echo $mysql|awk -F: '{print $3}') 
passwd=${mysql##*:} 

[ -z "$passwd" ] && mysql="mysql -h$host -P$port -u$user" || 
mysql="mysql -h$host -P$port -u$user -p$passwd" 

unset Uptime 
# 把show global status的值赋给相应的参数名称（这里相当于大量的变量赋值操作） 
eval $( $mysql -e "show global status" | awk '{print $1"=\x27"$2"\047"}') 
[ X = X"$Uptime" ] && continue 

# Mysql VER 
VER=`$mysql -e"status;"|grep 'Server version'|awk '{print $3}'` 

# Uptime 
UPTIME=`compute "$Uptime/3600/24"` 

# Threads_connected 
threads_connected=`compute "$Threads_connected"` 

# QPS Questions/Uptime 
qps=`compute "$Questions/$Uptime"` 

# TPS (Com_commit + Com_rollback)/Uptime 
tps=`compute "($Com_commit+$Com_rollback)/$Uptime"` 

# Reads Com_select + Qcache_hits 
reads=`compute "$Com_select+$Qcache_hits"` 

# Writes Com_insert + Com_update + Com_delete + Com_replace 
writes=`compute "$Com_insert+$Com_update+$Com_delete+$Com_replace"` 

# Read/Writes Ratio reads/writes*100% 
rwratio=`compute "$reads/$writes*100"`% 

# MyISAM Key_buffer_read_hits (1 - Key_reads/Key_read_requests) * 100 
key_buffer_read_hits=`compute "(1-$Key_reads/$Key_read_requests)*100"`% 

# MyISAM Key_buffer_write_hits (1 - Key_writes/Key_write_requests) * 100 
key_buffer_write_hits=`compute "(1-$Key_writes/$Key_write_requests)*100"`% 

# Query_cache_hits (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100% 
query_cache_hits=`compute "$Qcache_hits/($Qcache_hits+$Qcache_inserts)*100"`% 

# Innodb_buffer_read_hits (1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100 
innodb_buffer_read_hits=`compute "(1-$Innodb_buffer_pool_reads/$Innodb_buffer_pool_read_requests)*100"`% 

# Thread_cache_hits (1 - Threads_created / Connections) * 100% 
thread_cache_hits=`compute "(1-$Threads_created/$Connections)*100"`% 

# Slow_queries_per_second Slow_queries / Uptime * 60 
slow_queries_per_second=`compute "$Slow_queries/$Uptime"` 

# Select_full_join_per_second Select_full_join / Uptime * 60 
select_full_join_per_second=`compute "$Select_full_join/$Uptime*60"` 

# select_full_join_in_all_select (Select_full_join / Com_select) * 100 
select_full_join_in_all_select=`compute "($Select_full_join/$Com_select)*100"`% 

# MyISAM Lock Contention (Table_locks_waited / Table_locks_immediate) * 100 
myisam_lock_contention=`compute "($Table_locks_waited/$Table_locks_immediate)*100"`% 

# Temp_tables_to_disk (Created_tmp_disk_tables / Created_tmp_tables) * 100 
temp_tables_to_disk_ratio=`compute "($Created_tmp_disk_tables/$Created_tmp_tables)*100"`% 

# print formated MySQL status report 
title="******************** MySQL--${HOST}--${PORT} ***********************" 
width=$((`echo "$title"|wc -c`-1)) 

echo "$title" 

export IFS=':' 
while read name value ;do 
printf "%36s :\t%10s\n" $name $value 
done <<EOF 
Mysql Ver:$VER 
Uptime:$UPTIME days 
Threads connected:$threads_connected 
QPS:$qps 
TPS:$tps 
Reads:$reads 
Writes:$writes 
Read/Writes Ratio:$rwratio 
MyISAM Key buffer read hits(>99%):$key_buffer_read_hits 
MyISAM Key buffer write hits:$key_buffer_write_hits 
Query cache hits:$query_cache_hits 
InnoDB buffer read hits(>95%):$innodb_buffer_read_hits 
Thread cache hits(>90%):$thread_cache_hits 
Slow queries per second:$slow_queries_per_second 
Select full join per second:$select_full_join_per_second 
Select full join in all select:$select_full_join_in_all_select 
MyiSAM lock contention(<1%):$myisam_lock_contention 
Temp tables to disk ratio:$temp_tables_to_disk_ratio 
EOF 

unset IFS 

for i in `seq $width`;{ echo -n "*";};echo 
} 

exit 0

#Python版

#!/usr/bin/env python

#-*- coding: utf-8 -*-

# Script Name: mysql_status_check.py

# Description: check mysql servers status

# Author: Bruce.Zuo 

# Create Date: 2012/06/05

import os,sys

import MySQLdb

import getpass



host=raw_input("host:")

user=raw_input("user:")

password=getpass.getpass()



try:

   conn = MySQLdb.connect(host = host, user=user ,passwd = password, db = 'test')

except MySQLdb.ERROR,e:

   print "Error %d:%s"%(e.args[0],e.args[1])

   exit(1)

cursor=conn.cursor()



cursor.execute('show global status;')

result_set=cursor.fetchall()

cursor.close()

conn.close()



def get_value(key_name):

        for rows in result_set:

                if rows[0]==key_name:

                        return float(rows[1])



print ('MySQL-'+host+'-3306').center(60,'*')

print 'Uptime:'.rjust(40),get_value('Uptime')

print 'Threads_connected:'.rjust(40),get_value('Threads_connected')

print 'QPS:'.rjust(40),round(get_value('Questions') / get_value('Uptime'),2)

print 'TPS:'.rjust(40),round(get_value('Com_commit')+get_value('Com_rollback') / get_value('Uptime'),2)

reads=get_value('Com_select')+ get_value('Qcache_hits')

writes=get_value('Com_insert')+get_value('Com_update')+get_value('Com_delete')+get_value('Com_replace')

print 'Reads:'.rjust(40),get_value('Com_select')+ get_value('Qcache_hits')

print 'Writes:'.rjust(40),get_value('Com_insert')+get_value('Com_update')+get_value('Com_delete')+get_value('Com_replace')

print 'Read/Writes Ratio:'.rjust(40),round(reads / writes,2),'%'

print 'MyISAM Key buffer read hits(>99%):'.rjust(40),round(1-get_value('Key_reads') / (get_value('Key_read_requests')*100),2),'%'

print 'MyISAM Key buffer write hits:'.rjust(40),round(1-get_value('Key_writes') / (get_value('Key_write_requests')*100),2),'%'

print 'Query cache hits:'.rjust(40),round(get_value('Qcache_hits') / (get_value('Qcache_hits')+get_value('Qcache_inserts'))*100,2),'%'

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),'%'

print 'Thread cache hits(>90%):'.rjust(40),round(1-get_value('Threads_created') / (get_value('Connections')*100),2),'%'

print 'Slow queries per second:'.rjust(40),round(get_value('Slow_queries') / get_value('Uptime'),2)

print 'Select full join per second:'.rjust(40),round(get_value('Select_full_join') / get_value('Uptime'),2)

print 'Select full join in all select:'.rjust(40),round(get_value('Select_full_join') / (get_value('Com_select')*100),2),'%'

print 'MyiSAM lock contention(<1%):'.rjust(40),round(get_value('Table_locks_waited') / (get_value('Table_locks_immediate')*100),2),'%'

print 'Temp tables to disk ratio:'.rjust(40),round(get_value('Created_tmp_disk_tables') / (get_value('Created_tmp_tables')*100),2),'%'

print '*'*60

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

#效果图