MySQL, mystat, 监控, 脚本

#!/usr/bin/perl -w
#############################################
# Create: P.Linux
# Function: Check MySQL Status
# Usage: Run on any computer
# Modify:
# P.Linux 2010-01-22
# Create 0.1 Alpha
# P.Linux 2010-01-26
# Update 0.2 Alpha
# Add color
# Add stat Monitor
# P.Linux 2010-01-27
# Update 0.3 Alpha
# Move all traffic infromation to one area
# Add a simple warning function using color
#############################################
use strict;
use DBI;
use Curses;
use Getopt::Std;
use Term::ANSIColor;
use vars qw($opt_i $opt_c $opt_n);
#############################################

# Catch ctrl+c Quit
$SIG{TERM}=$SIG{INT} = \&quit;

# Set env var from shell profile
set_env();

# Autoflush for print
$| = 1;

# Global System Var
my $USERNAME='';
my $PASSWORD='';
my $HOSTNAME='localhost';

# Global Status Var
my $now;
my $status_res;
my @status;

# CmdLine Option vars
my($interval, $count, $name);

# Version
my $version='0.3Alpha';

#############################################
# Main Program
#############################################

# Get CmdLine Options
&get_option();

# Connect to database as root via DBI
my $dbconn;

eval{
local $SIG{ALRM} = sub { die "连接数据库超时\n" };
alarm 20;
$dbconn = DBI->connect("DBI:mysql:host=$HOSTNAME", $USERNAME, $PASSWORD, {'RaiseError' => 1}) or die "Connect to MySQL database error:". DBI->errstr;
alarm 0;
};

if($@){
print "Connect to MySQL database error:".$@."\n";
exit;
}

# Do loop
&do_loop();

# Disconnect from MySQL
$dbconn->disconnect;

###########################################
## Print Usage
###########################################
sub print_usage () {
print <<EOF
NAME:
mystat

SYNTAX:
mystat -i interval -c count -n statname

FUNCTION:
Report Status Information of MySQL

PARAMETER:
-i interval interval time,default 1 seconds
-c count times
-n name statistics name

EOF
}

#########################################################
## Get Options
#########################################################
sub get_option(){
my $rtn = getopts('i:c:n:');
unless ( "$rtn" eq "1" ) { print_usage(); exit 1;}

$interval=$opt_i?$opt_i:1;
$count=$opt_c?$opt_c+1:0;
$name=$opt_n?$opt_n:'nothing';

if($interval !~ /[0-9]/ || $count !~ /[0-9]/) { print_usage(); exit 1;}
if(($ARGV[0] && $ARGV[0] !~ /[0-9]/) || ($ARGV[1] && $ARGV[1] !~ /[0-9]/)) { print_usage(); exit 1;}

if($ARGV[0]){
$interval=$ARGV[0];
}
if($ARGV[1]){
$count=$ARGV[1]+1;
}
if($ARGV[2]){
$name=$ARGV[2];
}

$name=lc($name);
}

#############################################
## Main Loop to get MySQL Status
#############################################
sub do_loop{
$now = 0;
init();
if($count){
for(my $c=0;$c<$count;$c++){
refresh_all();
sleep $interval;
}
}
else{ # if $count == 0 then loop time is unlimit
for(my $c=0;;$c++){
refresh_all();
sleep $interval;
}
}
}

#########################################################
## Set env from profile
#########################################################
sub set_env {
my $user='plx';
chomp($user);
my $profile="/home/".$user."/.profile";
if (! -e $profile ){
$profile="/home/".$user."/.bash_profile"
}
open(NEWENV, ". $profile && env|");
while (<NEWENV>){
if (/(\w+)=(.*)/){
$ENV{$1}="$2";
}
}
close NEWENV;
}

#######################################################
## Catch Ctrl+C
#######################################################
sub quit {
print "\nExit...\n";
$dbconn->disconnect;
exit 1;
}

#######################################################
## Initialization
#######################################################
sub init {
get_stat();
$status_res->{'Max_Bytes_traffic'} = 0;
$status_res->{'Max_Bytes_received'} = 0;
$status_res->{'Max_Bytes_sent'} = 0;
$status_res->{'Max_Sort_rows'} = 0;
$status_res->{'Max_Sort_times'} = 0;
$status_res->{'Max_Sort_load'} = 0;
}

