python检测数据库变化_MYSQL 数据库状态检查脚本(Python版）

1 #!/bin/bash2

3 # Script Name: mysql_status_check.sh

4 # Description: check mysql servers status5 # Author: Xinggang Wang -OpsEye.com6 # Create Date: 2012/3/30

7

8 #获取MySQL所在服务器IP/端口/用户名/密码9 read -p "Host="HOST10 read -p "Port="PORT11 read -p "User="USER12 read -sp "Password="PASSWORD13 echo

14

15 #默认为127.0.0.1/3306/root16 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:$PASSWORD34 "

35 #计算函数，提高脚本效率36 compute(){37 formula="$1"

38 awk 'BEGIN{printf("%.2f",'$formula')}' 2>/dev/null &&

39 echo $value || echoNULL40 }41

42 for mysql in$mysql_list43 {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 Uptime53 # 把show global status的值赋给相应的参数名称(这里相当于大量的变量赋值操作)54 eval $( $mysql -e "show global status" | awk '{print $1"=\x27"$2"\047"}')55 [ X = X"$Uptime" ] &&continue56

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

60 # Uptime61 UPTIME=`compute "$Uptime/3600/24"`62

63 # Threads_connected64 threads_connected=`compute "$Threads_connected"`65

66 # QPS Questions/Uptime67 qps=`compute "$Questions/$Uptime"`68

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

72 # Reads Com_select +Qcache_hits73 reads=`compute "$Com_select+$Qcache_hits"`74

75 # Writes Com_insert + Com_update + Com_delete +Com_replace76 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 report112 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 $value120 done <99%):$key_buffer_read_hits130 MyISAM Key buffer writehits:$key_buffer_write_hits131 Query cache hits:$query_cache_hits132 InnoDB buffer read hits(>95%):$innodb_buffer_read_hits133 Thread cache hits(>90%):$thread_cache_hits134 Slow queries per second:$slow_queries_per_second135 Select full joinper second:$select_full_join_per_second136 Select full join in all select:$select_full_join_in_all_select137 MyiSAM lock contention(<1%):$myisam_lock_contention138 Temp tables to disk ratio:$temp_tables_to_disk_ratio139 EOF140

141 unset IFS142

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

144 }145

146 exit 0