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 <
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

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

效果图：