#######################################################
##### Print Value
#######################################################
sub print_val {
my ($val)=$_[0];
my ($fmt)=$_[1];
my $ret = $val/1024/1024/1024 < 1
? $val/1024/1024 < 1
? printf("$fmt K", $val/1024)
: printf("$fmt M", $val/1024/1024)
: printf("$fmt G", $val/1024/1024/1024);
}

#######################################################
#### Print Same Char
#######################################################
sub print_char {
my ($ch)=$_[0];
my ($cnt)=$_[1];
for(my $c=0; $c<$cnt; $c++) {
print "$ch";
}
}

#######################################################
### Print Header
#######################################################
sub print_header {
print color("red");
#print "+";print_char('-',75);print "+\n";
print "+";print_char('-',29);print "mystat Ver ".$version;print_char('-',29);print "+\n";
print "+";print_char('-',27);print "Powered by PengLiXun.NET";print_char('-',26);print "+\n";
#print "+";print_char('-',75);print "+\n";
print color("reset");
}

#######################################################
### Display Version & Hostname & Uptime
#######################################################
sub display_var_title {
# Display Version & Hostname
my $ver = $status_res->{'version'};
print "|--MySQL $ver";
printf "%25s", "@ ".$status_res->{'hostname'}." (".$status_res->{'version_compile_machine'}.")";
# Display Uptime
my($sec,$min,$hour,$day) = gmtime($status_res->{'Uptime'});
$day = $day-1;
printf " Uptime:%3sd%3sh%3sm%3ss", $day, $hour, $min, $sec;
printf "---%2ss--|\n",$interval;
}

#######################################################
### Display Cache
#######################################################
sub display_var_cache {
my $query = $status_res->{'query_cache_size'};
my $thd = $status_res->{'thread_cache_size'};
my $tbl = defined($status_res->{'table_open_cache'})
? $status_res->{'table_open_cache'}
: $status_res->{'table_cache'};
#print color("green");
print "\t|Query Cache: ";
print_val($query, "%3s");
printf " | Thread Cache: %5s", $thd;
printf " | Table Cache: %5s|\n", $tbl;
#print color("reset");
}

#######################################################
### Display Buffer
#######################################################
sub display_var_buffer {
my $key = $status_res->{'key_buffer_size'};
my $join = $status_res->{'join_buffer_size'};
my $sort = $status_res->{'sort_buffer_size'};
print "\t|Key Buffer: ";
print_val($key, "%4s");
print " | Sort Buffer: ";
print_val($sort, "%4s");
print " | Join Buffer: ";
print_val($join, "%3s");
print "|\n";
}

#######################################################
### Display Log Status
#######################################################
sub display_var_log {
my $g_log = $status_res->{'log'};
my $b_log = $status_res->{'log_bin'};
my $s_log = $status_res->{'log_slow_queries'};
printf "\t|General Log: %5s", $g_log;
printf " | Bin Log: %10s", $b_log;
printf " | Slow Log: %8s|\n", $s_log;
}

#######################################################
### Display Connections
#######################################################
sub display_var_conn {
my $max_conn = $status_res->{'max_connections'};
my $max_used = $status_res->{'Max_used_connections'};
my $act_conn = $status_res->{'Threads_connected'};
my $used_rate = $max_used/$max_conn*100;
my $now_rate = $act_conn/$max_conn*100;
printf "\t|Act User:%4s(%2.0f%%)", $act_conn, $now_rate;
if ($used_rate>80) {
printf " | ";
printf color("red");
printf "Max Used:%5s(%2.0f%%)", $max_used, $used_rate;
printf color("reset");
printf color("green");
} else {
printf " | Max Used:%5s(%2.0f%%)", $max_used, $used_rate;
}
printf " | Max Connect:%6s|\n", $max_conn;
}

#######################################################
### Display Query
#######################################################
sub display_var_query {
my $select = $status_res->{"Com_select"};
my $insert = $status_res->{"Com_insert"};
my $update = $status_res->{"Com_update"};
my $delete = $status_res->{"Com_delete"};
my $sql = $select+$insert+$update+$delete;
my $select_rate = $select/$sql*100;
my $insert_rate = $insert/$sql*100;
my $update_rate = $update/$sql*100;
my $delete_rate = $delete/$sql*100;
printf "\t|SELECT:%5.2f%%", $select_rate;
printf " | INSERT:%5.2f%%", $insert_rate;
printf " | UPDATE:%5.2f%%", $update_rate;
printf " | DELETE:%5.2f%%|\n", $delete_rate;
}

