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();
}