#######################################################
##### Print Variables
#######################################################
sub print_vars {
print color("blue");
display_var_title();
print color("reset");
print color("green");
display_var_cache();
display_var_buffer();
display_var_log();
display_var_conn();
display_var_query();
print color("reset");
}

#######################################################
### Display Traffic Information
#######################################################
sub display_stat_traffic {
my $now_received = $status_res->{'Now_Bytes_received'};
my $now_sent = $status_res->{'Now_Bytes_sent'};
my $now_traffic = $status_res->{'Now_Bytes_traffic'};
my $max_received = $status_res->{'Max_Bytes_received'};
my $max_sent = $status_res->{'Max_Bytes_sent'};
my $max_traffic = $status_res->{'Max_Bytes_traffic'};
my $all_received = $status_res->{'Bytes_received'};
my $all_sent = $status_res->{'Bytes_sent'};
my $all_traffic = $status_res->{'Bytes_traffic'};

print color("blue");
print "+";print_char('-',2);
print "Network Traffic";print_char('-',60);print "+\n";
print color("reset");

print_char(' ',2);
print "Now Traf:";
print_val($now_traffic, "%9.2f");
print "B/s";
print " | ";
print "Now Recv:";
print_val($now_received, "%9.2f");
print "B/s";
print " | ";
print "Now Sent:";
print_val($now_sent, "%9.2f");
print "B/s";
print "\n";

print_char(' ',2);
print "Max Traf:";
print_val($max_traffic, "%9.2f");
print "B/s";
print " | ";
print "Max Recv:";
print_val($max_received, "%9.2f");
print "B/s";
print " | ";
print "Max Sent:";
print_val($max_sent, "%9.2f");
print "B/s";
print "\n";

print_char(' ',2);
print "All Traf:";
print_val($all_traffic, "%11.4f");
print "B";
print " | ";
print "All Recv:";
print_val($all_received, "%11.4f");
print "B";
print " | ";
print "All Sent:";
print_val($all_sent, "%11.4f");
print "B";
print "\n";
}

#######################################################
### Display Key Buffer
#######################################################
sub display_stat_key {
my $key_buffer = $status_res->{'key_buffer_size'};
my $key_blocks_used = $status_res->{'Key_blocks_used'};
my $key_blocks_unused = $status_res->{'Key_blocks_unused'};
my $key_used_rate =$status_res->{'Key_used_rate'};
my $key_free_rate = $status_res->{'Key_free_rate'};
my $key_used = $status_res->{'Key_used'};
my $key_free = $status_res->{'Key_free'};

my $key_read_requests = $status_res->{'Key_read_requests'};
my $key_reads = $status_res->{'Key_reads'};
my $key_read_hit_rate =$status_res->{'Key_read_hit_rate'};

my $key_write_requests = $status_res->{'Key_write_requests'};
my $key_writes = $status_res->{'Key_writes'};
my $key_write_hit_rate = $status_res->{'Key_write_hit_rate'};

my $key_avg_hit_rate = $status_res->{'Key_avg_hit_rate'};

print color("blue");
print "+";
print_char('-',2);
print "Key Buffer";
print_char('-',65);print "+\n";
print color("reset");

print_char(' ',2);
printf "Buffer Used:";
print_val($key_used, "%10.2f");
printf "B (%6.2f%%)", $key_used_rate;
printf " | ";
printf "Buffer Free:";
print_val($key_free, "%10.2f");
printf "B (%6.2f%%)", $key_free_rate;
printf "\n";

print_char(' ',2);
printf "Avg Hit:%13.2f %%", $key_avg_hit_rate;
printf " | ";
printf "Read Hit:%12.2f %%", $key_read_hit_rate;
printf " | ";
printf "Write Hit:%11.2f %%\n", $key_write_hit_rate;
}
#######################################################
### Display Query Cache
#######################################################
sub display_stat_qcache {
my $query_cache_size = $status_res->{'query_cache_size'};
my $qcache_free_memory = $status_res->{'Qcache_free_memory'};
my $qcache_free_blocks = $status_res->{'Qcache_free_blocks'};
my $qcache_hits = $status_res->{'Qcache_hits'};
my $qcache_inserts = $status_res->{'Qcache_inserts'};
my $qcache_lowmem_prunes = $status_res->{'Qcache_lowmem_prunes'};
my $qcache_not_cached = $status_res->{'Qcache_not_cached'};
my $qcache_queries_in_cache = $status_res->{'Qcache_queries_in_cache'};
my $qcache_total_blocks = $status_res->{'Qcache_total_blocks'};
my $qcache_frag_rate = $status_res->{'Qcache_frag_rate'};
my $qcache_used_rate = $status_res->{'Qcache_used_rate'};
my $qcache_hit_rate = $status_res->{'Qcache_hit_rate'};

print color("blue");
print "+";print_char('-',2);
print "Query Cache";print_char('-',64);print "+\n";
print color("reset");

print_char(' ',2);
printf "Qcache Used: %8.2f %%", $qcache_used_rate;
printf " | ";
printf "Qcache Hit: %9.2f %%", $qcache_hit_rate;
printf " | ";
printf "Fragmentation: %6.2f %%", $qcache_frag_rate;
printf "\n";
print_char(' ',2);
printf "Query in Cache:%8s", $qcache_queries_in_cache;
printf " | ";
printf "Query Low Mem Prunes:%10s", $qcache_lowmem_prunes;
printf "\n";
}

#######################################################
### Display Sort
#######################################################
sub display_stat_sort {
my $now_sort_rows = $status_res->{'Now_Sort_rows'};
my $now_sort_times = $status_res->{'Now_Sort_times'};
my $now_sort_load = $status_res->{'Now_Sort_load'};
my $now_sort_range = $status_res->{'Now_Sort_range'};
my $now_sort_scan = $status_res->{'Now_Sort_scan'};
my $now_sort_merge_passes = $status_res->{'Now_Sort_merge_passes'};
my $max_sort_rows = $status_res->{'Max_Sort_rows'};
my $max_sort_times = $status_res->{'Max_Sort_times'};
my $max_sort_load = $status_res->{'Max_Sort_load'};


print color("blue");
print "+";
print_char('-',2);
print "Sort";
print_char('-',71);
print "+\n";
print color("reset");

print_char(' ',2);
printf "Now Rows:%7.0f Rows/s", $now_sort_rows;
printf " | ";
printf "Now Times:%5.0f Times/s", $now_sort_times;
printf " | ";
printf "Now Load:%4.0f Rows/Time", $now_sort_load;
printf "\n";

print_char(' ',2);
printf "Max Rows:%7.0f Rows/s", $max_sort_rows;
printf " | ";
printf "Max Times:%5.0f Times/s", $max_sort_times;
printf " | ";
printf "Max Load:%4.0f Rows/Time", $max_sort_load;
printf "\n";

print_char(' ',2);
printf "Scan: %9.0f Times/s", $now_sort_scan;
printf " | ";
printf "Range: %8.0f Times/s", $now_sort_range;
printf " | ";
printf "Merge: %8.0f Times/s", $now_sort_merge_passes;
printf "\n";
}

#######################################################
#### Print Status
#######################################################
sub print_stat {
display_stat_traffic();
display_stat_key();
display_stat_qcache();
display_stat_sort();
}

#######################################################
##### Get MySQL Variables & Status
#######################################################
sub get_stat {
# Get MySQL Version
my $sql = "SELECT version();";
my $ver = $dbconn->selectrow_arrayref($sql);
$status_res->{"version"} = $ver->[0];

# Get MySQL Variables
$sql = "SHOW GLOBAL VARIABLES;";
my $vars = $dbconn->selectall_arrayref($sql);
foreach my $row(@$vars){
$status_res->{"$row->[0]"} = $row->[1];
}

# Get MySQL Status
$sql="SHOW GLOBAL STATUS;";
my $stat=$dbconn->selectall_arrayref($sql);
foreach my $row(@$stat){
$status[$now]->{"$row->[0]"} = $row->[1];
$status_res->{"$row->[0]"} = $row->[1];
}
}

#######################################################
####### Calc Result Status
#######################################################
sub calc_stat {
# Calc Traffic
$status_res->{'Bytes_traffic'} = $status_res->{'Bytes_received'} + $status_res->{'Bytes_sent'};

$status_res->{'Now_Bytes_received'} =
($status[$now]->{'Bytes_received'} - $status[1-$now]->{'Bytes_received'})/$interval;
$status_res->{'Now_Bytes_sent'} =
($status[$now]->{'Bytes_sent'} - $status[1-$now]->{'Bytes_sent'})/$interval;
$status_res->{'Now_Bytes_traffic'} =
($status_res->{'Now_Bytes_received'} + $status_res->{'Now_Bytes_sent'})/$interval;

if ($status_res->{'Max_Bytes_traffic'} < $status_res->{'Now_Bytes_traffic'}) {
$status_res->{'Max_Bytes_traffic'} = $status_res->{'Now_Bytes_traffic'};
}
if ($status_res->{'Max_Bytes_received'} < $status_res->{'Now_Bytes_received'}) {
$status_res->{'Max_Bytes_received'} = $status_res->{'Now_Bytes_received'};
}
if ($status_res->{'Max_Bytes_sent'} < $status_res->{'Now_Bytes_sent'}) {
$status_res->{'Max_Bytes_sent'} = $status_res->{'Now_Bytes_sent'};
}

$status_res->{'Qcache_frag_rate'} =
$status_res->{'Qcache_total_blocks'}
? $status_res->{'Qcache_free_blocks'}/$status_res->{'Qcache_total_blocks'}*100
: 0;
$status_res->{'Qcache_used_rate'} =
$status_res->{'query_cache_size'}
? ($status_res->{'query_cache_size'}-$status_res->{'Qcache_free_memory'})/$status_res->{'query_cache_size'}*100
: 0;
$status_res->{'Qcache_hit_rate'} =
$status_res->{'Qcache_hits'}
? $status_res->{'Qcache_hits'}/$status_res->{'Qcache_inserts'}*100
: 0;

# Calc Key Buffer
$status_res->{'Key_used_rate'} =
($status_res->{'Key_blocks_used'}/($status_res->{'Key_blocks_unused'}+$status_res->{'Key_blocks_used'}))*100;
$status_res->{'Key_free_rate'} = 100 - $status_res->{'Key_used_rate'};
$status_res->{'Key_used'} = $status_res->{'Key_used_rate'}/100*$status_res->{'key_buffer_size'};
$status_res->{'Key_free'} = $status_res->{'Key_free_rate'}/100*$status_res->{'key_buffer_size'};

$status_res->{'Key_write_hit_rate'} =
(1 - $status_res->{'Key_writes'}/ $status_res->{'Key_write_requests'})*100;
$status_res->{'Key_read_hit_rate'} =
(1 - $status_res->{'Key_reads'}/ $status_res->{'Key_read_requests'})*100;
$status_res->{'Key_avg_hit_rate'} =
($status_res->{'Key_write_hit_rate'}+$status_res->{'Key_read_hit_rate'})/2;

# Calc Sort
$status_res->{'Now_Sort_rows'} =
($status[$now]->{'Sort_rows'} - $status[1-$now]->{'Sort_rows'})/$interval;
$status_res->{'Now_Sort_range'} =
($status[$now]->{'Sort_range'} - $status[1-$now]->{'Sort_range'})/$interval;
$status_res->{'Now_Sort_scan'} =
($status[$now]->{'Sort_scan'} - $status[1-$now]->{'Sort_scan'})/$interval;
$status_res->{'Now_Sort_merge_passes'} =
($status[$now]->{'Sort_merge_passes'} - $status[1-$now]->{'Sort_merge_passes'})/$interval;
$status_res->{'Now_Sort_times'} =
$status_res->{'Now_Sort_range'}+$status_res->{'Now_Sort_scan'}+$status_res->{'Now_Sort_merge_passes'};
$status_res->{'Now_Sort_load'} =
$status_res->{'Now_Sort_times'}
? $status_res->{'Now_Sort_rows'}/$status_res->{'Now_Sort_times'}
: 0;

if ($status_res->{'Max_Sort_times'} < $status_res->{'Now_Sort_times'}) {
$status_res->{'Max_Sort_times'} = $status_res->{'Now_Sort_times'};
}
if ($status_res->{'Max_Sort_rows'} < $status_res->{'Now_Sort_rows'}) {
$status_res->{'Max_Sort_rows'} = $status_res->{'Now_Sort_rows'};
}
if ($status_res->{'Max_Sort_load'} < $status_res->{'Now_Sort_load'}) {
$status_res->{'Max_Sort_load'} = $status_res->{'Now_Sort_load'};
}
}

#######################################################
###### Refresh All Status
#######################################################
sub refresh_all {
my ($cnt)=@_;
$now = 1-$now;
get_stat();
calc_stat();
system "clear";
print_header();
print_vars();
print_stat();
}